Recovery and Restore of Oracle Muti-tenant Environment

Scenario: In this scenario we have full backup of entire container and we want to perform point-in-time recovery (PITR) of the entire container on directory structure different than production.

NOTE: The procedure will be the same on Standalone and RAC, the only difference will come in picture in case of RAC that instead of file system you will have to restore and recover on ASM also I will suggest you to set cluster parameter to false in RAC, take one instance down and start restore and recover using single instance and once done start up the other instances.

 

–this will take L0 backup of complete container including all pdbs,controlfile,spfile.

NOTE: Make sure the directory already persist where backup files will be created.

RUN

{

  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch4 DEVICE TYPE DISK;

 

  BACKUP INCREMENTAL LEVEL 0 DATABASE FORMAT ‘/u01/backup/FULLBACKUP/L0_%d_%T_%s_%p.bkp’ TAG ‘CDB_L0’;

  BACKUP ARCHIVELOG ALL FORMAT ‘/u01/backup/FULLBACKUP/ARC_%d_%T_%s_%p.bkp’ TAG ‘ARC_L0’;

  BACKUP CURRENT CONTROLFILE FORMAT ‘/u01/backup/FULLBACKUP/CTRL_%d_%T_%s_%p.bkp’ TAG ‘CTRL_L0’;

  BACKUP SPFILE FORMAT ‘/u01/backup/FULLBACKUP/SPFILE_%d_%T_%s_%p.bkp’ TAG ‘SPFILE_L0’;

 

  RELEASE CHANNEL ch1;

  RELEASE CHANNEL ch2;

  RELEASE CHANNEL ch3;

  RELEASE CHANNEL ch4;

}

 

–this will take L1 backup of complete container including all pdbs,controlfile,spfile.

RUN

{

  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;

  ALLOCATE CHANNEL ch4 DEVICE TYPE DISK;

 

  BACKUP INCREMENTAL LEVEL 1 DATABASE FORMAT ‘/u01/backup/INCREMENTAL/L1_%d_%T_%s_%p.bkp’ TAG ‘CDB_L1’;

  BACKUP ARCHIVELOG ALL FORMAT ‘/u01/backup/INCREMENTAL/ARC_%d_%T_%s_%p.bkp’ TAG ‘ARC_L1’;

  BACKUP CURRENT CONTROLFILE FORMAT ‘/u01/backup/INCREMENTAL/CTRL_%d_%T_%s_%p.bkp’ TAG ‘CTRL_L1’;

  BACKUP SPFILE FORMAT ‘/u01/backup/INCREMENTAL/SPFILE_%d_%T_%s_%p.bkp’ TAG ‘SPFILE_L1’;

 

  RELEASE CHANNEL ch1;

  RELEASE CHANNEL ch2;

  RELEASE CHANNEL ch3;

RELEASE CHANNEL ch4;

}

 

–Backup is copied to the destination server where software only is already installed so we will proceed with the below steps.

sqlplus / as sysdba

SQL> startup nomount pfile=’/tmp/pfile.ora’;             –this pfile is manually created and has just db_name so that to start database in nomount

rman target /

RMAN> restore spfile from ‘/u01/backup/INCREMENTAL/T24_20260224_167_1_SPFILE’;

 

SQL> shu abort;

SQL> startup nomount;

SQL> create pfile=’/tmp/prod_pfile.ora’ from spfile;

[oracle@t24prd tmp]$ vi /tmp/prod_pfile.ora              –edit necessary information in pfile [oracle@t24prd ~]$ sqlplus / as sysdba

SQL> shu abort;

SQL> startup nomount pfile=’/tmp/prod_pfile.ora’;

SQL> create spfile from pfile=’/tmp/prod_pfile.ora’;

SQL> shu abort;

SQL> startup nomount;

 

SQL> show parameter spfile;          –verify instance started from spfile.

 

[oracle@t24prd ~]$ rman target /

RMAN> restore controlfile from ‘/u01/backup/INCREMENTAL/CTRL_T24_20260226_221_1.bkp’;

SQL> ALTER DATABASE MOUNT;

RMAN> catalog start with ‘/u01/backup/FULLBACKUP’;

RMAN> catalog start with ‘/u01/backup/INCREMENTAL’;

 

 

crosscheck backup;

crosscheck archivelog all;

delete expired backup;

delete expired archivelog all;

 

RUN

{

  SET UNTIL TIME “TO_DATE(‘2026-02-26 15:44:50′,’YYYY-MM-DD HH24:MI:SS’)”;

  SET NEWNAME FOR PLUGGABLE DATABASE “CDB$ROOT” TO ‘/u01/databases_realocate/CDBROOT/%f_%b’;

  SET NEWNAME FOR PLUGGABLE DATABASE “PDB$SEED” TO ‘/u01/databases_realocate/pdbseed/%f_%b’;

  SET NEWNAME FOR PLUGGABLE DATABASE PDB1 TO ‘/u01/databases_realocate/PDB1/%f_%b’;

  SET NEWNAME FOR PLUGGABLE DATABASE PDB2 TO ‘/u01/databases_realocate/PDB2/%f_%b’;

 

  RESTORE DATABASE;

  SWITCH DATAFILE ALL;

  RECOVER DATABASE;

}

 

–After restore and recover successfully don’t attempt to open directly because controlfile contains the path of redolog files and temp files from old server so most possibally that path won’t exists here so before opening that rename the redolog files and temp files to correct location.

 

sqlplus / as sysdba

set line 200 pages 200;

col member for a50;

select * from v$logfile;

 

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/redo03.log’ to ‘/u01/databases/redo03.log’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/redo02.log’ to ‘/u01/databases/redo02.log’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/redo01.log’ to ‘/u01/databases/redo01.log’;

 

set line 200 pages 200;

col name for a100;

SELECT file#, name FROM v$tempfile;

 

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/temp01.dbf’ to ‘/u01/databases/temp01.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/pdbseed/temp012026-02-24_14-48-40-183-PM.dbf’ to ‘/u01/databases/pdbseed_temp01.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/PDB1/temp01.dbf’ to ‘/u01/databases/pdb1_temp.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/T24/4B94C712A57610E0E0638A79A8C01473/datafile/o1_mf_temp_nsvop1ks_.tmp’ to ‘/u01/databases/o1_mf_temp_nsvop1ks_.tmp’;

 

–bounce database and closely monitor the alert log file.

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> shu immediate;

SQL> startup;