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.
