Flashback a PDB (Pluggable Database) in Oracle

From Oracle 12.2 onwards we can flashback a PDB (Pluggable DB). And flashback is very easy and simple if LOCAL UNDO is enabled (which is also a new feature in Oracle 12.2).

Let's see how it works:

• Check if LOCAL UNDO is enabled.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME           PROPE DESCRIPTION
----------------------- ----- ---------------------------------------------

LOCAL_UNDO_ENABLED TRUE true  if local undo is enabled;        

Check if FLASHBACK is enabled.

SQL> show con_nam

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ---------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 20000M


SQL> archive log list
Database log mode            Archive Mode
Automatic archival           Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   7
Next log sequence to archive 9
Current log sequence         9

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES        

Create the RESTORE POINT inside the PDB (Pluggable DB).

SQL> show pdb

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2          PDB$SEED                       READ ONLY  NO
3          RAULLAB1                       READ WRITE NO

SQL> alter session set container=RAULLAB1;

Session altered.


SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3          RAULLAB1                       READ WRITE NO

--- row_count at time of restore point:

SQL> select count(*) from PDBLAB.TABLE1;

COUNT(*)
----------
52875


SQL> create restore point STAGE1 guarantee flashback database;

Restore point created.


SQL> SELECT NAME,CON_ID ,TIME FROM V$RESTORE_POINT;

NAME         CON_ID     TIME
------------ ---------- -----------------------------------------
RAULLAB1     3          02-NOV-22 03.14.21.000000000 AMs        

Test its operation by running some DML.

SQL> insert into PDBLAB.TABLE1 select * from PDBLAB.TABLE1

52875 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from PDBLAB.TABLE1;

COUNT(*)
----------
105750        

FLASHBACK to the RESTORE POINT.

SQL> show pdb

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3          RAULLAB1                       READ WRITE NO

SQL> alter pluggable database RAULLAB1 CLOSE;

Pluggable database altered.

SQL> flashback pluggable database RAULLAB1 to restore point STAGE1;

Flashback complete.

SQL> alter pluggable database RAULLAB1 open;
alter pluggable database RAULLAB1 open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '+DATA/RAULLAB1/DATAFILE/wallet_ts1.262.1099908587'

SQL> alter pluggable database RAULLAB1 open resetlogs;

Pluggable database altered.        

Validate the success of the FLASHBACK by checking the data.

SQL> select count(*) from PDBLAB.TABLE1;

COUNT(*)
----------
52875


SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3          RAULLAB1                       READ WRITE NO        

• What if LOCAL_UNDO is not enabled?

In oracle 12.2 if local_undo is not enabled, then we can use AUXILIARY DESTINATION parameter, which will create an auxiliary instance with container database’s system, sysaux and undo tablespace, and then restore the PDB using rollback transactions.

This flashback method will take longer and will consume additional space on the auxiliary destination.

SQL> alter pluggable database RAULLAB1 close;

SQL>flashback pluggable database RAULLAB1 to restore point STAGE1 auxiliary destination '+FRA/AUXCDB';

SQL> alter pluggable database RAULLAB1 open resetlogs;        

From here, you can use the Unplug/Plug approach to replace the original database at the non-auxiliary CDB.