Oracle调整redo log大小

1 前言

前几天工作中生产环境数据库出现了一个很有趣的问题，早上看监控发现某个不太重要的业务系统的表空间满了，持续了差不多一个晚上，竟然没收到任何通知，赶忙上去扩容。

本以为完事大吉，扩容后过了好一会儿，工单还没录完，同一个库再次告警：归档空间满了。登录服务器看了一下，归档路径下有将近一千多个归档日志文件。

联系刚刚扩容表空间的情况，猜测就是这个库在做什么变更，大量在写入数据，redo日志组又比较小（只有默认50M），导致一致在切换日志，导致归档剧增。联系了应用负责人，确认了在进行版本升级，在往数据库导入大量数据。

以上的事件中，其中一个问题就是redo log file比较小，导致当数据库有大量进行修改的事务时会一直在切换，频繁生成归档日志文件，这会让服务器有较大的I/O压力，也会加剧文件系统的负担。

切换较快的影响：

• 频繁的日志切换会增加 CPU 和 I/O 负载，因为每次切换都需要更新控制文件和数据字典，并且产生新的归档。
• 等待事件频繁出现log file switch，日志组循环写满以后，LGWR进程要覆盖先前的日志文件未完成归档（archiving needed）或者数据落盘（checkpoint incomplete），就会导致无法切换，出现该等待，数据库将陷于停顿状态，直到要覆盖的日志文件完成归档或者数据落盘。

切换较慢影响：

• 检查点相差较长，影响数据库恢复时间，因为实例在启动时需要回滚所有未提交的事务和前滚所有已提交的事务，这可能需要较长时间。

因此，在生产环境中，设置一个相对合理的redo log大小是十分重要的，可以提升数据库的整体性能，减轻系统I/O负担，数据库恢复时间合理。

理性状态下，平均一个小时切换2-4次，6-8个日志组。

下面介绍在单机、集群环境中调整redo日志文件大小的方法。

2 查看日志切换情况

使用以下脚本查看日志每小时切换次数，平均每小时2-4次合适，也就是15-30分钟切一次日志。

set linesize 120
set pagesize 100
column day format a15 heading 'Day'
column d_0 format a3 heading '00'
column d_1 format a3 heading '01'
column d_2 format a3 heading '02'
column d_3 format a3 heading '03'
column d_4 format a3 heading '04'
column d_5 format a3 heading '05'
column d_6 format a3 heading '06'
column d_7 format a3 heading '07'
column d_8 format a3 heading '08'
column d_9 format a3 heading '09'
column d_10 format a3 heading '10'
column d_11 format a3 heading '11'
column d_12 format a3 heading '12'
column d_13 format a3 heading '13'
column d_14 format a3 heading '14'
column d_15 format a3 heading '15'
column d_16 format a3 heading '16'
column d_17 format a3 heading '17'
column d_18 format a3 heading '18'
column d_19 format a3 heading '19'
column d_20 format a3 heading '20'
column d_21 format a3 heading '21'
column d_22 format a3 heading '22'
column d_23 format a3 heading '23'
select
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_8,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0))) d_21,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'22',1,0))) d_22,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0)),0,'-',sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'23',1,0))) d_23
from
gv$log_history where first_time> sysdate-60
group by
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15)
order by
substr(to_char(FIRST_TIME,'YYYY/MM/DD,DY'),1,15) desc;


3 查看日志文件和日志组信息

主要是确认日志文件的组别、路径、大小、线程、成员、状态等信息。

SQL>
set lines 200;
col member for a50
select * from v$logfile;
select group#,thread#,sequence#,round(bytes/1024/1024,2) size_mb,members,archived,status,first_change#,first_time from v$log;
select group#,thread#,round(bytes/1024/1024,2) size_mb,status from v$standby_log;


4 计算redo日志组的合理值

根据2 查看日志切换情况和3 查看日志文件和日志组信息获得的信息计算日志文件所需调整的值。

4.1 扩展

当日志切换频率比较快，每小时切换次数远大于4次，需要扩展redo日志文件。

例如，当前redo log file的大小为200M，一个小时切换了20次，那么一个小时的日志量就是2000M，要将切换频率缩减到一个小时4次，则redo log file的大小为4000M/4=1G。

4.2 收缩

当日志切换频率比较慢，几个小时才切一次，则需要减小redo日志文件。

例如，redo log file的大小为2G，一个小时切换一次，那么将redo日志文件减小到512M则可以保障日志文件一个小时切换4次。

4.3 实验用例

当前日志文件大小为200M，6个组，每组1个成员。调整为大小1G，6个组，每组1个成员。

5 redo log替换过程

5.1 单机环境

5.1.1 添加两组临时日志文件

Oracle数据库最少需要存在两组在线重做日志文件，只添加一组无法同时删除全部旧的日志文件，会报下列错误：

ORA-01567: dropping log 3 would leave less than 2 log files for instance orcl (thread 1)

这个机制是为了避免当删除了旧的日志文件后，只剩下一个临时的日志文件，如果这时候这个日志满了，而新的文件还未添加进来，那就无法进行日志切换，数据库就会卡住。

SQL>
alter database add logfile group 7 '/u01/app/oracle/oradata/orcl/redo07' size 1024M;
alter database add logfile group 8 '/u01/app/oracle/oradata/orcl/redo08' size 1024M;


5.1.2 删除旧的日志文件

日志文件有3种状态：CURRENT、ACTIVE和INACTIVE，只有当状态为INACTIVE时，才允许删除日志文件。

使用添加的临时日志文件用作日志记录，手动切换日志和生成检查点，将旧的日志文件状态切到INACTIVE后删除。

小技巧：可以在一个窗口切日志，另开一个窗口看日志状态。

切日志:

将1-6组日志文件切到INACTIVE状态，7或8组日志文件切到CURRENT状态。

SQL> alter system switch logfile;


生成检查点（当旧日志为ACTIVE时执行）:

SQL> alter system checkpoint;


检查状态:

SQL> select * from v$log;


删除旧的日志文件：

当状态都为INACTIVE时删除。

SQL>
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;


OS层删除物理文件：

存储方式为文件系统的实例在执行alter database drop logfile命令删除redo日志文件后，OS层的物理文件并不会删除，空间得不到释放，所以需要到指定目录下使用rm命令进行手动删除。

5.1.3 添加新的日志文件

根据前面计算的结果重新添加新的日志组。

添加新日志文件：

SQL>
alter database add logfile group 1 '/u01/app/oracle/oradata/orcl/redo01' size 1024M;
alter database add logfile group 2 '/u01/app/oracle/oradata/orcl/redo02' size 1024M;
alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03' size 1024M;
alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04' size 1024M;
alter database add logfile group 5 '/u01/app/oracle/oradata/orcl/redo05' size 1024M;
alter database add logfile group 6 '/u01/app/oracle/oradata/orcl/redo06' size 1024M;


5.1.4 删除临时创建的日志文件

不断切换日志，令日志组7和8的状态为INACTIVE，然后删除。

切日志：

SQL> alter database switch logfile;


删除：

SQL>
alter database drop logfile group 7;
alter database drop logfile group 8;


5.2 RAC集群环境

5.2.1 添加两组临时日志文件

集群和单机有所不同，单机中一定只有一个线程，而集群中有几个实例，那日志文件就有几个线程。

RAC集群环境中添加日志文件时，需要都为每个线程添加，保持大小相同、个数相同和成员数目相同。

假设为2节点集群添加两组日志文件，则每个线程添加两个，一共就是4个：

节点1：
alter database add logfile thread 1 group 13 '+DATA' size 1024M;
alter database add logfile thread 1 group 14 '+DATA' size 1024M;

节点2：
alter database add logfile thread 2 group 15 '+DATA' size 1024M;
alter database add logfile thread 2 group 16 '+DATA' size 1024M;


注意：命令在任意一个节点执行就好。

5.2.2 删除旧的日志文件

切日志:

将1-12组日志文件切到INACTIVE状态，13、14或15、16组日志文件切到CURRENT状态。

SQL> alter system switch logfile;


生成检查点（当旧日志为ACTIVE时执行）:

SQL> alter system checkpoint;


检查状态:

SQL> select * from v$log;


删除旧的日志文件：

当状态都为INACTIVE时删除。

SQL>
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;


5.2.3 添加新的日志文件

注意区分不同的线程。

添加新日志文件：

--节点1：
SQL>
alter database add logfile thread 1 group 1 '+DATA' size 1024M;
alter database add logfile thread 1 group 2 '+DATA' size 1024M;
alter database add logfile thread 1 group 3 '+DATA' size 1024M;
alter database add logfile thread 1 group 4 '+DATA' size 1024M;
alter database add logfile thread 1 group 5 '+DATA' size 1024M;
alter database add logfile thread 1 group 6 '+DATA' size 1024M;

--节点2：
SQL>
alter database add logfile thread 2 group 7 '+DATA' size 1024M;
alter database add logfile thread 2 group 8 '+DATA' size 1024M;
alter database add logfile thread 2 group 9 '+DATA' size 1024M;
alter database add logfile thread 2 group 10 '+DATA' size 1024M;
alter database add logfile thread 2 group 11 '+DATA' size 1024M;
alter database add logfile thread 2 group 12 '+DATA' size 1024M;


5.2.4 删除临时创建的日志文件

不断切换日志，令13-16日志组的状态为INACTIVE，然后删除。

切日志：

SQL> alter database switch logfile;


删除：

SQL>
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;