Oracle 19c - Tablespace PITR on Pluggable Database in Multi-Tenant Environment - Pluggable Database PITR Demo!

December 28, 2020  

 

Hi All,

Oracle 19c : Point-In-Time Recovery in a PDB: Point-In-Time Recovery is also possible in a multitenant environment. As in Non-CDB, a recovery catalog can be used or not. In this video we will see how to recover a dropped tablespace in a PDB. We will also see the importance of using a recovery catalog or not. A PITR of a PDB does not affect remaining PBDs. That means that while doing a PITR in PDB, people can use the other PDBs. In this video we are using an oracle 19c database with local undo mode enabled SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED' SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%'; For this demo, we need to fullfil the following -We have a tablespace named "DEMOTBS" -We have a valid backup of the whole database -A recovery catalog is not used SQL> ALTER SESSION SET CONTAINER=OGGTRGPLUG; SQL> create tablespace DEMOTBS datafile '/oradata/OGGTRG/oggtrgplug/demo_tbs01.dbf' size 100M autoextend on; SQL> create user demo identified by demo123 default tablespace DEMOTBS temporary tablespace TEMP; SQL> GRANT CONNECT,RESOURCE to DEMO; Now connecting to the OGGTRGPLUG, let’s drop a tablespace after creating a restore point. SQL> show con_name; CON_NAME ------------------------------ OGGTRGPLUG SQL> create restore point Before_Changes_plug; SQL> drop tablespace demotbs including contents and datafiles; And now let’s perform a PITR to the restore point Before_Changes_plug 1- Connect to the root container [oracle@srlabgg1 admin]$ rman target / RMAN> 2- Close the PDB RMAN> ALTER PLUGGABLE DATABASE OGGTRGPLUG close; RMAN> 3- Do the PITR RMAN> run { SET TO RESTORE POINT oggtrg_change; RESTORE PLUGGABLE DATABASE OGGTRGPLUG; RECOVER PLUGGABLE DATABASE OGGTRGPLUG; } RMAN> 4- Open the PDB on resetlogs mode RMAN> alter pluggable DATABASE OGGTRGPLUG open resetlogs; 2020-12-25T21:23:50.120707+05:30 OGGTRGPLUG(3):drop tablespace TRGDATA including contents and datafiles 2020-12-25T21:24:10.248512+05:30 OGGTRGPLUG(3):Deleted file /oradata/OGGTRG/oggtrgplug/srcdata01.dbf OGGTRGPLUG(3):Completed: drop tablespace TRGDATA including contents and datafiles RMAN> I did not get any error from RMAN, but when looking the alert log file, I have following errors Seems there is some issue with the recovery of DEMOTBS tablespace. Connected to OGGSRCPLUG, I can have SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='TRGDATA'; The tablespace was not recovered as expected. What happens? In fact this issue is expected according Doc ID 2435452.1 where we can find If the point in time recovery of the pluggable database is performed without the catalog, then it is expected to fail As we are not using a recovery catalog, backup information are stored in the control file and it seems that the actual control file is no longer aware of the data file. As specified in the document, we have to use a recovery catalog Now let’s connect to a catalog and do again the same PITR After connecting to the catalog we do a full backup. Then we drop the tablespace and run again the same recovery command while connecting to the catalog. We use the time before the tablespace was dropped. [oracle@srlabgg1 admin]$ rman target / RMAN> connect connect catalog rcat/rcat12345@rcatdb; RMAN> ALTER PLUGGABLE DATABASE OGGTRGPLUG close; After closing GGSRCPLUG we run following block RMAN> run { SET UNTIL TIME "to_date('25-DEC-2020 15:27:00','DD-MON-YYYY HH24:MI:SS')"; RESTORE PLUGGABLE DATABASE OGGTRGPLUG; RECOVER PLUGGABLE DATABASE OGGTRGPLUG; } RMAN> We then open OGGTRGPLUG with resetlogs mode and then verify with sqlplus SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='DEMOTBS'; SQL> And this time the PITR works fine. The tablespace was restored. Conclusion: It is highly recommended to use a recovery catalog when coming to do some PITR operations in a multitenant environment

Hope this helps!

Cheers!

Ramesh.