After the introduction of multitenant architecture with Oracle 12cR1, the flashback database feature became irrelevant as it was limited to the root container which resulted in affecting all the PDBs associated with that root container.
But with the new release 12cR2, flashback now supports pluggable databases as well.
Now we’ll demonstrate how to flashback a PDB:-
Check if flashback is enabled on the database:-
SELECT flashback_on FROM v$database;
If its not, enable it with following command:-
ALTER DATABASE FLASHBACK ON;
This would only work if the database is in archivelog mode, if it isn’t please switch the mode to archivelog using following commanbds on root container:-
CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;
Now create a restore point for the PDB:-
CONN / AS SYSDBA CREATE RESTORE POINT before_change FOR PLUGGABLE DATABASE PDB1;
Then to test the flashback, make some change in the PDB:-
CONN test/test@PDB1; CREATE TABLE t1 (id NUMBER); INSERT INTO t1 VALUES (1); COMMIT;
Now flashback the PDB to the restore point:-
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE PDB1 CLOSE; FLASHBACK PLUGGABLE DATABASE PDB1 TO RESTORE POINT before_change; ALTER PLUGGABLE DATABASE PDB1 OPEN RESETLOGS;
Check if the change (table t1) still exists:-
CONN test/test@PDB1; SELECT * FROM t1; SELECT * FROM t1 * ERROR at line 1: ORA-00942: table or view does not exist
And that’s how the flashback feature is again relevant for us.
Hope this posts helps you understand how to flashback a pluggable database, if you have any questions or need any assistance please comment below and we would try to assist you.