Recovery and Restore of Oracle Muti-tenant Environment | Full Backup restore on Different location

 Scenario: In this scenario we have full backup of entire container and we want to restore the entire container on different location or directory structure 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 backup of complete container including all pdbs,controlfile,spfile.

–the below script is just for the sake of knowledge sharing how the backup was taken.

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

 

RUN

{

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c3 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c4 DEVICE TYPE DISK;

 

  BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT ‘/u01/backup/FULLBACKUP/%d_%T_%s_%p_FULL’;

  BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT ‘/u01/backup/FULLBACKUP/%d_%T_%s_%p_ARCH’;

  BACKUP CURRENT CONTROLFILE FORMAT ‘/u01/backup/FULLBACKUP/%d_%T_%s_%p_CTRL’;

  BACKUP SPFILE FORMAT ‘/u01/backup/FULLBACKUP/%d_%T_%s_%p_SPFILE’;

 

  RELEASE CHANNEL c1;

  RELEASE CHANNEL c2;

  RELEASE CHANNEL c3;

  RELEASE CHANNEL c4;

}

 

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/FULLBACKUP/T24_20260224_167_1_SPFILE’;        –restoring spfile from backup you can skip this step as well if you create pfile on production copy it to the target server do necessary changes.

 

SQL> shu abort;

SQL> startup nomount;

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

vi /tmp/prod_pfile.ora        –edit necessary information in pfile

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.

 

rman target /

RMAN> restore controlfile from ‘/u01/backup/FULLBACKUP/T24_20260224_166_1_CTRL’;  –restore controlfile from backup.

SQL> ALTER DATABASE MOUNT;

RMAN> list incarnation;   –check the current incarnation

RMAN> catalog start with ‘/u01/backup/FULLBACKUP’;        –catalog all the required backups.

RMAN> list incarnation;   –-check the current incarnation

 

NOTE: Why did I check the incarnation before and after backup catalog because sometimes what happens is that there are old archivelogs present in the FRA so when we catalog the backup, implicit catalog also runs which catalog the old archivelogs due to which the current incarnation changes and hence we face problems in recovery.

 

crosscheck backup;

crosscheck archivelog all;

delete expired backup;

delete expired archivelog all;

 

–this will restore complete contaniner’s datafiles to dedicated directories i.e pdb files will go to pdb directories and so on.

–but make sure the directories are already created.

 

mkdir -p /u01/databases/CDBROOT

mkdir -p /u01/databases/PDB1

mkdir -p /u01/databases/PDB2

mkdir -p /u01/databases/pdbseed

chown -R oracle:oinstall /u01/databases

 

RUN {

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c3 DEVICE TYPE DISK;

  ALLOCATE CHANNEL c4 DEVICE TYPE DISK;

 

# CDB$ROOT

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

# PDB$SEED

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

# PDB1

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

 # PDB2

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

 

  RESTORE DATABASE;

  SWITCH DATAFILE ALL;

  RECOVER DATABASE;

 

  RELEASE CHANNEL c1;

  RELEASE CHANNEL c2;

  RELEASE CHANNEL c3;

  RELEASE CHANNEL c4;

}

 

–after restore and recovery completes we will do post steps

 

sqlplus / as sysdba             –get the location recorded in controlfile for redologs, as we have restored on diff loca so will rename.

SQL> set line 200 pages 200;

SQL> col member for a50;

SQL> select * from v$logfile;

 

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

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

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

 

SQL> set line 200 pages 200;          –get the location of temporary files and rename.

SQL> col name for a100;

SQL> SELECT file#, name FROM v$tempfile;

 

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

SQL> ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/T24/pdbseed/tmp01.dbf’ to ‘/u01/databases/pdbseed_temp01.dbf’;

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

 

SQL> ALTER DATABASE OPEN RESETLOGS;

 

SQL> Shutdown immediate;

SQL> Startup;