By Mouhamadou Diaw
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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
1
2
3
4
5
6
7
8
9
10
11
12
|
[oracle@oraadserver ~]$ rman target / [oracle@oraadserver ~]$ 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
1
2
3
4
5
6
|
RMAN> ALTER PLUGGABLE DATABASE PDB2 close ; using target database control file instead of recovery catalog Statement processed RMAN> |
3- Do the PITR
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
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
1
2
3
4
5
|
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
1
2
3
4
5
6
7
|
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
1
2
3
4
5
6
7
8
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
|
[oracle@oraadserver trace]$ rman catalog rman/rman@rmancat 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
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
1
2
3
4
5
6
7
8
9
10
11
|
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.
Moh Amin
17.05.2023Many Thanks , Great Blog