Flashback Pluggable database in RDMBS 12c.

In Oracle 12.1 it is not possible to flashback a pluggable database, but starting with 12.2 it is. Here we start with a demo on how this taks can be accomplished:

First we connect to the pluggable and want to create a restore point.

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

SQL> create restore point mypoint guarantee flashback database;
Restore point created.

After some time we want to use the previous restore point, let’s execute a flashback statement, and check the result.

SQL> flashback database to restore point mypoint;

flashback database to restore point mypoint
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

Oke, the message is clear not possible within a PDB, change to the root container and retry

SQL> connect / as sysdba
Connected.

SQL> alter pluggable database pdbproda close;
Pluggable database altered.

SQL> flashback pluggable database pdbproda to restore point mypoint;
flashback pluggable database pdbproda to restore point mypoint
*
ERROR at line 1:

ORA-39883: Restore point MYPOINT for pluggable database 3 is not a clean pluggable database restore point.

Still not possible. Not a clean restore point? Due to the fact we make use of SHARED undo, Oracle is unable to determine what undo to use. So flashback of a pluggable database is not possible when using shared undo, but flashback of the total container (ROOT) is possible, just like in 12.1. A clean restore point can also be created when the pdb is close and shared undo is used.

We need to run in LOCAL undo mode.   How to do accomplis that task is explain in this BLOG. Lets remove the previous restore point, enable local undo, create a restore point again and try again.

SQL> connect / as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size                 4584704 bytes
Variable Size             603982592 bytes
Database Buffers         1476395008 bytes
Redo Buffers               12189696 bytes
Database mounted.
Database opened.

SQL> alter database local undo on;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size                 4584704 bytes
Variable Size             603982592 bytes
Database Buffers         1476395008 bytes
Redo Buffers               12189696 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE RESTRICTED
———- —————————— ———- ———
2 PDB$SEED                       READ ONLY NO
3 PDBPRODA                      MOUNTED

SQL> alter pluggable database all open;
Pluggable database altered

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

SQL> create restore point mypoint_again guarantee flashback database;
Restore point created.

SQL> SELECT CON_ID,SCN,CLEAN_PDB_RESTORE_POINT,PDB_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

SCN CLE PDB_INCARNATION#     CON_ID GUA
———- — —————- ———- —
NAME
——————————————————————————–
1792604 NO                 0         3 YES
MYPOINT_AGAIN

SQL> flashback pluggable database pdbproda to restore point mypoint_again;
Flashback complete.

Alert log shows:

flashback pluggable database pdbproda to restore point mypoint_again
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 21 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/CDBPRODA/onlinelog/o1_mf_3_c2v8s72y_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/cdbproda/CDBPRODA/onlinelog/o1_mf_3_c2v8sbsm_.log
Incomplete Recovery applied until change 1792605 time 10/26/2015 20:52:40
Flashback Media Recovery Complete
Completed: flashback pluggable database pdbproda to restore point mypoint_again

Of course flashback a pluggable DB is a point in time recovery, so an open resetlogs is required to complete the action.

SQL> alter pluggable database pdbproda open resetlogs;
Pluggable database altered.

And we are done. Starting with 12.2 we are now able to flashback a pluggable database. Great feature in 12.2 but keep the requirement of local undo into account. For me this mean local undo will be our company best practice if we have more than 1 PDB in our consolidated containers.