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;
<10 .00="" then="">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 10>
*****
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
******
<10 .00="" then=""> 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;
10>
<10 .00="" then=""> 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;10>
<10 .00="" then=""> 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'; ============================================================10>
<10 .00="" then=""> 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; 10><10 .00="" then=""> 10>
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;