An initiative of Mobiliar IT

PDB Point in Time Restore fails under certain conditions

Recently I wanted to perform a PITR of a PDB because during a wrong deployment, the data tablespace was deleted and then recreated. The tablespace was there afterwards, but the developers noticed relatively fast that it was empty ūüôā
Flashback of the PDB was not an option in this case, because a flashback across datafile modifications does not work, but a PITR of the PDB works just as well and is one of the advantages of the Multitenant architecture.

Unfortunately, I noticed that in this case a PITR of the PDB does not work and I had to do a PITR of the whole CDB for a restore which is very unpleasant.
I opened an SR at Oracle Support and it turned out that this is a known bug(27966859) that can be fixed with patch(27855651) .

So if you encounter the same problem then you have the possibility to install the patch on the corresponding Oracle Home before the PITR of the PDB, or you do a PITR of the complete CDB

I’ll show you a little example of when the problem occurs.

Starting Point

We have a pluggable database pdb1 and we create a table t1 on the user tablespace of the pdb. Afterwards we create a full backup of the cdb including the pdb1

SQL> alter session set container=pdb1;
Session altered.

SQL> create table t1(name varchar2(50),id number) tablespace users;
Table created.

SQL> insert into t1 values('Test',1);
1 row created.

SQL> insert into t1 values('Test',2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;
NAME                         ID
-------------------- ----------
Test                          1
Test                          2


oracle@lb1s1000:~/ [do99mm47] rmanch
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 16 09:21:44 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DO99MM47 (DBID=2315758108)
connected to recovery catalog database

RMAN> backup database plus archivelog;

starting full resync of recovery catalog
full resync complete
Starting backup at 2019-01-16_09:22:04
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=9 STAMP=997385606
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.91404.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=31 STAMP=997694524
channel ORA_DISK_1: starting piece 1 at 2019-01-16_09:22:16
channel ORA_DISK_2: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.98507.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_4: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.100551.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_5: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.47852.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_6: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.25257.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_7: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.44651.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_8: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.78607.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_3: finished piece 1 at 2019-01-16_09:22:16
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.13905.997694535 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 2019-01-16_09:22:18
piece handle=+FRA/DO99MM47_06/BACKUPSET/2019_01_16/annnf0_tag20190116t092212_0.65774.997694537 tag=TAG20190116T092212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2019-01-16_09:22:18

Starting backup at 2019-01-16_09:22:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile file number=00005 name=+DATAFS/DO99MM47_06/DATAFILE/undo.2621.997276653
channel ORA_DISK_1: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATAFS/DO99MM47_06/DATAFILE/dba_data.519.997276677
channel ORA_DISK_2: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATAFS/DO99MM47_06/DATAFILE/sysaux.5828.997276653
channel ORA_DISK_3: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATAFS/DO99MM47_06/DATAFILE/system.708.997276649
channel ORA_DISK_4: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_5: starting full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATAFS/DO99MM47_06/7F2EADB3F9387DA6E05382C10B0AD75D/DATAFILE/undo.5826.997276655
channel ORA_DISK_5: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_6: starting full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/undo.517.997694289
channel ORA_DISK_6: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_7: starting full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATAFS/DO99MM47_06/7F2EADB3F9387DA6E05382C10B0AD75D/DATAFILE/system.5827.997276649
channel ORA_DISK_7: starting piece 1 at 2019-01-16_09:22:20
channel ORA_DISK_8: starting full datafile backup set
channel ORA_DISK_7: finished piece 1 at 2019-01-16_09:22:23
piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.21665.997694543 tag=TAG20190116T092218 comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:01
Finished backup at 2019-01-16_09:22:23


Starting backup at 2019-01-16_09:22:23
current log archived
using channel ORA_DISK_1

Starting Control File and SPFILE Autobackup at 2019-01-16_09:22:26
piece handle=+FRA/DO99MM47_06/AUTOBACKUP/2019_01_16/s_997694547.57373.997694549 comment=NONE
Finished Control File and SPFILE Autobackup at 2019-01-16_09:22:28

Now we delete the user tablespace, afterward we recreate it and create a new table t2 on it

oracle@lb1s1000:~/ [do99mm47] sqh
Connected to
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=pdb1;
Session altered.

SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.

SQL> create tablespace users datafile '+DATAFS' size 10M;
Tablespace created.

SQL> create table t2(name varchar2(50),id number) tablespace users;
Table created.

SQL> insert into t2 values('Test',3);
1 row created.

SQL> insert into t2 values('Test',4);
1 row created

SQL> commit;
Commit complete.

SQL> select * from t2;
NAME                                                       ID
-------------------------------------------------- ----------
Test                                                        3
Test                                                        4

At this point we realized that we have done something wrong and decide to restore the pdb1 to that state of the old users TS with the table t1.

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 16 09:35:18 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DO99MM47 (DBID=2315758108)
connected to recovery catalog database

RMAN> run{
alter pluggable database pdb1 close immediate;
SET UNTIL TIME "TO_DATE('16-JAN-2019 09:23:00','DD-MON-YYYY HH24:MI:SS')";
restore pluggable database pdb1;
recover pluggable database pdb1 auxiliary destination='/oradata/aux_dest';
alter pluggable database pdb1 open resetlogs;
}

starting full resync of recovery catalog
full resync complete
Statement processed
starting full resync of recovery catalog
full resync complete

executing command: SET until clause
Starting restore at 2019-01-16_09:38:11

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=570 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 00008 to +DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/system.6483.997694289
channel ORA_DISK_1: reading from backup piece +FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.33943.997694541
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 00009 to +DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/sysaux.518.997694289
channel ORA_DISK_1: reading from backup piece +FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.28873.997694543
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 00010 to +DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/undo.517.997694289
channel ORA_DISK_1: reading from backup piece +FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.90582.997694541
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 00011 to +DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/dba_data.508.997694355
channel ORA_DISK_1: reading from backup piece +FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.26891.997694543
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 00012 to +DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/users.6491.997694405
channel ORA_DISK_1: reading from backup piece +FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.21665.997694543
channel ORA_DISK_1: piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.33943.997694541 tag=TAG20190116T092218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.28873.997694543 tag=TAG20190116T092218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.90582.997694541 tag=TAG20190116T092218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.26891.997694543 tag=TAG20190116T092218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=+FRA/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/BACKUPSET/2019_01_16/nnndf0_tag20190116t092218_0.21665.997694543 tag=TAG20190116T092218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2019-01-16_09:38:24
Starting recover at 2019-01-16_09:38:25
current log archived
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019-01-16_09:38:28
Statement processed
starting full resync of recovery catalog
full resync complete

As you can see there are no Errors/Warnings during the PITR of the PDB so you expect that everything works fine, but if we connect to the pdb and try to query the table t1 on the users tablespace there is an error because the file was not restored and we only get a ‚Äúmissing datafile‚ÄĚ entry

SQL> alter session set container=pdb1;
Session altered.

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01111: name for data file 12 is unknown - rename to correct file
ORA-01110: data file 12:
'/oradata/u00/app/oracle/product/12201/dbs/MISSING00012'

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/system.6483.997694289
+DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/sysaux.518.997694289
+DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/undo.517.997694289
+DATAFS/DO99MM47_06/7F8FEAF2AD1F3765E05382C10B0A4B9C/DATAFILE/dba_data.508.997694355
/oradata/u00/app/oracle/product/12201/dbs/MISSING00012
SQL>

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: