{"id":869,"date":"2014-12-16T01:34:47","date_gmt":"2014-12-15T23:34:47","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=869"},"modified":"2020-08-18T16:38:12","modified_gmt":"2020-08-18T14:38:12","slug":"clone-pdb-asm-dg-no-network-transfer","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/clone-pdb-asm-dg-no-network-transfer\/","title":{"rendered":"Cloning a PDB with ASM and Data Guard (no ADG) without network transfer"},"content":{"rendered":"

Ok, if you’re reading this post, you may want to read also the previous one<\/a> that explains something more about the problem.<\/p>\n

Briefly said, if you have a CDB running on ASM in a MAA architecture and you do not have Active Data Guard, when\u00a0you clone a PDB you have to “copy” the datafiles somehow on the standby. The only solution offered by Oracle (in a MOS Note, not in the documentation) is to restore the PDB from the primary to the standby site, thus transferring it over the network. But if you have a huge PDB this is a bad solution because it impacts your network connectivity. (Note: ending up with a huge PDB IMHO can only be caused by bad consolidation. I do not recommend to consolidate huge databases on Multitenant).<\/p>\n

So I’ve worked out another\u00a0solution, that still has many defects and is almost not viable, but it’s technically interesting because it permits to discover a little more about Multitenant and Data Guard.<\/p>\n

The three options<\/strong><\/p>\n

At the primary site, the process is always the same: Oracle copies the datafiles of the source, and it\u00a0modifies the headers so that they can be used by the new PDB (so it changes CON_ID, DBID, FILE#, and so on).<\/p>\n

On the standby site, by opposite, it changes depending on the option you choose:<\/p>\n

Option 1: Active Data Guard<\/strong><\/p>\n

If you have ADG, the ADG itself will take care of copying the datafile on the standby site, from the source standby pdb to the destination standby pdb. Once the copy is done, the MRP0 will continue the recovery. The modification of the header block of the destination PDB is done by the MRP0 immediately after the copy (at least this is what I understand).<\/p>\n

\"ADG_PDB_copy\"<\/p>\n

Option 2: No Active Data Guard, but STANDBYS=none<\/strong><\/p>\n

In this case, the copy on the standby site doesn’t happen, and the recovery process just add the entry of\u00a0the new datafiles in the controlfile, with status OFFLINE and name UNKNOWNxxx. \u00a0However, the source file cannot be copied\u00a0anymore, because the MRP0 process will expect to have a copy of the destination datafile, not the source datafile. Also, any tentative of restore of the datafile 28 (in this example) will give an error because it does not belong to the destination PDB. So the only chance is to restore the destination PDB from the primary.
\n\"NOADG_PDB_STANDBYS_NONE_copy\"<\/a><\/p>\n

Option 3: No Active Data Guard, no STANDBYS=none<\/strong><\/p>\n

This is the case that I want to explain actually. Without the flag STANDBYS=none, the MRP0 process will expect to change the header of the\u00a0new datafile, but because the file does not exist yet, the recovery process dies.
\nWe can then copy it manually from the source standby pdb, and restart the recovery process, that will change the header. This process needs to be repeated for each datafile. (that’s why it’s not a viable solution, right now).<\/p>\n

\"NOADG_PDB_copy\"<\/a><\/p>\n

Let’s try it together:<\/p>\n

The Environment<\/strong><\/p>\n

Primary<\/p>\n

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;\r\n\r\nDB_UNIQUE_NAME                 INSTANCE_NAME\r\n------------------------------ ----------------\r\nCDBATL                         CDBATL_2\r\nCDBATL                         CDBATL_1\r\n<\/pre>\n
Standby<\/p>\n
07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;\r\n\r\nDB_UNIQUE_NAME                 INSTANCE_NAME\r\n------------------------------ ----------------\r\nCDBGVA                         CDBGVA_1\r\nCDBGVA                         CDBGVA_2\r\n<\/pre>\n
The current user PDB (any resemblance to real people is purely coincidental \ud83d\ude09\u00a0#haveUSeenMaaz):<\/p>\n
08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';\r\n\r\nOPEN_MODE  NAME\r\n---------- ------------------------------\r\nOPEN       MAAZ\r\nOPEN       MAAZ<\/pre>\n
Cloning the PDB on the primary<\/strong><\/p>\n
First, make sure that the source PDB is open read-only<\/p>\n
08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;\r\n\r\nPluggable database altered.\r\n\r\n08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;\r\n\r\nPluggable database altered.\r\n\r\n08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;\r\n\r\nOPEN_MODE  NAME\r\n---------- ------------------------------\r\nREAD ONLY  MAAZ\r\nREAD ONLY  MAAZ\r\n<\/pre>\n
Then, clone the PDB on the primary without<\/span><\/strong> the clause STANDBYS=NONE:<\/p>\n
08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;\r\n\r\nPluggable database created.<\/pre>\n
Review the clone\u00a0on the Standby<\/strong><\/p>\n
At this point, on the standby the alert log show that the SYSTEM datafile is missing, and the recovery process stops.<\/p>\n
Mon Dec 15 17:46:11 2014\r\nRecovery created pluggable database LUDO\r\nMon Dec 15 17:46:11 2014\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdbgva\/CDBGVA_2\/trace\/CDBGVA_2_mrp0_16464.trc:\r\nORA-01565: error in identifying file '+DATA'\r\nORA-17503: ksfdopn:2 Failed to open file +DATA\r\nORA-15045: ASM file name '+DATA' is not in reference form\r\nRecovery was unable to create the file as:\r\n'+DATA'\r\nMRP0: Background Media Recovery terminated with error 1274\r\nMon Dec 15 17:46:11 2014\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdbgva\/CDBGVA_2\/trace\/CDBGVA_2_mrp0_16464.trc:\r\nORA-01274: cannot add data file that was originally created as '+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765'\r\nMon Dec 15 17:46:11 2014\r\nManaged Standby Recovery not using Real Time Apply\r\nMon Dec 15 17:46:11 2014\r\nRecovery interrupted!\r\nRecovery stopped due to failure in applying recovery marker (opcode 17.34).\r\nDatafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.\r\nMon Dec 15 17:46:11 2014\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdbgva\/CDBGVA_2\/trace\/CDBGVA_2_mrp0_16464.trc:\r\nORA-01274: cannot add data file that was originally created as '+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765'\r\nMon Dec 15 17:46:11 2014\r\nMRP0: Background Media Recovery process shutdown (CDBGVA_2)\r\n<\/pre>\n
One remarkable thing, is that in the standby controlfile, ONLY THE SYSTEM DATAFILE exists:<\/p>\n
18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';\r\n\r\n    CON_ID\r\n----------\r\n         4\r\n\r\n18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;\r\n\r\nNAME\r\n---------------------------------------------------------------------------\r\n+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765\r\n\r\n<\/pre>\n
We need to fix the datafiles one by one, but most of the steps can be done once for all the datafiles.<\/p>\n
Copy the source PDB from\u00a0the standby<\/strong><\/p>\n
What do we need to do? Well, the recovery process is stopped, so we can safely copy the datafiles of\u00a0\u00a0the source PDB from the standby site because they have not moved yet. (meanwhile, we can put the primary source PDB back in read-write mode).<\/p>\n
-- on primary\r\n08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;\r\n\r\nPluggable database altered.\r\n\r\n08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;\r\n\r\nPluggable database altered.<\/pre>\n
Copy the datafiles:<\/p>\n
## on the standby:\r\nRMAN> backup as copy pluggable database MAAZ;\r\n\r\nStarting backup at 15-DEC-14\r\nusing target database control file instead of recovery catalog\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00029 name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.463.857404625\r\noutput file name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00028 name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.283.857404623\r\noutput file name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03\r\nFinished backup at 15-DEC-14\r\n\r\nStarting Control File and SPFILE Autobackup at 15-DEC-14\r\npiece handle=+DATA\/CDBGVA\/AUTOBACKUP\/2014_12_15\/s_866396771.865.866397053 comment=NONE\r\nFinished Control File and SPFILE Autobackup at 15-DEC-14<\/pre>\n
Do the magic<\/strong><\/p>\n
Now there’s the interesting part: we need to assign the datafile copies of the maaz PDB to LUDO.<\/p>\n
Sadly, the OMF will create the copies on the bad location (it’s a copy, to they are created on the same location as the source PDB).<\/p>\n
We\u00a0cannot try to uncatalog and recatalog the copies, because they will ALWAYS be affected to the source PDB. Neither\u00a0we can use RMAN because it will never associate the datafile copies to the new PDB. We need to rename the files manually<\/strong>.<\/p>\n
RMAN> list datafilecopy all;\r\n\r\nList of Datafile Copies\r\n=======================\r\n\r\nKey File S Completion Time Ckp SCN Ckp Time\r\n------- ---- - --------------- ---------- ---------------\r\n55 28 A 15-DEC-14 10295232 14-DEC-14\r\n Name: +DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.86639709\r\n Tag: TAG20141215T175041\r\n\r\n54 29 A 15-DEC-14 10295232 14-DEC-14\r\n Name: +DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.86639703\r\n Tag: TAG20141215T175041\r\n\r\n\r\nRMAN> select name, guid from v$pdbs;\r\n\r\nNAME       GUID\r\n---------- --------------------------------\r\nPDB$SEED   FFBCECBB503D606BE043334EA8C019B7\r\nMAAZ       0243BF7B39D4440AE053334EA8C0E471\r\nLUDO       0A4A0048D5321597E053334EA8C0E40A\r\n\r\n<\/pre>\n
It’s better to uncatalog the datafile copies before, so we keep the catalog clean:<\/p>\n
RMAN> change datafilecopy '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049' uncatalog;\r\n\r\nuncataloged datafile copy\r\ndatafile copy file name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049 RECID=55 STAMP=866397051\r\nUncataloged 1 objects\r\n\r\n\r\nRMAN> change datafilecopy '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.866397043' uncatalog;\r\n\r\nuncataloged datafile copy\r\ndatafile copy file name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.866397043 RECID=54 STAMP=866397046\r\nUncataloged 1 objects\r\n<\/pre>\n
Then, because we cannot rename files on a standby database with standby file management set to AUTO, we need to put it temporarily to MANUAL.<\/p>\n
10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765' to '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049';\r\nalter database rename file '+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765' to '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049'\r\n*\r\nERROR at line 1:\r\nORA-01275: Operation RENAME is not allowed if standby file management is automatic.<\/pre>\n
10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';\r\n\r\nNAME                                                         ISPDB\r\n------------------------------------------------------------ -----\r\nstandby_archive_dest                                         FALSE\r\nstandby_file_management                                      FALSE\r\n<\/pre>\n
standby_file_management is not PDB modifiable, so we need to do it for the whole CDB.<\/p>\n
10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;\r\n\r\nSystem altered.\r\n\r\n18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA\/CDBATL\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.825.866396765' to '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049';\r\n\r\nDatabase altered.\r\n\r\n\r\n\r\n<\/pre>\n
then we need to set back the\u00a0standby_file_management=auto or the recover will not start:<\/p>\n
10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto;\r\nSystem altered.<\/pre>\n
We can now restart the recovery.<\/p>\n
The recovery process will:
\n– change the new datafile by modifying the\u00a0header for the new PDB
\n– create the entry for the second datafile in the controlfile
\n– crash again because the datafile is missing<\/p>\n
18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;\r\nalter database recover managed standby database\r\n*\r\nERROR at line 1:\r\nORA-00283: recovery session canceled due to errors\r\nORA-01111: name for data file 61 is unknown - rename to correct file\r\nORA-01110: data file 61: '\/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1\/dbs\/UNNAMED00061'\r\nORA-01157: cannot identify\/lock data file 61 - see DBWR trace file\r\nORA-01111: name for data file 61 is unknown - rename to correct file\r\nORA-01110: data file 61: '\/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1\/dbs\/UNNAMED00061'\r\n\r\n\r\n18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;\r\n\r\nNAME\r\n---------------------------------------------------------------------------\r\n+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049\r\n\/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1\/dbs\/UNNAMED00061<\/pre>\n
We already have the SYSAUX datafile, right? So we can alter the name again:<\/p>\n
18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;\r\n\r\nSystem altered.\r\n\r\n18:14:29 SYS@CDBGVA_2> alter database rename file '\/u01\/app\/oracle\/product\/12.1.0.2\/dbhome_1\/dbs\/UNNAMED00061' to '+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.866397043';\r\n\r\nDatabase altered.\r\n\r\n18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;\r\n\r\nSystem altered.\r\n\r\n18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;\r\n<\/pre>\n
This time all the datafiles have been copied (no user datafile for this example) and the recovery process will continue!! \ud83d\ude42 so we can hit ^C and start it in background.<\/p>\n
18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;\r\nalter database recover managed standby database\r\n*\r\nERROR at line 1:\r\nORA-16043: Redo apply has been canceled.\r\nORA-01013: user requested cancel of current operation\r\n\r\n\u00a0\r\n\r\n18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\r\n\r\nDatabase altered.\r\n\r\n18:18:19 SYS@CDBGVA_2><\/pre>\n
The Data Guard configuration reflects the\u00a0success of this operation.<\/p>\n
Do we miss anything?<\/strong><\/p>\n
Of course, we do!! The datafile names of the new PDB reside in the wrong ASM path. We need to fix them!<\/p>\n
18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;\r\n\r\nDatabase altered.\r\n\r\nRMAN> backup as copy pluggable database ludo;\r\n\r\nStarting backup at 15-DEC-14\r\nusing target database control file instead of recovery catalog\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00061 name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/sysaux.863.866397043\r\noutput file name=+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07\r\nchannel ORA_DISK_1: starting datafile copy\r\ninput datafile file number=00060 name=+DATA\/CDBGVA\/0243BF7B39D4440AE053334EA8C0E471\/DATAFILE\/system.864.866397049\r\noutput file name=+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943\r\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03\r\nFinished backup at 15-DEC-14\r\n\r\nStarting Control File and SPFILE Autobackup at 15-DEC-14\r\npiece handle=+DATA\/CDBGVA\/AUTOBACKUP\/2014_12_15\/s_866398689.868.866398945 comment=NONE\r\nFinished Control File and SPFILE Autobackup at 15-DEC-14\r\n\r\nRMAN> switch pluggable database ludo to copy;\r\n\r\nusing target database control file instead of recovery catalog\r\ndatafile 60 switched to datafile copy \"+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.867.866398941\"\r\ndatafile 61 switched to datafile copy \"+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/sysaux.866.866398933\"\r\n\r\n18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;\r\n\r\nNAME\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/system.867.866398941\r\n+DATA\/CDBGVA\/0A4A0048D5321597E053334EA8C0E40A\/DATAFILE\/sysaux.866.866398933<\/pre>\n
 <\/p>\n
I know there’s no practical use of this procedure, but it helps a lot in understanding how Multitenant has been implemented.<\/p>\n
I expect some improvements in 12.2!!<\/p>\n
Cheers<\/p>\n
—<\/p>\n
Ludo<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Ok, if you’re reading this post, you may want to read also the previous one that explains something more about the problem. Briefly said, if you have a CDB running on ASM in a MAA architecture and you do not … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[327,326,329,3,52,328,149,132],"tags":[212,202,66,209,211,292,289,210,161,160],"class_list":["post-869","post","type-post","status-publish","format-standard","hentry","category-oracle-maa","category-oracle","category-oracle-dg","category-oracledb","category-12c","category-oracle-mt","category-oracle-rac","category-triblog","tag-12c-2","tag-active-data-guard","tag-backup","tag-clone","tag-cloning","tag-data-guard","tag-multitenant","tag-pdb","tag-recovery","tag-restore"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=869"}],"version-history":[{"count":7,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/869\/revisions"}],"predecessor-version":[{"id":892,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/869\/revisions\/892"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}