How to Flashback Pluggable database with Guaranteed restore point

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 30 Sep, 2021
  • 0 Comments
  • 2 Mins Read

How to Flashback Pluggable database with Guaranteed restore point

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