Duplicate, Restore and ZDLRA

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.

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.

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.

The Good

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).

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

oracle@orcloel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 21:21:59 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create pfile = '/tmp/pfile.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ cat /tmp/pfile.ora
ORCL19C.__data_transfer_cache_size=0
ORCL19C.__db_cache_size=2399141888
ORCL19C.__inmemory_ext_roarea=0
ORCL19C.__inmemory_ext_rwarea=0
ORCL19C.__java_pool_size=50331648
ORCL19C.__large_pool_size=16777216
ORCL19C.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL19C.__pga_aggregate_target=1610612736
ORCL19C.__sga_target=3254779904
ORCL19C.__shared_io_pool_size=134217728
ORCL19C.__shared_pool_size=637534208
ORCL19C.__streams_pool_size=0
ORCL19C.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL19C/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL19C/control01.ctl','/u01/app/oracle/oradata/ORCL19C/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='ORCL19C'
*.db_recovery_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL19CXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL18C'
*.log_archive_config='DG_CONFIG=(ORCL19C,ZDLRAS1)'
*.log_archive_dest_2='SERVICE="zdlras1-scan:1521/zdlras1:VPCSRC" SYNC NOAFFIRM DB_UNIQUE_NAME=zdlras1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_state_2='DEFER'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1536m
*.processes=300
*.redo_transport_user='VPCSRC'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=4096m
*.sga_target=3096m
*.undo_tablespace='UNDOTBS1'
[oracle@orcloel7 ~]$

And if I connect to RMAN catalog, I can see that exists only one database registered with this name:

[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 17 21:35:12 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19C (DBID=1487680695)
connected to recovery catalog database

RMAN> list db_unique_name of database ORCL19C;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
15917   ORCL19C  1487680695       PRIMARY          ORCL19C

RMAN> exit


Recovery Manager complete.
[oracle@orcloel7 ~]$

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).

[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ cp /tmp/pfile.ora /tmp/DBCLON-pfile.ora
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ vi /tmp/DBCLON-pfile.ora
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ cat /tmp/DBCLON-pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/DBCLON/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/DBCLON/control01.ctl','/u01/app/oracle/oradata/DBCLON/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='DBCLON'
*.db_recovery_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBCLONXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL18C'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1536m
*.processes=300
*.redo_transport_user='VPCSRC'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=4096m
*.sga_target=3096m
*.undo_tablespace='UNDOTBS1'
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ mkdir /u01/app/oracle/admin/DBCLON/adump -p
[oracle@orcloel7 ~]$

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:

[oracle@orcloel7 ~]$ export ORACLE_SID=DBCLON
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 10:30:29 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile = '/tmp/DBCLON-pfile.ora';
ORACLE instance started.

Total System Global Area 4294963960 bytes
Fixed Size                  9143032 bytes
Variable Size            1694498816 bytes
Database Buffers         2583691264 bytes
Redo Buffers                7630848 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@orcloel7 ~]$

Now, I can use the RMAN to do the duplicate. First, check that the auxiliary is local DBCLON connection with “connect auxiliary /”, and one catalog connection with “connect catalog vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1”:

[oracle@orcloel7 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:31:00 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> 

RMAN> connect auxiliary /

connected to auxiliary database: DBCLON (not mounted)

RMAN> connect catalog vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

connected to recovery catalog database

RMAN>

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). The important here is the auxiliary channel allocation pointing to ZDLRA (and this is crucial, explained later):

RMAN> run{
2> 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')";
3> duplicate database ORCL19C to DBCLON NOFILENAMECHECK;
4> }

allocated channel: RA1
channel RA1: SID=38 device type=SBT_TAPE
channel RA1: RA Library (ZDLRAS1) SID=B4AFD129EAE2193DE053010310ACDCD0

Starting Duplicate Db at 22/11/2020 10:36:50

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
...
...
input datafile copy RECID=7 STAMP=1057142666 file name=/u01/app/oracle/oradata/DBCLON/89CCF7B8BDCC0C30E053010310ACD002/datafile/o1_mf_undotbs1_hvndn5kd_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=8 STAMP=1057142666 file name=/u01/app/oracle/oradata/DBCLON/89CCF7B8BDCC0C30E053010310ACD002/datafile/o1_mf_users_hvndpbc9_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Cannot remove created server parameter file
Finished Duplicate Db at 22/11/2020 10:44:37

RMAN>

RMAN> exit


Recovery Manager complete.
[oracle@orcloel7 ~]$

After the duplicate, you have the new database DBCLON with the last scn of ORCL19C that is registered at ZDLRA. And the most important is that if connect to the RMAN catalog, there is only one database registered with this DBID:

[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:46:25 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19C (DBID=1487680695)
connected to recovery catalog database

RMAN> list db_unique_name of database ORCL19C;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
19283   ORCL19C  1487680695       PRIMARY          ORCL19C

RMAN> exit


Recovery Manager complete.
[oracle@orcloel7 ~]$

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.

The Bad

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.

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:

[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 10:56:46 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19C (DBID=1487680695)
connected to recovery catalog database

RMAN> list db_unique_name of database ORCL19C;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
19283   ORCL19C  1487680695       PRIMARY          ORCL19C

RMAN> exit


Recovery Manager complete.
[oracle@orcloel7 ~]$ 
[oracle@orcloel7 ~]$ cp /tmp/pfile.ora /tmp/DBREST-pfile.ora
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ vi /tmp/DBREST-pfile.ora
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ cat /tmp/DBREST-pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/DBREST/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/DBREST/control01.ctl','/u01/app/oracle/oradata/DBREST/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='ORCL19C'
*.db_unique_name='DBREST'
*.db_recovery_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRESTXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL18C'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1536m
*.processes=300
*.redo_transport_user='VPCSRC'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=4096m
*.sga_target=3096m
*.undo_tablespace='UNDOTBS1'
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ mkdir -p /u01/app/oracle/admin/DBREST/adump
[oracle@orcloel7 ~]$
[oracle@orcloel7 ~]$ mkdir /u01/app/oracle/oradata/DBREST/
[oracle@orcloel7 ~]$

After that I started the database in nomount state:

[oracle@orcloel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 11:22:54 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile = '/tmp/DBREST-pfile.ora';
ORACLE instance started.

Total System Global Area 4294963960 bytes
Fixed Size                  9143032 bytes
Variable Size            1694498816 bytes
Database Buffers         2583691264 bytes
Redo Buffers                7630848 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@orcloel7 ~]$

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 “target /” and the report says that we are connected at target database ORCL19C.

[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 16:05:23 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19C (not mounted)
connected to recovery catalog database

RMAN>

After that we can check the last backup of controlfile and retore it:

RMAN> set dbid=1487680695;

executing command: SET DBID
database name is "ORCL19C" and DBID is 1487680695

RMAN>


RMAN> list backup of controlfile completed after "sysdate - 1";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
21238   Full    18.50M     SBT_TAPE    00:00:01     22/11/2020 09:51:06
        BP Key: 21239   Status: AVAILABLE  Compressed: NO  Tag: TAG20201122T095105
        Handle: c-1487680695-20201122-00   Media: Recovery Appliance (ZDLRAS1)
  Control File Included: Ckp SCN: 2824319      Ckp time: 22/11/2020 09:51:05

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
21338   Full    18.50M     SBT_TAPE    00:00:02     22/11/2020 10:34:15
        BP Key: 21339   Status: AVAILABLE  Compressed: NO  Tag: TAG20201122T103413
        Handle: c-1487680695-20201122-01   Media: Recovery Appliance (ZDLRAS1)
  Control File Included: Ckp SCN: 2832107      Ckp time: 22/11/2020 10:32:57

RMAN> run{
2> 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')";
3> restore controlfile from 'c-1487680695-20201122-01';
4> release channel RA1;
5> }

allocated channel: RA1
channel RA1: SID=34 device type=SBT_TAPE
channel RA1: RA Library (ZDLRAS1) SID=B4B46BA594766AFBE053010310AC8517

Starting restore at 22/11/2020 16:06:23

channel RA1: restoring control file
channel RA1: restore complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/DBREST/control01.ctl
output file name=/u01/app/oracle/oradata/DBREST/control02.ctl
Finished restore at 22/11/2020 16:06:27

released channel: RA1

RMAN> alter database mount;

Statement processed

RMAN>

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 “nid” (later when we finish the process). But I will not reach there, the point here is to show the problems to use the restore.

If you check above, the last command was the “alter database mount”, 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:

[oracle@orcloel7 ~]$ export ORACLE_SID=ORCL19C
[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 22 16:19:14 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19C (DBID=1487680695)
connected to recovery catalog database

RMAN> list db_unique_name of database ORCL19C;

starting full resync of recovery catalog
full resync complete

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
19283   ORCL19C  1487680695       PRIMARY          ORCL19C
19283   ORCL19C  1487680695       STANDBY          DBREST

RMAN>

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:

SQL> select DB_UNIQUE_NAME, DBID from ra_database_synonym;

DB_UNIQUE_NAME             DBID
-------------------- ----------
ORCL19C              1487680695
DBREST               1487680695

SQL> select db_key, name from rc_database;

    DB_KEY NAME
---------- --------
     19283 ORCL19C

SQL> select db_key, DB_UNIQUE_NAME from ra_database;

    DB_KEY DB_UNIQUE_NAME
---------- --------------------
     19283 ORCL19C

SQL>

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.

The Ugly

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.

So, if you try to delete the DBREST with the normal dbms_ra.delete_db:

SQL> BEGIN
  2      DBMS_RA.DELETE_DB (
  3          db_unique_name => 'DBREST'
  4      );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

This will happens:

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;

   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
---------- ------------------------------ ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------
     30566 DELETE_DB                      RUNNING                                   19283 ORCL19C                        22-NOV-20 04.51.18.659540 PM +01:00           0               0

SQL>

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.

So, avoid use RESTORE to clone/duplicate your databases when using ZDLRA. Is safe for you, for your sanity, and for your job.

 

Disclaimer: “The postings on this site are my own and don’t 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.”

2 thoughts on “Duplicate, Restore and ZDLRA

  1. LUCAS SANTOS BRAUER

    Hi Fernando,
    For me one thing wasn’t clear…This is just a ugly information or you have a Backup Issue that has to repair ?

    Good article, congrats!!!

    Reply
    1. Simon Post author

      Hi,
      The point is that if you use RESTORE to duplicate/clone your database you add bad information to RMAN Catalog. Two databases became registered with the same DBID inside of the catalog (and ZDLRA tables).
      And if you try to clean this bad database name, it deletes the other database too because has the same DBID. Thi sis the ugly part.
      There is no problem/backup issue. Is this understand the right command to do what. RESTORe is to restore the database. DUPLICATE you clone/duplicate the database with another name.
      You’re welcome for the comment and feedback.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *