Create 12c Standby

Basic note:
Creating a Physical Standby from Primary on Version 12c (Doc ID 1570958.1)
Please follow that note to create a standby. The following are additional notes when following the steps in the note.


Our example servers:
primsrva,primsrvb: primary, running dbcp12, 2-node RAC
stbysrv: standby, will have DB dbcp12 as db_name, dbcp12sb as db_unique_name and SID
(SID can be the same as db_name or db_unique_name. Suggestion: keep the same as db_unique_name unless standby is RAC)


Add
dbcp12sb:/u01/app/oracle/product/12.2.0/db:N
to /etc/oratab so you can run . oraenv


Creating additional directories on the standby server
mkdir -p /u01/app/oracle/admin/dbcp12sb/adump
mkdir -p /u01/app/oracle/diag/rdbms/dbcp12sb/dbcp12sb/cdump


Create $ORACLE_HOME/dbs/initdbcp12sb.ora
db_name=dbcp12 <-- must be set
db_unique_name=dbcp12sb <-- may be optional
cluster_database=false  <-- if standby is not RAC; may be optional

Create password (specify format=12 to avoid OPW-00029):
orapwd file=orapwdbcp12sb password=xxxxxx format=12


sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/db/dbs/initdbcp12sb.ora'


Add entry to listener.ora in <GI ORACLE_HOME>/network/admin:
Note it's GI ORACLE_HOME even though the line ORACLE_HOME points to DB ORACLE_HOME.
GLOBAL_DBNAME may be needed as well. Also note we use SID not service_name.
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = dbcp12sb)
     (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
     (SID = dbcp12sb)
   )
  )


Add entry to all nodes of primary in file <DB ORACLE_HOME>/network/admin/tnsnames.ora:
DBCP12SB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbysrv)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = dbcp12sb)
    )
  )
Check by running on primary:
sqlplus sys/xxxxxx@dbcp12sb as sysdba
Should not see "Connected to an idle instance".


On primary, if <DB ORACLE_HOME>/dbs does not have spfiledbcp12.ora, create it by:
sqlplus / as sysdba
sho parameter spfile
create pfile='/tmp/init.ora' from spfile='+DATA/DBCP12/PARAMETERFILE/spfile.309.951559717';
create spfile='/u01/app/oracle/product/12.2.0/db/dbs/spfiledbcp121.ora' from pfile='/tmp/init.ora';


RMAN duplicate on primary:
Make sure to add to the duplicate command:
set cluster_database='false' <-- if standby is single node while primary is RAC
set db_name='dbcp12' <-- to avoid ORA-01103
(Note: The Oracle note wrote "set log_archive_config='dg_config=(boston,chicago)'" twice. Omit one.)
target must be sys, not /.

rman target sys/xxxxxx auxiliary sys/xxxxxx@dbcp12sb 
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel prim type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database spfile
parameter_value_convert 'dbcp12','dbcp12sb'
set db_file_name_convert='dbcp12','dbcp12sb'
set log_file_name_convert='dbcp12','dbcp12sb'
set log_archive_max_processes='10'
set db_unique_name='dbcp12sb'
set db_name='dbcp12'
set cluster_database='false'
set standby_file_management='AUTO'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dbcp12sb'
set log_Archive_dest_2='service=dbcp12 async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=dbcp12'
set log_archive_config='dg_config=(dbcp12,dbcp12sb)';
sql channel aux "alter database add standby logfile ''+FRA'' size 500M";
sql channel aux "alter database add standby logfile ''+FRA'' size 500M";
sql channel aux "alter database add standby logfile ''+FRA'' size 500M";
sql channel aux "alter database add standby logfile ''+FRA'' size 500M";
sql channel prim "alter system archive log current";
sql channel aux "alter database recover managed standby database disconnect";
}

If there's error not resolving the TNS name or invalid username/password, after checking anything obvious, try
* changing the single / to sys/password@<connect identifier>
* explicitly specifying nocatalog (even though it's default)
* making sure sys account is open.

Once done,
On primary:
alter system set log_archive_config='dg_config=(dbcp12,dbcp12sb)';
alter system set log_archive_dest_2='service=dbcp12sb async valid_for=(all_logfiles,primary_role) db_unique_name=dbcp12sb';
select * from v$log where status='CURRENT';
select * from v$archive_dest where dest_id=2;

On standby:
select status, thread#, sequence# from v$managed_standby where process='MRP0';
cd <directory where alert.log is>
tail -f alert_dbcp12sb.log

Switch logfile on primary and see the switch on standby


Add (additional) standby logfiles on both primary and standby
alter database add standby logfile ('+DATA','+FRA') size 500m;
...




On primary:
alter database force logging;
alter system set standby_file_management = auto;
There's no need to set *convert* or *fal* parameters.