Fallback Strategy: Flashback Pluggable Database

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:

https://mikedietrichde.com/2017/08/29/fallback-strategy-guaranteed-restore-point/

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 12.2.0.1 and above as Multitenant databases below Oracle Database 12.2.0.1 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):[5716] 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):[5716] 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;

Summary

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.

Further Information

• 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

–Mike