SELECT
TO_CHAR(first_time, 'YYYY-MON-DD') DAY
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)), '99') "00"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)), '99') "01"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)), '99') "02"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)), '99') "03"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)), '99') "04"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)), '99') "05"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)), '99') "06"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)), '99') "07"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)), '99') "0"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)), '99') "09"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)), '99') "10"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)), '99') "11"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)), '99') "12"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)), '99') "13"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)), '99') "14"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)), '99') "15"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)), '99') "16"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)), '99') "17"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)), '99') "18"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)), '99') "19"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)), '99') "20"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)), '99') "21"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)), '99') "22"
, TO_CHAR(SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)), '99') "23"
FROM v$log_history
GROUP BY TO_CHAR(first_time, 'YYYY-MON-DD') ;
根据经验值,redo log的在高峰期的切换不应超过每小时10次,redo log 文件组为5组,每个日志文件组的日志位于不同的文件系统或者存储设备上。具体redo log大小和文件组数需要根据实际情况确定。
查询现有redo log大小,日志状态,日志文件组和每组日志文件的成员数。
SELECT
group#
, thread#
, bytes / 1024 / 1024 mb
, members
, status
FROM v$log;
可以看到共有个日志文件组9个日志文件组,每组1个日志文件,第4组为正在使用的日志文件组,如果status为active说明该日志文件需要用于实例恢复。应进行归档后才能将其删除。
redo日志调整
计算redo日志大小
30(最多每小时切换次数) * 5000MB / 10 = 15000MB
根据上面计算值,理论上应将日志文件组大小扩大至15000MB,考虑到ODS数据库DML操作时间段分布不均的特点,过大的redo日志设置会使数据库在丢失当前日志文件组进行数据库恢复的时候丢失较多数据。因此,综合考虑将日志文件扩大至10GB,这样也保证了系统在不繁忙的时候可以进行redo日志的归档和切换。具体操作脚本如下,请根据实际情况调整日志文件路径和日志文件大小。
-- 注意,在删除所有归档日志之前,请保留3个日志文件组已确保系统稳定,待修改完日志文件组后再将其删除。
sqlplus / as sysdba
host mkdir -p $ORACLE_BASE/standby/redo
host mkdir -p /mnt/EMC1/redo/
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database add logfile group 1 ('/mnt/EMC1/redo/group1redo1.log', '/opt/app/oracle/standby/redo/group1redo2.log') size 10G;
alter database add logfile group 2 ('/mnt/EMC1/redo/group2redo1.log', '/opt/app/oracle/standby/redo/group2redo2.log') size 10G;
alter database add logfile group 3 ('/mnt/EMC1/redo/group3redo1.log', '/opt/app/oracle/standby/redo/group3redo2.log') size 10G;
-- 切换当前日志文件组
alter system archive log current;
alter system archive log current;
alter system checkpoint;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database add logfile group 4 ('/mnt/EMC1/redo/group4redo1.log', '/opt/app/oracle/standby/redo/group4redo2.log') size 10G;
alter database add logfile group 5 ('/mnt/EMC1/redo/group5redo1.log', '/opt/app/oracle/standby/redo/group5redo2.log') size 10G;
-- 确认日志文件组当前状态
select * from v$logfile order by member;
select * from v$log;
-- 删除操作系统上无用的日志文件组
rm -f /mnt/EMC1/redo1.log
rm -f /mnt/EMC1/redo10.log
rm -f /mnt/EMC1/redo11.log
rm -f /mnt/EMC1/redo12.log
rm -f /mnt/EMC1/redo2.log
rm -f /mnt/EMC1/redo3.log
rm -f /mnt/EMC1/redo4.log
rm -f /mnt/EMC1/redo5.log
rm -f /mnt/EMC1/redo6.log
rm -f /mnt/EMC1/redo7.log
rm -f /mnt/EMC1/redo8.log
rm -f /mnt/EMC1/redo9.log