UPDATE: please check my more recent post about this problem and the information I’ve got at the Oracle Demo Grounds during OOW14: https://www.ludovicocaldara.net/dba/demo-grounds-clone-pdb-rw/
I feel the strong need to blog abut this very recent problem because I’ve spent a lot of time debugging it… especially because there’s no information about this error on the MOS.
Introduction
For a lab, I have prepared two RAC Container databases in physical stand-by.
Real-time query is configured (real-time apply, standby in read-only mode).
Following the doc, http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#CCHDFDDG, I’ve cloned one local pluggable database to a new PDB and, because Active Data Guard is active, I was expecting the PDB to be created on the standby and its files copied without problems.
BUT! I’ve forgot to put my source PDB in read-only mode on the primary and, strangely:
- The pluggable database has been created on the primary WITHOUT PROBLEMS (despite the documentation explicitly states that it needs to be read-only)
- The recovery process on the standby stopped with error.
|
1 2 3 4 5 6 7 8 |
Recovery copied files for tablespace SYSTEM Recovery successfully copied file +DATA/CDBGVA/01B838F74693443FE053334EA8C03527/DATAFILE/system.437.856805523 from +DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413 MRP0: Background Media Recovery terminated with error 1274 Thu Aug 28 17:32:05 2014 Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace/CDBGVA_1_mrp0_13949.trc: ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/01B838F74693443FE053334EA8C03527/DATAFILE/system.477.856805517' ORA-19729: File 22 is not the initial version of the plugged in datafile Thu Aug 28 17:32:05 2014 |
Now, the primary had all its datafiles (the new PDB has con_id 4):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CON_ID NAME ---------- ---------------------------------------------------------------------------------------------------- 1 +DATA/CDB/DATAFILE/system.283.854626623 1 +DATA/CDB/DATAFILE/undotbs1.290.854627639 1 +DATA/CDB/DATAFILE/users.291.854627695 1 +DATA/CDB/DATAFILE/undotbs2.287.854627063 1 +DATA/CDB/DATAFILE/sysaux.285.854626879 2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.286.854627011 2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.284.854626785 3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.390.855681795 3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.389.855681795 4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061 4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061 |
and the standby was missing the datafiles of the new PDB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
1* select con_id, name from v$datafile order by 1 CON_ID NAME ---------- ---------------------------------------------------------------------------------------------------- 1 +DATA/CDBGVA/DATAFILE/system.319.855054997 1 +DATA/CDBGVA/DATAFILE/undotbs2.283.855055141 1 +DATA/CDBGVA/DATAFILE/users.285.855055149 1 +DATA/CDBGVA/DATAFILE/undotbs1.284.855055145 1 +DATA/CDBGVA/DATAFILE/sysaux.281.855055061 2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.282.855055127 2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.280.855055053 3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.363.855681865 3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.362.855681863 |
But, on the standby database, the PDB somehow was existing.
|
1 2 3 4 5 6 7 |
16:20:58 SYS@CDBGVA_1> select name from v$pdbs; NAME ------------------------------ PDB$SEED MAAZ LUDO |
I’ve tried to play a little, and finally decided to disable the recovery for the PDB (new in 12.1.0.2).
But to disable the recovery I was needing to connect to the PDB, but the PDB was somehow “inexistent”:
|
1 2 3 4 5 6 7 8 9 10 11 |
16:21:35 SYS@CDBGVA_1> alter session set container=LUDO; ERROR: ORA-65011: Pluggable database LUDO does not exist. 16:21:39 SYS@CDBGVA_1> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY MAAZ MOUNTED LUDO MOUNTED |
So I’ve tried to drop it, but off course, the standby was read-only and I could not drop the PDB:
|
1 2 3 4 5 |
16:22:01 SYS@CDBGVA_1> drop pluggable database ludo; drop pluggable database ludo * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access |
Then I’ve shutted down the standby, but one instance hung and I’ve needed to do a shutdown abort (I don’t know if it was related with my original problem..)
|
1 2 3 |
# [ oracle@racb02:/u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace [16:22:45] [12.1.0.2.0 EE SID=CDBGVA_1] 1 ] # # srvctl stop database -d CDBGVA -o immediate [HANGS] |
After mounting again the standby, the PDB was also accessible:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 28 16:30:19 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options 16:30:19 SYS@CDBGVA_1> alter session set container=LUDO; Session altered. |
So I’ve been able to disable the recovery:
|
1 2 3 |
16:31:19 SYS@CDBGVA_1> alter pluggable database ludo disable recovery; Pluggable database altered. |
Then, on the primary, I’ve took a fresh backup of the involved datafiles:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
RMAN> backup as copy datafile 16,17 format '/tmp/%f.dbf'; Starting backup at 28-AUG-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=88 instance=CDBATL_2 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00017 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061 output file name=/tmp/17.dbf tag=TAG20140828T163251 RECID=4 STAMP=856801976 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00016 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061 output file name=/tmp/16.dbf tag=TAG20140828T163251 RECID=5 STAMP=856801981 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 28-AUG-14 Starting Control File and SPFILE Autobackup at 28-AUG-14 piece handle=+DATA/CDBATL/AUTOBACKUP/2014_08_28/s_856801982.471.856801983 comment=NONE Finished Control File and SPFILE Autobackup at 28-AUG-14 |
and I’ve copied and cataloged the copies to the controlfile:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
RMAN> catalog start with '/tmp/1'; searching for all files that match the pattern /tmp/1 List of Files Unknown to the Database ===================================== File Name: /tmp/17.dbf File Name: /tmp/16.dbf Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /tmp/17.dbf File Name: /tmp/16.dbf |
but the restore was impossible, because the controlfile was not knowing these datafiles!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
16:38:48 SYS@CDBGVA_1> select file# from v$datafile; FILE# ---------- 1 2 3 4 5 6 7 10 11 RMAN> run { 2> set newname for datafile 16 to new; 3> set newname for datafile 17 to new; 4> restore datafile 16,17; 5> } executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 28-AUG-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=64 instance=CDBGVA_1 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/28/2014 16:37:02 RMAN-20201: datafile not found in the recovery catalog RMAN-06010: error while looking up datafile: 17 RMAN> exit |
So I’ve RESTARTED the recovery for a few seconds, and because the PDB had the recovery disabled, the recovery process has added the datafiles and set them offline.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
16:38:08 SYS@CDBGVA_1> alter database recover managed standby database ; alter database recover managed standby database * ERROR at line 1: ORA-16043: Redo apply has been canceled. ORA-01013: user requested cancel of current operation 16:38:48 SYS@CDBGVA_1> select file# from v$datafile; FILE# ---------- 1 2 3 4 5 6 7 10 11 16 17 |
Then I’ve been able to restore the datafiles 🙂
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
RMAN> run { 2> set newname for datafile 16 to new; 3> set newname for datafile 17 to new; 4> restore datafile 16,17; 5> } executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 28-AUG-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=33 instance=CDBGVA_1 device type=DISK channel ORA_DISK_1: restoring datafile 00016 input datafile copy RECID=21 STAMP=856802136 file name=/tmp/16.dbf destination for restore of datafile 00016: +DATA channel ORA_DISK_1: copied datafile copy of datafile 00016 output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413 RECID=22 STAMP=856802416 channel ORA_DISK_1: restoring datafile 00017 input datafile copy RECID=20 STAMP=856802136 file name=/tmp/17.dbf destination for restore of datafile 00017: +DATA channel ORA_DISK_1: copied datafile copy of datafile 00017 output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417 RECID=23 STAMP=856802421 Finished restore at 28-AUG-14 RMAN> RMAN> switch datafile 16, 17 to copy; datafile 16 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413" datafile 17 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417" RMAN> |
Finally, I’ve enabled again the recovery for the PDB and restarted the apply process.
|
1 2 3 4 5 6 7 |
16:41:14 SYS@CDBGVA_1> alter session set container=LUDO; Session altered. 16:41:19 SYS@CDBGVA_1> alter pluggable database ludo enable recovery; Pluggable database altered. |
Lesson learned: if you want to clone a PDB never, ever, forget to put your source PDB in read-only mode or you’ll have to deal with it!! 🙂
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Hi Ludovico,
should the PDB be in yet,version 19.3, in READ Only Status if DG is in use?
Hi Alessandra,
Cloning PDBs in Multitenant with Data Guard is still a painful process. The PDB on the standby still needs to be restored from the primary in most cases.
I have blogged something related to 19c here:
http://www.ludovicocaldara.net/dba/fix-dataguard-pdb/
Hi Peter, I will try the same testcase and see if I can reproduce it, but it looks like a bug (or lack of feature 😉 ).
Hi,
during restore with “set newname to new” there will no guid inserted in die directory path.
I found that issue also here described
https://asanga-pradeep.blogspot.de/2016/08/guid-directory-not-created-for-pdbs.html
I have posted a question about that also here:
https://community.oracle.com/message/14059057
Maybe you can confirm that. Maybe you have an explaination/solution?
Best regards
Peter
Thanks for sharing this. If I have ADG configured, then how does Oracle handle remote clone? Will it be copied to standby ?
Hi Jagjeet, I’m not sure 100% now, but I guess that with release 12.1 a remote clone needs to be threated with a manual copy on the standby as the datafiles are not physically in the same CDB.
Pingback: A PDB is cloned while in read-write, Data Guard loose its marbles (12.1.0.2, ORA-19729) - Ludovico Caldara - Blogs - triBLOG