Flashback Database quickly rewinds an Oracle database to a previous point in time to
correct any problems caused by logical data corruptions or user errors. Flashback
A database is like a ‘rewind button’ for your database.

To revert the entire database to a prior point in time, you can either revert the entire database to a prior point in time by restoring a backup and doing point-in-time recovery, or you can enable Flashback Database. When you enable Flashback Database, the database generates flashback logs in the fast recovery area. These logs are used to flash back the database to a specified time. During usual operation, the database occasionally logs images of data blocks to the flashback logs. The database automatically creates, deletes, and resizes flashback logs.

So let us start :

Step 1:
Use the following commands to check related parameter and Flashback Database is enabled or not on your target database:

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

SQL> Show parameter db_flashback_retention_target

NAME                                   TYPE             VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target         integer            1440

SQL> Show parameter db_recovery_file_dest

NAME                                   TYPE              VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                  string            +FRA
db_recovery_file_dest_size             big integer       50G

Step 2:

Set related parameters.Optionally, specify the length of the desired flashback window (in minutes) by setting the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The default value for this parameter is 1440 minutes, which is one day. The following command specifies that the flashback window must be 3 days.

alter system set db_flashback_retention_target=4320 SCOPE=spfile; --> Means keep 3 days logs

alter system set db_recovery_file_dest='+FRA' SCOPE=spfile; --> Set db_recovery_file_dest if its not set before!

alter system set db_recovery_file_dest_size=100G SCOPE=spfile; --> Set or change db_recovery_file_dest_size if its not set before!

Step 3:
Bounce db and open with mount mode option

SQL>shutdown immediate;
SQL>Startup mount;

Enable flashback on db

SQL>alter database flashback on;

Step 4:
recheck all configuration

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES

SQL> Show parameter db_flashback_retention_target

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




SQL> Show parameter db_recovery_file_dest

NAME                                    TYPE               VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                  string                 +FRA
db_recovery_file_dest_size             big integer           100G

 

Advertisements