Oracle Flashback Database Configuration Guide
Step-by-step instructions for enabling Fast Recovery Area and Flashback Database in Oracle RAC environment
Overview
This guide demonstrates how to configure Oracle Flashback Database feature along with Fast Recovery Area (FRA). Flashback Database allows you to quickly revert the entire database to a previous point in time, which is invaluable for recovery from human errors or logical corruptions.
The configuration involves two main steps:
- Enabling the Fast Recovery Area
- Enabling Flashback Database
Note: These parameters can be set without having to shut down and restart the database instance.
Step 1: Enabling the Fast Recovery Area
Prerequisites
The Fast Recovery Area is configured using two initialization parameters:
- DB_RECOVERY_FILE_DEST_SIZE: Sets the size of the fast recovery area
- DB_RECOVERY_FILE_DEST: Sets the physical location of the flash recovery files
Configuration
To enable the fast recovery area:
-- Set the size of the fast recovery area
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 9000M;
-- Set the physical location of the flash recovery files
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+FRA';
Step 2: Enabling Flashback Database
Prerequisites
- Configure the recovery area as described in Step 1
- Ensure the database instance is open or mounted
- If the instance is mounted, the database must be shut down cleanly (unless it is a physical standby database)
- Other Oracle Real Application Clusters (Oracle RAC) instances can be in any mode
Optional: Set Flashback Retention Target
This parameter defines the length of the flashback window in minutes:
-- Set flashback retention to 12 hours (720 minutes)
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 720 SCOPE = BOTH;
By default, DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).
Enable Flashback Database Feature
-- Enable Flashback Database for the whole database
ALTER DATABASE FLASHBACK ON;
Practical Demonstration
Below is a step-by-step demonstration of the configuration process on an Oracle RAC server.
1. Check Current Database Status
SQL> SELECT log_mode, flashback_on FROM gv$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG NO
ARCHIVELOG NO
2. Configure Fast Recovery Area Parameters
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 9000M;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest = '+FRA';
System altered.
3. Verify Parameter Settings
SQL> SHOW PARAMETER db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 9000M
4. Set Flashback Retention Target
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 720;
System altered.
SQL> SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 720
5. Enable Database Flashback Logging
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
6. Verify Database Flashback Status
SQL> SELECT log_mode, flashback_on FROM gv$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
ARCHIVELOG YES
ARCHIVELOG YES
7. Check for Existing Restore Points
SQL> SELECT * FROM v$restore_point;
no rows selected
8. Check Flashback Log Status and Information
-- How Far Back Can We Flashback To (Time)?
SELECT TO_CHAR(oldest_flashback_time, 'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
FROM v$flashback_database_log;
Oldest Flashback Time
-----------------------------
05-feb-2025 12:29:23
-- How Far Back Can We Flashback To (SCN)?
SELECT oldest_flashback_scn FROM v$flashback_database_log;
OLDEST_FLASHBACK_SCN
---------------------------
3786337
-- Flashback Area Usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE .12 0 1 0
REDO LOG 8.93 0 4 0
ARCHIVED LOG 7.18 0 14 0
BACKUP PIECE 15.49 .61 11 0
IMAGE COPY 3.73 0 1 0
FLASHBACK LOG 8.93 0 4 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
-- Flashback Location and Space Usage
SELECT name AS "Flashback Location",
ROUND(space_limit/1048576) AS "Space Allocated (MB)",
ROUND(space_used/1048576) AS "Space Used (MB)"
FROM v$RECOVERY_FILE_DEST;
Flashback Location Space Allocated (MB) Space Used (MB)
---------------------------------------- -------------------- ---------------
+FRA 9000 3996
9. Alert Log and Background Process Information
2025-02-05T12:20:57.818346+05:30
ALTER SYSTEM SET db_recovery_file_dest='+FRA' SCOPE=BOTH;
2025-02-05T12:21:24.420699+05:30
ALTER SYSTEM SET db_flashback_retention_target=720 SCOPE=BOTH;
2025-02-05T12:28:23.617618+05:30
ALTER DATABASE FLASHBACK ON
2025-02-05T12:28:24.166583+05:30
Starting background process RVWR
2025-02-05T12:28:24.177258+05:30
RVWR started with pid=64, OS id=16598
2025-02-05T12:28:44.029130+05:30
Already allocated 15933248 bytes in shared pool for flashback generation buffer.
2025-02-05T12:29:23.854734+05:30
Flashback Database Enabled at SCN 3786338
Completed: ALTER DATABASE FLASHBACK ON
✓ Our RAC DB is now configured to use FLASHBACK feature
Summary of Configuration Parameters
| Parameter | Value | Description |
|---|---|---|
| DB_RECOVERY_FILE_DEST_SIZE | 9000M | Size of the Fast Recovery Area |
| DB_RECOVERY_FILE_DEST | +FRA | Location of recovery files (ASM disk group) |
| DB_FLASHBACK_RETENTION_TARGET | 720 | Flashback window in minutes (12 hours) |
ConversionConversion EmoticonEmoticon