Zukus and Oracle: Duplicate Point-In-Time Recovered PDB Using Backup

Resources

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 in NOMOUNT 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/TCDB19

Generate 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:     2080

Start 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

Zukus and Oracle

среда, 28 января 2026 г.

Duplicate Point-In-Time Recovered PDB Using Backup

Resources

Subject

Scenario

Objective:

Implementation

Rename recovered PPDB pdb to PPDB_RCVRD

Plug in PDB PPDB as PPDB_RCVRD into dummy orcl-01/TCDB19 instance using existing data files:

Disassemble dummy(temporary) orcl-01/TCDB19 instance

Комментариев нет:

Отправить комментарий

среда, 28 января 2026 г.

Duplicate Point-In-Time Recovered PDB Using Backup

Resources

Subject

Scenario

Objective:

Implementation

Rename recovered PPDB pdb to PPDB_RCVRD

Plug in PDB PPDB as PPDB_RCVRD into dummy orcl-01/TCDB19 instance using existing data files:

Disassemble dummy(temporary) orcl-01/TCDB19 instance

Комментариев нет:

Отправить комментарий

среда, 28 января 2026 г.