Configure Flashback Database

Configuring Flashback Database

The steps for configuring Flashback database option in  are:

Configure database to run in ARCHIVELOG mode.

Ensure the datbase is operating in ARCHIVELOG mode.


SQL> ARCHIVE LOG LIST

Database log mode            Archive Mode
Automatic archival              Enabled
Archive destination             /home/oracle/archiev1
Oldest online log sequence          5
Next log sequence to archive      7
Current log sequence                 7


If not, shutdown immediate, then startup mount, and alter the database

SQL> alter database archivelog;


Configure Flashbask when the instance in mount operating mode

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;

Database altered.


Configuring flashback database related parameters

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =
          '/home/oracle/flash_recover_area' scope=both;


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=both;


SQL> ALTER SYSTEM SET DB_FLASHBACK_RENTENTION_TARGET = 4320 SCOPE=both;


SQL> show parameter DB_FLASH

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     4320


SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/flash_recovery_area

db_recovery_file_dest_size           big integer 3G


Obtain SCN before initiating a database change.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     945046


Perform database change

SQL> drop table scott.salgrade;

Table dropped.


Performing  flashback database

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;

ORACLE instance started.
Total System Global Area  608174080 bytes
Fixed Size                  1281624 bytes
Variable Size             163578280 bytes
Database Buffers          436207616 bytes
Redo Buffers                7106560 bytes
Database mounted.



SQL> FLASHBACK DATABASE TO SCN 945046;

Flashback complete.

Open the Database with RESETLOGS.


SQL> alter database open resetlogs;

Database altered.

Confirm success of flashback.



SQL> select * from scott.salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1             700          1200
         2             1201        1400
         3             1401        2000
         4             2001        3000
         5             3001        9999