Enable/Disable Flashback Database
Pre-request:
1.Must be in archivelog mode
2.Before enable flashback database we need to make sure the database is in archivelog mode.
3.Must do this from the root container.
[oracle@ram ~]$ export ORACLE_SID=dbwr [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 00:53:58 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> conn / as sysdba Connected. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
We can now enable/disable flashback database with the open mode in 12c.
SQL> alter database flashback on; Database altered. SQL> alter database flashback off; Database altered. SQL> alter database flashback on; Database altered.
We can check the flashback status from v$database.
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL>
with db_flashback_retention_target parameter we can set flashback retention time.
SQL> sho parameter retention NAME TYPE VALUE ------------------------------------ ----------- ---------------------- db_flashback_retention_target integer 1440 undo_retention integer 900 SQL> alter system set db_flashback_retention_target=10080 scope=both; System altered. SQL>
Creating Restore Points
- Creating restore point is just like same in normal database.
- Its alias for a SCN.
- Guaranteed restored point prevents the database from removing any flashback logs between that point and the current time.
- Always remove unwanted guaranteed restore point.
Normal restore point:
SQL> conn / as sysdba Connected. SQL> SQL> SQL> create restore point rsp; Restore point created. SQL> drop restore point rsp; Restore point dropped. SQL>
Guaranteed restore point:
SQL> create restore point rsp1 guarantee flashback database; Restore point created. SQL> drop restore point rsp1; Restore point dropped. SQL>
Creating PDB level restore point and Guarantee restore point as follows.
SQL> conn / as sysdba Connected. SQL> alter session set container=pdb5; Session altered. SQL> create restore point rsp; Restore point created. SQL> drop restore point rsp; Restore point dropped. SQL> create restore point rsp1 guarantee flashback database; Restore point created. SQL> drop restore point rsp1; Restore point dropped.
From the root container also we can create PDB level restore point and Guarantee restore point
SQL> conn / as sysdba Connected. SQL> SQL> SQL> create restore point rsp for pluggable database pdb5; Restore point created. SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. SQL>
Guarantee restore point from root container:
SQL> SQL> create restore point rsp for pluggable database pdb5 guarantee flashback database; Restore point created. SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. SQL>
Clean restore points can be created while connected to the PDB
[oracle@ram ~]$ export ORACLE_SID=dbwr [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 02:09:44 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> conn / as sysdba Connected. SQL> col name for a15; SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE --------------- ---------- PDB$SEED READ ONLY PDB5 MOUNTED PDB6 MOUNTED PDB1 MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE --------------- ---------- PDB$SEED READ ONLY PDB5 READ WRITE PDB6 READ WRITE PDB1 READ WRITE SQL> alter session set container=pdb5; Session altered. SQL> shutdown; Pluggable Database closed. SQL> create clean restore point crsp; create clean restore point crsp * ERROR at line 1: ORA-39891: Clean PDB restore point cannot be created when CDB is in local undo mode. SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME ----------------------------------------------------------------------PROPERTY_VALUE ----------------------------------------------------------------------- LOCAL_UNDO_ENABLED TRUE
- The container database to be running in local undo mode, but flashback PDB does not depend on it.
- If the CDB is running in shared undo mode, it is more efficient to flashback to clean restore points.
- These are restore points taken when the pluggable database is down, with no outstanding transactions.
SQL> conn / as sysdba Connected. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> alter database local undo off; Database altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> col property_name for a20; SQL> col property_value for a20; SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE -------------------- -------------------- LOCAL_UNDO_ENABLED FALSE SQL>
SQL> create clean restore point crsp; Restore point created. SQL> drop restore point crsp; Restore point dropped. SQL>
SQL> create clean restore point crsp guarantee flashback database; Restore point created. SQL> drop restore point crsp; Restore point dropped. SQL> startup; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened.
They can also be created from the root container.
SQL> conn / as sysdba Connected. SQL> alter pluggable database pdb5 close; Pluggable database closed. SQL> conn / as sysdba Connected. SQL> SQL> create clean restore point rsp for pluggable database pdb5; Restore point created. SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. SQL> SQL> create clean restore point rsp for pluggable database pdb5 guarantee flashback database; Restore point created. SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. SQL>
Flashback CDB and PDB:
SQL> select * from t1; ID ---------- 1 2 3 4 5 SQL> create restore point rsp; Restore point created. SQL> desc v$restore_point Name Null? Type ----------------------------------- -------- -------------------- SCN NUMBER DATABASE_INCARNATION# NUMBER GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3) STORAGE_SIZE NUMBER TIME TIMESTAMP(9) RESTORE_POINT_TIME TIMESTAMP(9) PRESERVED VARCHAR2(3) NAME VARCHAR2(128) PDB_RESTORE_POINT VARCHAR2(3) CLEAN_PDB_RESTORE_POINT VARCHAR2(3) PDB_INCARNATION# NUMBER CON_ID NUMBER SQL> select name,scn from v$restore_point; NAME SCN -------------------- ---------- RSP 3096868 RSP1 3098148
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. SQL> conn / as sysdba Connected. SQL> flashback database to restore point rsp; Flashback complete. SQL> alter database open resetlogs; Database altered. SQL> alter pluggable database all open; Pluggable database altered. SQL> select * from t1; select * from t1 * ERROR at line 1: ORA-00942: table or view does not exist
Flashback can work in different situations:
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. SQL> flashback database to scn 3096868; Flashback complete. SQL> flashback database to before scn 3096868; Flashback complete. SQL> flashback database to restore point rsp1; Flashback complete.
Flashback of a PDB differ on whether local undo mode or not.
SQL> conn / as sysdba Connected. SQL> alter pluggable database pdb5 close; Pluggable database closed. SQL>flashback pluggable database pdb5 to restore point rsp; Flashback complete. SQL> alter pluggable database pdb5 open resetlogs; Database altered.
Flashback can work in different situations:
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. SQL> flashback database pdb5 to scn 3196745; Flashback complete. SQL> flashback database pdb5 to before scn 3196745; Flashback complete. SQL> flashback database pdb5 to restore point rsp1; Flashback complete.
Flashback a Pluggable Database:
- create a restore point.
- connect inside the PDB and make changes.
- Flashback the PDB to the restore point.
- Check the table content is missing.
SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered. SQL> conn / as sysdba Connected. SQL> create restore point rsp2 for pluggable database pdb5; Restore point created. SQL> conn hari/hari@pdb5 Connected. SQL> select * from t1; ID ---------- 1 2 3 4 5 SQL> conn / as sysdba Connected. SQL> alter pluggable database pdb5 close; Pluggable database closed. SQL> flashback pluggable database pdb5 to restore point rsp2; Flashback complete. SQL> alter pluggable database pdb5 open resetlogs; Pluggable database altered. SQL> conn hari/hari@pdb5 Connected. SQL> select * from t1; select * from t1 * ERROR at line 1: ORA-00942: table or view does not exist
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
