In my last post, I talked about Flashback Drop feature of this technology which is really cool feature in a way that it helps us in restoring the dropped table if the table is not dropped with PURGE option.
Let us assume that we discovered that sometime in past there were N-number of objects modified either via DDL or DML which were not really necessary but now what to do? Change has either been explicitly or implicitly committed. The problem here is Oracle on its own does not track DDL/DMLs performed by the users unless we implement a mechanism for that purpose. The only feasible solution is either to restore the database to the point in past when such modifications were performed or better option would be REWIND the entire database itself. Restoration task is more of a DBA job and needs more skill set but rewinding the DB does not need lot of skills. I believe this would be better option compared to restoration. Why?
Answer is FLASHBACK DATABASE is usually much faster than a RESTORE operation. Time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. But, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.
FLASHBACK DATABASE command can be used to rewind the database to a target time, SCN. This command basically undo the changes made to the datafiles that exist when this command is/was run.
One thing to note here is : Flashback can fix logical failures but not physical failures. Thus, it cannot be used to recover from disk failures or the accidental deletion of datafiles. In short, RESTORE has its own usage and importance and so does flashback database. this can not be used when restore is needed.
Note that it can not be used for single schema/tablespace rewind. This is a quick way for incomplete recovery. In order to use this, you must have:
- Database Must be in archivelog mode
- FLASHBACK_ON should be ON
- Must have SYSDBA role
- FLASH RECOVERY AREA must be configured
- Database must be in MOUNTed mode to use this command
SQL:SYS@tp11g>select log_mode,flashback_on from v$database; LOG_MODE FLASHBACK_ON ============ ================== ARCHIVELOG NO SQL:SYS@tp11g>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL:SYS@tp11g>startup mount ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 331350480 bytes Database Buffers 197132288 bytes Redo Buffers 5804032 bytes Database mounted. SQL:SYS@tp11g>alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38708: not enough space for first flashback database log file SQL:SYS@tp11g>show parameter db_recovery NAME TYPE VALUE ============================= =========== ==================== db_recovery_file_dest string c:\app\Oracle\flash_recovery_area db_recovery_file_dest_size big integer 4977M SQL:SYS@tp11g>alter system set db_recovery_file_dest_size=10000M; System altered. SQL:SYS@tp11g>alter database flashback on; Database altered. SQL:SYS@tp11g>alter database open; Database altered. SQL:SYS@tp11g>select log_mode,flashback_on from v$database; LOG_MODE FLASHBACK_ON ============ ================== ARCHIVELOG YES SQL:SYS@tp11g> SQL:SYS@tp11g>select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ======================== 26668862 SQL:SYS@tp11g>create table scott.flash_db(sr number,name varchar2(100)); Table created. SQL:SYS@tp11g>desc scott.flash_db Name Null? Type ------------ -------- ----------------------- SR NUMBER NAME VARCHAR2(100) SQL:SYS@tp11g>
Now let’s flashback our database to SCN 26668862 and find out it our table FLASH_DB exists of not.
SQL:SYS@tp11g>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL:SYS@tp11g>startup mount; ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 331350480 bytes Database Buffers 197132288 bytes Redo Buffers 5804032 bytes Database mounted. SQL:SYS@tp11g>flashback database to scn 26668862; Flashback complete. SQL:SYS@tp11g>alter database open resetlogs; Database altered. SQL:SYS@tp11g>desc scott.flash_db ERROR: ORA-04043: object scott.flash_db does not exist SQL:SYS@tp11g>
DB_FLASHBACK_RETENTION_TARGET parameter decides upto what time the database can be flashed back. V$FLASHBACK_DATABASE_LOG view can be used to check this time. If you want you can create flashback restore point also which can later be used to flashback the database.
CREATE RESTORE POINT original_point;
FLASHBACK DATABASE TO RESTORE POINT original_point;
- You cannot use
FLASHBACK DATABASEto return to a point in time before the restore or re-creation of a control file.
- If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.
- The earliest SCN that can be used for a Flashback Database operation depends on the setting of the
DB_FLASHBACK_RETENTION_TARGETinitialization parameter, and on the actual retention of flashback logs permitted by available disk.