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.
Tuesday 2 August 2011
Oracle flashback info
Subscribe to:
Posts (Atom)
