Resources
- Backup and Recovery User's Guide - 25 Duplicating Databases
- red-gate.com - Duplicate Point-In-Time Recovered PDB Using Backup
Subject
At times, we might need to duplicate a production pluggable database to a Past Point in Time- in order to:
- Refresh Test/Dev environment
- Perform testing
Table Point-in-Time Recovery requires Enterprise Edition, therefore sometimes we need have scenario how to restore table in PDB on our SE2 instances
RMAN-06455: Table Point-in-Time Recovery requires Enterprise Edition- Generate reports, etc.
Scenario
Source: orcl-01/CDB19 instance with PDBs: PPDB, TPDB, PDB$SEED Destination: orcl-01/TCDB19 dummy(temporary) created instance for recover all PDBs from backup of orcl-01/CDB19 instance- In order to duplicate a PDB, an auxiliary instance has to be created as a CDB on the same or different host.
- The PDB can be restored / recovered point-in-time using older backups of a production database and the required archive logs so as not to affect the production database.
- On duplication of PDB(s), RMAN duplicates the root (CDB$ROOT) and the seed database (PDB$SEED) as well.
- The resulting duplicate database is a fully-functional CDB that contains the root, the seed database, and the duplicated PDBs.
- Subsequently, the recovered PDB may be plugged into another CDB (e.g. to source orcl-01/CDB19 as PPDB_RCVRD pdb in case if planned remain PDB for a long time of testing/generating reports and etc.)
Objective:
We need to perform Point-In-Time Restoration (PITR) on orcl-01/CDB19/PPDB pdb and then plug it as PPDB_RCVRD pdb into dummy(temporary) orcl-01/TCDB19 instance using backups and archive logs of orcl-01/CDB19 instance.Implementation
Prepare pfile and start the auxiliary instance of orcl-01/TCDB19 inNOMOUNT mode using a pfile that includes the declaration:
enable_pluggable_database=TRUE show parameter compatible='19.0.0' sga_max_size and sga_target less than on orcl-01/CDB19 and available on host orcl-01 local_listener='LISTENER_CDB19' will used, because it primary listener on orcl-01 log_archive_dest_1 don't forget specify to /arch/TCDB19 disk to prevent creating archivelogs in the default dir /opt/oracle/db19c/dbs
su - mkdir -p /data/TCDB19 chown oracle:oinstall /data/TCDB19 mkdir -p /arch/TCDB19 chown oracle:oinstall /arch/TCDB19Generate pfile from source orcl-01/CDB19 instance for take into account all configured parameters in template of pfile for auxiliary instance of orcl-01/TCDB19:
su - oracle ORACLE_HOME=/opt/oracle/db19c ORACLE_SID=CDB19 sqlplus / as sysdba SQL> create pfile from spfile; !ls -lht /opt/oracle/db19c/dbs | head -3 exit cat /opt/oracle/db19c/dbs/initCDB19.ora
cat > /opt/oracle/db19c/dbs/initTCDB19.ora
*.compatible='19.0.0' *.control_files='/data/TCDB19/control01.ctl' *.cpu_count=5 *.db_block_size=8192 *.db_create_file_dest='/data' *.db_name='TCDB19' *.dispatchers='(PROTOCOL=TCP) (SERVICE=TCDB19XDB)' *.enable_pluggable_database=true *.filesystemio_options='SETALL' *.global_names=FALSE *.local_listener='LISTENER_CDB19' *.processes=200 *.remote_login_passwordfile='EXCLUSIVE' *.sga_max_size=10G *.sga_target=10G *.diagnostic_dest='/opt/oracle' *.use_large_pages='ONLY' *.log_archive_dest_1 = 'LOCATION=/arch/TCDB19 mandatory reopen=20'Start the auxiliary instance of orcl-01/TCDB19 in
NOMOUNT mode using a pfile
export ORACLE_HOME=/opt/oracle/db19c export ORACLE_SID=TCDB19 # Made sure that we have Huge pages enough to start instance with 12G sga grep Huge /proc/meminfo AnonHugePages: 0 kB HugePages_Total: 36876 HugePages_Free: 8201 sqlplus / as sysdba Connected to an idle instance. SYS@TCDB19:SQL> startup nomount pfile='/opt/oracle/db19c/dbs/initTCDB19.ora'; SYS@TCDB19:SQL> !grep Huge /proc/meminfo AnonHugePages: 0 kB HugePages_Total: 36876 HugePages_Free: 2080Start the
RMAN client and connect to the auxiliary instance as AUXILIARY:
#export ORACLE_SID=TCDB19 echo $ORACLE_SID export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS' rman auxiliary sys/oracle catalog rmancat@rmandb connected to auxiliary database: TCDB19 (not mounted) RMAN>Potential issues:
# RMAN-06617 [Doc ID 1646262.1|https://support.oracle.com/epmos/faces/DocumentDisplay?id=1646262.1] # LIST BACKUP OF ARCHIVELOG ALL; # RMAN-20005: target database name is ambiguous # export ORACLE_SID=CDB19 # rman target=/ catalog rmancat@rmandb # connected to target database: CDB19 (DBID=3865731291) # RMAN> list db_unique_name all; # RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command # sqlplus / as sysdba # SYS@TCDB19:SQL> # select status from v$instance; # shutdown immediate; # startup nomount pfile='/opt/oracle/db19c/dbs/initTCDB19.ora'; # exit rman auxiliary sys/oracle catalog rmancat@rmandb connected to auxiliary database: TCDB19 (not mounted) RMAN>
RUN {
set until time "to_date('Jan 29 2026 04:00:00','Mon DD YYYY HH24:MI:SS')";
duplicate database 'CDB19' dbid 3865731291 to 'TCDB19' noopen backup location '/db_backup/CDB19/rman/backup' pluggable database PPDB;
}
🛈 dbid 3865731291 was specified for orcl-01/CDB19 instance because in RMAN catalog registered several instances with the same name
e.g. orcl-01/CDB19 & orcl-stage-02/CDB19 therefore issued RMAN-20005: target database name is ambiguous above, so need to specify dbid
🛈 Example duplication orcl-01/CDB19 CDB with CDB$ROOT, PDB$SEED and all PDBs if require to PITR CDB and all PDBs
#duplicate database 'CDB19' to 'TCDB19' noopen backup location '/backup/CDB19/rman/backup'Open the duplicate database with the RESETLOGS option:
#export ORACLE_HOME=/opt/oracle/db19C
export ORACLE_SID=TCDB19
sqlplus / as sysdba
SYS@TCDB119:SQL>
select status from v$instance;
alter database open resetlogs;
show pdbs;
SYS@TCDB19:SQL>
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PPDB MOUNTED
4 TPDB MOUNTED
Rename recovered PPDB pdb to PPDB_RCVRD
🛈 Reason: In case if we open PITR pdb with old name the listener will register second service PPDB as for source(production) PPDB pdb,
and this may cause create connections from applications to temporary recovered PDB, that's why it must be renamed to prevent such collision
SYS@TCDB19:SQL> -- Unplug and drop PPDB pdb from dummy orcl-01/TCDB19 instance while retaining its data files ALTER PLUGGABLE DATABASE PPDB CLOSE; ALTER PLUGGABLE DATABASE PPDB UNPLUG INTO '/data/TCDB19/ppdb_rcvrd.xml'; DROP PLUGGABLE DATABASE PPDB KEEP DATAFILES;
🛈 We issued rman duplicate just for orcl-01/CDB19/PPDB pdb, so others pdbs (TPDBS) can be removed from destination dummy(temporary) orcl-01/TCDB19 instance:
--ALTER PLUGGABLE DATABASE TPDB UNPLUG INTO '/data/TCDB19/tpdb_rcvrd.xml'; -- ORA-01173: data dictionary indicates missing data file from system tablespace --DROP PLUGGABLE DATABASE TPDB KEEP DATAFILES; -- Therefore can be deleted from dummy orcl-01/TCDB19 instance DROP PLUGGABLE DATABASE TPDB INCLUDING DATAFILES;
Plug in PDB PPDB as PPDB_RCVRD into dummy orcl-01/TCDB19 instance using existing data files:
CREATE PLUGGABLE DATABASE PPDB_RCVRD USING '/data/TCDB19/ppdb_rcvrd.xml' NOCOPY TEMPFILE REUSE; alter pluggable database PPDB_RCVRD open; alter pluggable database PPDB_RCVRD save state; alter session set container=PPDB_RCVRD;
🛈 Subsequently, the recovered PDB may be plugged into another CDB instead of dummy(temporary) orcl-01/TCB19 instance, e.g. to source orcl-01/CDB19 as PPDB_RCVRD pdb in case if planned remain PDB for a long time of testing/generating reports and etc. So on orcl-01/CDB19 instance must have free PDB slot according SE2 license, therefore:
🛈 Unplug and drop PDB3 pdb from production orcl-01/CDB19 instance while retaining its data files to free PDB slot for PPDB_RCVRD PDB
export ORACLE_HOME=/opt/oracle/db19c export ORACLE_SID=CDB19 sqlplus / as sysdba SYS@CDB19:SQL> ALTER PLUGGABLE DATABASE PDB3 CLOSE; ALTER PLUGGABLE DATABASE PDB3 UNPLUG INTO '/data/CDB19/pdb3.xml'; DROP PLUGGABLE DATABASE PDB3 KEEP DATAFILES; --DROP PLUGGABLE DATABASE PDB3 INCLUDING DATAFILES;Plug in PDB
PPDB as PPDB_RCVRD into destination production orcl-01/CDB19 instance using existing data files:
export ORACLE_HOME=/opt/oracle/db19c export ORACLE_SID=CDB19 sqlplus / as sysdba SYS@CDB19:SQL> show pdbs; CREATE PLUGGABLE DATABASE PPDB_RCVRD USING '/data/TCDB19/ppdb_rcvrd.xml' NOCOPY TEMPFILE REUSE; alter pluggable database PPDB_RCVRD open; alter pluggable database PPDB_RCVRD save state; alter session set container=PPDB_RCVRD;
Disassemble dummy(temporary) orcl-01/TCDB19 instance
🛈 Use DBCA or do the same as removing the datafiles, redo logs, controlfiles manually:
export ORACLE_HOME=/opt/oracle/db19c export ORACLE_SID=TCDB19 grep Huge /proc/meminfo # HugePages_Total: 36876 # HugePages_Free: 2080 vi /opt/oracle/db19c/dbs/initTCDB19.ora # *.sga_max_size=1G # *.sga_target=1G sqlplus / as sysdba SYS@TCDB19:SQL> DROP PLUGGABLE DATABASE PPDB_RCVRD INCLUDING DATAFILES; shutdown immediate --startup mount exclusive restrict startup mount pfile='/opt/oracle/db19c/dbs/initTCDB19.ora' exclusive restrict; !grep Huge /proc/meminfo # HugePages_Total: 36876 # HugePages_Free: 1570 exit
du -hs /data/TCDB19 # 14G rman target / # connected to target database: TCDB19 (DBID=xxxxxxx, not open) drop database including backups noprompt; # database name is "TCDB19" and DBID is xxxxxxxx # # using target database control file instead of recovery catalog # # backup piece handle=/opt/oracle/db19c/dbs/c-xxxxxxxxx-20250202-00 RECID=1 STAMP=1192043760 # Deleted 1 objects # # database name is "TCDB19" and DBID is xxxxxxxxx # database dropped exit
du -hs /data/TCDB19 # 4.0K du -hs /arch/TCDB19/ # 4.0K du -hs /opt/oracle/diag/rdbms/tcdb19 # 18M grep Huge /proc/meminfo # HugePages_Total: 36876 # HugePages_Free: 8020 su - #rm -R /data/TCDB19 #rm -R /arch/TCDB19
