Script:收集Flashback Database Log诊断信息

以下脚本可以用于收集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