Restoring a single PDB to a new Container database (Manual)

Sometimes, we may get a need to restore a subset of a CDB to a different host. As the restoration requires only a limited set of PDBs, it is essential to backup and/or restore and do recovery at a faster rate than meddling with the entire CDB.

The setup

PAYROLL PDB is running on an isolated PAYDB CDB.

CDB: PAYDB

PDBSs: 2 (PAYROLL, PDB2)

Version: 19.16

Backup of the PAYDB is available. Backup of PAYDB has been transferred to the target host running 19.25. Note that the backup may only include the backups of the subset. For restoration, we will restore root , PDB$SEED and the required PAYROLL PDB.

Once the CDB is restored and required, it would be in a Restricted state due to the Release update mismatch. We will then relocate the PAYROLL PDB to PROD which is on 19.25 and update using datapatch.

Source Database

Prepare Destination (DB HOME running 19.25)

1. Create a temporary parameter file

control_files=’+DATA/PROD/CONTROLFILE/paydb.ctl’

db_name=’paydb’

compatible=’19.0.0.0′

sga_target= ‘400M’

enable_pluggable_database=’true’ – set this parameter to avoid below error

diagnostic_dest=’/u02/app/oracle’

enable_pluggable_database

• Start the Instance and exit

• Restore the Controlfile (from the copied backup) and mount the database

• Optionally catalog the backups , if the copied location is different than the source backup location

Restoring and recovering Target CDB

Note: I’ve used an SCN to restore/recover, obtained from the Source CDB

• Start the restoration process and indicate the needed PDB (PAYROLL) along with root and PDB$SEED

• recover the needed PDB (PAYROLL) along with root and PDB$SEED

Note : the warning below is expected as the newly opened CDB is carrying 19.16 when the DB host is having 19.25

• Open the PDBs and verify the non-existence of the excluded PDB (PDB2)

• If tried to open all PDBs, they will be opened (except for skipped PDB2) in with underlying 19.16. However, verify the data is available on PAYROLL as expected.
• Drop meta data of PDB2 (drop pluggable database including datafiles;)

Bringing PAYROLL PDB to latest 19.25

When the database home is already on a latest RU , a restored lower version of PDB on the same home might have to relocate or copy its needed PDB(s) to a new 19.25 CDB.

Moving PDB from Lower Release Update (or Patch Set Update, Bundle Patch) on Same or Higher DB Version (Doc ID 2847234.1)

I will attempt a remote copy.

Below is the output of the PAYROLL DB

Close and open PAYROLL in read only (as we have not enabled archive log mode)

From Target PROD CDB

SQL> create database link link1 connect to c##remote_user

  2  identified by password1 using ‘payroll’;