Script – Monitor Flashback Logs

PROMPT How Far Back Can We Flashback To (Time)?

PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"

 from v$flashback_database_log;

PROMPT

PROMPT How Far Back Can We Flashback To (SCN)?

PROMPT

col oldest_flashback_scn format 99999999999999999999999999

select oldest_flashback_scn from v$flashback_database_log;

PROMPT

PROMPT Flashback Area Usage

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999

col round(space_used/1048576) heading "Space Used (MB)" format 99999

col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)

 from  v$RECOVERY_FILE_DEST;

How Far Back Can We Flashback To (Time)?

Oldest Flashback Time

-----------------------------

05-jul-2009 22:53:07

How Far Back Can We Flashback To (SCN)?

       OLDEST_FLASHBACK_SCN

---------------------------

                15321928761

Flashback Area Usage

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

------------ ------------------ ------------------------- ---------------

CONTROLFILE                   0                         0               0

ONLINELOG                     0                         0               0

ARCHIVELOG                16.33                     16.33              51

BACKUPPIECE               16.34                     16.34               6

IMAGECOPY                     0                         0               0

FLASHBACKLOG              45.94                     10.61             204

6 rows selected.

Flashback Location                       Space Allocated (MB) Space Used (MB)

---------------------------------------- -------------------- ---------------

/u05/oradata/flash_recovery_area                         5120            4025