{"id":837,"date":"2020-12-07T20:12:16","date_gmt":"2020-12-07T23:12:16","guid":{"rendered":"https:\/\/www.fernandosimon.com\/blog\/?p=837"},"modified":"2020-12-07T20:12:16","modified_gmt":"2020-12-07T23:12:16","slug":"duplicate-restore-and-zdlra","status":"publish","type":"post","link":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/","title":{"rendered":"Duplicate, Restore and ZDLRA"},"content":{"rendered":"

From time to time we need to clone\/duplicate some databases and we have several ways to do that, most common are duplicate and restore (with a new name) commands. But when using RMAN catalogs we need to take extra care because we can up lose the backups of the entire database because of the wrong way to do that. And this is even more crucial when using ZDLRA.<\/p>\n

You need to choose between The Good and The Bad. Because if you choose wrong you will have troubles with The Ugly. The key factor here is the RMAN\/ZDLRA catalog, choose wrong and you will automatically add bad data in the internal catalog tables and if you will try to clean, can delete database backups.<\/p>\n

In this post, I will show how correctly clone one database when you are using the RMAN\/ZDLRA catalog and the reasons for that. I will show the problems and the collateral effects for ZDLRA when you choose the bad way.<\/p>\n

<\/p>\n

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

The Good<\/h2>\n

The Good is to use DUPLICATE to clone the database. In a simple way, the duplicate command is easy to use and is fast. You just need to startup the database with the spfile and execute the duplicate. But since we are using the RMAN catalog, some details need to be done correctly, and since we are using ZDLRA, the duplicate will read data from there (and not from the source database).<\/p>\n

The first step is to create\/restore one spfile from the source database. Here I just made a simple create pfile from spfile:<\/p>\n

oracle@orcloel7 ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 21:21:59 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n\r\nSQL> create pfile = '\/tmp\/pfile.ora' from spfile;\r\n\r\nFile created.\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ cat \/tmp\/pfile.ora\r\nORCL19C.__data_transfer_cache_size=0\r\nORCL19C.__db_cache_size=2399141888\r\nORCL19C.__inmemory_ext_roarea=0\r\nORCL19C.__inmemory_ext_rwarea=0\r\nORCL19C.__java_pool_size=50331648\r\nORCL19C.__large_pool_size=16777216\r\nORCL19C.__oracle_base='\/u01\/app\/oracle'#ORACLE_BASE set from environment\r\nORCL19C.__pga_aggregate_target=1610612736\r\nORCL19C.__sga_target=3254779904\r\nORCL19C.__shared_io_pool_size=134217728\r\nORCL19C.__shared_pool_size=637534208\r\nORCL19C.__streams_pool_size=0\r\nORCL19C.__unified_pga_pool_size=0\r\n*.audit_file_dest='\/u01\/app\/oracle\/admin\/ORCL19C\/adump'\r\n*.audit_trail='db'\r\n*.compatible='19.0.0'\r\n*.control_files='\/u01\/app\/oracle\/oradata\/ORCL19C\/control01.ctl','\/u01\/app\/oracle\/oradata\/ORCL19C\/control02.ctl'\r\n*.db_block_size=8192\r\n*.db_create_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_name='ORCL19C'\r\n*.db_recovery_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_recovery_file_dest_size=12732m\r\n*.diagnostic_dest='\/u01\/app\/oracle'\r\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL19CXDB)'\r\n*.enable_pluggable_database=true\r\n*.local_listener='LISTENER_ORCL18C'\r\n*.log_archive_config='DG_CONFIG=(ORCL19C,ZDLRAS1)'\r\n*.log_archive_dest_2='SERVICE=\"zdlras1-scan:1521\/zdlras1:VPCSRC\" SYNC NOAFFIRM DB_UNIQUE_NAME=zdlras1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)'\r\n*.log_archive_dest_state_2='DEFER'\r\n*.nls_language='AMERICAN'\r\n*.nls_territory='AMERICA'\r\n*.open_cursors=300\r\n*.pga_aggregate_target=1536m\r\n*.processes=300\r\n*.redo_transport_user='VPCSRC'\r\n*.remote_login_passwordfile='EXCLUSIVE'\r\n*.sga_max_size=4096m\r\n*.sga_target=3096m\r\n*.undo_tablespace='UNDOTBS1'\r\n[oracle@orcloel7 ~]$<\/pre>\n
And if I connect to RMAN catalog, I can see that exists only one database registered with this name:<\/p>\n
[oracle@orcloel7 ~]$ rman target=\/ catalog=vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 17 21:35:12 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: ORCL19C (DBID=1487680695)\r\nconnected to recovery catalog database\r\n\r\nRMAN> list db_unique_name of database ORCL19C;\r\n\r\n\r\nList of Databases\r\nDB Key  DB Name  DB ID            Database Role    Db_unique_name\r\n------- ------- ----------------- ---------------  ------------------\r\n15917   ORCL19C  1487680695       PRIMARY          ORCL19C\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\n[oracle@orcloel7 ~]$<\/pre>\n
For the pfile, you can see that is a simple file without fancy parameters. And next, I copy to a new name and edited some parameters removing the pointers from the old database name (ORCL19C) and redirecting to the new database name (DBCLON).<\/p>\n
[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ cp \/tmp\/pfile.ora \/tmp\/DBCLON-pfile.ora\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ vi \/tmp\/DBCLON-pfile.ora\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ cat \/tmp\/DBCLON-pfile.ora\r\n*.audit_file_dest='\/u01\/app\/oracle\/admin\/DBCLON\/adump'\r\n*.audit_trail='db'\r\n*.compatible='19.0.0'\r\n*.control_files='\/u01\/app\/oracle\/oradata\/DBCLON\/control01.ctl','\/u01\/app\/oracle\/oradata\/DBCLON\/control02.ctl'\r\n*.db_block_size=8192\r\n*.db_create_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_name='DBCLON'\r\n*.db_recovery_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_recovery_file_dest_size=12732m\r\n*.diagnostic_dest='\/u01\/app\/oracle'\r\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBCLONXDB)'\r\n*.enable_pluggable_database=true\r\n*.local_listener='LISTENER_ORCL18C'\r\n*.nls_language='AMERICAN'\r\n*.nls_territory='AMERICA'\r\n*.open_cursors=300\r\n*.pga_aggregate_target=1536m\r\n*.processes=300\r\n*.redo_transport_user='VPCSRC'\r\n*.remote_login_passwordfile='EXCLUSIVE'\r\n*.sga_max_size=4096m\r\n*.sga_target=3096m\r\n*.undo_tablespace='UNDOTBS1'\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ mkdir \/u01\/app\/oracle\/admin\/DBCLON\/adump -p\r\n[oracle@orcloel7 ~]$<\/pre>\n
The most important part is the db_name parameter that I changed to DBCLON. After this change at pfile I started the instance in nomount state:<\/p>\n
[oracle@orcloel7 ~]$ export ORACLE_SID=DBCLON\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 10:30:29 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\r\n\r\nConnected to an idle instance.\r\n\r\nSQL> startup nomount pfile = '\/tmp\/DBCLON-pfile.ora';\r\nORACLE instance started.\r\n\r\nTotal System Global Area 4294963960 bytes\r\nFixed Size                  9143032 bytes\r\nVariable Size            1694498816 bytes\r\nDatabase Buffers         2583691264 bytes\r\nRedo Buffers                7630848 bytes\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n[oracle@orcloel7 ~]$<\/pre>\n
Now, I can use the RMAN to do the duplicate. First, check that the auxiliary is local DBCLON connection with \u201cconnect auxiliary \/<\/strong>\u201d, and one catalog connection with \u201cconnect catalog vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1<\/strong>\u201d:<\/p>\n
[oracle@orcloel7 ~]$ rman\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:31:00 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nRMAN> \r\n\r\nRMAN> connect auxiliary \/\r\n\r\nconnected to auxiliary database: DBCLON (not mounted)\r\n\r\nRMAN> connect catalog vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nconnected to recovery catalog database\r\n\r\nRMAN><\/pre>\n
After that, I executed a simple duplicate where I specified that I want to duplicate ORCL19C to DBCLON. The output below is cropped, but the full output can see here in this file (Example-Full-Duplicate-Database-ZDLRA<\/a>). The important here is the auxiliary channel allocation pointing to ZDLRA (and this is crucial, explained later):<\/p>\n
RMAN> run{\r\n2> allocate auxiliary channel RA1 DEVICE TYPE 'SBT_TAPE' PARMS \"SBT_LIBRARY=\/u01\/app\/oracle\/product\/19.3.0.0\/dbhome_1\/lib\/libra.so, ENV=(RA_WALLET='location=file:\/u01\/app\/oracle\/product\/19.3.0.0\/dbhome_1\/dbs\/ra_wallet CREDENTIAL_ALIAS=zdlras1-scan:1521\/zdlras1:VPCBRONZE')\";\r\n3> duplicate database ORCL19C to DBCLON NOFILENAMECHECK;\r\n4> }\r\n\r\nallocated channel: RA1\r\nchannel RA1: SID=38 device type=SBT_TAPE\r\nchannel RA1: RA Library (ZDLRAS1) SID=B4AFD129EAE2193DE053010310ACDCD0\r\n\r\nStarting Duplicate Db at 22\/11\/2020 10:36:50\r\n\r\ncontents of Memory Script:\r\n{\r\n   sql clone \"create spfile from memory\";\r\n}\r\nexecuting Memory Script\r\n...\r\n...\r\ninput datafile copy RECID=7 STAMP=1057142666 file name=\/u01\/app\/oracle\/oradata\/DBCLON\/89CCF7B8BDCC0C30E053010310ACD002\/datafile\/o1_mf_undotbs1_hvndn5kd_.dbf\r\ndatafile 12 switched to datafile copy\r\ninput datafile copy RECID=8 STAMP=1057142666 file name=\/u01\/app\/oracle\/oradata\/DBCLON\/89CCF7B8BDCC0C30E053010310ACD002\/datafile\/o1_mf_users_hvndpbc9_.dbf\r\n\r\ncontents of Memory Script:\r\n{\r\n   Alter clone database open resetlogs;\r\n}\r\nexecuting Memory Script\r\n\r\ndatabase opened\r\n\r\ncontents of Memory Script:\r\n{\r\n   sql clone \"alter pluggable database all open\";\r\n}\r\nexecuting Memory Script\r\n\r\nsql statement: alter pluggable database all open\r\nCannot remove created server parameter file\r\nFinished Duplicate Db at 22\/11\/2020 10:44:37\r\n\r\nRMAN>\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\n[oracle@orcloel7 ~]$<\/pre>\n
After the duplicate, you have the new database DBCLON with the last scn of ORCL19C that is registered at ZDLRA. And the most important<\/strong> is that if connect to the RMAN catalog, there is only one database registered with this DBID:<\/p>\n
[oracle@orcloel7 ~]$ rman target=\/ catalog=vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:46:25 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: ORCL19C (DBID=1487680695)\r\nconnected to recovery catalog database\r\n\r\nRMAN> list db_unique_name of database ORCL19C;\r\n\r\n\r\nList of Databases\r\nDB Key  DB Name  DB ID            Database Role    Db_unique_name\r\n------- ------- ----------------- ---------------  ------------------\r\n19283   ORCL19C  1487680695       PRIMARY          ORCL19C\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\n[oracle@orcloel7 ~]$<\/pre>\n
So, as you can above, The Good is using the duplicate command. We have not registered the double database inside of RMAN\/ZDLRA catalog because the duplicate command + auxiliary channel never upload\/insert new database data inside the tables. Auxiliary channels never upload data to RMAN\/ZDLRA catalog<\/strong>.<\/p>\n
The Bad<\/h2>\n
The Bad is using RESTORE. The process is more complex to do but can also insert bad data inside of the RMAN\/ZDLRA catalog tables and lead you to The Ugly.<\/p>\n
First, remember that inside of the catalog we still have just a database registered with the DBID of t ORCL19c. The process starts with the same pfile that I generated from ORCL19C (source database), and now I created a new spfile and changed some parameters. The most important here is that db_name remains ORCL19C, and the db_unique_name point to the new name DBREST:<\/p>\n
[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ rman target=\/ catalog=vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:56:46 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: ORCL19C (DBID=1487680695)\r\nconnected to recovery catalog database\r\n\r\nRMAN> list db_unique_name of database ORCL19C;\r\n\r\n\r\nList of Databases\r\nDB Key  DB Name  DB ID            Database Role    Db_unique_name\r\n------- ------- ----------------- ---------------  ------------------\r\n19283   ORCL19C  1487680695       PRIMARY          ORCL19C\r\n\r\nRMAN> exit\r\n\r\n\r\nRecovery Manager complete.\r\n[oracle@orcloel7 ~]$ \r\n[oracle@orcloel7 ~]$ cp \/tmp\/pfile.ora \/tmp\/DBREST-pfile.ora\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ vi \/tmp\/DBREST-pfile.ora\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ cat \/tmp\/DBREST-pfile.ora\r\n*.audit_file_dest='\/u01\/app\/oracle\/admin\/DBREST\/adump'\r\n*.audit_trail='db'\r\n*.compatible='19.0.0'\r\n*.control_files='\/u01\/app\/oracle\/oradata\/DBREST\/control01.ctl','\/u01\/app\/oracle\/oradata\/DBREST\/control02.ctl'\r\n*.db_block_size=8192\r\n*.db_create_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_name='ORCL19C'\r\n*.db_unique_name='DBREST'\r\n*.db_recovery_file_dest='\/u01\/app\/oracle\/oradata'\r\n*.db_recovery_file_dest_size=12732m\r\n*.diagnostic_dest='\/u01\/app\/oracle'\r\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRESTXDB)'\r\n*.enable_pluggable_database=true\r\n*.local_listener='LISTENER_ORCL18C'\r\n*.nls_language='AMERICAN'\r\n*.nls_territory='AMERICA'\r\n*.open_cursors=300\r\n*.pga_aggregate_target=1536m\r\n*.processes=300\r\n*.redo_transport_user='VPCSRC'\r\n*.remote_login_passwordfile='EXCLUSIVE'\r\n*.sga_max_size=4096m\r\n*.sga_target=3096m\r\n*.undo_tablespace='UNDOTBS1'\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ mkdir -p \/u01\/app\/oracle\/admin\/DBREST\/adump\r\n[oracle@orcloel7 ~]$\r\n[oracle@orcloel7 ~]$ mkdir \/u01\/app\/oracle\/oradata\/DBREST\/\r\n[oracle@orcloel7 ~]$<\/pre>\n
After that I started the database in nomount state:<\/p>\n
[oracle@orcloel7 ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 11:22:54 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\r\n\r\nConnected to an idle instance.\r\n\r\nSQL> startup nomount pfile = '\/tmp\/DBREST-pfile.ora';\r\nORACLE instance started.\r\n\r\nTotal System Global Area 4294963960 bytes\r\nFixed Size                  9143032 bytes\r\nVariable Size            1694498816 bytes\r\nDatabase Buffers         2583691264 bytes\r\nRedo Buffers                7630848 bytes\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n[oracle@orcloel7 ~]$<\/pre>\n
Since we started the database and still using the db_name parameter with the original on, we need to connect the RMAN catalog to restore the controlfile. Check that the connection is done with \u201ctarget \/<\/strong>\u201d and the report says that we are connected at target database ORCL19C.<\/p>\n
[oracle@orcloel7 ~]$ rman target=\/ catalog=vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 16:05:23 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: ORCL19C (not mounted)\r\nconnected to recovery catalog database\r\n\r\nRMAN><\/pre>\n
After that we can check the last backup of controlfile and retore it:<\/p>\n
RMAN> set dbid=1487680695;\r\n\r\nexecuting command: SET DBID\r\ndatabase name is \"ORCL19C\" and DBID is 1487680695\r\n\r\nRMAN>\r\n\r\n\r\nRMAN> list backup of controlfile completed after \"sysdate - 1\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n21238   Full    18.50M     SBT_TAPE    00:00:01     22\/11\/2020 09:51:06\r\n        BP Key: 21239   Status: AVAILABLE  Compressed: NO  Tag: TAG20201122T095105\r\n        Handle: c-1487680695-20201122-00   Media: Recovery Appliance (ZDLRAS1)\r\n  Control File Included: Ckp SCN: 2824319      Ckp time: 22\/11\/2020 09:51:05\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n21338   Full    18.50M     SBT_TAPE    00:00:02     22\/11\/2020 10:34:15\r\n        BP Key: 21339   Status: AVAILABLE  Compressed: NO  Tag: TAG20201122T103413\r\n        Handle: c-1487680695-20201122-01   Media: Recovery Appliance (ZDLRAS1)\r\n  Control File Included: Ckp SCN: 2832107      Ckp time: 22\/11\/2020 10:32:57\r\n\r\nRMAN> run{\r\n2> ALLOCATE CHANNEL RA1 DEVICE TYPE 'SBT_TAPE' PARMS \"SBT_LIBRARY=\/u01\/app\/oracle\/product\/19.3.0.0\/dbhome_1\/lib\/libra.so, ENV=(RA_WALLET='location=file:\/u01\/app\/oracle\/product\/19.3.0.0\/dbhome_1\/dbs\/ra_wallet CREDENTIAL_ALIAS=zdlras1-scan:1521\/zdlras1:VPCBRONZE')\";\r\n3> restore controlfile from 'c-1487680695-20201122-01';\r\n4> release channel RA1;\r\n5> }\r\n\r\nallocated channel: RA1\r\nchannel RA1: SID=34 device type=SBT_TAPE\r\nchannel RA1: RA Library (ZDLRAS1) SID=B4B46BA594766AFBE053010310AC8517\r\n\r\nStarting restore at 22\/11\/2020 16:06:23\r\n\r\nchannel RA1: restoring control file\r\nchannel RA1: restore complete, elapsed time: 00:00:04\r\noutput file name=\/u01\/app\/oracle\/oradata\/DBREST\/control01.ctl\r\noutput file name=\/u01\/app\/oracle\/oradata\/DBREST\/control02.ctl\r\nFinished restore at 22\/11\/2020 16:06:27\r\n\r\nreleased channel: RA1\r\n\r\nRMAN> alter database mount;\r\n\r\nStatement processed\r\n\r\nRMAN><\/pre>\n
Until now we have the instance DBREST running, but this instance remains in the database ORCL19C due to the db_name parameter used at pfile that was used to start. This is needed because the process to do the clone using restore we rename later the database with \u201cnid\u201d (later when we finish the process). But I will not reach there, the point here is to show the problems to use the restore.<\/p>\n
If you check above, the last command was the \u201calter database mount\u201d, and this triggers The Bad because at this moment the DBREST database will be registered inside of RMAN\/ZDLRA catalog tables. Look below if we connect with the target database ORCL19C at RMAN and try to list of databases:<\/p>\n
[oracle@orcloel7 ~]$ export ORACLE_SID=ORCL19C\r\n[oracle@orcloel7 ~]$ rman target=\/ catalog=vpcbronze\/vpcbronze@zdlras1-scan:1521\/zdlras1\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 16:19:14 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: ORCL19C (DBID=1487680695)\r\nconnected to recovery catalog database\r\n\r\nRMAN> list db_unique_name of database ORCL19C;\r\n\r\nstarting full resync of recovery catalog\r\nfull resync complete\r\n\r\nList of Databases\r\nDB Key  DB Name  DB ID            Database Role    Db_unique_name\r\n------- ------- ----------------- ---------------  ------------------\r\n19283   ORCL19C  1487680695       PRIMARY          ORCL19C\r\n19283   ORCL19C  1487680695       STANDBY          DBREST\r\n\r\nRMAN><\/pre>\n
As you can see, the DBREST was registered as a standby database of the ORCL19C. And this occurred because the process to clone a database with the restore command uses the db_name as ORCL19C at pfile to startup the database. And if we look inside of RMAN\/ZDLRA catalog tables, we can see that a new line was inserted at ra_database_synonym:<\/p>\n
SQL> select DB_UNIQUE_NAME, DBID from ra_database_synonym;\r\n\r\nDB_UNIQUE_NAME             DBID\r\n-------------------- ----------\r\nORCL19C              1487680695\r\nDBREST               1487680695\r\n\r\nSQL> select db_key, name from rc_database;\r\n\r\n    DB_KEY NAME\r\n---------- --------\r\n     19283 ORCL19C\r\n\r\nSQL> select db_key, DB_UNIQUE_NAME from ra_database;\r\n\r\n    DB_KEY DB_UNIQUE_NAME\r\n---------- --------------------\r\n     19283 ORCL19C\r\n\r\nSQL><\/pre>\n
This was caused by the normal way as the restore is done since internally uses the dbms_rcvman.setDatabase and other internal functions to register data at catalog. You can debug the rman and check the difference between the logs in the two options.<\/p>\n
The Ugly<\/h1>\n
The Ugly is a collateral effect of The Bad because there is no official way to clean this bad data inside of RMAN\/ZDLRA tables (I will not cover the unofficial one here). And if you try to delete this DBREST database, this will vanish the ORCL19C because they have the same DBID internally.<\/p>\n
So, if you try to delete the DBREST with the normal dbms_ra.delete_db:<\/p>\n
SQL> BEGIN\r\n  2      DBMS_RA.DELETE_DB (\r\n  3          db_unique_name => 'DBREST'\r\n  4      );\r\n  5  END;\r\n  6  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL><\/pre>\n
This will happens:<\/p>\n
SQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, ERROR_COUNT, INTERRUPT_COUNT, BP_KEY,BS_KEY,DF_KEY,VB_KEY from rasys.ra_task where archived = 'N' order by 5,2,7,10,11,12,13;\r\n\r\n   TASK_ID TASK_TYPE                      STATE                     WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       ERROR_COUNT INTERRUPT_COUNT     BP_KEY     BS_KEY     DF_KEY     VB_KEY\r\n---------- ------------------------------ ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------\r\n     30566 DELETE_DB                      RUNNING                                   19283 ORCL19C                        22-NOV-20 04.51.18.659540 PM +01:00           0               0\r\n\r\nSQL>\r\n<\/pre>\n
As you can see, the database ORCL19C will be deleted as well. And as I explained before, this occurs because both databases will have the same DBID when using the RESTORE. For some period of time, they are basically the same when accessing data from the RMAN catalog. This is ugly, very ugly.<\/p>\n
So, avoid use RESTORE to clone\/duplicate your databases when using ZDLRA. Is safe for you, for your sanity, and for your job.<\/p>\n
\"\"<\/a><\/p>\n
 <\/p>\n
Disclaimer<\/strong>: \u201cThe postings on this site are my own and don\u2019t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.\u201d<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
From time to time we need to clone\/duplicate some databases and we have several ways to do that, most common are duplicate and restore (with a new name) commands. But when using RMAN catalogs we need to take extra care because we can up lose the backups of the entire database because of the wrong […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[29,77,45,91,51],"tags":[157,100,65,158,74],"class_list":["post-837","post","type-post","status-publish","format-standard","hentry","category-database","category-engineeredsystems","category-restore","category-rman","category-zdlra","tag-duplicate","tag-engineered-systems","tag-oracle","tag-restore","tag-zdlra"],"yoast_head":"\nDuplicate, Restore and ZDLRA - Fernando Simon<\/title>\n<meta name=\"description\" content=\"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Duplicate, Restore and ZDLRA - Fernando Simon\" \/>\n<meta property=\"og:description\" content=\"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\" \/>\n<meta property=\"og:site_name\" content=\"Fernando Simon\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-07T23:12:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png\" \/>\n<meta name=\"author\" content=\"Simon\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Simon\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\"},\"author\":{\"name\":\"Simon\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9\"},\"headline\":\"Duplicate, Restore and ZDLRA\",\"datePublished\":\"2020-12-07T23:12:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\"},\"wordCount\":1148,\"commentCount\":16,\"image\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png\",\"keywords\":[\"Duplicate\",\"Engineered Systems\",\"Oracle\",\"Restore\",\"ZDLRA\"],\"articleSection\":[\"Database\",\"Engineered Systems\",\"Restore\",\"RMAN\",\"ZDLRA\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\",\"url\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\",\"name\":\"Duplicate, Restore and ZDLRA - Fernando Simon\",\"isPartOf\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png\",\"datePublished\":\"2020-12-07T23:12:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9\"},\"description\":\"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage\",\"url\":\"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png\",\"contentUrl\":\"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png\",\"width\":625,\"height\":350},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.fernandosimon.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Duplicate, Restore and ZDLRA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#website\",\"url\":\"https:\/\/www.fernandosimon.com\/blog\/\",\"name\":\"Fernando Simon\",\"description\":\"Have you hugged your backup today?\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.fernandosimon.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9\",\"name\":\"Simon\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a3dbc48de62fffb1829befb4a588d789ec6dc5e05977afabb3407a5f37a16482?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a3dbc48de62fffb1829befb4a588d789ec6dc5e05977afabb3407a5f37a16482?s=96&d=mm&r=g\",\"caption\":\"Simon\"},\"sameAs\":[\"http:\/\/www.fernandosimon.com\"],\"url\":\"https:\/\/www.fernandosimon.com\/blog\/author\/simon\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Duplicate, Restore and ZDLRA - Fernando Simon","description":"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/","og_locale":"en_US","og_type":"article","og_title":"Duplicate, Restore and ZDLRA - Fernando Simon","og_description":"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.","og_url":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/","og_site_name":"Fernando Simon","article_published_time":"2020-12-07T23:12:16+00:00","og_image":[{"url":"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png","type":"","width":"","height":""}],"author":"Simon","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Simon","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#article","isPartOf":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/"},"author":{"name":"Simon","@id":"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9"},"headline":"Duplicate, Restore and ZDLRA","datePublished":"2020-12-07T23:12:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/"},"wordCount":1148,"commentCount":16,"image":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage"},"thumbnailUrl":"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png","keywords":["Duplicate","Engineered Systems","Oracle","Restore","ZDLRA"],"articleSection":["Database","Engineered Systems","Restore","RMAN","ZDLRA"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/","url":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/","name":"Duplicate, Restore and ZDLRA - Fernando Simon","isPartOf":{"@id":"https:\/\/www.fernandosimon.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage"},"image":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage"},"thumbnailUrl":"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png","datePublished":"2020-12-07T23:12:16+00:00","author":{"@id":"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9"},"description":"How to correct clone database when using ZDLRA, choosing between DUPLICATE and RESTORE to avoid errors and delete RMAN backups.","breadcrumb":{"@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#primaryimage","url":"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png","contentUrl":"https:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2020\/12\/Three-Paths.png","width":625,"height":350},{"@type":"BreadcrumbList","@id":"https:\/\/www.fernandosimon.com\/blog\/duplicate-restore-and-zdlra\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.fernandosimon.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Duplicate, Restore and ZDLRA"}]},{"@type":"WebSite","@id":"https:\/\/www.fernandosimon.com\/blog\/#website","url":"https:\/\/www.fernandosimon.com\/blog\/","name":"Fernando Simon","description":"Have you hugged your backup today?","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.fernandosimon.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/386da956604bca0d5be5dd52210c1dd9","name":"Simon","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.fernandosimon.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a3dbc48de62fffb1829befb4a588d789ec6dc5e05977afabb3407a5f37a16482?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a3dbc48de62fffb1829befb4a588d789ec6dc5e05977afabb3407a5f37a16482?s=96&d=mm&r=g","caption":"Simon"},"sameAs":["http:\/\/www.fernandosimon.com"],"url":"https:\/\/www.fernandosimon.com\/blog\/author\/simon\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5ofTp-dv","_links":{"self":[{"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/posts\/837","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/comments?post=837"}],"version-history":[{"count":0,"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/posts\/837\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/media?parent=837"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/categories?post=837"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fernandosimon.com\/blog\/wp-json\/wp\/v2\/tags?post=837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}