PDB Cloning in a Dataguard Environment
In this article, I’m demonstrating on how or what impact would be seen on the Physical standby database when you clone a Pluggable Database on the Primary.
Here is the environment detail.
Primary database name: targetdb Standby database name: tarstdb PDB name already plugged into CDB: targetpdb PDB name to be created: targetpdb1
Please note that both TARGETPDB and TARGETPDB1 would be under the same container.
Before I start, let me give a brief outcome of the setup.
I have DG broker configured for the setup and I could see below that the standby database is in sync with the primary database.
[oracle@ora1-1 ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration
Configuration - dgtest
Protection Mode: MaxPerformance
Members:
targetdb - Primary database
tarstdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 49 seconds ago)
TARGETPDB1 will be created from TARGETPDB and would be plugged into the same ROOT container as TARGETPDB
Below shows the list of datafiles that TARGETPDB has.
SYS@targetdb> select status,instance_Name,database_role from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN targetdb PRIMARY
SYS@targetdb> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TARGETPDB READ WRITE NO
SYS@targetdb> alter session set container=targetpdb;
Session altered.
SYS@targetdb> col file_name for a99 SYS@targetdb> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/system.272.906204413 +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/sysaux.290.906204413 +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/test.271.907334287 +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/myobjects.291.930751499 +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/migtbs1.279.930751523 +DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/users.273.913039191 6 rows selected.
Let me clone TARGETPDB to create TARGETPDB1. In order to clone, I need the TARGETPDB to be opened in READ ONLY mode which is done in the below steps.
Logging into the CDB$ROOT container, I OPEN TARGETPDB in READ ONLY mode followed by which I issue the CREATE PLUGGABLE DATABASE command. Please note that while creating the PDB, I’m also using the clause “STANDBY=NONE”.
In case if I was cloning from PDB$SEED, then the datafiles of the newly created PDB would automatically get replicated on the physical standby side too without using the “STANDBY=NONE” clause. But, when a PDB is cloned from another PDB under the same container in a dataguard environment, then, the datafiles may not get replicated to the Physical standby side if the physical standby database is in MOUNT mode. In such a case, use the “STANDBYS=NONE” clause which would defer the recover of the newly created PDB on the physical standby database. The datafiles for the new PDB would be marked as OFFLINE/RECOVER in physical standby database and any additional redo for that PDB will be ignored. This is interpreted as at some point in the future, the PDBs files will be copied to the physical standby database and recovery for the PDB will be enabled.
Here are the cloning and creation steps:
On Primary, place the “targetpdb” in read only mode.
SYS@targetdb> alter pluggable database targetpdb close immediate; Pluggable database altered. SYS@targetdb> alter pluggable database targetpdb open read only; Pluggable database altered. SYS@targetdb>
Now, create pluggable database “targetpdb1” with the clause “standbys=none”.
SYS@targetdb> create pluggable database targetpdb1 from targetpdb standbys=none;
Pluggable database created.
SYS@targetdb>
SYS@targetdb> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TARGETPDB READ ONLY NO
4 TARGETPDB1 MOUNTED
SYS@targetdb>
As seen above, since “targetpdb” is still in READ ONLY mode, we need to revert it back to “READ WRITE” mode. Also, we need to open the newly created pdb “targetpdb1”.
SYS@targetdb> alter pluggable database targetpdb close immediate;
Pluggable database altered.
SYS@targetdb> alter pluggable database targetpdb open;
Pluggable database altered.
SYS@targetdb> alter pluggable database targetpdb1 open;
Pluggable database altered.
SYS@targetdb> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TARGETPDB READ WRITE NO
4 TARGETPDB1 READ WRITE NO
SYS@targetdb>
Moving on to the physical standby database side, let’s check the status of the PDBs.
SYS@tarstdb> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 TARGETPDB MOUNTED
4 TARGETPDB1 MOUNTED
SYS@tarstdb>
On checking the recovery status of each of the PDBs that are plugged into the physical standby container database, we see that the recovery of the newly created PDB “targetpdb1” is “DISABLED” and this is because we had used the clause “standbys=none” while creating the pdb.
SYS@tarstdb> select name, recovery_status from v$pdbs; NAME RECOVERY ------------------------------ -------- PDB$SEED ENABLED TARGETPDB ENABLED TARGETPDB1 DISABLED SYS@tarstdb> alter session set container=targetpdb1; Session altered. SYS@tarstdb> select name, status from v$datafile; NAME STATUS ------------------------------------------------------- ------- /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021 SYSOFF /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026 RECOVER 6 rows selected.
When I query the v$recovery_file, I see that all the files of the newly created PDB seem to be errored out as “Missing” on the standby.
SYS@tarstdb> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
21 OFFLINE OFFLINE FILE MISSING 0 4
22 OFFLINE OFFLINE FILE MISSING 0 4
23 OFFLINE OFFLINE FILE MISSING 0 4
24 OFFLINE OFFLINE FILE MISSING 0 4
25 OFFLINE OFFLINE FILE MISSING 0 4
26 OFFLINE OFFLINE FILE MISSING 0 4
6 rows selected.
Ok, so how do we get these files on to the standby ? Simple option would be to restore them from the Primary PDB (Targetpdb) through RMAN using the “net service”.
I have DG Broker configured for my setup, so just making sure that the standby database is in sync with the primary database.
[oracle@ora1-1 ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration
Configuration - dgtest
Protection Mode: MaxPerformance
Members:
targetdb - Primary database
tarstdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 9 seconds ago)
Now, it’s time for me to restore the files of the newly created PDB (targetpdb1) from primary to the standby site. I restore it using the “service” clause connecting to the primary.
I use the “set newname for pluggable database targetpdb1 to new” to get the OMF format for the datafiles to be restored which would be done to the value specified in the “db_create_file_dest” parameter. Followed by this, is the “restore pluggable database targetpdb1 from service targetdb” suggesting to restore that particular PDB using the NET SERVICE to connect to primary. And finally switching the datafiles restored.
[oracle@ora1-4 dbs]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Mar 17 12:22:59 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TARGETDB (DBID=1297732342, not open)
RMAN> run
2> {
3> set newname for pluggable database targetpdb1 to new;
4> restore pluggable database targetpdb1 from service targetdb;
5> switch datafile all;
6> }
executing command: SET NEWNAME
Starting restore at 17-MAR-18
Starting implicit crosscheck backup at 17-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 17-MAR-18
Starting implicit crosscheck copy at 17-MAR-18
using channel ORA_DISK_1
Crosschecked 11 objects
Finished implicit crosscheck copy at 17-MAR-18
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00023 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00024 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00025 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00026 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-MAR-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch command at 03/17/2018 12:26:38
ORA-19563: datafile copy header validation failed for file +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125
RMAN>
Quite strange !! The datafiles of “targetpdb1” are restored but errors out while performing the switch operation. This datafile was plugged in as part of transportable tablespace migration. Suspecting that could be the case but unsure why it failed during the execution of the switch command. I shall investigate on that and post in the upcoming days.
As a workaround, I thought to rename those files manually. In order to do that, I set the “StandbyFileManagement” parameter to “Manual” on standby database and stop the RECOVERY on the standby database (tarstdb).
DGMGRL> edit database tarstdb set property StandbyFileManagement =Manual; Property "standbyfilemanagement" updated DGMGRL> edit database tarstdb set state='apply-off'; Succeeded.
The datafile status and it’s name for the newly created PDB (targetpdb1) on standby site needs to be captured.
SYS@tarstdb> select name,status from v$datafile; NAME STATUS -------------------------------------------------------------------------------- ------- /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021 SYSOFF /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025 RECOVER /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026 RECOVER 6 rows selected.
From the alert log of the standby database, I get the OMF format of the files restored in the above step for the new PDB (targetpdb1).
Sat Mar 17 13:34:38 2018 Full restore complete of datafile 21 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063. Elapsed time: 0:00:13 checkpoint is 10105360 last deallocation scn is 8421271 Undo Optimization current scn is 10090649 Sat Mar 17 13:35:19 2018 Full restore complete of datafile 22 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089. Elapsed time: 0:00:30 checkpoint is 10105405 last deallocation scn is 4478083 Full restore complete of datafile 23 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125. Elapsed time: 0:00:00 checkpoint is 10105471 last deallocation scn is 1594145 Full restore complete of datafile 24 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125. Elapsed time: 0:00:01 checkpoint is 10105481 last deallocation scn is 907776 Full restore complete of datafile 25 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127. Elapsed time: 0:00:00 checkpoint is 10105484 last deallocation scn is 907776 Full restore complete of datafile 26 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129. Elapsed time: 0:00:01 checkpoint is 10105489 last deallocation scn is 3 Sat Mar 17 13:37:45 2018 Checker run found 13 new persistent data failures
I then rename each of these datafiles manually by connecting to the CDB$ROOT of the standby database. I understand that this is a tedious process, but for time being I had to go with this.
SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063'; Database altered. SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089'; Database altered. SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125'; Database altered. SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125'; Database altered. SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127'; Database altered. SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129'; Database altered.
Now, let me check the datafile names after renaming them. All looks good as below.
SYS@tarstdb> select name,status from v$datafile; NAME STATUS ------------------------------------------------------------------------------------- ------- +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063 SYSOFF +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089 RECOVER +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125 RECOVER +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125 RECOVER +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127 RECOVER +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129 RECOVER 6 rows selected.
We now need to enable the recovery for the newly created PDB targetpdb1 on standby database. For this, connect to the targetpdb1 container and enable the recovery.
SYS@tarstdb> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 TARGETPDB MOUNTED
4 TARGETPDB1 MOUNTED
SYS@tarstdb>
SYS@tarstdb> alter session set container=targetpdb1;
Session altered.
SYS@tarstdb> alter pluggable database enable recovery;
Pluggable database altered.
The changes made above to the parameter “StandbyFileManagement” needs to be reverted back to the value “AUTO” and start MRP on the standby database.
DGMGRL> edit database tarstdb set property StandbyFileManagement =AUTO; Property "standbyfilemanagement" updated DGMGRL> edit database tarstdb set state='apply-on'; Succeeded.
Crosscheck if all looks fine.
SYS@tarstdb> select name, recovery_status from v$pdbs; NAME RECOVERY ------------------------------ -------- PDB$SEED ENABLED TARGETPDB ENABLED TARGETPDB1 ENABLED
SYS@tarstdb> alter session set container=targetpdb1; Session altered. SYS@tarstdb> col name for a90 SYS@tarstdb> SYS@tarstdb> select * from v$recovery_status; no rows selected
Verfiy if the standby database is in sync with the primary database.
On Primary:
SYS@targetdb> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
335
SYS@targetdb> alter system switch logfile;
System altered.
SYS@targetdb> /
System altered.
SYS@targetdb> /
System altered.
SYS@targetdb> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
338
On standby:
SYS@tarstdb> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 337 ARCH CONNECTED 0 ARCH CLOSING 336 ARCH CLOSING 338 RFS IDLE 0 RFS IDLE 339 MRP0 APPLYING_LOG 339 7 rows selected.
SYS@tarstdb> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
337
All looks good !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organisation. The views expressed by visitors on this blog are theirs solely and may not reflect mine
Shivananda Rao 
Leave a Reply