以下脚本可以用于收集10g以后的闪回数据库日志Flashback Database Log的诊断信息:
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
/
Sample Output:
ORDER_ NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
0 db_recovery_file_dest +SYSTEMDG
0 db_recovery_file_dest_size 5218762752
1 retention target (minutes) 1440
2 Current flashback log count 33
2 estimated size for flashback logs (MB) 142.15
3 Most recent flashback log (minutes) 164.03
3 oldest flashback log (minutes) 5846.35
4 Total size of all files in MB 1963.11
5 Total size of reclaimable files in MB 534.47
6 unused space in MB 3014
