In the database versions before 18c, PDB could not be duplicated. Users could only duplicate CDBs. But starting with Oracle database 18c, users can now duplicate one PDB into another CDB. This post gives an example of how to do it.
Example Environment
Here is an example of how to duplicate PDB from one CDB to another CDB. Let’s say that we have the following CDB on both source site and dest site:
Source:
hostname: test001.tst.com CDB: ORC181U01 PDB: pdb01
Dest:
hostname: test002.tst.com CDB: ORC181U02 PDB: pdb99
There is a PDB named as pdb01, we want to duplicate it from host test001 to host test002, into cdb on test002. Create that pdb named as dev.
Below is the summary of steps involved in the conversion:
- Make sure the source database is in archive log mode.
- Configure tnsnames.ora for the source and the dest.
- Check if the network connection is available.
- Ensure that the source and dest have the same sys password.
- On the dest side, create directory used for duplicating.
- Perform duplicate operation via RMAN.
- Confirm whether duplication succeeded.
The detailed steps are as follows:
Step 1. Make sure the source database is in archive log mode.
At source site, execute:
SQL> Archive log list
Result should be like:
SQL> Archive log list Database log mode Archive Mode
Step 2. Configure tnsnames.ora for the source site and the dest site.
On both the source and dest site, edit tnsnames.ora and add entries like the following:
source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test001.tst.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORC181U01.us.oracle.com) ) ) dest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test002.tst.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORC181U02.us.oracle.com) ) )
Step 3. Check if the network connection is available.
On source site:
Assure that all the following command succeeds without error:
$ lsnrctl start $ tnsping source tnsping dest $ sqlplus sys/oracle@source as sysdba $ sqlplus sys/oracle@dest as sysdba
On dest site:
Assure that all the following command succeeds without error:
$lsnrctl start $tnsping source tnsping source $sqlplus sys/oracle@source as sysdba $sqlplus sys/oracle@dest as sysdba
Step 4. Ensure that the source and dest have the same sys password.
Make sure the sys password on both source and destination databases are same. Use the below command to set same passwords.
SQL>Alter user sys identified by;
Step 5. On the dest side, create directory used for duplicating.
Create directory used for storing archive logs when duplicating:
$ mkdir -p /refresh/home/mydata
Set remote_recovery_file_dest parameter using the above directory:
SQL> alter system set remote_recovery_file_dest='/refresh/home/mydata';
Create directory used for storing datafiles of the new PDB:
$ mkdir -p /refresh/64bit/app/oracle/oradata/ORC181U/dev
Step 6. Perform duplicate operation via RMAN.
On source site, open the PDB we want to duplicate:
SQL> alter pluggable database pdb01 open read write;
On dest site, duplicate PDB via RMAN:
$ rman RMAN> connect target sys/oracle@source RMAN> connect auxiliary sys/oracle@dest RMAN> DUPLICATE PLUGGABLE DATABASE pdb01 as dev TO orc181u02 DB_FILE_NAME_CONVERT('pdb01','dev') FROM ACTIVE DATABASE SECTION SIZE 400M;
Step 7. Confirm whether duplication succeeded.
On dest site:
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- --------- 2 PDB$SEED READ ONLY NO 3 PDB99 MOUNTED 4 DEV READ WRITE NO
SQL> select con_id,name from v$datafile where con_id=4; CON_ID NAME --------- ------------------------------------------------------------------------------- 4 /refresh/64bit/app/oracle/oradata/ORC181U/dev/system01.dbf 4 /refresh/64bit/app/oracle/oradata/ORC181U/dev/sysaux01.dbf 4 /refresh/64bit/app/oracle/oradata/ORC181U/dev/undotbs01.dbf
Now we have succeeded duplicated one PDB from source site to dest site.
Oracle Database 18c: Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command
How To Cancel A SQL Query In Oracle Database 18c