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;
flashbackmultitenantPDB ORACLE 12C