Flashback Technology – Flashback Database

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:

1. Database Must be in archivelog mode
2. FLASHBACK_ON should be ON
3. Must have SYSDBA role
4. FLASH RECOVERY AREA must be configured
5. 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;

Limitations

1. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
2. If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.
3. The earliest SCN that can be used for a Flashback Database operation depends on the setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter, and on the actual retention of flashback logs permitted by available disk.