Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host

February 3, 2022
()

Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host


In a recent article, we demonstrated about Step by Step RMAN Active Duplicate Database in Oracle from ASM to ASM click here to know more about it. In this article, we will demonstrate step by step Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host with practical.

The Active duplicate database doesn’t require backup. Duplication of database performs over the network copying database files to the auxiliary instance from the live source database which should be either mount or open mode, the preferably open state with zero downtime.

Below are the environment details.

COMPONENTSSOURCETARGET
INSTANCE TYPEMultitenantMultitenant
DATABASE(PDB)DBSGURU_PDB@DBSGURUDBS_PDB1@LABCDB01
STORAGEFile SystemFile System
VERSION19.12.0.0.019.12.0.0.0
OSLinux 8Linux 8


1. Create a table for testing purposes to validate after cloning on a new host.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DBSGURU_PDB                    READ WRITE NO
SQL> alter session set container=DBSGURU_PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DBSGURU_PDB
SQL> create table pdb_tab (name varchar2(31));

Table created.

SQL> insert into pdb_tab values ('DBsGuru');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from pdb_tab;

  COUNT(*)
----------
         1

SQL> select * from pdb_tab;

NAME
-------------------------------
DBsGuru


2. Validate the size of PDB, DBID, creation timestamp, archive enable, etc. Click here to get the SQL command to check the size of DATABASE.

SQL> COL NAME FOR A15
SQL> SELECT NAME,DBID,GUID,CREATION_TIME FROM V$PDBS;

NAME                  DBID GUID                             CREATION_
--------------- ---------- -------------------------------- ---------
DBSGURU_PDB      183645943 C40921A8BBF963F9E055515375A76B71 05-JUN-21

SQL> @PDB_DATABASE_SIZE.sql

DATED     HOST_NAME            INSTANCE_NAME    VERSION           TOTAL_ALLOCATED_MB    USED_MB    FREE_MB
--------- -------------------- ---------------- ----------------- ------------------ ---------- ----------
08-JAN-22 DBsGuruN.dbsguru.com dbsguru          19.0.0.0.0                       954      784.5    152.625

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           5


3. Capture all physical files of PDB. Click here to get the SQL command for How to Find all Physicals Files in Multinanent Architecture CDB and PDB DATABASE in Oracle.

SQL> @PDB_PHYSICAL_FILES.sql

NAME
------------------------------------------------------------
/oradata/DBSGURU/controlfile/control01.ctl
/oradata/DBSGURU/dbsguru_pdb/sysaux01.dbf
/oradata/DBSGURU/dbsguru_pdb/system01.dbf
/oradata/DBSGURU/dbsguru_pdb/temp01.dbf
/oradata/DBSGURU/dbsguru_pdb/users01.dbf
/oradata/DBSGURU/onlinelog/o1_mf_1_jxloxy34_.log
/oradata/DBSGURU/onlinelog/o1_mf_2_jxloxy96_.log
/oradata/DBSGURU/onlinelog/o1_mf_3_jxloxyg5_.log
/u01/DBSGURU/controlfile/control02.ctl

9 rows selected.


4. Create a password file it’s not already available and make sure created with the correct SYS’s password, applicable on both source & target.

[oracle@DBsGuruN dbs]$ orapwd file=orapwdbsguru password=XXXXXXXXXX force=y entries=10 format=12
[oracle@DBsGuruN dbs]$ ls -lrt orapwdbsguru
-rw-r-----. 1 oracle oinstall 2048 Jan  8 14:52 orapwdbsguru

Now login to Target Host and perform the below activities on the target server/database level.

5. Create required Directories.

[oracle@DBsGuruN3 ~]$ mkdir -p /oradata/LABCDB01/dbs_pdb1
[oracle@DBsGuruN3 ~]$ mkdir -p /oradata/RRF
[oracle@DBsGuruN3 ~]$ ls -ld /oradata/LABCDB01/dbs_pdb1 /oradata/RRF
drwxr-xr-x. 2 oracle oinstall 6 Jan  8 16:35 /oradata/LABCDB01/dbs_pdb1
drwxr-xr-x. 2 oracle oinstall 6 Jan  8 16:36 /oradata/RRF


6. Set requires parameter REMOTE_RECOVERY_FILE_DEST.

SQL> show parameter REMOTE_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_recovery_file_dest            string
SQL> alter system set remote_recovery_file_dest='/oradata/RRF' scope=both;

System altered.

SQL> show parameter remote_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_recovery_file_dest            string      /oradata/RRF


7. Prepare TNS entries for both Source and Target Databases.

DBSGURU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN.dbsguru.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbsguru)
    )
  )
 
LABCDB01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN3.labdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = labcdb01)
    )
  )


8. Add both TNS entries in both Source & Target Host into the file $ORACLE_HOME/network/admin/tnsnames.ora and validate it.

===On Source Host

[oracle@DBsGuruN dbs]$ tnsping LABCDB01

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2022 16:44:28

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN3.labdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = labcdb01)))
OK (0 msec)
[oracle@DBsGuruN dbs]$ tnsping DBSGURU

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2022 16:44:41

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN.dbsguru.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBSGURU)))
OK (0 msec)
[oracle@DBsGuruN dbs]$

===On Target Host

[oracle@DBsGuruN3 ~]$ tnsping DBSGURU

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2022 16:43:36

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u02/app/oracle/product/19c/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN.dbsguru.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DBSGURU)))
OK (0 msec)
[oracle@DBsGuruN3 ~]$ tnsping LABCDB01

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2022 16:43:49

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u02/app/oracle/product/19c/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN3.labdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = labcdb01)))
OK (0 msec)

[oracle@DBsGuruN3 ~]$ sqlplus sys@DBSGURU as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 8 16:46:28 2022
Version 19.12.0.0.0

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

Enter password:

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

SQL> SELECT NAME,HOST_NAME FROM V$DATABASE,V$INSTANCE;

NAME      HOST_NAME
--------- ----------------------------------------------------------------
DBSGURU   DBsGuruN.dbsguru.com


9. Connect to RMAN.

[oracle@DBsGuruN3 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 8 16:50:08 2022
Version 19.12.0.0.0

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

RMAN> connect target sys/XXXXXXXXXXX@DBSGURU

connected to target database: DBSGURU (DBID=278540964)

RMAN> connect auxiliary sys/XXXXXXXXXXX@LABCDB01

connected to auxiliary database: LABCDB01 (DBID=3327670761)

RMAN>


10. Validate allocate target and auxiliary channels before moving to actual active duplication for PDB, this is an optional step.

RMAN> run{
allocate channel C1 type disk;
allocate channel C2 type disk;
allocate channel C3 type disk;
allocate auxiliary channel A1 device type disk;
allocate auxiliary channel A2 device type disk;
allocate auxiliary channel A3 device type disk;
release channel C1;
release channel C2;
release channel C3;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=460 device type=DISK

allocated channel: C2
channel C2: SID=2 device type=DISK

allocated channel: C3
channel C3: SID=3 device type=DISK

allocated channel: A1
channel A1: SID=132 device type=DISK

allocated channel: A2
channel A2: SID=390 device type=DISK

allocated channel: A3
channel A3: SID=137 device type=DISK

released channel: C1

released channel: C2

released channel: C3
released channel: A1
released channel: A2
released channel: A3

RMAN>


11. Perform Active Duplicate Pluggable Database connected to RMAN session.

RMAN> duplicate pluggable database DBSGURU_PDB as DBS_PDB1 to LABCDB01 db_file_name_convert('/oradata/DBSGURU/dbsguru_pdb/','/oradata/LABCDB01/dbs_pdb1/') from active database nofilenamecheck;

Starting Duplicate PDB at 08-JAN-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=132 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=390 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=137 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=129 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate PDB command at 01/08/2022 16:58:50
RMAN-05501: aborting duplication of target database
RMAN-05654: target database local undo not enabled

RMAN>


Oops…. you can see above RMAN duplicate pluggable database command failed due to the Local Undo mode is not enabled in original source database. I followed this article How to Enable Local UNDO Mode in CDB-PDB Multitenant in Oracle to resolve this error and then execute the duplicate command.

[oracle@DBsGuruN3 ~]$ rman

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 8 16:50:08 2022
Version 19.12.0.0.0

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

RMAN> connect target sys/XXXXXXXXXXX@DBSGURU

connected to target database: DBSGURU (DBID=278540964)

RMAN> connect auxiliary sys/XXXXXXXXXXX@LABCDB01

connected to auxiliary database: LABCDB01 (DBID=3327670761)
 
RMAN> duplicate pluggable database DBSGURU_PDB as DBS_PDB1 to LABCDB01 db_file_name_convert('/oradata/DBSGURU/dbsguru_pdb/','/oradata/LABCDB01/dbs_pdb1/') from active database nofilenamecheck;

Starting Duplicate PDB at 08-JAN-22
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
current log archived
duplicating Online logs to Oracle Managed File (OMF) location
current log archived

contents of Memory Script:
{
   set newname for datafile  9 to
 "/oradata/LABCDB01/dbs_pdb1/system01.dbf";
   set newname for datafile  10 to
 "/oradata/LABCDB01/dbs_pdb1/sysaux01.dbf";
   set newname for datafile  11 to
 "/oradata/LABCDB01/dbs_pdb1/users01.dbf";
   set newname for datafile  13 to
 "/oradata/LABCDB01/dbs_pdb1/system01_i1_undo.dbf";
   restore
   from  nonsparse   clone foreign pluggable database
    "DBSGURU_PDB"
   from service  'DBSGURU'   ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-JAN-22
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service DBSGURU
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using network backup set from service DBSGURU
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using network backup set from service DBSGURU
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using network backup set from service DBSGURU
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring foreign file 11 to /oradata/LABCDB01/dbs_pdb1/users01.dbf
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_4: restoring foreign file 13 to /oradata/LABCDB01/dbs_pdb1/system01_i1_undo.dbf
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: restoring foreign file 9 to /oradata/LABCDB01/dbs_pdb1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_2: restoring foreign file 10 to /oradata/LABCDB01/dbs_pdb1/sysaux01.dbf
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:26
Finished restore at 08-JAN-22

contents of Memory Script:
{
   set archivelog destination to  '/oradata/RRF';
   restore clone force from service  'DBSGURU'
           foreign archivelog from scn  4823584;
}
executing Memory Script

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 08-JAN-22
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oradata/RRF
channel ORA_AUX_DISK_1: using network backup set from service DBSGURU
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_AUX_DISK_2: starting archived log restore to user-specified destination
archived log destination=/oradata/RRF
channel ORA_AUX_DISK_2: using network backup set from service DBSGURU
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_3: starting archived log restore to user-specified destination
archived log destination=/oradata/RRF
channel ORA_AUX_DISK_3: using network backup set from service DBSGURU
channel ORA_AUX_DISK_3: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:00
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:00
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:01
Finished restore at 08-JAN-22

Performing import of metadata...
Finished Duplicate PDB at 08-JAN-22

RMAN>


Congratulation!!! We have completed successfully cloned the pluggable database from source PDB DBSGURU_PDB to target PDB DBS_PDB1 using Active Duplicate in Oracle. Let’s follow the below steps for post validation on Target Host/database.

12. Validate PDB, DBID, creation timestamp, etc. 

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 DBS_PDB1                       READ WRITE NO

SQL> alter session set container=DBS_PDB1;

Session altered.

SQL> COL NAME FOR A15
SQL> SELECT NAME,CON_ID,DBID,GUID,CREATION_TIME FROM V$PDBS;

NAME                CON_ID       DBID GUID                             CREATION_
--------------- ---------- ---------- -------------------------------- ---------
PDB$SEED                 2 1456590033 D45D8E6AC79F4469E0536B38A8C0785A 30-DEC-21
PDB                      3 1647078761 D45DAEDF2FB84E72E0536B38A8C0CD9D 30-DEC-21
DBS_PDB1                 4 2841395684 D511E6D320CE519CE0536B38A8C014A1 08-JAN-22


13. Capture all physical files of cloned PDB. Click here to get the SQL command for How to Find all Physicals Files in Multinanent Architecture CDB and PDB DATABASE in Oracle.

SQL> @PDB_PHYSICAL_FILES.sql

NAME
------------------------------------------------------------
/oradata/LABCDB01/control01.ctl
/oradata/LABCDB01/redo01.log
/oradata/LABCDB01/redo02.log
/oradata/LABCDB01/redo03.log
/oradata/LABCDB01/sysaux01.dbf
/oradata/LABCDB01/system01.dbf
/oradata/LABCDB01/temp01.dbf
/oradata/LABCDB01/undotbs01.dbf
/oradata/LABCDB01/users01.dbf
/oradata/fra/LABCDB01/control02.ctl

10 rows selected.


14. Validate the table as created in step 1.

SQL> alter session set container=DBS_PDB1;

Session altered.

SQL> select * from pdb_tab;

NAME
-------------------------------
DBsGuru


15. Validate the listener status for PDB. 

[oracle@DBsGuruN3 dbs_pdb1]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2022 17:38:56

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN3.labdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                08-JAN-2022 16:08:14
Uptime                    0 days 1 hr. 30 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/product/19c/db_1/network/admin/listener.ora
Listener Log File         /u02/app/oracle/diag/tnslsnr/DBsGuruN3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN3.labdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "d45daedf2fb84e72e0536b38a8c0cd9d" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "d511e6d320ce519ce0536b38a8c014a1" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "dbs_pdb1" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "labcdb01" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "labcdb01XDB" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
  Instance "labcdb01", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@DBsGuruN3 dbs_pdb1]$


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Related Articles

, , , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

🌐

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

Leave a Reply

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