Since Oracle Database 12.2 an important feature works now for pluggable databases as a Fallback Strategy: Flashback Pluggable Database in Oracle 12.2. This was missing in Oracle Database 12.1.
Actually I missed this feature a lot as I use “Flashback” almost everyday. And you could only rewind the entire deployment with a
FLASHBACK DATABASE command.
Fallback Strategy: Flashback Pluggable Database
Actually having the possibility to flashback a pluggable database without effecting the other PDBs is really important. Did you ever want to try out what happens if you
TRUNCATE TABLE SYS.PROPS$? Give it a try – but set a Guaranteed Restore Point first.
See my related blog post about:
for the basic details on Guaranteed Restore Points.
Requirements for Flashback Pluggable Database
In order to use Flashback Pluggable Database you will have to enable Local Undo. This limits the feature to Oracle Database 188.8.131.52 and above as Multitenant databases below Oracle Database 184.108.40.206 work only with Shared Undo. Shared Undo does exist in Oracle 12.2 as well but the recommendation is clearly to use Local Undo instead.
Enabling Local Undo
You simply can enable Local Undo by setting the database into STARTUP UPGRADE mode and then changing the undo mode to Local Undo:
-- Issue in SQL*Plus connected as SYSDBA -- startup upgrade; alter database local undo ON; alter database open;
Of course the database must operate in archivelog mode.
With this change all PDBs will now get an UNDO tablespace in their designated directoy automatically. The UNDO in the CDB$ROOT container will be converted internally into a local undo tablespace belonging to the CDB$ROOT only.
Setting a Guaranteed Restore Point
Setting a guaranteed restore point in a pluggable database works exactly the same as setting it in a non-CDB or in the root container:
-- Run in SQL*Plus as user with privileges to set guaranteed restore points alter session set container=pdb1; create restore point PDB1_GRP1 guarantee flashback database;
Now you can do something really mean, for instance
TRUNCATE TABLE PROPS$(do this only in a test/toy/destroy environment but never in a real production database):
truncate table props$; shutdown startup -- This will result now in: -- ORA-00600: internal error code, arguments: [qkslvcnfy: PDB propExists], , , , , , , , , , , 
The pluggable database is shutdown after this action and does not restart anymore.
Flashing Back a Pluggable Database
But of course I can
FLASHBACK to my Guaranteed Restore Point.
flashback pluggable database PDB1 to restore point PDB1_GRP1;
Having a quick look into the alert.log is quite interesting:
PDB1(3):Serial Media Recovery started 2017-08-21T21:04:32.634803+02:00 PDB1(3):Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0 PDB1(3): Mem# 0: /u02/oradata/CDB2/redo03.log 2017-08-21T21:04:32.638202+02:00 PDB1(3):Incomplete Recovery applied until change 1110500 time 08/21/2017 20:57:30 PDB1(3):Flashback Media Recovery Complete PDB1(3):Flashback Pluggable Database PDB1 (3) recovered until change 1110500 PDB1(3):Completed: flashback pluggable database PDB1 to restore point PDB1_GRP1
And now I will have to open the PDB:
alter pluggable database PDB1 open resetlogs;
Wait a minute. Does this really make sense? All containers share the same redo logs. Therefore the question is: How can we do an “open resetlogs” for a single PDB? Simple answer: We don’t. But the command is kept the same in order to ease the usage. Let’s have a quick look into the alert.log again:
2017-08-21T21:07:23.539635+02:00 PDB1(3):alter pluggable database PDB1 open resetlogs 2017-08-21T21:07:23.571132+02:00 Online datafile 10 Online datafile 9 Online datafile 8 PDB1(3):Autotune of undo retention is turned on. PDB1(3):Endian type of dictionary set to little PDB1(3): Successfully onlined Undo Tablespace 2. PDB1(3):Undo initialization finished serial:0 start:986076 end:986158 diff:82 ms (0.1 seconds) PDB1(3):Database Characterset for PDB1 is AL32UTF8 PDB1(3):JIT: pid 5716 requesting stop PDB1(3):Autotune of undo retention is turned on. PDB1(3):Endian type of dictionary set to little PDB1(3): Successfully onlined Undo Tablespace 2. PDB1(3):Undo initialization finished serial:0 start:986532 end:986599 diff:67 ms (0.1 seconds) PDB1(3):Pluggable database PDB1 dictionary check beginning PDB1(3):Pluggable Database PDB1 Dictionary check complete PDB1(3):Database Characterset for PDB1 is AL32UTF8 PDB1(3):*************************************************************** PDB1(3):WARNING: Pluggable Database PDB1 with pdb id - 3 is PDB1(3): altered with errors or warnings. Please look into PDB1(3): PDB_PLUG_IN_VIOLATIONS view for more details. PDB1(3):*************************************************************** 2017-08-21T21:07:24.952498+02:00 PDB1(3):Opening pdb with no Resource Manager plan active Starting control autobackup Control autobackup written to DISK device handle '/u02/oradata/CDB2/CDB2/autobackup/2017_08_21/o1_mf_s_952636045_dspcvf9h_.bkp' Pluggable database PDB1 closed PDB1(3):Completed: alter pluggable database PDB1 open resetlogs
It looks like as Local Undo is used. And some magic which is not in the alert.log. But for sure there isn’t a real
RESETLOGS operation happening. And of course PROPS$ is populated again, the pluggable database is fully functioning.
Just don’t forget to drop your guaranteed restore point the sooner or later:
drop restore point PDB1_GRP1;
Flashback Pluggable Database is a really cool feature – and very helpful. It is fast and simple, easy to use, reliable and most welcome in daily work with Multitenant databases.
- Fallback Strategy: Flashback to a Guaranteed Restore Point
- Fallback Strategy: Flashback Pluggable Database
- Fallback Strategy: Partial Offline Backups
- Fallback Strategies with Full Transportable Export/Import