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
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
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:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment