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 blog 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 blog we are using an oracle 19c database with local undo mode enabled
SQL> 1 SELECT property_name, property_value 2 FROM database_properties 3* WHERE property_name = 'LOCAL_UNDO_ENABLED' PROPERTY_NAME PROPE -------------------- ----- LOCAL_UNDO_ENABLED TRUE SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%'; CON_ID TABLESPACE_NAME ---------- ------------------------------ 3 UNDOTBS1 4 UNDOTBS1 1 UNDOTBS1 SQL>
We suppose that
-We have a tablespace named MYTABPDB2
-We have a valid backup of the whole database
-A recovery catalog is not used
Now connecting to the PDB2, let’s drop a tablespace after creating a restore point.
SQL> show con_name; CON_NAME ------------------------------ PDB2 SQL> create restore point myrestpoint; Restore point created. SQL> SQL> drop tablespace mytabpdb2 including contents and datafiles; Tablespace dropped. SQL>
And now let’s perform a PITR to the restore point myrestpoint
1- Connect to the root container
[[email protected] ~]$ rman target / [[email protected] ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 20 13:07:07 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1546409981) RMAN>
2- Close the PDB
RMAN> ALTER PLUGGABLE DATABASE PDB2 close; using target database control file instead of recovery catalog Statement processed RMAN>
3- Do the PITR
RMAN> run { SET TO RESTORE POINT myrestpoint; RESTORE PLUGGABLE DATABASE pdb2; RECOVER PLUGGABLE DATABASE pdb2; }2> 3> 4> 5> 6> executing command: SET until clause Starting restore at 20-SEP-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=54 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T141945_gr9jzry9_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T141945_gr9jzry9_.bkp tag=TAG20190920T141945 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 20-SEP-19 Starting recover at 20-SEP-19 current log archived using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 20-SEP-19 RMAN>
4- Open the PDB on resetlogs mode
RMAN> alter pluggable DATABASE pdb2 open resetlogs; Statement processed RMAN>
I did not get any error from RMAN, but when looking the alert log file, I have following errors
PDB2(4):Pluggable database PDB2 dictionary check beginning PDB2(4):Tablespace 'MYTABPDB2' #7 found in data dictionary, PDB2(4):but not in the controlfile. Adding to controlfile. PDB2(4):File #25 found in data dictionary but not in controlfile. PDB2(4):Creating OFFLINE file 'MISSING00025' in the controlfile. PDB2(4):Pluggable Database PDB2 Dictionary check complete PDB2(4):Database Characterset for PDB2 is AL32UTF8
Seems there is some issue with the recovery of MYTABPDB2 tablespace. Connected to PDB2 I can have
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MYTABPDB2'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /u01/app/oracle/product/19.0.0/dbhome_3/dbs/MISSING00025 MYTABPDB2
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 25.
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.
[[email protected] trace]$ rman catalog rman/[email protected] Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 20 15:28:29 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> connect target / connected to target database: ORCL (DBID=1546409981)
After closing PDB2 we run following bloc
RMAN> run { SET UNTIL TIME "to_date('20-SEP-2019 15:27:00','DD-MON-YYYY HH24:MI:SS')"; RESTORE PLUGGABLE DATABASE pdb2; RECOVER PLUGGABLE DATABASE pdb2; } 2> 3> 4> 5> 6> executing command: SET until clause Starting restore at 20-SEP-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T152554_gr9nws0x_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T152554_gr9nws0x_.bkp tag=TAG20190920T152554 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 datafile 26 switched to datafile copy input datafile copy RECID=5 STAMP=1019489668 file name=/u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf Finished restore at 20-SEP-19 starting full resync of recovery catalog full resync complete Starting recover at 20-SEP-19 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 20-SEP-19 RMAN>
We then open PDB2 with resetlogs mode and then verify with sqlplus
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MYTABPDB2'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ /u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf MYTABPDB2 SQL>
And this time the PITR works fine. The tablespace was restored.
Conclusion
As seen in this blog, it is recommended to use a recovery catalog when coming to do some PITR operations in a multitenant environment.
Hi Mouhamadou!
Thanks for this post, this will help to know.
Have you ever succesfully restored and recovered a PDB to a point BETWEEN two “pdb open resetlogs” actions?
I did a straight testcase.
PDB-PITR to 12:30
open resetlogs
-> Works
PDB-PITR to 12:20
open resetlogs
-> Works
PDB-PITR to 12:25
open resetlogs
-> Doesn’t work
-> ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.
Oracle Support is (as usual) not very helpful…
Hi Andreas
Thanks
No I did not test this yet. Will try when get a moment.
Then will let you know
Regards