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

Let’s start the DEMO.

DEMO:

1. Check whether local undo is enabled or not.

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

For more info – Local undo and shared undo in oracle 12.2

 

2. Check whether flashback is enabled or not.

In a multitenant database, flashback can be enabled at container level only.

SQL> show con_name

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

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /export/home/oracle/product/12
                                                 c/recovery_area
db_recovery_file_dest_size           big integer 12690M


SQL> Database log mode         Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

SQL>
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

2. Create a restore point in the pluggable database.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SDCP1                          READ WRITE NO
SQL> alter session set container=SDCP1;

Session altered.


SQL> show pdbs

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

--- row_count at time of restore point:

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

  COUNT(*)
----------
     72633


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
------------ ---------- -----------------------------------------------------------------------
STAGE1                3 27-NOV-18 10.18.25.000000000 AM

3. Do some DML changes on the PDB.

SQL> insert into PDBCLASS.TABLE1 select * from PDBCLASS.TABLE1;
72633 rows created.

SQL> commit;

Commit complete.

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

COUNT(*)
----------
145266

4. Now do the flashback to the restore point(we created in the previous stage).

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SDCP1 READ WRITE NO
SQL> alter pluggable database SDCP1 CLOSE;

Pluggable database altered.

SQL> flashback pluggable database SDCP1 to restore point STAGE1;

Flashback complete.

SQL> alter pluggable database SDCP1 open;
alter pluggable database SDCP1 open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/export/home/oracle/product/12c/oradata/ORA12CR2/SDCP1/encryp_ts1.dbf'

SQL> alter pluggable database SDCP1 open resetlogs;

Pluggable database altered.

5. Check data again(whether we are seeing the old data or not):

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

  COUNT(*)
----------
     72633



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SDCP1                          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. However this flahback will take a long time and will consume additional space on the auxiliary destination.

steps FOR SHARED UNDO:

SQL> alter pluggable database SDCP1 close;
SQL>flashback pluggable database SDCP1 to restore point STAGE1 auxiliary destination '/oradata/aux_inst';
SQL> alter pluggable database SDCP1 open resetlogs;