Flashback Recovery Info in Oracle: A Complete Guide

Flashback recovery is an essential feature in Oracle databases, allowing users to recover data quickly and efficiently. This guide provides a step-by-step explanation of how to retrieve complete flashback recovery information in Oracle, with tips for adjusting paths according to your system setup.

Purpose of Flashback Recovery

Oracle’s flashback recovery offers:

• The ability to restore a database to a specific point in time.
• Undo unwanted changes.
• Improved reliability for the recovery process.

How to Retrieve Flashback Recovery Info in Oracle

Follow these steps to gather flashback recovery information and save it in an HTML format:

Steps to Run the Script

1. Copy the script provided below into a text editor.
2. Replace the default file path C:\flashback_info.html with your preferred location, such as /home/oracle/flashback_info.html for Linux systems, or adjust it according to your operating system requirements.
3. Save the script and execute it in Oracle SQL*Plus.

Script to Generate Flashback Recovery Info

SET MARKUP HTML ON spool on
SET TERMOUT OFF
SPOOL /home/oracle/flashback_info.html
SET LINESIZE 132 SERVEROUTPUT ON PAGESIZE 1000;
ALTER SESSION SET NLS_DATE_FORMAT = 'mm/dd/yy hh24:mi:ss';

COLUMN name FORMAT A30

-- Display recovery-related parameters
SHOW PARAMETER recovery;
SHOW PARAMETER retention;
SHOW PARAMETER keep;

-- Flashback log information
SELECT * FROM v$FLASHBACK_DATABASE_LOGFILE;
SELECT * FROM V$FLASHBACK_DATABASE_STAT;
SELECT * FROM V$FLASHBACK_DATABASE_LOG;
SELECT * FROM x$krfblog;

-- Flashback recovery area usage
SELECT FILE_TYPE,
       PERCENT_SPACE_USED,
       PERCENT_SPACE_USED * p.value / 100 / 1024 / 1024 "Mb used",
       PERCENT_SPACE_RECLAIMABLE,
       NUMBER_OF_FILES,
       PERCENT_SPACE_RECLAIMABLE * p.value / 100 / 1024 / 1024 "Mb reclaimable"
FROM v$flash_recovery_area_usage f, v$parameter p
WHERE p.name = 'db_recovery_file_dest_size';

-- Restore point information
COLUMN time FORMAT A30
COLUMN name FORMAT A20
SELECT SCN,
       GUARANTEE_FLASHBACK_DATABASE,
       STORAGE_SIZE,
       TIME,
       NAME
FROM v$restore_point;

-- Recovery area usage
SELECT file_type,
       percent_space_used AS used,
       percent_space_reclaimable AS reclaimable,
       number_of_files AS "number"
FROM v$recovery_area_usage;

-- Recovery file destination information
SELECT name,
       space_limit AS Total_size,
       space_used AS Used,
       SPACE_RECLAIMABLE AS reclaimable,
       NUMBER_OF_FILES AS "number"
FROM V$RECOVERY_FILE_DEST;

SELECT * FROM v$flash_recovery_area_usage;

SET MARKUP HTML OFF
SPOOL OFF
EXIT;

Sample Output (HTML Format)

Recovery-Related Parameters

Parameter Name	Value
db_recovery_file_dest	/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size	50G
db_flashback_retention_target	1440

Flashback Log Information

From V$FLASHBACK_DATABASE_LOGFILE:

RECOVERY_DEST	NAME	STATUS	BYTES
FRA	/u01/app/oracle/flashback1.log	ACTIVE	209715200
FRA	/u01/app/oracle/flashback2.log	INACTIVE	157286400

From V$FLASHBACK_DATABASE_STAT:

BEGIN_TIME	END_TIME	FLASHBACK_DATA	DB_DATA	REDO_DATA	ESTIMATED_FLASHBACK_SIZE
01/21/25 12:00:00	01/21/25 12:59:59	1024 MB	512 MB	256 MB	2048 MB

From V$FLASHBACK_DATABASE_LOG:

BEGIN_TIME	OLDEST_FLASHBACK_SCN	OLDEST_FLASHBACK_TIME	FLASHBACK_SIZE
01/21/25 12:00:00	123456789	01/21/25 11:50:00	512 MB

Flashback Recovery Area Usage

From v$flash_recovery_area_usage:

File Type	Space Used (%)	Space Used (MB)	Space Reclaimable (%)	Space Reclaimable (MB)	Number of Files
Flashback Logs	25	125	10	50	2
Archive Logs	20	100	5	25	3

Restore Point Information

From v$restore_point:

SCN	Guarantee Flashback	Storage Size (MB)	Time	Name
1234567	Yes	30	01/21/25 15:30:00	PRE_UPDATE

Recovery Area Usage

From v$recovery_area_usage:

File Type	Space Used (%)	Space Reclaimable (%)	Number of Files
Flashback Logs	50	20	5
Archive Logs	30	10	3

Recovery File Destination Information

From V$RECOVERY_FILE_DEST:

Name	Total Size (MB)	Space Used (MB)	Space Reclaimable (MB)	Number of Files
/u01/app/oracle/flash_recovery_area	51200	25600	10240	8

This is an example of how the output would appear for each section. The actual data will depend on the state of your Oracle database and recovery settings.