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