Flashback PDB in Oracle 12cR2

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s