Oracle Data Guard on 12.2 CDB with Oracle Restart

This post lists the steps for setting up an Oracle data guard on 12.2 with Oracle restart. Both primary and standby uses ASM for data file storage. The primary database is a CDB with two PDBs. The primary and standby Oracle restart has been setup with role separation. There's a similar post for setting up data guard on 11.2 with Oracle restart.
Below are few of the new features introduced in 12.2.0.1, some of which is relevant to setting up DG with CDB and PDB.

As of Oracle Database 12c Release 2 (12.2.0.1) password file changes done on a primary database are automatically propagated to standby databases. The only exception to this is far sync instances.
A new INSTANCES [ ALL | integer] clause is available on the SQL ALTER RECOVER MANAGED STANDBY DATABASE command which enables you to control the number of instances on a physical standby that Redo Apply uses.
You can now detect lost writes and also inconsistencies between a primary database and physical standby databases by using the new PL/SQL procedure, DBMS_DBCOMP.DBCOMP.
The new ENABLED_PDBS_ON_STANDBY initialization parameter enables you to specify a subset of pluggable databases (PDBs) for replication on a physical standby of a multitenant container database (CDB). In releases prior to Oracle Database 12c Release 2 (12.2.0.1), you had to specify either all PDBs or none.
As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby connected, without disruption, during the switchover/failover. To enable this feature, set the STANDBY_DB_PRESERVE_STATES initialization parameter in your init.ora file before the standby instance is started.
This parameter applies to physical standby databases only. The allowed values are:
• NONE — No sessions on the standby are retained during a switchover/failover. This is the default value.
• ALL — User sessions are retained during switchover/failover.
• SESSION — User sessions are retained during switchover/failover.
You can now use the Oracle Diagnostic Pack with an Oracle Active Data Guard standby database that is open read-only.

The table below list the relevant names used in the DG setup.

Item	On Primary	On Standby
Host name	city7	city7s
Database Name	prodcdb	stbycdb
Diskgroup Names	DATA FRA	DATA FRA
TNS Entry Name	PRODCDBTNS	STBYTNS

1. Create the relevant *dump directories in the standby server

cd $ORACLE_BASE/admin
mkdir stbycdb
cd stbycdb
mkdir adump  dpdump  hdump  pfile

2.If not done already, put the primary database into archive log mode.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

3. Enable force logging on the primary database

alter database force logging;

4. Create standby log files for each thread on primary db. These should be same size as the online redo log. There should be at least one more redo log group per thread than the online redo log groups.

alter database add standby logfile thread 1 size 209715200;

5. Add static listener entries on standby and primary $GI_HOME's listener.ora file. This must be done as grid user. On primary add the following static listener entry to listener.ora

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = prodcdb)
                (SID_NAME = prodcdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
)

On standby

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = stbycdb)
                (SID_NAME = stbycdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
)

stop and start the listener and verify service for static listener entry is shown (below only standby entry is shown).

srvctl stop listener
srvctl start listener

lsnrctl status

Service "stbycdb" has 1 instance(s).
  Instance "stbycdb", status UNKNOWN, has 1 handler(s) for this service...

6. As oracle user create tns entries in $ORACLE_HOME/network/admin/tnsnames.ora. The listeners on standby and primary runs on non-default port of 1581. On primary server's oracle home create a TNS entry pointing to the standby instance

STBYTNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = city7s)(PORT = 1581))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stbycdb)
    )
  )

On standby's tnsnames.ora file add an entry pointing to the primary instance

PRODCDBTNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = city7)(PORT = 1581))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prodcdb)
    )
  )

7. Create a backup pfile from the primary spfile

create pfile='/home/oracle/pfile.ora' from spfile;

8. Modify the initialization parameters on the primary db. Remote archive dest state is set to defer, enable it before running the standby duplication. LGWR with ASYNC and NOAFFIRM is used as the redo transport this should be changed to suite the desired protection mode.

alter system set log_archive_config='dg_config=(prodcdb,stbycdb)' scope=both ;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prodcdb' scope=both;
alter system set log_archive_dest_2='service=STBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stbycdb' scope=both;
alter system set log_archive_dest_state_2='defer' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set fal_server='STBYTNS' scope=both;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/stbycdb/','/prodcdb/' scope=spfile;
alter system set log_file_name_convert='/stbycdb/','/prodcdb/' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;

Stop and start the database for changes to take effect. The initialization parameter enabled_PDBs_on_standby which is related to PDBs is left at the default value. Which means all PDBs on primary will be created on all the standbys.

SQL> show parameter enabled_PDBs_on_standby

NAME                     TYPE        VALUE
------------------------ ----------- ------
enabled_PDBs_on_standby  string      *

This only sets the minimum parameters needed for data guard setup. Optionally other data guard and performance related parameters could be set at this time or once the data guard setup is completed. One of the common parameters in this category is the lost write protection. In this setup, this parameter was set once the data guard setup was complete.

 alter system set db_lost_write_protect='typical' scope=both;

9. Copy password to standby and rename with the instance name

scp $ORACLE_HOME/dbs/orapwprodcdb city7s:$ORACLE_HOME/dbs/orapwstbycdb

10. Create pfile on standby with db_name parameter only and start the standby instance in nomount mode.

export ORACLE_SID=stbycdb
cat initstbycdb.ora
db_name=stbycdb

SQL> startup nomount;

11. Enable the remote log archive destination on primary (this was set to defer when settign the initialization parameters on step 7).

alter system set log_archive_dest_state_2='enable' SCOPE=both sid='*';

12. Unlike previous post, this DG creation does not use active data database option to create the standby DB. (At the end of the post, commands to create standby DB using active data base option is shown). Instead it uses backups from primary to create the standby database (similar to duplicating with backups). Below are the necessary RMAN commands to create the required backup files for standby duplication.

backup spfile format '/home/oracle/backup/spbackup.bkp';
backup current controlfile for standby format '/home/oracle/backup/stdbycontro.ctl';
backup database format '/home/oracle/backup/dbbackup%U' plus archivelog format '/home/oracle/backup/archbackup%U' delete all input;

switch few log files and backup the archive logs

sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
backup archivelog all format '/home/oracle/backup/archbkp%U' delete all input;

13. Copy backup files to standby host. Backups are on a different location on standby than primary.

cd /home/oracle/backup
scp * city7s:/home/oracle/backup_dir

14. Login to standby server and connect to the standby instance using an auxiliary connection and run duplicate database for standby command. Since the command is run from standby omit the target keyword (duplicating while connected to primary is shown at the end of the post). Since ASM disk group names are same on both primary and standby lot of the db_*_dest* parameters could be kept same as primary. If not these would require file name conversions with disk groups prefixed as shown in previous post.

rman auxiliary sys/prodcdb
  
duplicate database for standby
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
BACKUP LOCATION '/home/oracle/backup_dir' dorecover nofilenamecheck;

Following output shows the full duplication process.

rman auxiliary sys/prodcdbdb

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Feb 16 14:23:14 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: STBYCDB (not mounted)

RMAN> duplicate database for standby
2> spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
3> 4> set db_name='prodcdb'
5> set db_unique_name='stbycdb'
6> set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
7> 8> set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
9> 10> 11> set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
12> 13> BACKUP LOCATION '/home/oracle/backup_dir' dorecover nofilenamecheck;

Starting Duplicate Db at 16-FEB-18

contents of Memory Script:
{
   restore clone spfile to  '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora' from
 '/home/oracle/backup_dir/spbackup.bkp';
   sql clone "alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora''";
}
executing Memory Script

Starting restore at 16-FEB-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup_dir/spbackup.bkp
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-FEB-18

sql statement: alter system set spfile= ''/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest =
 ''/opt/app/oracle/admin/stbycdb/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''+DATA/STBYCDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBYCDB/CONTROLFILE/current.256.965841019'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=stbycdbXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_name =
 ''prodcdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''stbycdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/prodcdb/'', ''/stbycdb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/prodcdb/'', ''/stbycdb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''PRODCDBTNS'' comment=
 '''' scope=spfile";
   sql clone "alter system reset  local_listener scope=spfile";
   sql clone "alter system set  log_archive_dest_2 =
 ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/opt/app/oracle/admin/stbycdb/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/STBYCDB/CONTROLFILE/current.266.965841019'', ''+FRA/STBYCDB/CONTROLFILE/current.256.965841019'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbycdbXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''prodcdb'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stbycdb'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/prodcdb/'', ''/stbycdb/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/prodcdb/'', ''/stbycdb/'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  10 comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''PRODCDBTNS'' comment= '''' scope=spfile

sql statement: alter system reset  local_listener scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1191182336 bytes

Fixed Size                     8792104 bytes
Variable Size                452986840 bytes
Database Buffers             721420288 bytes
Redo Buffers                   7983104 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/STBYCDB/CONTROLFILE/current.263.968250233'', ''+FRA/STBYCDB/CONTROLFILE/current.263.968250233'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone standby controlfile from  '/home/oracle/backup_dir/stdbycontro.ctl';
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/STBYCDB/CONTROLFILE/current.263.968250233'', ''+FRA/STBYCDB/CONTROLFILE/current.263.968250233'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 16-FEB-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/STBYCDB/CONTROLFILE/current.262.968250233
output file name=+FRA/STBYCDB/CONTROLFILE/current.264.968250235
Finished restore at 16-FEB-18

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  1495617;
   set newname for tempfile  1 to
 "+DATA";
   set newname for tempfile  2 to
 "+DATA";
   set newname for tempfile  3 to
 "+DATA";
   set newname for tempfile  4 to
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA";
   set newname for datafile  2 to
 "+DATA";
   set newname for datafile  3 to
 "+DATA";
   set newname for datafile  4 to
 "+DATA";
   set newname for datafile  5 to
 "+DATA";
   set newname for datafile  6 to
 "+DATA";
   set newname for datafile  7 to
 "+DATA";
   set newname for datafile  11 to
 "+DATA";
   set newname for datafile  12 to
 "+DATA";
   set newname for datafile  13 to
 "+DATA";
   set newname for datafile  14 to
 "+DATA";
   set newname for datafile  15 to
 "+DATA";
   set newname for datafile  16 to
 "+DATA";
   set newname for datafile  17 to
 "+DATA";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file
renamed tempfile 4 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-FEB-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2isrce37_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2isrce37_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2ksrce3e_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2ksrce3e_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2lsrce3h_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2lsrce3h_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00015 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/dbbackup2jsrce3a_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/dbbackup2jsrce3a_1_1 tag=TAG20180216T122822
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 16-FEB-18

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=968250346 file name=+DATA/STBYCDB/DATAFILE/system.261.968250253
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=968250346 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.271.968250291
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=968250346 file name=+DATA/STBYCDB/DATAFILE/sysaux.267.968250255
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=968250346 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.270.968250291
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=968250347 file name=+DATA/STBYCDB/DATAFILE/undotbs1.268.968250255
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=968250347 file name=+DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.272.968250291
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=968250347 file name=+DATA/STBYCDB/DATAFILE/users.269.968250255
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/system.274.968250315
datafile 12 switched to datafile copy
input datafile copy RECID=23 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/sysaux.273.968250315
datafile 13 switched to datafile copy
input datafile copy RECID=24 STAMP=968250347 file name=+DATA/STBYCDB/6360B564DAA26345E0535500A8C04976/DATAFILE/undotbs1.275.968250315
datafile 14 switched to datafile copy
input datafile copy RECID=25 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/system.277.968250331
datafile 15 switched to datafile copy
input datafile copy RECID=26 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/sysaux.276.968250331
datafile 16 switched to datafile copy
input datafile copy RECID=27 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/undotbs1.278.968250331
datafile 17 switched to datafile copy
input datafile copy RECID=28 STAMP=968250347 file name=+DATA/STBYCDB/6360CF1837BF7EBBE0535500A8C0DE5B/DATAFILE/users.279.968250331

contents of Memory Script:
{
   set until scn  1495617;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 16-FEB-18
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbackup2hsrce34_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbackup2hsrce34_1_1 tag=TAG20180216T122820
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_42.265.968250369 thread=1 sequence=0
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_42.265.968250369 RECID=1 STAMP=968250369
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=43
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbackup2msrce3l_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbackup2msrce3l_1_1 tag=TAG20180216T122837
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_43.265.968250371 thread=1 sequence=43
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_43.265.968250371 RECID=2 STAMP=968250371
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=44
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=45
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=46
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=47
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=48
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup_dir/archbkp2osrce4b_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup_dir/archbkp2osrce4b_1_1 tag=TAG20180216T122859
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_44.265.968250373 thread=1 sequence=44
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_44.265.968250373 RECID=4 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_45.266.968250373 thread=1 sequence=45
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_45.266.968250373 RECID=5 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_46.269.968250373 thread=1 sequence=46
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_46.269.968250373 RECID=3 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_47.268.968250373 thread=1 sequence=47
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_47.268.968250373 RECID=7 STAMP=968250373
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_48.267.968250373 thread=1 sequence=48
channel clone_default: deleting archived log(s)
archived log file name=+FRA/STBYCDB/ARCHIVELOG/2018_02_16/thread_1_seq_48.267.968250373 RECID=6 STAMP=968250373
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-FEB-18

Finished Duplicate Db at 16-FEB-18

15. Once the duplication for standby is completed start the log apply process on standby. Monitor the standby instance's alert log for MRP starting and query the archived log view for log apply progress.

alter database recover managed standby database using current logfile disconnect;

select sequence#,applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
       273 YES
       274 YES
       275 YES
       276 YES
       277 YES
       278 YES
       279 YES
       280 IN-MEMORY

16. Final step is to bring the database under the control of oracle restart. To do this add the database to the oracle restart configuration with srvctl add database. This must be run as oracle user not as grid user. Running the command as grid user would fail as oracle home is owned by a different user

[grid@city7s ~]$ srvctl add database -db stbycdb -oraclehome /opt/app/oracle/product/12.2.0/dbhome_1  -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"
PRCD-1025 : Failed to create database stbycdb
PRKH-1014 : Current user "grid" is not the oracle owner user "oracle" of oracle home "/opt/app/oracle/product/12.2.0/dbhome_1"

As oracle user run

$GI_HOME/bin/srvctl add database -db stbycdb -oraclehome $ORACLE_HOME  -spfile "/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora" -role physical_standby -startoption mount -diskgroup "DATA,FRA"

Verify the database configuration

srvctl config database -db stbycdb
Database unique name: stbycdb
Database name:
Oracle home: /opt/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: /opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfilestbycdb.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group:
OSOPER group:
Database instance: stbycdb

At this stage the oracle binary in standby oracle home will not have the correct ownership with regard to when role separation is used.

[oracle@city7s bin]$ cd $ORACLE_HOME/bin
[oracle@city7s bin]$ ls -l oracle
-rwsr-s--x. 1 oracle oinstall 408115488 Jan 19 16:27 oracle

This could lead to the issue highlighted here. In fact if the standby database is to be started without correcting the permission it will fail as shown below

srvctl  start database -db stbycdb
PRCR-1079 : Failed to start resource ora.stbycdb.db
CRS-5017: The resource action "ora.stbycdb.db start" encountered the following error:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1001 (oinstall), current egid = 1005 (asmadmin)
. For details refer to "(:CLSN00107:)" in "/opt/app/oracle/diag/crs/city7s/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.stbycdb.db' on 'city7s' failed

Interestingly after the failure first time the permission get applied automatically to the oracle binary (without any manual intervention). So, if attempted the standby database start will succeed second time around.
This conclude the setting up of DG with CDB.

Standby DB creation with target database
In this case the location to which backup files are copied to in step 13 above must match the backup file location on primary. (i.e. if on primary backup files are on /home/oracle/backup then on standby also it must be on /home/oracle/backup).
While in primary server, connect to both target and standby (with an auxiliary connection) and run the duplicate command

rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stbytns
run {
duplicate target database for standby
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb'
dorecover nofilenamecheck;
}

Standby DB creation with active database
With active database option there's no need to create any backups. Connect to both target and standby and run the duplicate command with active database.

rman target sys/prodcdbdb auxiliary sys/prodcdbdb@stbytns
duplicate target database for standby from active database
spfile
parameter_value_convert 'prodcdb','stbycdb','PRODCDB','STBYCDB'
set db_name='prodcdb'
set db_unique_name='stbycdb'
set db_file_name_convert='/prodcdb/','/stbycdb/'
set log_file_name_convert='/prodcdb/','/stbycdb/'
set log_archive_max_processes='10'
set fal_server='PRODCDBTNS'
reset local_listener
set log_archive_dest_2='service=PRODCDBTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=prodcdb'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stbycdb';

If the active data guard license is available then standby database could be open for read only access. To do this cancel the managed recovery, open the CDB root in read only mode and then the PDBs.

SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter pluggable database all open read only;
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBAPP1                        READ ONLY  NO
         4 PDBAPP2                        READ ONLY  NO

Related Posts
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
11gR2 Standalone Data Guard (with ASM and Role Separation)
Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync
Enable Active Dataguard on 11gR2 RAC Standby