Configuring Flashback Recovery in Oracle Database
Flashback Database is a powerful feature in Oracle that allows you to rewind your database to a previous state without requiring traditional point-in-time recovery. Starting from Oracle 11g R2, enabling Flashback Database no longer requires a database restart. For earlier versions, a database bounce is needed for these settings to take effect.
Step-by-Step Guide to Enabling Flashback
1. Verify the Oracle Version
Connect to the database as SYSDBA and check the Oracle version to confirm compatibility:
$ sqlplus / as sysdba
SQL> SELECT * FROM v$version;
BANNER
------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
2. Check Flashback Status
Verify if Flashback Database is already enabled by querying v$database and v$instance:
SQL> SELECT flashback_on, status FROM v$database, v$instance;
FLASHBACK_ON STATUS
------------------ --------
NO OPEN
If FLASHBACK_ON is “NO”, proceed to enable Flashback.
3. Enable Flashback
Enable Flashback Database with the following command:
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
Verify the status again:
SQL> SELECT flashback_on, status FROM v$database, v$instance;
FLASHBACK_ON STATUS
------------------ --------
YES OPEN
4. Configure Recovery Area
The recovery area is where Flashback logs are stored. Check the current recovery area size:
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;
NAME TYPE VALUE
----------------------------------- ----------- -------------------
db_recovery_file_dest_size big integer 20000M
Adjust the size to at least 30GB (recommended for development and testing environments):
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=30G;
System altered.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;
NAME TYPE VALUE
----------------------------------- ----------- -------------------
db_recovery_file_dest_size big integer 30G
Verify the location of the recovery area:
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;
NAME TYPE VALUE
----------------------------------- ----------- -------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
To change the recovery area location:
SQL> ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/flash_recovery/oraflash';
System altered.
5. Set Retention Period
The retention period defines how long Flashback logs are retained. Check the current setting:
SQL> SHOW PARAMETER db_flashback_retention_target;
NAME TYPE VALUE
----------------------------------- ----------- -------------------
db_flashback_retention_target integer 1440
This value is in minutes. For example, 1440 minutes equals 24 hours. To change it to 2 days (2880 minutes):
SQL> ALTER SYSTEM SET db_flashback_retention_target=2880;
System altered.
Enabling Flashback on a Standby Database
If you have a standby database configured, Flashback operations can only be performed when the database is in the MOUNT state. Follow these steps:
- Cancel recovery on the standby database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; - Enable Flashback:
ALTER DATABASE FLASHBACK ON; - Restart recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Disabling Flashback
To disable Flashback Database, use the following command:
SQL> ALTER DATABASE FLASHBACK OFF;
Database altered.
Conclusion
Flashback Database is a valuable feature for recovering from accidental changes or errors in Oracle. By enabling Flashback and properly configuring the recovery area and retention period, you can leverage this feature to minimize downtime and data loss.
For standby databases, remember to follow the specific steps to activate Flashback. Always ensure adequate recovery area size and monitor storage usage to avoid unexpected issues.
Home 