- 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