Creating a PDB in a Data Guard Configuration

Oracle Data guard configuration used in this post is the 12.2 data guard configuration created earlier. The primary DB has enabled_PDBs_on_standby='*'. What needs to be done when creating a PDB in the primary DB of a data guard configuration depends whether standby is open with read only (active data guard) or whether the PDB was created with standbys=none or not. This post lists all three of these options.

    Creating a PDB when active data guard is in use
    Creating a PDB with standbys=none
    Creating a PDB with standbys=all (default) option and without active data guard

Creating a PDB when active data guard is in use
Oracle documentation states "to create a PDB as a local clone from a different PDB or from the seed PDB within the same primary CDB, copy the data files that belong to the source PDB over to the standby database. (This step is not necessary in an Oracle Active Data Guard environment because the data files are copied automatically at the standby when the PDB is created on the standby database.)". So when active data guard is in use (which require separation license options) PDB could be created same as in a non-data guard configuration. The PDB will be automatically created on standby without any manual intervention.
1. Standby is open for read only access and redo apply is on.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ ONLY

DGMGRL> show database stbycdb;

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON

2. Create PDB without any standby options and open it.

SQL> create pluggable database pdbuat admin user pdbuat identified by pdbuat default tablespace users;
Pluggable database created.

SQL> alter pluggable database pdbuat open;
Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDBUAT                         READ WRITE NO

3. The PDB will be created on the standby. The standby alert log will show data file being created for the PDB.

2018-03-14T15:24:55.972459+00:00
Recovery created pluggable database PDBUAT
*****************************************
WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/system.302.970753855'
         is an ASM fully qualified filename.
         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************
2018-03-14T15:25:00.621144+00:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/system.289.970759497 from +DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.281.968524885
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/system.302.970753855'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_56006.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/system.302.970753855'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):Datafile 134 added to flashback set
PDBUAT(4):Successfully added datafile 134 to media recovery
PDBUAT(4):Datafile #134: '+DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/system.289.970759497'
*****************************************
WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/sysaux.279.970753857'
         is an ASM fully qualified filename.
         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************
2018-03-14T15:25:09.173271+00:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/sysaux.306.970759501 from +DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/sysaux.305.968524855
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/sysaux.279.970753857'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_56006.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/sysaux.279.970753857'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):Datafile 135 added to flashback set
PDBUAT(4):Successfully added datafile 135 to media recovery
PDBUAT(4):Datafile #135: '+DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/sysaux.306.970759501'
*****************************************
WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/undotbs1.301.970753855'
         is an ASM fully qualified filename.
         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************
2018-03-14T15:25:13.544410+00:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file +DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/undotbs1.274.970759509 from +DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/undotbs1.300.968524893
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/undotbs1.301.970753855'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_56006.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):*****************************************
PDBUAT(4):WARNING: The converted filename '+DATA/stbycdb/6760aa08f64a2dd6e0535500a8c02c77/datafile/undotbs1.301.970753855'
PDBUAT(4):         is an ASM fully qualified filename.
PDBUAT(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
PDBUAT(4):         Please rename it accordingly.
PDBUAT(4):*****************************************
PDBUAT(4):Datafile 136 added to flashback set
PDBUAT(4):Successfully added datafile 136 to media recovery
PDBUAT(4):Datafile #136: '+DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/DATAFILE/undotbs1.274.970759509'

4. PDB on the standby could be open read only.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDBUAT                         MOUNTED

SQL> alter pluggable database pdbuat open read only;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDBUAT                         READ ONLY  NO

5. When the PDB is open in read only mode, the alert log on standby will output a warning about the missing tempfile. If needed add the temp file for the PDB on the standby

2018-03-14T15:26:53.544876+00:00
alter pluggable database pdbuat open read only
PDBUAT(4):Autotune of undo retention is turned on.
2018-03-14T15:26:53.963014+00:00
PDBUAT(4):Endian type of dictionary set to little
PDBUAT(4):Undo initialization finished serial:0 start:2259280636 end:2259280636 diff:0 ms (0.0 seconds)
2018-03-14T15:26:54.540760+00:00
PDBUAT(4):Database Characterset for PDBUAT is AL32UTF8
PDBUAT(4):*********************************************************************
PDBUAT(4):WARNING: The following temporary tablespaces in container(PDBUAT)
PDBUAT(4):         contain no files.
PDBUAT(4):         This condition can occur when a backup controlfile has
PDBUAT(4):         been restored.  It may be necessary to add files to these
PDBUAT(4):         tablespaces.  That can be done using the SQL statement:
PDBUAT(4):
PDBUAT(4):         ALTER TABLESPACE  ADD TEMPFILE
PDBUAT(4):
PDBUAT(4):         Alternatively, if these temporary tablespaces are no longer
PDBUAT(4):         needed, then they can be dropped.
PDBUAT(4):           Empty temporary tablespace: TEMP
PDBUAT(4):*********************************************************************
2018-03-14T15:26:56.374791+00:00
PDBUAT(4):Opening pdb with no Resource Manager plan active
Pluggable database PDBUAT opened read only
Completed: alter pluggable database pdbuat open read only

SQL> alter session set container=pdbuat;
Session altered.

SQL> select name from v$tempfile;
no rows selected

SQL> alter tablespace temp add tempfile;
Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/STBYCDB/6760AA08F64A2DD6E0535500A8C02C77/TEMPFILE/temp.278.970759733

Creating a PDB with standbys=none
1. 12c allows the option of creating a PDB on primary without creating the datafiles in the standby. In order to use this feature create the PDB with standbys=none option. This option is useful when standby cannot use the active data guard option.

SQL>  create pluggable database pdb2 admin user pdb2 identified by pdb2 standbys=none;
Pluggable database created.

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

show pdbs;
SQL>
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBDEV                         READ WRITE NO
         5 PDB2                           READ WRITE NO

2. The stadby CDB will list the PDB name but it will have recovery status disable and unnamed datafiles with recover status.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDBDEV                         MOUNTED
         5 PDB2                           MOUNTED

SQL> alter session set container=pdb2;
Session altered.

SQL> select name, recovery_status from v$pdbs;

NAME     RECOVERY
-------- --------
PDB2     DISABLED

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                         STATUS
---------- ------------------------------------------------------------ -------
       140 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00140     SYSOFF
       141 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00141     RECOVER
       142 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00142     RECOVER

The alert log on standby will have the following with regard to the datafiles

2018-03-14T16:50:21.769872+00:00
Recovery created pluggable database PDB2
PDB2(5):File #140 added to control file as 'UNNAMED00140'. Originally created as:
PDB2(5):'+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/system.293.970758983'
PDB2(5):because the pluggable database was created with nostandby
PDB2(5):or the tablespace belonging to the pluggable database is
PDB2(5):offline.
PDB2(5):File #141 added to control file as 'UNNAMED00141'. Originally created as:
PDB2(5):'+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/sysaux.294.970758983'
PDB2(5):because the pluggable database was created with nostandby
PDB2(5):or the tablespace belonging to the pluggable database is
PDB2(5):offline.
PDB2(5):File #142 added to control file as 'UNNAMED00142'. Originally created as:
PDB2(5):'+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/undotbs1.295.970758983'
PDB2(5):because the pluggable database was created with nostandby
PDB2(5):or the tablespace belonging to the pluggable database is
PDB2(5):offline.

3. To make the PDB part of the data guard by enabling recovery on it require copying the datafiles to the standby. The post list two methods of copying the datafiles. One using RMAN from primary and other using RMAN from Standby. Only need to do one of these methods to get the files copied.
  3.1 The first method of copying the datafile from primary to standby is by using an auxiliary channel to the standby. Connect to both primary and standby and issue a backup as copy on the pluggable database.

rman target / auxiliary sys/prodcdbdb@stbytns

connected to target database: PRODCDB (DBID=2963914998)
connected to auxiliary database: PRODCDB (DBID=2963914998, not open)

RMAN> backup as copy pluggable database pdb2 auxiliary format '+DATA';

Starting backup at 14-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00141 name=+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/sysaux.294.970758983
output file name=+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/sysaux.275.970764731 tag=TAG20180314T151811
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00140 name=+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/system.293.970758983
output file name=+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/system.292.970764747 tag=TAG20180314T151811
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00142 name=+DATA/PRODCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/undotbs1.295.970758983
output file name=+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/undotbs1.286.970764753 tag=TAG20180314T151811
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-MAR-18

Once the files are copied to the standby they must be added to the PDB on the standby. This too could be done different ways. Below are few methods (some of the outputs are from different PDB creations. So some of the file names, file numbers may not match the above copy command file name and numbers).
    3.1.1. First method is using switch to datafile copies. This could be done at PDB level or file level. As the first step catalog the copied datafiles on standby.

 rman target /

connected to target database: PRODCDB (DBID=2963914998, not open)

RMAN> catalog start with '+data/stbycdb';

Starting implicit crosscheck backup at 19-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 19-MAR-18

Starting implicit crosscheck copy at 19-MAR-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-MAR-18

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern +data/stbycdb

List of Files Unknown to the Database
=====================================
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/sysaux.297.971189621
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/system.281.971189637
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/undotbs1.287.971189643
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/users.266.971189659
File Name: +DATA/STBYCDB/CONTROLFILE/current.285.970934541

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/sysaux.297.971189621
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/system.281.971189637
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/undotbs1.287.971189643
File Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/users.266.971189659

List of Files Which Were Not Cataloged
=======================================
File Name: +DATA/STBYCDB/CONTROLFILE/current.285.970934541
  RMAN-07517: Reason: The file header is corrupted

RMAN> LIST DATAFILECOPY ALL;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
26      12   A 19-MAR-18       11731251   19-MAR-18       NO
        Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/system.281.971189637
        Tag: TAG20180319T131942

25      13   A 19-MAR-18       11731222   19-MAR-18       NO
        Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/sysaux.297.971189621
        Tag: TAG20180319T131942

27      14   A 19-MAR-18       11731268   19-MAR-18       NO
        Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/undotbs1.287.971189643
        Tag: TAG20180319T131942

28      15   A 19-MAR-18       11731297   19-MAR-18       NO
        Name: +DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/users.266.971189659
        Tag: TAG20180319T131942

      3.1.1.1. If PDB level copying is preferred switch the PDB to copy.

RMAN> switch pluggable database pdb2 to copy;

datafile 12 switched to datafile copy "+DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/system.281.971189637"
datafile 13 switched to datafile copy "+DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/sysaux.297.971189621"
datafile 14 switched to datafile copy "+DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/undotbs1.287.971189643"
datafile 15 switched to datafile copy "+DATA/STBYCDB/678982658F142261E0535500A8C05B68/DATAFILE/users.266.971189659"

      3.1.1.2. Or if invidual data file level switching is preferred then switch to individual datafile.

RMAN> list datafilecopy all;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
23      178  A 16-MAR-18       10526422   16-MAR-18       NO
        Name: +DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/system.278.970926583
        Tag: TAG20180316T121537

22      179  A 16-MAR-18       10526406   16-MAR-18       NO
        Name: +DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/sysaux.274.970926577
        Tag: TAG20180316T121537

24      180  A 16-MAR-18       10526436   16-MAR-18       NO
        Name: +DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/undotbs1.309.970926591
        Tag: TAG20180316T121537

25      181  A 16-MAR-18       10526450   16-MAR-18       NO
        Name: +DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/users.263.970926597
        Tag: TAG20180316T121537

RMAN> switch datafile 178 to copy;
datafile 178 switched to datafile copy "+DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/system.278.970926583"

RMAN> switch datafile 179 to copy;
datafile 179 switched to datafile copy "+DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/sysaux.274.970926577"

RMAN> switch datafile 180 to copy;
datafile 180 switched to datafile copy "+DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/undotbs1.309.970926591"

RMAN> switch datafile 181 to copy;
datafile 181 switched to datafile copy "+DATA/STBYCDB/678784B473516D01E0535500A8C0AAEE/DATAFILE/users.263.970926597"

When either 3.1.1.1 or 3.1.1.2 is done go to step 4.
  3.1.2. Second option is to rename the unamed file to copied file. This could require stopping redo apply and putting standby file management to manual.

DGMGRL> edit database stbycdb set state='APPLY-OFF';
Succeeded.

SQL> alter system set standby_file_management='manual';
System altered.

SQL> alter session set container=pdb2;
Session altered.

SQL> alter database rename file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00140' to '+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/system.292.970764747';
Database altered.

SQL> alter database rename file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00141' to '+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/sysaux.275.970764731';
Database altered.

SQL> alter database rename file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00142' to '+DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/undotbs1.286.970764753';
Database altered.


     FILE# NAME                                                                             STATUS
---------- -------------------------------------------------------------------------------- -------
       140 +DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/system.292.970764747     SYSOFF
       141 +DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/sysaux.275.970764731     RECOVER
       142 +DATA/STBYCDB/6761DB93F6D36101E0535500A8C00734/DATAFILE/undotbs1.286.970764753   RECOVER

alter system set standby_file_management='auto';

Once all the files are renamed, go to step 4.

3.2 The second method is restoring to the files onto the PDB on standby while connected to the standby CDB. This method uses FROM SERVICE caluse to get the datafile from primary.
  3.2.1. Disable redo apply on standby

DGMGRL> edit database stbycdb set state='apply-off';
Succeeded.

  3.2.2. From the standby CDB run the following RMAN commadns(PRODCDBTNSis a TNS entry to the primary CDB).

RMAN> run {
 set newname for pluggable database pdb2 to new;
 restore pluggable database pdb2 from service PRODCDBTNS;
 switch datafile all;
}

executing command: SET NEWNAME

Starting restore at 16-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRODCDBTNS
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00173 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRODCDBTNS
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00174 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRODCDBTNS
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00175 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRODCDBTNS
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00176 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-MAR-18

datafile 173 switched to datafile copy
input datafile copy RECID=18 STAMP=970925746 file name=+DATA/STBYCDB/67875857381C604EE0535500A8C01310/DATAFILE/system.274.970925743
datafile 174 switched to datafile copy
input datafile copy RECID=19 STAMP=970925755 file name=+DATA/STBYCDB/67875857381C604EE0535500A8C01310/DATAFILE/sysaux.278.970925751
datafile 175 switched to datafile copy
input datafile copy RECID=20 STAMP=970925758 file name=+DATA/STBYCDB/67875857381C604EE0535500A8C01310/DATAFILE/undotbs1.309.970925759
datafile 176 switched to datafile copy
input datafile copy RECID=21 STAMP=970925759 file name=+DATA/STBYCDB/67875857381C604EE0535500A8C01310/DATAFILE/users.263.970925759

Once RMAN commands complete follow step 4.

4. Once the datafiles are copied the next step is to enable recovery on the PDB. Stop redo apply if enabled and execute the recovery enable command on the PDB.

DGMGRL> edit database stbycdb set state='apply-off';
Succeeded.

SQL> alter session set container=pdb2;
Session altered.

SQL> select name, recovery_status from v$pdbs;

NAME     RECOVERY
-------- --------
PDB2     DISABLED

SQL> alter pluggable database enable recovery;
Pluggable database altered.

SQL> select name, recovery_status from v$pdbs;

NAME       RECOVERY
---------- --------
PDB2       ENABLED

DGMGRL> edit database stbycdb set state='APPLY-ON';
DGMGRL> show configuration

5. To add a temp file to the PDB, stop the redo apply and open the PDB in read only mode (stopping redo and opening PDB in read only mode doesn't require active data guard). Then add the temp file as before.



Creating a PDB with standbys=all (default) option and without active data guard
In this scenario the PDB is created with default standbys option, which will create the PDB in all the standbys, but the standby CDB is not using active data guard (running in mount mode). In this case once the PDB is created on the primary standby will encounter error and redo apply will stop for the standby CDB.
1. PDB created on primary

SQL> create pluggable database pdb1 admin user pdb1 identified by pdb1;
Pluggable database created.

SQL> alter pluggable database pdb1 open;
Pluggable database altered.

2. On standby alert log following could be seen. Note the stopping of MRP

2018-03-14T17:28:31.871065+00:00
Recovery created pluggable database PDB1
*****************************************
WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/system.293.970761273'
         is an ASM fully qualified filename.
         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_0.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************
2018-03-14T17:28:33.593037+00:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.292.970766911 from +DATA/STBYCDB/6325282AC695380EE0535500A8C0D89D/DATAFILE/system.281.968524885
PDB1(4):*****************************************
PDB1(4):WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/system.293.970761273'
PDB1(4):         is an ASM fully qualified filename.
PDB1(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_27685.4294967295.4294967295'.
PDB1(4):         Please rename it accordingly.
PDB1(4):*****************************************
PDB1(4):*****************************************
PDB1(4):WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/system.293.970761273'
PDB1(4):         is an ASM fully qualified filename.
PDB1(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_144.4294967295.4294967295'.
PDB1(4):         Please rename it accordingly.
PDB1(4):*****************************************
2018-03-14T17:28:33.720328+00:00
PDB1(4):Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_5655.trc:
ORA-01516: nonexistent log file, data file, or temporary file "+DATA/MUST_RENAME_THIS_DATAFILE_144.4294967295.4294967295" in the current container
PDB1(4):Recovery was unable to create the file as:
PDB1(4):'+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.292.970766911'
PDB1(4):*****************************************
PDB1(4):WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/system.293.970761273'
PDB1(4):         is an ASM fully qualified filename.
PDB1(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_144.4294967295.4294967295'.
PDB1(4):         Please rename it accordingly.
PDB1(4):*****************************************
MRP0: Background Media Recovery terminated with error 1274
2018-03-14T17:28:33.848228+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_5655.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/PRODCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.293.970761273'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 9544096 but controlfile could be ahead of datafiles.
2018-03-14T17:28:34.163336+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_5655.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/PRODCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.293.970761273'
2018-03-14T17:28:34.265287+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)
2018-03-14T17:28:34.896975+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_5889.trc:
ORA-01110: data file 130: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00130'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00130'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-14T17:28:35.631197+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_5889.trc:
ORA-01110: data file 131: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00131'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00131'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-14T17:28:36.057283+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_5889.trc:
ORA-01110: data file 132: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00132'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00132'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-14T17:28:36.654044+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_5889.trc:
ORA-01110: data file 133: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00133'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00133'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-14T17:28:37.161034+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_5889.trc:
ORA-01110: data file 144: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00144'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00144'

Check DG Broker status

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 5669 seconds ago)

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 minute 49 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

3. It was noticed several times that in this situation the PDB that's been created doens't appear on the standby CDB. However the PDB becomes visible after the restart of the standby CDB.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDBDEV                         MOUNTED

SQL> shutdown immediate;
SQL> startup mount;

show pdbs
SQL>
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDBDEV                         MOUNTED
         4 PDB1                           MOUNTED

Unlike the previous case the recovery status of the PDB will be enabled.

SQL> select name,recovery_status from v$pdbs;

NAME       RECOVERY
---------- --------
PDB1       ENABLED

4. Querying the datafiles for the PDB shows only one unnamed datafile.

SQL> alter session set container=pdb1;
Session altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
       144 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00144

5. Copy the datafile 144 from primary to standby

rman target / auxiliary sys/prodcdbdb@stbytns

connected to target database: PRODCDB (DBID=2963914998)
connected to auxiliary database: PRODCDB (DBID=2963914998, not open)

RMAN> backup as copy datafile 144 auxiliary format '+DATA';

Starting backup at 14-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00144 name=+DATA/PRODCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.293.970761273
output file name=+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465 tag=TAG20180314T160346
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-MAR-18

Alternatively could use use the FROM SERVICE clause to get the datafile from primary while connected to standby as target (output below is from a different PDB). This will eliminate the need for manual renaming and changing standby file management setting.

rman target /

connected to target database: PRODCDB (DBID=2963914998, not open)

run {
 set newname for datafile 30 to new;
 restore datafile 30 from service PRODCDBTNS;
 switch datafile 30;
 }

executing command: SET NEWNAME

Starting restore at 19-MAR-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRODCDBTNS
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00030 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-MAR-18

datafile 30 switched to datafile copy
input datafile copy RECID=47 STAMP=971197093 file name=+DATA/STBYCDB/67C6738768FC06BAE0535500A8C0138A/DATAFILE/sysaux.304.971197089

6. Once the file is copied to standby, rename the un-named datafiles (if files were copied using auxiliary channel)

SQL> alter system set standby_file_management='manual';
System altered.

SQL> alter session set container=pdb1;
Session altered.

SQL> alter database rename file  '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00144' to '+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465';
Database altered.

SQL>  alter system set standby_file_management='auto';
System altered.
    
DGMGRL>  edit database stbycdb set state='APPLY-ON';
Succeeded.

7. Starting of the redo apply on last step cause the next data file to be added to the PDB as an unnamed datafile and redo apply will stop with an error.

SQL> alter session set container=pdb1;
SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       144 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465
       145 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00145

PDB1(4):Recovery was unable to create the file as:
PDB1(4):'+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.286.970767625'
PDB1(4):*****************************************
PDB1(4):WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/sysaux.295.970761273'
PDB1(4):         is an ASM fully qualified filename.
PDB1(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_145.4294967295.4294967295'.
PDB1(4):         Please rename it accordingly.
PDB1(4):*****************************************
MRP0: Background Media Recovery terminated with error 1274

8. To resolve the issue copy the next datafile and rename the unnamed datafile.

RMAN> backup as copy datafile 145 auxiliary format '+DATA';

Starting backup at 14-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00145 name=+DATA/PRODCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.295.970761273
output file name=+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.282.970767731 tag=TAG20180314T160811
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-MAR-18

SQL> alter system set standby_file_management='manual';
System altered.

SQL> alter database rename file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00145' to '+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.282.970767731';
Database altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       144 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465
       145 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.282.970767731

SQL>  alter system set standby_file_management='auto';

DGMGRL>  edit database stbycdb set state='APPLY-ON';
Succeeded

9. Same as before starting redo apply will fail on the next datafile.

DGMGRL>  edit database stbycdb set state='APPLY-ON';    
PDB1(4):*****************************************
PDB1(4):WARNING: The converted filename '+DATA/stbycdb/67626414ddd22ec4e0535500a8c0cddd/datafile/undotbs1.294.970761273'
PDB1(4):         is an ASM fully qualified filename.
PDB1(4):         Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_146.4294967295.4294967295'.
PDB1(4):         Please rename it accordingly.
PDB1(4):*****************************************

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       144 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465
       145 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.282.970767731
       146 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00146

10. Copy all the files part of the PDB to standby as they error. This PDB only needed to copy 3 files.

RMAN> backup as copy datafile 146  auxiliary format '+DATA';

Starting backup at 14-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00146 name=+DATA/PRODCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/undotbs1.294.970761273
output file name=+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/undotbs1.277.970767877 tag=TAG20180314T161038
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-MAR-18

SQL> alter system set standby_file_management='manual';
System altered.

SQL> alter database rename file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00146' to '+DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/undotbs1.277.970767877';
Database altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       144 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/system.275.970767465
       145 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/sysaux.282.970767731
       146 +DATA/STBYCDB/67626414DDD22EC4E0535500A8C0CDDD/DATAFILE/undotbs1.277.970767877

11. Once all the files are copied the redo apply will continue without any errors. Since PDB already has the recovery status enable no other steps are necessary.

DGMGRL> edit database stbycdb set state='APPLY-ON';
Succeeded.
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5648 seconds ago)

Flashback Primary DB in a Data Guard - PDB vs non-CDB vs CDB

This post shows the steps of recovering the standby after a PDB is flashback on the primary DB. Later on shows the same on a non-CDB and CDB. It's assumed that standby DB has flashback on.

Flashback a PDB in the primary CDB
12.2 introduced new features which allows creation of restore points at PDB level and then flashback an individual PDB without impacting the other PDBs or the CDB. Flashback a PDB that's plugged into a CDB in a data guard configuration would cause recovery at the standby to fail and terminate. This is because the standby PDB would have a higher SCN compared to PDB on primary that underwent a flashback. Steps below shows how to recover from this situation.
1. The flashback is done using a restore point. As such first create the restore point inside the PDB. Steps are same even if the restore point is a CDB restore point (i.e. when a restore point created in CDB is used to flashback a PDB)

SQL>  alter session set container=pdbapp1;

SQL> create restore point pdb_restore_point guarantee flashback database;

SQL> select con_id,scn,TIME,name from v$restore_point;

    CON_ID        SCN TIME                           NAME
---------- ---------- ------------------------------ ------------------
         5    5662626 27-FEB-18 15.06.47.000000000   PDB_RESTORE_POINT

2. If a DB is a CDB then application data would reside in a PDB. So it's safe to assume that restore points would be used to recover from user errors at PBD level than CDB level. To simulate some error such as accidental deletion of data from a table. Restore point is used to recover from this error.

sqlplus  asanga/asa@city7:1581/pdbapp1

SQL> select count(*) from a;

  COUNT(*)
----------
       100

SQL> truncate table a;

Table truncated.

SQL> select count(*) from a;

  COUNT(*)
----------
         0

3. Close the PDB and execute the flashback. Once complete open the PDB with resetlog option

SQL> alter session set container=pdbapp1;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDBAPP1

SQL> alter pluggable database close;
Pluggable database altered.

SQL>  flashback pluggable database PDBAPP1 to restore point PDB_RESTORE_POINT;
Flashback complete.

SQL> alter pluggable database open resetlogs;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBAPP1                        READ WRITE NO

4. On the alert log of the primary DB following could be seen during the flashback.

PDBAPP1(5): flashback pluggable database PDBAPP1 to restore point PDB_RESTORE_POINT
2018-02-27T15:12:44.433139+00:00
PDBAPP1(5):Flashback Restore Start
PDBAPP1(5):Restore Flashback Pluggable Database PDBAPP1 (5) until change 5662627
PDBAPP1(5):Flashback Restore Complete
PDBAPP1(5):Flashback Media Recovery Start
2018-02-27T15:12:45.086123+00:00
PDBAPP1(5):Serial Media Recovery started
2018-02-27T15:12:45.233559+00:00
PDBAPP1(5):Recovery of Online Redo Log: Thread 1 Group 8 Seq 257 Reading mem 0
PDBAPP1(5):  Mem# 0: +DATA/PRODCDB/ONLINELOG/group_8.284.968237763
PDBAPP1(5):  Mem# 1: +FRA/PRODCDB/ONLINELOG/group_8.264.968237763
2018-02-27T15:12:45.245468+00:00
PDBAPP1(5):Incomplete Recovery applied until change 5662627 time 02/27/2018 15:06:47
PDBAPP1(5):Flashback Media Recovery Complete
PDBAPP1(5):Flashback Pluggable Database PDBAPP1 (5) recovered until change 5662627
PDBAPP1(5):Completed:  flashback pluggable database PDBAPP1 to restore point PDB_RESTORE_POINT

5. On the standby DB's alert log following could be seen. Note the SCN number mentioned on the standby alert log for the PDB is same as the SCN mentioned in primary. Another point to note is that, flashback of a PDB on primary doesn't affect other PDBs in primary but it impacts the entire standby as recovery is stopped. This means that flashback a single PDB on primary could stop recovery of the entire standby until standby PDB is flashback to a state where it can apply redo.

2018-02-27T16:47:45.999901+00:00
(5):Recovery of pluggable database PDBAPP1 aborted due to pluggable database open resetlog marker.
(5):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 5662627, or timestamp before 02/27/2018 15:06:47, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
2018-02-27T16:47:46.008223+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_3398.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 5662627.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.46).
Datafiles are recovered to a consistent state at change 5663609 but controlfile could be ahead of datafiles.
2018-02-27T16:47:46.295066+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_3398.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 5662627.
2018-02-27T16:47:46.402997+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)

The data guard broker configuration will show an error status and standby database will show redo apply has stopped.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 5658 seconds ago)

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 minute 9 seconds (computed 0 seconds ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

6. To recover from this error, flashback the standby PDB to SCN lower than the the primary PDB. In a non-CDB (and in CDB) this value is obtained from the RESETLOGS_CHANGE# column in v$database view. But in CDBs this column is updated only at CDB level. If the CDB doesn't under go a resetlog then this column is not updated with the resetlog_change#. Querying within the PDB also has no effect, as that too will reflect the resetlog_change# shown at the CDB level. In this case the CDB didn't under go any resetlog so querying the v$database inside the PDB shows the following.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         5 PDBAPP1                        MOUNTED

SQL> alter session set container=pdbapp1;
Session altered.

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
                1

There are two places that could be used to obtain the SCN number to flashback to. First one is the alert log as highlighted above. Second place is INCARNATION_SCN column in the v$pdb_incarnation view in the primary PDB. The Oracle reference doc gives the description for INCARNATION_SCN column as "The SCN to flashback or recover to for this PDB incarnation". During testing it was found that SCN shown on the alert log is same as the one shown in v$pdb_incarnation.INCARNATION_SCN.

SQL> select incarnation_scn from v$pdb_incarnation where status='CURRENT';

INCARNATION_SCN
---------------
        5662627

7. Flashback the PDB at standby to a SCN number that is two less than the one shown in INCARNATION_SCN (INCARNATION_SCN - 2). If the PDB was open for read only then close the PDB and have it in the mount state before the flashback.

SQL> flashback pluggable database to scn 5662625;
Flashback complete.

Check the alert log for any issues. Following could be seen on the standby alert log during PDB flashback

PDBAPP1(5):flashback pluggable database to scn 5662625
2018-02-27T16:51:42.113608+00:00
PDBAPP1(5):Flashback Restore Start
PDBAPP1(5):Restore Flashback Pluggable Database PDBAPP1 (5) until change 5659443
PDBAPP1(5):Flashback Restore Complete
PDBAPP1(5):Completed: flashback pluggable database to scn 5662625

8. Start the redo apply and check the DG broker and standby database status

DGMGRL> edit database stbycdb set state='APPLY-ON';
Succeeded.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 66.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

Database Status:
SUCCESS

This conclude the first part of the post, flashback a PDB in a data guard.
As mentioned earlier the SCN to flashback the PDB at standby could be found at alert log and v$pdb_incarnation. Each time the PDB under goes a resetlog the incarnation goes up. So it's important get the SCN from the current incarnation. Output below is a new PDB (also called PDBAPP1). When the primary PDB is flashback the standby alert log has the following

2018-03-14T18:28:58.891103+00:00
(4):Recovery of pluggable database PDBAPP1 aborted due to pluggable database open resetlog marker.
(4):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 9555922, or timestamp before 03/14/2018 16:53:35, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
2018-03-14T18:28:58.897371+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_10568.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 9555922.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 9556551
2018-03-14T18:28:59.052367+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_10568.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 9555922.
2018-03-14T18:28:59.154425+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)

On v$pdb_incarnation view at primary

SQL> select * from v$pdb_incarnation;

DB_INCARNATION# PDB_INCARNATION# STATUS  INCARNATION_SCN INCARNATI BEGIN_RESETLOGS_SCN BEGIN_RES END_RESETLOGS_SCN END_RESET PRIOR_DB_INCARNATION# PRIOR_PD FLA     CON_ID
--------------- ---------------- ------- --------------- --------- ------------------- --------- ----------------- --------- --------------------- -------- --- ----------
              1                3 CURRENT         9555922 14-MAR-18             9556548 14-MAR-18           9556548 14-MAR-18                     1 0        YES          4
              1                0 PARENT                1 19-JAN-18                   1 19-JAN-18                 1 19-JAN-18                     0          YES          4

When the PDB is flashback again on primary, the standby alert log and the v$pdb_incarnation view at primary

(4):Recovery of pluggable database PDBAPP1 aborted due to pluggable database open resetlog marker.
(4):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 9797566, or timestamp before 03/15/2018 10:06:43, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
2018-03-15T11:41:57.572285+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_11330.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 9797566.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.46).
Datafiles are recovered to a consistent state at change 9797804 but controlfile could be ahead of datafiles.
2018-03-15T11:41:57.850051+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_11330.trc:
ORA-39874: Pluggable Database PDBAPP1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 9797566.
2018-03-15T11:41:57.953757+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)

DB_INCARNATION# PDB_INCARNATION# STATUS  INCARNATION_SCN INCARNATI BEGIN_RESETLOGS_SCN BEGIN_RES END_RESETLOGS_SCN END_RESET PRIOR_DB_INCARNATION# PRIOR_PD FLA     CON_ID
--------------- ---------------- ------- --------------- --------- ------------------- --------- ----------------- --------- --------------------- -------- --- ----------
              1                4 CURRENT         9797566 15-MAR-18             9797801 15-MAR-18           9797801 15-MAR-18                     1 3        YES          4
              1                3 PARENT          9555922 14-MAR-18             9556548 14-MAR-18           9556548 14-MAR-18                     1 0        YES          4
              1                0 PARENT                1 19-JAN-18                   1 19-JAN-18                 1 19-JAN-18                     0          YES          4

Flashback PDB Created With standbys=none on Primary
If the PDB was created with standbys=none, then there's nothing to be done on the standby if the PDB goes through a flashback.



Flashback Primary DB (non-CDB)
These steps shows a flashback of a non-CDB primary in a data guard configuration. The database version is 11.2.0.4 and data guard configuration is similar to an earlier post (single instance with Oracle restart).

1. Create a restore point on the primary to be used for the flashback.

SQL>  create restore point first_restore guarantee flashback database;
Restore point created.

SQL> select scn,TIME,name from v$restore_point;

       SCN TIME                                               NAME
---------- -------------------------------------------------- -------------
   6864231 27-FEB-18 03.10.15.000000000 PM                    FIRST_RESTORE

2. Simulate a user error as before

SQL> conn asanga/asa
Connected.
SQL> select count(*) from ins;

  COUNT(*)
----------
     17223

SQL> truncate table ins;
Table truncated.

SQL> select count(*) from ins;

  COUNT(*)
----------
         0

3. Start the primary in mount mode and issue the flashback command. Once flashback is complete open the primary with resetlogs

SQL> shutdown immediate;
SQL> startup mount;

SQL> FLASHBACK DATABASE TO RESTORE POINT FIRST_RESTORE;
Flashback complete.

SQL> alter database open resetlogs;

4. Select the resetlogs_change# from the primary DB

SQL>  select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          6864233

5. Check the user error is corrected

conn asanga/asa
SQL> select count(*) from ins;

  COUNT(*)
----------
     17223

6. At this stage the redo applied on standby would have been stopped. Following could be seen on the standby alert log. Highlighted is the SCN where primary underwent resetlog.

Tue Feb 27 16:08:08 2018
RFS[14]: Assigned to RFS process 3828
RFS[14]: New Archival REDO Branch: 969203936 Current: 967399936
RFS[14]: Selected log 9 for thread 1 sequence 1 dbid 376209786 branch 969203936
A new recovery destination branch has been registered
RFS[14]: Standby in the future of new recovery destinationBranch(resetlogs_id) 969203936
Incomplete Recovery SCN: 6864715
Resetlogs SCN: 6864233
Standby Became Primary SCN: 4218629
Flashback database to SCN 4218629 to follow new branch
Flashback database to SCN 4218629 to follow new branch
RFS[14]: New Archival REDO Branch(resetlogs_id): 969203936  Prior: 967399936
RFS[14]: Archival Activation ID: 0x16b5779b Current: 0x169a808c
RFS[14]: Effect of primary database OPEN RESETLOGS
RFS[14]: Managed Standby Recovery process is active
RFS[14]: Incarnation entry added for Branch(resetlogs_id): 969203936 (stdby)
...
Tue Feb 27 16:08:09 2018
MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_19722.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
...
Tue Feb 27 16:08:10 2018
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 6864715) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_3859.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/stdby/datafile/system.258.964883351'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Archived Log entry 4437 added for thread 1 sequence 175 rlc 967399936 ID 0x169a808c dest 3:
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[15]: Selected log 10 for thread 1 sequence 4 dbid 376209786 branch 969203936
Tue Feb 27 16:08:12 2018
Archived Log entry 4438 added for thread 1 sequence 3 ID 0x16b5779b dest 1:
Tue Feb 27 16:08:31 2018
MRP0: Background Media Recovery process shutdown (stdby)

7. The data guard broker would show the following status

DGMGRL> show configuration

Configuration - fz_db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL>  show database stdby

Database - stdby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      137.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    stdby

  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

8. Flashback the standby to the SCN that is (RESETLOGS_CHANGE# -2 ). If the standby was open in read only mode then close and start it in mount mode.

SQL> flashback database to scn 6864231;
Flashback complete.

On standby alert log following could be seen

Tue Feb 27 16:11:46 2018
Media Recovery Log +FRA/stdby/archivelog/2018_02_27/thread_1_seq_175.459.969206891
Incomplete Recovery applied until change 6864231 time 02/27/2018 15:10:15
Flashback Media Recovery Complete
Setting recovery target incarnation to 3
Completed: flashback database to scn 6864230

9. Enable redo apply and check the data guard broker status

DGMGRL> edit database stdby set state='APPLY-ON';
Succeeded.

DGMGRL> show configuration

Configuration - fz_db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stdby ;

Database - stdby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    stdby

Database Status:
SUCCESS

Flashback Primary DB (CDB)
When the primary CDB is flashback all PDBs pluged to it also under go the flashback. Steps are similar to that of non-CDB except for few key considerations specific to CDB. These are mentioned at the end of this section.
1. Currently CDB has two PDBs plugged

SQL> show pdbs

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

2. Create a CDB restore point

SQL> create restore point first_restore guarantee flashback database;

Restore point created.

SQL> select scn,TIME,name,PDB_RESTORE_POINT from  v$restore_point;

       SCN TIME                           NAME                 PDB
---------- ------------------------------ -------------------- ---
  10050579 15-MAR-18 16.04.22.000000000   FIRST_RESTORE        NO

3. Similar to before simulate a failure to test the flashback.
4. Put the primary CDB into mount mode and issue the flashback command

startup mount;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDBDEV                         MOUNTED
         4 PDBAPP1                        MOUNTED

SQL> FLASHBACK DATABASE TO RESTORE POINT FIRST_RESTORE;

Flashback complete.

SQL> alter database open resetlogs;

4. Get the resetlogs_change# from primary CDB after opening

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
         10050581

5. Similar to previous cases the redo apply on standby will stop. Following could be seen on alert log.

A new recovery destination branch has been registered
RFS[7]: Standby in the future of new recovery destinationBranch(resetlogs_id) 970848377
Incomplete Recovery SCN: 10050343
Resetlogs SCN: 10050581

Standby Became Primary SCN: 10049187

Flashback database to SCN 10049187 to follow new branch
Flashback database to SCN 10049187 to follow new branch
RFS[7]: New Archival REDO Branch(resetlogs_id): 970848377  Prior: 970844177
RFS[7]: Archival Activation ID: 0xb0f552e2 Current: 0xb0f5fd63
RFS[7]: Effect of primary database OPEN RESETLOGS
RFS[7]: Managed Standby Recovery process is active
2018-03-15T17:40:26.153911+00:00
RFS[7]: Incarnation entry added for Branch(resetlogs_id): 970848377 (stbycdb)
2018-03-15T17:40:26.170006+00:00
Setting recovery target incarnation to 3
2018-03-15T17:40:26.220855+00:00
MRP0: Incarnation has changed! Retry recovery...
2018-03-15T17:40:26.228354+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_3093.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 10050799
2018-03-15T17:40:26.525958+00:00
...
2018-03-15T17:40:32.758453+00:00
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[6]: Selected log 9 for T-1.S-4 dbid 2963914998 branch 970848377
2018-03-15T17:40:32.943141+00:00
Archived Log entry 36 added for T-1.S-3 ID 0xb0f552e2 LAD:1
2018-03-15T17:40:48.131861+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)

6. Data guard broker shows error status

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 5670 seconds ago)

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 7.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16856: transport lag could not be determined

Database Status:
ERROR

7. To resolve the issue start standby CDB in mount mode and flashback to SCN value of (RESETLOGS_CHANGE# - 2)

SQL> flashback database to scn 10050579;
Flashback complete.

Monitor the alert log for any issues. In this case flashback was able to go across creation and dropping of a tablespace

2018-03-15T17:44:41.398074+00:00
flashback database to scn 10050579
2018-03-15T17:44:41.879824+00:00
Flashback Restore Start
Flashback: created tablespace #5: 'TEST' of pdb #4 with key index #-546742896 in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00152' for tablespace #5 of pdb #4 in the controlfile.
Filename was:
'+DATA/STBYCDB/67632B96894E2116E0535500A8C05DA5/DATAFILE/test.268.970853853' when dropped.
File will have to be restored from a backup and recovered.
2018-03-15T17:44:42.444166+00:00
Flashback: deleted datafile #152 in tablespace #5 of pdb #4 from control file.
Flashback: dropped tablespace #5: 'TEST' of pdb #4 from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
2018-03-15T17:44:42.582223+00:00
Setting recovery target incarnation to 2
2018-03-15T17:44:42.629691+00:00
 Started logmerger process
2018-03-15T17:44:43.352732+00:00
Parallel Media Recovery started with 4 slaves
2018-03-15T17:44:43.577325+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_15/thread_1_seq_30.281.970854029
2018-03-15T17:44:43.778830+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_15/thread_1_seq_31.326.970854029
2018-03-15T17:44:43.939967+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_15/thread_1_seq_32.327.970854029
(4):Flashback recovery: Added file #152 to control file as OFFLINE and 'UNNAMED00152'
(4):because it was dropped during the flashback interval
(4):or it was added during flashback media recovery.
(4):File was originally created as:
(4):'+DATA/PRODCDB/67632B96894E2116E0535500A8C05DA5/DATAFILE/test.282.970848211'
(4):File will have to be restored from a backup or
(4):recreated using ALTER DATABASE CREATE DATAFILE command,
(4):and the file has to be onlined and recovered.
(4):Recovery deleting file #152:'/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00152' from controlfile.
(4):Recovery dropped tablespace 'TEST'
2018-03-15T17:44:44.498297+00:00
Incomplete Recovery applied until change 10050580 time 03/15/2018 16:04:22
2018-03-15T17:44:44.504472+00:00
Flashback Media Recovery Complete
2018-03-15T17:44:44.945998+00:00
Setting recovery target incarnation to 3
Completed: flashback database to scn 10050579

8. Start the redo apply and verify DG broker change

DGMGRL> edit database stbycdb set state='apply-on';
Succeeded.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5684 seconds ago)

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 33.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

Database Status:
SUCCESS

Primary CDB has PDB created with standbys=none
On 12.2 PDBs could be created only on primary DB without datafiles of the PDB being created on the standby. On standby the PDB will have recovery status disabled

SQL> select name,recovery_status from v$pdbs;

NAME       RECOVERY
---------- --------
PDB$SEED   ENABLED
PDBAPP1    ENABLED
PDBDEV     DISABLED

SQL> alter session set container=pdbdev;

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       183 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00183
       184 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00184
       185 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00185
       186 /opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00186

The steps to flashback the CDB and then to recover the standby CDB is same as with non-CDB.

1. Create a restore point and put the CDB into mount mode and flashback the database followed by open resetlogs.

SQL> create restore point first_restore guarantee flashback database;
Restore point created.


SQL> select scn,TIME,name,PDB_RESTORE_POINT from  v$restore_point;

       SCN TIME                           NAME                           PDB
---------- ------------------------------ ------------------------------ ---
  10537244 16-MAR-18 13.22.44.000000000   FIRST_RESTORE                  NO

SQL> startup force mount;

SQL> SQL> FLASHBACK DATABASE TO RESTORE POINT FIRST_RESTORE;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

2. Standby redo apply will stop due to diverging from the primary. The unanemd files mentioned are the files of the PDB with disabled recovery.

2018-03-16T14:59:57.788003+00:00
Deleted Oracle managed file +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_0_seq_0.314.970930797
2018-03-16T14:59:58.485425+00:00
RFS[5]: Assigned to RFS process (PID:21851)
RFS[5]: New Archival REDO Branch: 970925153 Current: 970913416
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[5]: Selected log 9 for T-1.S-3 dbid 2963914998 branch 970925153
2018-03-16T14:59:59.108930+00:00
RFS[6]: Assigned to RFS process (PID:21854)
RFS[6]: Selected log 10 for T-1.S-2 dbid 2963914998 branch 970925153
A new recovery destination branch has been registered
RFS[6]: Standby in the future of new recovery destinationBranch(resetlogs_id) 970925153
Incomplete Recovery SCN: 10537681
Resetlogs SCN: 10537246

Standby Became Primary SCN: 10516843

Flashback database to SCN 10516843 to follow new branch
Flashback database to SCN 10516843 to follow new branch
...
2018-03-16T15:00:03.991683+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_21894.trc:
ORA-01110: data file 183: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00183'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00183'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-16T15:00:04.365763+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_21894.trc:
ORA-01110: data file 184: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00184'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00184'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-16T15:00:04.805232+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_21894.trc:
ORA-01110: data file 185: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00185'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00185'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-03-16T15:00:05.209166+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_m000_21894.trc:
ORA-01110: data file 186: '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00186'
ORA-01565: error in identifying file '/opt/app/oracle/product/12.2.0/dbhome_1/dbs/UNNAMED00186'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 8 new persistent data failures
...
MRP0: Background Media Recovery process shutdown (stbycdb)

3. Get the resetlogs_change# from primary

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
         10537246

and flashback the standby DB (same as with a CDB).

SQL> flashback database to scn 10537244;
Flashback complete.

During the flashback warnings will be issued for offline data files which could be ignored.

2018-03-16T15:03:49.487890+00:00
flashback database to scn 10537244
2018-03-16T15:03:49.840095+00:00
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2018-03-16T15:03:50.286960+00:00
Setting recovery target incarnation to 4
2018-03-16T15:03:50.341298+00:00
 Started logmerger process
2018-03-16T15:03:51.035344+00:00
Parallel Media Recovery started with 4 slaves
2018-03-16T15:03:51.102859+00:00
Warning: Datafile 183 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/system.276.970921923) is offline during full database recovery and will not be recovered
Warning: Datafile 184 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/sysaux.275.970921923) is offline during full database recovery and will not be recovered
Warning: Datafile 185 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/undotbs1.277.970921923) is offline during full database recovery and will not be recovered
2018-03-16T15:03:51.270043+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_45.317.970930803
2018-03-16T15:03:51.510617+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_46.330.970930807
2018-03-16T15:03:51.688125+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_47.321.970930807
2018-03-16T15:03:51.840959+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_48.287.970930807
2018-03-16T15:03:51.987769+00:00
Incomplete Recovery applied until change 10537245 time 03/16/2018 13:22:45
2018-03-16T15:03:51.995117+00:00
Flashback Media Recovery Complete
2018-03-16T15:03:52.385714+00:00
Setting recovery target incarnation to 5
2018-03-16T15:03:52.565357+00:00
Completed: flashback database to scn 10537244

4. Finally enable redo apply.

DGMGRL> edit database stbycdb set state='apply-on';
Succeeded.

On standby alert log

2018-03-16T15:04:40.487316+00:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2018-03-16T15:04:40.495896+00:00
Attempt to start background Managed Standby Recovery process (stbycdb)
Starting background process MRP0
2018-03-16T15:04:40.548175+00:00
MRP0 started with pid=75, OS id=22252
2018-03-16T15:04:40.553249+00:00
MRP0: Background Managed Standby Recovery process started (stbycdb)
2018-03-16T15:04:45.608480+00:00
 Started logmerger process
2018-03-16T15:04:45.819166+00:00
Managed Standby Recovery starting Real Time Apply
2018-03-16T15:04:46.797164+00:00
Parallel Media Recovery started with 4 slaves
2018-03-16T15:04:46.866458+00:00
Warning: Datafile 183 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/system.276.970921923) is offline during full database recovery and will not be recovered
Warning: Datafile 184 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/sysaux.275.970921923) is offline during full database recovery and will not be recovered
Warning: Datafile 185 (+DATA/PRODCDB/6787CB93C0372211E0535500A8C052D4/DATAFILE/undotbs1.277.970921923) is offline during full database recovery and will not be recovered
2018-03-16T15:04:46.956819+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_1.278.970930809
2018-03-16T15:04:47.118435+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_2.314.970930803
2018-03-16T15:04:47.560091+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2018-03-16T15:04:48.937435+00:00
Media Recovery Log +FRA/STBYCDB/ARCHIVELOG/2018_03_16/thread_1_seq_3.331.970930811
2018-03-16T15:04:52.905396+00:00
Media Recovery Waiting for thread 1 sequence 4 (in transit)
2018-03-16T15:04:52.920078+00:00
Recovery of Online Redo Log: Thread 1 Group 10 Seq 4 Reading mem 0
  Mem# 0: +DATA/STBYCDB/ONLINELOG/group_10.283.970922707
  Mem# 1: +FRA/STBYCDB/ONLINELOG/group_10.324.970922711

Issues noticed
This test (CDB flashback when a PDB is having recovery disabled) was done fwe times. At one time the recovery start failed on standby after the flashback. The issue was case an ora-600 error.

2018-03-15T16:34:18.223019+00:00
Managed Standby Recovery starting Real Time Apply2018-03-15T16:34:19.670057+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_28192.trc  (incident=34209) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kcvaor_pdb_3], [148], [0], [0], [1], [0], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/incident/incdir_34209/stbycdb_pr00_28192_i34209.trc
2018-03-15T16:34:28.897393+00:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2018-03-15T16:34:28.899991+00:00
MRP0: Background Media Recovery terminated with error 600
2018-03-15T16:34:28.901477+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_pr00_28192.trc:
ORA-00600: internal error code, arguments: [kcvaor_pdb_3], [148], [0], [0], [1], [0], [], [], [], [], [], []
Managed Standby Recovery not using Real Time Apply
2018-03-15T16:34:28.952661+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2018-03-15T16:34:29.181019+00:00
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_mrp0_28186.trc  (incident=34201) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kcvaor_pdb_3], [148], [0], [0], [1], [0], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/incident/incdir_34201/stbycdb_mrp0_28186_i34201.trc
...
Errors in file /opt/app/oracle/diag/rdbms/stbycdb/stbycdb/trace/stbycdb_mrp0_28186.trc:
ORA-00600: internal error code, arguments: [kcvaor_pdb_3], [148], [0], [0], [1], [0], [], [], [], [], [], []
2018-03-15T16:34:32.201983+00:00
MRP0: Background Media Recovery process shutdown (stbycdb)

The trace file suggest issue is related to incarnation but none of the usual remedies worked.

cat stbycdb_pr00_28192.trc
Media Recovery apply resetlogs offline range for datafile 1, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 3, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 5, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 7, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 121, incarnation : 0

ora-600 are internal Oracle internal errors and ora-600 error look up tool didn't have any information on this (153788.1).
Other time the flashback on the standby would fail due to datafiles belonging to orphan incarnations.

RMAN> flashback database to scn 10545305;

Starting flashback at 16-MAR-18
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 03/16/2018 15:49:16
ORA-00283: recovery session canceled due to errors
ORA-38770: FLASHBACK DATABASE failed during recovery.
ORA-19909: datafile 148 belongs to an orphan incarnation
ORA-01110: data file 148: '+DATA/STBYCDB/67632B96894E2116E0535500A8C05DA5/DATAFILE/system.282.970920351'

Refer MOS 1509932.1 for resolving such cases.

Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

This post list the steps for adding a data guard broker to an existing 12.2 Data Guard setup. There's an earlier post on setting up data guard broker on 12cR1. The primary database is called prodcdb and standby database is called stbycdb (refer here for other names in DG setup).
12.2 also introduced several new features. Below are few highlights from the DG broker guide.
To increase high availability, you can now set multiple fast-start failover targets and the broker automatically selects which one to use.
You can now start observers on multiple hosts to manage a single Data Guard broker configuration.
The new Data Guard broker property ApplyInstances can be used to engage multiinstance Redo Apply as well as to restrict the number of instances that are involved in redo apply on an Oracle Real Application Clusters (Oracle RAC) database.
The broker now supports migrating a pluggable database (PDB) from one multitenant container database (CDB) to another on the same host. You can migrate a PDB from a primary CDB to another primary CDB or failover a PDB from a standby CDB to a primary CDB.
The DGMGRL commands SHOW DATABASE VERBOSE and SHOW INSTANCE VERBOSE now show the locations of the Oracle alert log file and of the broker log file.
The configuration member to which a connection is made using the CONNECT command is now displayed upon successful completion of the command.
1. DG broker uses static listener service names to connect and start databases. Add static service names to listener.ora file in GI_HOME as grid user. Oracle documentation states as of Oracle Database 12.1.0.2 "a static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used. Broker will use the clusterware to restart an instance". In this case the static _DGMGRL service was created to illustrate the steps that could be used in non-RAC and non Oracle restart setups.
On primary DB

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

)

On standby DB

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

)

Restart the listeners on both primary and standby and view static service registration. On primary

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

On standby

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

2. As this is non-RAC database setup, the default locations are chosen for dg broker config files. If this was a RAC configuration then the dg broker config file location must be shared by all instances. On primary the dg broker config files location is shown below.

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1prodcdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2prodcdb.dat

On standby

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1stbycdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2stbycdb.dat

3. Set dg broker to auto start on both primary and standby

alter system set dg_broker_start=true scope=both sid='*';

4. Create the DG broker configuration by adding the primary database.

$ dgmgrl /
Welcome to DGMGRL, type "help" for information.
Connected to "prodcdb"
Connected as SYSDG.
DGMGRL> create configuration dg12c2 as primary database is prodcdb connect identifier is prodcdbtns;
Configuration "dg12c2" created with primary database "prodcdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

4. Add the standby database into the DG broker. Before adding the standby database, the LOG_ARCHIVE_DEST_n value used for redo transport must be cleared on on both primary and standby. Adding of the standby DB would fail without clearing the LOG_ARCHIVE_DEST_n value

DGMGRL> add database stbycdb as connect identifier is stbytns;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Reason for this has been explained in the previous 12cR1 dg broker post and MOS 1582179.1. Once log_archive_dest is cleared, standby DB addition works. In this DG setup, log_archive_dest_2 is used for redo transport. Reset it on both primary and standby

SQL> alter system reset log_archive_dest_2 scope=both;

System altered.

Add the standby database

DGMGRL> add database stbycdb as connect identifier is stbytns;
Database "stbycdb" added

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

5. Enable the configuration

DGMGRL>  enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 168 seconds ago)

6. At this point the DG broker configuration is complete. But it is good to check out the database status, inconsistent properties and validate the databases.

DGMGRL> show database prodcdb

Database - prodcdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    prodcdb

Database Status:
SUCCESS

DGMGRL> show database stbycdb

Database - stbycdb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

Database Status:
SUCCESS

If the verbose option is used then as stated in new features section log files are shown

DGMGRL> show database verbose prodcdb
...
  Log file locations:
    Alert log               : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/alert_prodcdb.log
    Data Guard Broker log   : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/drcprodcdb.log

Database Status:
SUCCESS

Check inconsistent properties

DGMGRL> show database prodcdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

 DGMGRL>  show database stbycdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

Validate the databases

 DGMGRL> validate database prodcdb

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    prodcdb:  YES

DGMGRL> validate database stbycdb

  Database Role:     Physical standby database
  Primary Database:  prodcdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    prodcdb:  YES
    stbycdb:  YES

7. As part of the DG setup, if possible carry out a switchover to check if DG broker can bring up the databases without any issue.

DGMGRL> show configuration when primary is stbycdb

Configuration when stbycdb is primary - dg12c2

  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Connect with password to dgmgrl and perform the switchover

$ dgmgrl sys/prodcdbdb

DGMGRL> switchover to stbycdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

On the new primary (stbycdb) the PDBs will be in read write mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDBAPP1                        READ WRITE NO

On the new standby (prodcdb) PDBs will be in mount mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         5 PDBAPP1                        MOUNTED

Switchover back to original primary

DGMGRL> switchover to prodcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Oracle Clusterware is restarting database "stbycdb" ...
Switchover succeeded, new primary is "prodcdb"

This conclude the setting up of DG broker for an existing 12.2 data guard setup with CDB. Below is an additional step, showing how to increase the protection mode to maximum availability using DG Broker.



Changing Protection Mode to Maximum Availability
The current protection mode is maximum performance with redo transport mode set to ASYNC.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'ASYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'ASYNC'

To change the protection mode set the redo transport to SYNC and upgrade the protection mode

DGMGRL> edit database prodcdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL>  edit database stbycdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'SYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'SYNC'

  
DGMGRL>  EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

Related Posts
Oracle Data Guard on 12.2 CDB with Oracle Restart
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
Data Guard Broker for 11gR2 RAC
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

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