RMAN 12cR1 : Say goodbye to your backup when dropping your PDB – Part II

19 Jun RMAN 12cR1 : Say goodbye to your backup when dropping your PDB – Part II

Posted at 15:12h in Posts in English by
0 Likes

So my very last entry in my Pythian blog, before I joined Oracle, I talked about what happens if you try to recover a PLUGGABLE DATABASE if you do a DROP PLUGGABLE DATABASE in 12cR1, long story short (Also a joke if you watch South Park), you are not going to be able to restore/recover your database. Now I will dig a bit deeper into why and about what happens in the control file, and why you need to restore the control file so that you can recover your pluggable database.

If I create a Database called TESTDB

SQL> CREATE PLUGGABLE DATABASE testdb ADMIN USER testadm IDENTIFIED BY oracle ROLES=(DBA);

Pluggable database created.

If I do a dump of the control file type 4, which it includes , file header, the database info record, and checkpoint progress records,the earliest and latest records for circular reuse record types and the the 4 most recent records for circular reuse record types.

I can see in the dump of the control file that the pluggable database has a “id” of 4 and a “Record id” of 4, which are not the same, as one is the PDB_ID and the other is the Control File Record ID, as you can see below :

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump controlf 4;
Statement processed.
...

***************************************************************************
PLUGGABLE DATABASE RECORDS
***************************************************************************
(size = 684, compat size = 684, section max = 10, section in-use = 4,
last-recid= 13, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 540, numrecs = 10)
Pluggable DataBase record=1
id=1
dbid=2835408111
name=CDB$ROOT
first datafile link=1
pdbinc=0, pdbrdi=0, status=0x00000000, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.00000000, crescn scn: 0x0000.00000000
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=2
id=2
dbid=4092965150
name=PDB$SEED
first datafile link=7
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001dd3e3, crescn scn: 0x0000.001a4224
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=3
id=3
dbid=1554252633
name=PDBEM
first datafile link=10
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e0a9c, crescn scn: 0x0000.001dd76b
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=4
id=4
dbid=2633748847
name=TESTDB
first datafile link=12
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000001
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e066d, crescn scn: 0x0000.001e061c
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0

Now if we see a little bit down below in the file, which I will trim for this exercise to just show the datafile 12, we can see that there is a relationship between this id, and the datafiles for this pluggable database.

...
***************************************************************************
DATA FILE RECORDS
***************************************************************************
...
DATA FILE #12:
name #17: /u01/app/oracle/oradata/cdborcl/pdbs/sysaux01.dbf
creation size=87040 block size=8192 status=0x80 flg=0x5 head=17 tail=17 dup=1
pdb_id 4, tablespace 1, index=14 krfil=4 prev_file_in_ts=0 prev_file_in_pdb=11
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2 scn: 0x0000.001e066d 06/16/2014 16:09:37
Stop scn: 0x0000.001e066d 06/16/2014 16:09:37
Creation Checkpointed at scn: 0x0000.001e0620 06/16/2014 16:09:13
thread:1 rba:(0xf.54b4.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0

What I proceed to do now, is drop the pluggable database

SQL> DROP PLUGGABLE DATABASE TESTDB INCLUDING DATAFILES;

Pluggable database dropped.

And now doing another dump of my control file, I can see that the ID from dump of the controlfile is 0 and the Record ID is still 4, and now the relationship between the datafiles, which is not lost, is still number 4, which means that is not related to id number 0, so basically there are no datafiles related to this PDB, which my logic tells me that it is what I want, as I just dropped my PDB.

…
***************************************************************************
PLUGGABLE DATABASE RECORDS
***************************************************************************
(size = 684, compat size = 684, section max = 10, section in-use = 4,
last-recid= 14, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 540, numrecs = 10)
Pluggable DataBase record=1
id=1
dbid=2835408111
name=CDB$ROOT
first datafile link=1
pdbinc=0, pdbrdi=0, status=0x00000000, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.00000000, crescn scn: 0x0000.00000000
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=2
id=2
dbid=4092965150
name=PDB$SEED
first datafile link=7
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001dd3e3, crescn scn: 0x0000.001a4224
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=3
id=3
dbid=1554252633
name=PDBEM
first datafile link=10
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e0a9c, crescn scn: 0x0000.001dd76b
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=4
id=0
dbid=2633748847
name=TESTDB
first datafile link=0
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000001
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e066d, crescn scn: 0x0000.001e061c
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0

...

***************************************************************************
DATA FILE RECORDS
***************************************************************************
...
DATA FILE #12:
creation size=87040 block size=8192 status=0x88c flg=0x1 head=0 tail=0 dup=0
pdb_id 4, tablespace 1, index=14 krfil=4 prev_file_in_ts=0 prev_file_in_pdb=11
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:2 scn: 0x0000.001e066d 06/16/2014 16:09:37
Stop scn: 0x0000.001e066d 06/16/2014 16:09:37
Creation Checkpointed at scn: 0x0000.001e0620 06/16/2014 16:09:13
thread:1 rba:(0xf.54b4.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0

Now, I don’t know what it takes for the PDB_ID=0 to be removed from the controlfile, but if I go ahead and create another pluggable database, I can see from the dump that the record for that DB(TESTDB) is now deleted,the new DB grabs the PDB record ID of 4 and Controlfile Record of 4 , and now there is actually no record that it even existed, making it impossible for RMAN to recover/restore your database with the record being 0 or if the pluggable database not existing in the PLUGGABLE DATABASE RECORDS section.

SQL> CREATE PLUGGABLE DATABASE testdb1 ADMIN USER testadm IDENTIFIED BY oracle ROLES=(DBA);

Pluggable database created.

...
***************************************************************************
PLUGGABLE DATABASE RECORDS
***************************************************************************
(size = 684, compat size = 684, section max = 10, section in-use = 4,
last-recid= 18, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 540, numrecs = 10)
Pluggable DataBase record=1
id=1
dbid=2835408111
name=CDB$ROOT
first datafile link=1
pdbinc=0, pdbrdi=0, status=0x00000000, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.00000000, crescn scn: 0x0000.00000000
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=2
id=2
dbid=4092965150
name=PDB$SEED
first datafile link=7
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001dd3e3, crescn scn: 0x0000.001a4224
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=3
id=3
dbid=1554252633
name=PDBEM
first datafile link=10
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000000
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e0a9c, crescn scn: 0x0000.001dd76b
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0
Pluggable DataBase record=4
id=4
dbid=602641775
name=TESTDB1
first datafile link=14
pdbinc=0, pdbrdi=0, status=0x00000001, flags=0x00000001
incrcv scn scn: 0x0000.00000000, clnscn scn: 0x0000.001e7a44, crescn scn: 0x0000.001e79d6
dbrls scn: 0x0000.00000000, dbrlc=0
iscn scn: 0x0000.00000000, itime=0
bscn scn: 0x0000.00000000, btime=0
escn scn: 0x0000.00000000, etime=0

Conclusion

This is just to give you and idea of what happens in the control file when you issue DROP PLUGGABLE DATABASE, which is where the registry of your PDBs/Datafiles and Backups are kept, which is why when you issue that command , the only way to restore/recover your PDB is to restore the controlfile, root and PDB, this can be done in an auxilary database, if you do need to do it.

Rene Antunez
[email protected]
1 Comment
  • Pingback:René Antúnez - Oracle ACE | ORA-00600 [kpdbHashTableIns:dup] When Creating PDB in 19c
    Posted at 10:11h, 11 July

    […] You can know that a PDB is plugged in the container if the Pluggable DataBase record number is equal to the id number. If the id number is equal to 0, then it means that the PDB has been unplugged from the container.You can reade more about this in a blog post I wrote several years ago going into more detail of the status of a PDB in the controlfile. […]