Thursday, November 7, 2013

Script for checking the database flashback settings

I normally use the following script to gather the most important facts about my flashback settings and readyness for a potential flashback database operation:

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
set linesize 300
set trimspool on
col name format a35
col time format a35
col guaranteed format a10
col "oldest flback SCN" format 9999999999999
col SCN format 9999999999999
set pagesize 200
spool chk_flashb.log
prompt =================================================
Prompt give me the
prompt * estimated flashback log size
prompt * retention target
prompt * current accumulated size of all flashback logs
prompt =================================================
select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb",
       retention_target/60                "Hours of flback logs",
       flashback_size/1024/1024           "Current Flbacklog Size mb"
from v$flashback_database_log
/

prompt ===============================================
Prompt How far back can the database be flashed back?
prompt ===============================================
select oldest_flashback_scn  "oldest flback SCN",
       oldest_flashback_time "oldest flback time"
from v$flashback_database_log
/

prompt =================================================
prompt show the restore points created the last 2 weeks
prompt =================================================
SELECT NAME,
       SCN,
       TIME,
       DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE "guaranteed",
       STORAGE_SIZE
FROM V$RESTORE_POINT
where time >= SYSDATE-14;
Prompt ===================================
prompt Show the flash recovery area usage
Prompt ===================================
select * from v$flash_recovery_area_usage;

Prompt ======================================
prompt I/O information for flashback logging
Prompt ======================================
select begin_time "Begin time",
        end_time "End time",
        round(flashback_data/1024/1024) "MB of flbk data written",
        round(redo_data/1024/1024)      "MB of redo data"
from   v$flashback_database_stat
order by begin_time asc;
exit

No comments:

Post a Comment