Oracle Oracle flashback info ... Oracle flashback Hourly info http://anuj-singh.blogspot.com/2023/ Oracle flashback Hourly info https://anuj-singh.blogspot.com/search?q=Oracle+flashback+Hourly+info set linesize 300 COL name FORMAT A50 HEADING 'Parameter' COL value FORMAT A32 HEADING 'Setting' SELECT decode(name,'db_flashback_retention_target','db_flashback_retention_target_In_Min->',name) name ,value FROM v$parameter WHERE NAME LIKE '%flash%' OR NAME LIKE '%recovery%' or name like 'log_archive_dest_1' ORDER BY NAME; OR prompt -- ----------------------------------------------------------------------- --- prompt -- Flash Recovery Area --- prompt -- ----------------------------------------------------------------------- --- prompt set heading off select ' db_recovery_file_dest '||value from V$PARAMETER where name='db_recovery_file_dest' union select ' db_recovery_file_dest_size (G) '||to_char(value/1024/1024/1024) from V$PARAMETER where name='db_recovery_file_dest_size' union select ' db_flashback_retention_target (minutes) '||value||' (hours : '||value/60||')' from V$PARAMETER where name='db_flashback_retention_target' union select ' Flashback size estimated (G) '||to_char(round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024)) From V$FLASHBACK_DATABASE_LOG ; set line 300 select FILE_TYPE, PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES,PERCENT_SPACE_USED, case when ((100 - PERCENT_SPACE_USED) <10.00) then '---(>90.00)% full ##' else 'Good' end as "ATTENTION-FLASHBACK LOG" from v$recovery_area_usage -- v$flash_recovery_area_usage where 1=1 -- and FILE_TYPE='FLASHBACK LOG' ; alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; select min(FIRST_TIME) min_time,max(FIRST_TIME) Max_time from v$flashback_database_logfile; set linesize 300 col FILE_TYPE for a30 col PERCENT_SPACE_USED for 999.99 heading 'PERCENT_SPACE_USED%' SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE, SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB, SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED, SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE, SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB, SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES, SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD GROUP BY ROLLUP(FILE_TYPE); show parameter reco show parameter flashback_retention set linesize 300 col NAME for a40 col value for a60 wrap select (select value from v$parameter where NAME='log_archive_dest_1') value ,a.name, round((a.space_limit/1024/1024/1024),2) as Total_in_Gb, round((a.space_used/1024/1024/1024),2) as flash_used_in_Gb, round((a.space_reclaimable/1024/1024/1024), 2) as flash_reclaimable_Gb, sum(b.percent_space_used) as "PERCENT_OF_SPACE_USED%" from v$recovery_file_dest a, v$flash_recovery_area_usage b group by a.name,space_limit, space_used , space_reclaimable ; set linesize 300 column name format A30 column value format A50 select name, value from v$parameter where name in ('db_flashback_retention_target', 'db_recovery_file_dest','db_recovery_file_dest_size') order by name; set linesize 200 col name for a50 SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,round(((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024)) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST ; set pagesize 300 linesize 300 prompt -- status of the Flash Recovery Area ? TTITLE 'Flash Recovery Area Status' COL name FORMAT A40 HEADING 'File Name' COL spc_lmt_mb FORMAT 999999999.99 HEADING 'Space|Limit|(GB)' COL spc_usd_mb FORMAT 999999999.99 HEADING 'Space|Used|(GB)' COL spc_rcl_mb FORMAT 999999999.99 HEADING 'Reclm|Space|(GB)' COL number_of_files FORMAT 999999999 HEADING 'Files' SELECT name ,trunc(space_limit /(1024*1024*1024),2) spc_lmt_gb ,trunc(space_used /(1024*1024*1024),2) spc_usd_gb ,trunc(space_reclaimable /(1024*1024*1024),2) spc_rcl_gb ,number_of_files FROM v$recovery_file_dest; The workaround is to lower the flashback retention target so that all changes fit in the FRA: <<<<<<<<<<<<< if FRA full SQL> alter system set db_flashback_retention_target=1440; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=???G scope=both; archive log list; alter system set log_archive_dest_1='/xxx/backups' scope=memory; ALTER SYSTEM SET log_archive_dest_1 ="LOCATION=+DATA" SCOPE=BOTH; Delete archive log file run { crosscheck backupset of database; crosscheck backupset of controlfile; crosscheck backupset of archivelog all; delete force noprompt obsolete; delete expired archivelog all; delete noprompt obsolete; delete noprompt obsolete orphan; crosscheck archivelog all; delete expired archivelog all; } delete noprompt archivelog all completed before 'sysdate - 1/24'; ---1 hour delete noprompt archivelog all completed before 'sysdate - 4'; *************************************** Take Archive log Backup nohup rman target / log=Arch.log @arch.rmn & cat arch.rmn run { backup as compressed backupset format '/XXXX/Arch_%T_@_%s_%p_%t' (archivelog all delete input); } ***************************************** prompt -- What Flashback options are currently enabled for this database? TTITLE 'Flashback Options Currently Enabled:' Oracle flashback info set numf 99999999999999999999999999 ***** prompt DB_DATA Number of bytes of database data read and written during the interval prompt REDO_DATA Number of bytes of redo data written during the interval set linesize 400 col DB_DATA for 999999999999 col FLASHBACK_SIZE for 999999999999 col "FLASH BEGIN" for a14 col "FLASH END" for a14 SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA "DB-READ-WRITE",a.REDO_DATA ,B.FLASHBACK_SIZE, (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B; FLASH BEGIN FLASH END DB-READ-WRITE REDO_DATA FLASHBACK_SIZE % COMPLETE -------------- -------------- ---------------- ---------------- -------------- ---------------- 100423 12:02 100423 12:15 7406247936 7188010496 599147937792 1 100423 11:02 100423 12:02 31025061888 32838619136 599147937792 5 100423 10:02 100423 11:02 26978738176 29367271936 599147937792 5 100423 09:02 100423 10:02 22409895936 24943251456 599147937792 4 ****** col oldest_flashback_scn format 999999999999999 heading 'oldest|flashback|scn #' col oldest_flashback_time format a20 heading 'oldest|flashback|time' col retention_target format 999999999 heading 'retention|target' col flashback_size format 999999999999999 heading 'flashback|Gb-size' col estimated_flashback_size format 999999999999 heading 'estimated|flashback|Gb-size' select --CON_ID, oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size from v$flashback_database_log; SELECT CURRENT_SCN FROM V$DATABASE; SELECT trunc(estimated_flashback_size/1024/1024/1024,2) estimated_flashback_size_GB ,trunc(flashback_size/1024/1024/1024,2) flashback_size_gb FROM v$flashback_database_log; db_flashback_retention_target SELECT ROUND((A.SPACE_LIMIT/1024/1024/1024), 2) AS FLASH_IN_GB, ROUND((A.SPACE_USED/1024/1024/1024), 2) AS FLASH_USED_IN_GB, ROUND((A.SPACE_RECLAIMABLE/1024/1024/ 1024), 2) AS FLASH_RECLAIMABLE_GB, SUM(B.PERCENT_SPACE_USED) as "PERCENT_OF_SPACE_USED%" FROM V$RECOVERY_FILE_DEST A, V$FLASH_RECOVERY_AREA_USAGE B GROUP BY SPACE_LIMIT, SPACE_USED , SPACE_RECLAIMABLE ; archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +LOGS The workaround is to lower the flashback retention target so that all changes fit in the FRA: SQL> alter system set db_flashback_retention_target=1440; prompt -- Is Flashback Database On ? TTITLE 'Is Flashback Database Enabled?' COL name FORMAT A12 HEADING 'Database' COL current_scn FORMAT 9999999999999 HEADING 'Current|SCN #' COL flashback_on FORMAT A8 HEADING 'Flash|Back On?' SELECT name ,current_scn ,flashback_on FROM v$database; set linesize 300 pagesize 300 alter session set nls_date_format='dd-mm-yyy mi:ss'; prompt -- What Flashback Logs are available? TTITLE 'Current Flashback Logs Available' COL log# FORMAT 9999 HEADING 'FLB|Log#' COL bytes FORMAT 99999999 HEADING 'Flshbck|Log Size' COL first_change# FORMAT 99999999999999 HEADING 'Flshbck|SCN #' COL first_time FORMAT A24 HEADING 'Flashback Start Time' SELECT LOG# ,trunc(bytes/1024/1024,2) MB ,first_change# ,first_time FROM v$flashback_database_logfile; 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 set linesize 200 col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 9999999999 col round(space_used/1048576) heading "Space Used (MB)" format 9999999999999 col name Heading "Flashback Location" format a50 select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST; set linesize 200 col name for a50 SELECT NAME, (SPACE_LIMIT/1024/1024 /1024) SPACE_LIMIT_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024/1024) AS SPACE_AVAILABLE_GB, ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST ; / in the MOUNT (exclusive) mode then issue one of the commands: FLASHBACK DATABASE TO SCN 5964663 FLASHBACK DATABASE TO BEFORE SCN 5964663 FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24) FLASHBACK DATABASE TO SEQUENCE 12345 FLASHBACK TABLE persons TO SCN 6039341 ===== set heading on linesize 300 column name format a40 heading "Name" column sl format 9999999 heading "Space Limit|(Gb)" column su format 9999999 heading "Space Used|(Gb)" column sr format 9999999 heading "Space|Reclaimable|(Gb)" column nf format 9999999 heading "N of Files" Select Substr(Name,1,40) name , Space_Limit/1024/1024/1024 sl , Space_Used/1024/1024/1024 su , Space_Reclaimable/1024/1024/1024 sr , Number_Of_Files nf From V$RECOVERY_FILE_DEST ; column ft format a30 heading "File Type" column psu format 9999999 heading "Space|Used %" column psr format 9999999 heading "Space|Reclaimable %" Select File_Type ft , Percent_Space_Used psu , Percent_Space_Reclaimable psr , Number_Of_Files nf From V$FLASH_RECOVERY_AREA_USAGE ; Space Space File Type Used % Reclaimable % N. Files ------------------------------ -------- ------------- -------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 70 23 219 FOREIGN ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 8 rows selected. -- After that you can resize the FRA with: -- ALTER SYSTEM SET db_recovery_file_dest_size= 20 G; -- Or change the FRA to a new location : -- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=' '; column os format 99999999999999 heading "Oldest Flashback SCN" column ot format a25 heading "Oldest Flashback Time" Select OLDEST_FLASHBACK_SCN os, TO_CHAR(OLDEST_FLASHBACK_TIME, 'DD-MM-YYYYD HH24:MI:SS') ot From V$FLASHBACK_DATABASE_LOG; col oldest_flashback_scn format 999999999999999 heading 'oldest|flashback|scn #' col oldest_flashback_time format a20 heading 'oldest|flashback|time' col retention_target format 999999999 heading 'retention|target' col flashback_size format 999999999999999 heading 'flashback|Gb-size' col estimated_flashback_size format 999999999999 heading 'estimated|flashback|Gb-size' select --CON_ID, oldest_flashback_scn,to_char(oldest_flashback_time,'dd-mm-yyyy hh24:mi:ss') oldest_flashback_time,retention_target,flashback_size/1024/1024/1024 flashback_size,estimated_flashback_size /1024/1024/1024 estimated_flashback_size from v$flashback_database_log; select * from V$RESTORE_POINT; -- How much space is used by Flashback Logs for each GRP? SELECT NAME, SCN, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES'; How much space do all GRP Flashback Logs use? SELECT SUM(STORAGE_SIZE) FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES'; ============================================================ Set heading off; select '+----------------------------------------+' from dual union all select '| Timestamp: '||to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS TZH:TZM') from dual union all select '| Machine: '||host_name from v$instance union all select '| Version: '||version from v$instance union all select '| DBName: '||name from v$database union all select '| Instance: '||instance_name from v$instance union all select '+----------------------------------------+' from dual; Set heading on / set echo on feedback on numwidth 30 pagesize 50000 linesize 300 alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS'; select sysdate from dual; show parameter retention prompt ## v$database select flashback_on from v$database; prompt ## v$rman_configuration select * from v$rman_configuration; prompt ## v$restore_point select * from v$restore_point; prompt ## V$FLASH_RECOVERY_AREA_USAGE select * from V$FLASH_RECOVERY_AREA_USAGE; prompt ## V$RECOVERY_FILE_DEST col name for a35 select * from V$RECOVERY_FILE_DEST; prompt ## v$flashback_database_log col VALUE for a30 select * from v$flashback_database_log; prompt ##v$flashback_database_logfile select * from v$flashback_database_logfile; prompt ##v$flashback_database_logfile select min(first_time), min(first_change#) from v$flashback_database_logfile; set linesize 300 col object_name for a30 col ORIGINAL_NAME for a30 col type for a10 col owner for a10 col CREATETIME for a20 col DROPTIME for a20 select OBJECT_NAME, ORIGINAL_NAME, TYPE,owner,CREATETIME,DROPTIME from dba_recyclebin; col NAME for a50 col value for a40 WITH flashback_database_log AS (SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes, retention_target retention_target_minutes, flashback_size / 1048576 flashback_size_mb, estimated_flashback_size / 1048576 estimated_flashback_size_mb FROM v$flashback_database_log), flashback_database_logfile AS (SELECT COUNT(*) logs, SUM(BYTES / 1048576) size_mb, MIN(first_time) oldest_log, MAX(first_time) latest_log FROM v$flashback_database_logfile), flashback_usage AS (SELECT file_type, ROUND(mb_used, 2) mb_used, ROUND(mb_reclaimable, 2) mb_reclaimable, DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used, DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable, number_of_files, total_mb db_recovery_file_dest_mb, flashback_retention_target, oldest_record, ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec FROM (SELECT SUM(DECODE(NAME, 'db_recovery_file_dest_size', VALUE / 1048576, 0)) total_mb, SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target FROM v$parameter WHERE NAME IN ('db_recovery_file_dest_size', 'db_flashback_retention_target')), (SELECT 'FLASHBACKLOG' file_type, NVL(SUM(BYTES) / 1048576, 0) mb_used, sum(CASE WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN bytes / 1048576 ELSE 0 END) mb_reclaimable, COUNT(*) number_of_files, MIN(first_time) oldest_record FROM (select bytes, lead(first_time) over(order by first_time asc) last_time, first_time from v$flashback_database_logfile) fla_log, (SELECT value value FROM v$parameter WHERE name = 'db_flashback_retention_target') tgt UNION SELECT 'BACKUPPIECE' file_type, NVL(SUM(BYTES / 1048576), 0) mb, SUM(CASE WHEN dl.rectype = 13 THEN (BYTES / 1048576) ELSE 0 END) reclaimable_mb, COUNT(*) no_of_files, MIN(start_time) oldest_record FROM v$backup_piece bp, x$kccagf dl WHERE is_recovery_dest_file = 'YES' AND deleted = 'NO' AND bp.recid = dl.recid(+) AND dl.rectype(+) = 13 UNION SELECT 'ARCHIVELOG' file_type, NVL(SUM(blocks * block_size) / 1048576, 0) mb, SUM(CASE WHEN dl.rectype = 11 THEN (LOG.blocks * LOG.block_size / 1048576) ELSE 0 END) reclaimable_mb, COUNT(*) no_of_files, MIN(first_time) oldest_record FROM v$archived_log log, x$kccagf dl WHERE deleted = 'NO' AND is_recovery_dest_file = 'YES' AND dl.recid(+) = log.recid AND dl.rectype(+) = 11 UNION SELECT 'ONLINELOG' file_type, SUM(BYTES / 1048576) mb, 0 reclaimable, COUNT(*) no_of_files, MIN(first_time) oldest_record FROM v$logfile lf, (SELECT group#, BYTES, first_time FROM v$standby_log UNION SELECT group#, BYTES, first_time FROM v$log) l WHERE l.group# = lf.group# AND lf.is_recovery_dest_file = 'YES' UNION SELECT 'IMAGECOPY', NVL(SUM(blocks * (block_size / 1048576)), 0) mb, 0 reclaimable_mb, COUNT(*) no_of_files, MIN(creation_time) oldest_record FROM v$datafile_copy WHERE deleted = 'NO' AND is_recovery_dest_file = 'YES' UNION SELECT 'CONTROLFILE', NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb, 0 reclaimable, COUNT(*) no_of_files, NULL oldest_record FROM v$controlfile WHERE is_recovery_dest_file = 'YES')) SELECT order_, NAME, VALUE FROM( SELECT 0 order_, NAME, VALUE FROM v$parameter WHERE NAME LIKE 'db_recovery_file%' UNION SELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2)) FROM flashback_database_log UNION SELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2)) FROM flashback_database_log UNION SELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2)) FROM flashback_database_log UNION SELECT 2, 'Current flashback log count', TO_CHAR(logs) FROM flashback_database_logfile UNION SELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2)) FROM flashback_database_logfile UNION SELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2)) FROM flashback_usage UNION SELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2)) FROM flashback_usage UNION SELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used))) FROM flashback_usage) ORDER BY order_, NAME; ORDER_ NAME VALUE ---------- -------------------------------------------------- ---------------------------------------- 0 db_recovery_file_dest /oracle/db/fast_recovery_area 0 db_recovery_file_dest_size 5033164800 2 Current flashback log count 0 3 Most recent flashback log (minutes) 4 Total size of all files in MB 0 5 Total size of reclaimable files in MB 0 6 unused space in MB 4800 7 rows selected.
Search This Blog
Total Pageviews
Tuesday 2 August 2011
Oracle flashback info
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)
No comments:
Post a Comment