Oracle归档配置及检查

• 配置归档位置到 USE_DB_RECOVERY_FILE_DEST，并设置存储大小

startup mount;
!mkdir /db/archivelog
ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest='/db/archivelog' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
ALTER DATABASE ARCHIVELOG;

select name,value from v$parameter where name like 'db_recovery_file_dest' or name='log_archive_dest' or name='log_archive_dest_1' or name='log_archive_dest_2'
union
select name,to_char(value/1024/1024/1024)||' GB' from v$parameter where name='db_recovery_file_dest_size' order by name;

• 查看归档占用空间及归档个数

set line 900 COLSEP '|' pagesize 50
col name for a40
col value for a100
!clear
select * from v$flash_recovery_area_usage;
archive log list;

• 24小时的归档数量检查

set line 900 COLSEP '|' pagesize 50
col Data for a10
col day format a10
col total format 99999
col h00 format 9999
col h01 format 9999
col h02 format 9999
col h03 format 9999
col h04 format 9999
col h04 format 9999
col h05 format 9999
col h06 format 9999
col h07 format 9999
col h08 format 9999
col h09 format 9999
col h10 format 9999
col h11 format 9999
col h12 format 9999
col h13 format 9999
col h14 format 9999
col h15 format 9999
col h16 format 9999
col h17 format 9999
col h18 format 9999
col h19 format 9999
col h20 format 9999
col h21 format 9999
col h22 format 9999
col h23 format 9999
col h24 format 9999
break on report
compute max of "total" on report
compute max of "h00" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute max of "Avg" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report
SELECT thread#,TRUNC(first_time) "Date",TO_CHAR(first_time, 'Dy') "Day",COUNT(1) "Total",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) "h00",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h01",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h02",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h03",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h04",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h05",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h06",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h07",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h08",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h09",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) "h10",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) "h11",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) "h12",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) "h13",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) "h14",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) "h15",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) "h16",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) "h17",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) "h18",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) "h19",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) "h20",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) "h21",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) "h22",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) "h23",
ROUND(COUNT(1) / 24, 2) "Avg" FROM gv$log_history WHERE thread# = inst_id GROUP BY thread#,TRUNC(first_time), TO_CHAR(first_time, 'Dy') ORDER BY 1;

• 每天的归档数量及大小

set line 900 COLSEP '|' pagesize 50
compute max of "NUM" on report
compute max of "GB" on report
compute max of "MB" on report
compute max of "KB" on report
select THREAD#, trunc(completion_time) as "DATE",count(1) num,trunc(sum(blocks*block_size)/1024/1024/1024) as GB,trunc(sum(blocks*block_size)/1024/1024) as MB,
round(sum(blocks*block_size)/1024,0) as KB from v$archived_log where first_time > trunc(sysdate-10) and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS 
where status='VALID' and type='LOCAL') group by thread#, trunc(completion_time) order by 2,1;

年归档数量及大小

set line 900 COLSEP '|' pagesize 50
select round(min(sum(blocks*block_size)/1024/1024/1024)) MIN_Gb,round(max(sum(blocks*block_size)/1024/1024/1024)) MAX_Gb,
round(avg(sum(blocks*block_size)/1024/1024/1024)) AVG_Gb from (select a.THREAD#,trunc(first_time) as logtime,a.BLOCKS,
a.BLOCK_SIZE from v$archived_log a where a.DEST_ID = 1 and a.FIRST_TIME between sysdate-365 and sysdate) group by 
logtime order by logtime desc;

月归档数量

set line 900 COLSEP '|' pagesize 100
select a_date,a_count from (select to_char(first_time,'YYYY-MM-DD') a_date,count(*) a_count 
from gv$log_history group by to_char(first_time,'YYYY-MM-DD') order by 1 ) where rownum<=31;