Restoring Oracle Pluggable Database from RMAN Container Backup

 

** Prerequisites **

• RMAN full or incremental backup is available to restore and recover database.

• Ensure Oracle Home version and patch level is same on target server as source server.

• Check the file system/ASM disk group have sufficient space available on target server. 

• For checking ASM disk group space use the below command

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

** Restoration steps **

   A) Backup files availability and permission  

      1) Ensure the required RMAN backup files have been restored/copied on target server.

      2) Make sure owner and group of backup files are same as Oracle Home's ownership on target server.

  

   B) Configuring files and directories on target server

      1) Updating /etc/oratab by adding a new entry for the auxiliary container      

         vi /etc/oratab

         CHRMSP1:/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_5:N # Line added to test restore

      2) Create Audit Trail File location for the auxiliary container.

 mkdir -p ${ORACLE_BASE}/admin/${CONTAINER}/adump

 cd ${ORACLE_BASE}/admin/${CONTAINER}/adump

         pwd

      3) Create password file

 Set ORACLE HOME

 cd $ORACLE_HOME/dbs

 orapwd file=orapw${ORACLE_SID} password=Temp123sys entries=10 format=12

      4) Create initialization parameter file for auxiliary container (Change parameters' value according 

         to your environment)

cd $ORACLE_HOME/dbs

cat <<EOF >init${ORACLE_SID}.ora

*.db_name='${CONTAINER}'

*.db_unique_name='${CONTAINER}'

*.remote_login_passwordfile=EXCLUSIVE

*.enable_pluggable_database=TRUE

*.standby_file_management=AUTO

*.sga_target=5G

*.pga_aggregate_target=2G

*.compatible=19.0.0.0

*.db_create_file_dest='+DATA'

*.db_recovery_file_dest='+RECO'

*.db_recovery_file_dest_size=100G

*.db_files=5000

*.undo_tablespace='UNDOTBS1'

EOF

 

   C) Database restoration steps

      1) Start the auxiliary container in nomount mode using the pfile created in section B, step 4

 cd $ORACLE_HOME/dbs

 sqlplus / as sysdba <<EOF

 startup nomount pfile='init${ORACLE_SID}.ora';            

 exit;

 EOF

      2) Create a .rmn file with duplicate command. Spfile parameters needs to change as per the 

         environment.

 vi restore_database.rmn

    run{

 ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;

 ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;

 ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE DISK;

 DUPLICATE database to 'CHRMSP1'

 PLUGGABLE DATABASE P_HRDP, root

 SPFILE

 SET db_name='CHRMSP1'

 SET db_unique_name='CHRMSP1'

 SET CLUSTER_DATABASE='FALSE'

 SET CONTROL_FILES='+DATA/CHRMSP1/CONTROLFILE/CHRMSP1_CTR.ctl'

SET remote_login_passwordfile='EXCLUSIVE'

SET enable_pluggable_database='TRUE'

SET standby_file_management="AUTO"

SET sga_target='5G'

SET pga_aggregate_target='2G'

SET compatible='19.0.0.0'

SET db_create_file_dest='+DATA'

         SET db_create_online_log_dest_1='+RECO'

SET db_recovery_file_dest='+RECO'

SET log_file_name_convert='+FLASH','+RECO'   

SET db_recovery_file_dest_size='500G'

SET db_files='5000'

SET undo_tablespace='UNDOTBS1'   

noopen backup location '/acfs01/backup/db/CHRMSP1';

       }

  3) Execute below command to start duplicate

             Set ORACLE HOME

             nohup rman auxiliary / @restore_database.rmn log=restore_database_1.log &

             Note: We can ignore below oracle error

         Oracle Error:

         ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

         ORA-01157: cannot identify/lock data file 8 - see DBWR trace file

         ORA-01110: data file 8: '+DATA/C_EMAP01/DATAFILE/userstbs.271.954495079'

  4) Open the auxiliary database with resetlogs

             SQL> alter database open resetlogs;

     SQL> select open_mode from v$database;

     SQL> show pdbs;

             SQL> select CREATED from v$database;

  

   D) Configure database for user connection

      1) Register the database with local listener by using below command

 Alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=INDODAPD01.db.test.com)(PORT=1521))' sid='*' scope=both;

      2) Create the service for the PDBs

         Set ORACLE Home

         srvctl add service -db CHRMSP1 -pdb P_HRDP -service P_HRDP_SVC

         srvctl start service -db CHRMSP1 -service P_HRDP_SVC

         srvctl status service -db CHRMSP1

      3) Update the tnsnames.ora for remote connectivity test and test the connection

         cd $ORACLE_HOME/network/admin/

         vi tnsnames.ora

         P_HRDP=

           (DESCRIPTION =

             (ADDRESS = (PROTOCOL = TCP)(HOST =  indodapdb01.db.test.com )(PORT = 1521))

             (CONNECT_DATA =

               (SERVER = DEDICATED)

               (SERVICE_NAME =   P_HRDP)

             )

           )