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
Hi Mike,
What about the pdb flashback of the standby database ? I tried to do the same flashback as on Primary on the Standby PDB, but the recover over open resetlogs on standby don’t work.
See below error.
2017-11-29T14:33:30.835499+01:00
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Datafile 8 (ckpscn 1394514) is orphaned on PDB incarnation#=1
MRP0: Background Media Recovery terminated with error 65138
2017-11-29T14:33:30.882131+01:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1_site2/CDB1/trace/CDB1_mrp0_14099.trc:
ORA-65138: Data file 8 of pluggable database PDB belongs to an orphan PDB incarnation.
ORA-01110: data file 8: ‘/u02/oradata/CDB1/PDB/system01CDB1.dbf’
Managed Standby Recovery not using Real Time Apply
2017-11-29T14:33:30.941723+01:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1_site2/CDB1/trace/CDB1_mrp0_14099.trc:
ORA-65138: Data file 8 of pluggable database PDB belongs to an orphan PDB incarnation.
ORA-01110: data file 8: ‘/u02/oradata/CDB1/PDB/system01CDB1.dbf’
2017-11-29T14:33:30.941816+01:00
LG
HErvé
Hi Hervé,
hope all is well 🙂
I can just think of an “old” problem when MRP started before the standby SYNCup with primary – MRP crashed with ORA-65138
Would you please mind to open an SR and send me the SR number via email?
I won’t be able to verify this before end of next week. But it looks like unintended misbehavior to me 😉
Thanks,
Mike
very good and rich article
thanks
Thanks for the feedback 🙂
Cheers,
Mike
Hi,
we encoutered a weird problem on one of our custommer exadata with Oracle 18.7. After doing the restore point and flashback as described in the article, the “open” part ended with an error:
SQL> FLASHBACK PLUGGABLE DATABASE PDB3 TO RESTORE POINT PDB3_RP;
Flashback complete.
SQL> alter pluggable database PDB3 open resetlogs;
alter pluggable database PDB3 open resetlogs
*
ERROR at line 1:
ORA-01152: file 200 was not restored from a sufficiently old backup
ORA-01110: data file 200: ‘+DTDATA/PDB/979AB1D3BA923EBAE053A0FD940A1168/DATAFILE/ts_tmp_ix.1169.1024647293’
How can that even happen and how can we put the database online again? We performed another restore point on another instance and another pluggable database on the same machine and it worked well. This error surprised us. The restore point supposed to be a guaranteed one.
Thanks a lot.
Hi Vaclav,
this looks strange to me – is this a temp tablespace this gets logged for?
You need please open an SR (Sev.1) and have Support check this. There are so many things to check and clarify, please understand that I can’t do this via this blog here.
Cheers,
Mike