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-ON2. 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 NO3. 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 NO5. 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 standby2018-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 TABLESPACEADD 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 NO2. 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 RECOVERThe alert log on standby will have the following with regard to the datafiles2018-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-18Once 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.970925759Once 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 configuration5. 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 statusDGMGRL> 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:
ERROR3. 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 MOUNTEDUnlike the previous case the recovery status of the PDB will be enabled.SQL> select name,recovery_status from v$pdbs; NAME RECOVERY ---------- -------- PDB1 ENABLED4. 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/UNNAMED001445. Copy the datafile 144 from primary to standbyrman 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-18Alternatively 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.9711970896. 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 12748. 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';
Succeeded9. 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/UNNAMED0014610. 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.97076787711. 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)
