- 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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 enabled2. Check whether flashback is enabled or not.In a multitenant database, flashback can be enabled at container level only.SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> show parameter recover;NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /data/flashbackdb_recovery_file_dest_size big integer 10Gdb_unrecoverable_scn_tracking boolean TRUErecovery_parallelism integer 0remote_recovery_file_dest stringSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /data/archiveOldest online log sequence 1Next log sequence to archive 1Current log sequence 1SQL>SQL> select FLASHBACK_ON from v$database;FLASHBACK_ON------------------NOSQL> 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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 MOUNTEDSQL> alter session set container=PDBPRIM;Session altered.SQL> show pdbsCON_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(*)---------- 10003SQL> 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------3 PDBPRIM READ WRITE NOSQL> 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 recoveryORA-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(*)----------10003SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------3 PDBPRIM READ WRITE NO |