Blog

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).

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

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 recover;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/flashback
db_recovery_file_dest_size           big integer 10G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


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.

2. Create a restore point in the pluggable database.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPRIM                        READ WRITE NO
         4 PDBPRIM2                       MOUNTED
         5 PDBPRIM3                       MOUNTED

SQL> alter session set container=PDBPRIM;

Session altered.


SQL> show pdbs

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

--- row_count at time of restore point:

SQL> select count(*) from TESTUSER.testtable;

  COUNT(*)
----------
     10003


SQL> create restore point CLEAN_DB guarantee flashback database;

Restore point created.



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

NAME                               CON_ID TIME
------------------------------ ---------- ------------------------------
CLEAN_DB                                3 30-SEP-21 03.18.45.000000000 PM

3. Do some DML changes on the PDB.

SQL> insert into TESTUSER.testtable values (3,'xyz');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL> select count(*) from TESTUSER.testtable;

COUNT(*)
----------
10011

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 PDBPRIM READ WRITE NO

SQL> alter pluggable database PDBPRIM CLOSE;

Pluggable database altered.

SQL> flashback pluggable database PDBPRIM to restore point CLEAN_DB;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 22 needs media recovery
ORA-01110: data file 22: '/data/app/oracle/oradata/PRIM/pdbprim/bigtbs02.dbf'

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> alter pluggable database PDBPRIM open resetlogs;

Pluggable database altered.

SQL> alter session set container=PDBPRIM;

Session altered.

SQL> select count(*) from TESTUSER.testtable;

COUNT(*)
----------
10003

SQL> show pdbs;

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

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.