Oracle 11g调整redo log大小 (单机、RAC集群、容灾ADG)

原创 lu9up的数据库笔记 2024-07-28

790

1 前言

最近，在生产环境中遇到一个有趣的问题：某个不太重要的业务系统的表空间满了，并且这种情况持续了整整一个晚上，但系统没有触发任何警报。我们迅速对该表空间进行了扩容。

然而，扩容后不久，同一个数据库再次发出警报——这一次是因为归档空间满了。我们登录服务器查看后发现，归档路径下积累了近一千个归档日志文件。

通过联系之前对表空间进行扩容的情况，我们推测数据库正在进行某些变更操作，导致了大量的数据写入。由于redo日志组较小（仅为50MB），这使得日志切换频繁，进而产生了大量的归档日志文件。经与应用程序负责人确认，得知他们正在进行版本升级，并向数据库导入大量数据。

此事件揭示了一个问题：当数据库执行大量修改事务时，如果redo log文件过小，会导致日志频繁切换并生成过多的归档日志文件，这不仅增加了服务器的I/O负载，还会给文件系统带来额外的压力。

切换较快的影响：

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

切换较慢影响：

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

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

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

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

2 查看日志切换情况

为了确定日志文件是否需要调整，可以使用以下SQL查询来查看每小时的日志切换次数。理想的切换频率是每小时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次，那么一个小时的日志量就是4000M，要将切换频率缩减到一个小时4次，则redo log file的大小为4000M/4=1G。

4.2 收缩

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

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

4.3 实验用例

调整项	原配置	调整后配置
大小	200M	1024M
组个数	6	6
每组成员	1	1

当前日志文件大小为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;

5.3 容灾ADG

5.3.1 确认同步是否正常

一、检查数据库角色

--主库
SQL> select db_unique_name,open_mode,switchover_status,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE
------------------------------ -------------------- -------------------- ----------------
ORCL                           READ WRITE           TO STANDBY           PRIMARY


--备库
SQL> select db_unique_name,open_mode,switchover_status,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE
------------------------------ -------------------- -------------------- ----------------
ORCLADG                        READ ONLY WITH APPLY NOT ALLOWED          PHYSICAL STANDBY

二、查看进程

主库主要看 LNS 进程，此进程负责将主数据库的重做日志条目传输到备用数据库。

SQL> select process ,status , sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING             185
ARCH      CLOSING             183
ARCH      CLOSING             159
ARCH      CLOSING             184
LNS       WRITING             186

备库主要看 MRP0 进程，此进程负责将接收到的归档日志应用到备用数据库上，以维持与主数据库的同步。MRP进程是ADG中的关键组件，它确保备用数据库的数据与主数据库保持一致。

SQL> select process ,status , sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING             183
ARCH      CLOSING             185
ARCH      CONNECTED             0
ARCH      CLOSING             184
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                186
MRP0      APPLYING_LOG        186

9 rows selected.

三、检查归档是否一致

SQL> select max(sequence#),thread# from v$archived_log where RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           185          1

四、查看GAP看是否有延迟

GAP产生的原因是，一般是备库已经长时间未与主库同步，等发现的时候，主库的归档日志已经删除，备库无法再次与主库同步，这时候GAP就产生了。

SQL> select * from v$archive_gap;

no rows selected

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

5.3.2 主库重建redo log file

我的环境中1-6组为online redo log，7-13组为standby log。

5.3.2.1 查看redo日志组及大小

查看日志文件，注意看type字段，区分在新重做日志和备用日志。

SQL> select * from v$logfile;

查看redo log信息。

SQL> select group#,thread#,sequence#,round(bytes/1024/1024,2) size_mb,members,archived,status,first_change#,first_time from v$log;

查看standby log信息。

SQL> select group#,thread#,round(bytes/1024/1024,2) size_mb,status from v$standby_log;

5.3.2.2 主库删除standby log

删除旧的standby log（7-13组）。

SQL> 
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;
alter database drop logfile group 13;

5.3.2.3 主库替换redo log

添加临时日志文件：

SQL> 
alter database add logfile group 14 '+DATA' size 1024M;
alter database add logfile group 15 '+DATA' size 1024M;

切日志，让旧的日志文件（1-6）组为INACTIVE：

SQL> 
alter system switch logfile;
alter system checkpoint;

删除1-6组日志文件：

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;

添加新的日志文件：

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

切日志，删除临时添加的日志文件：

SQL> 
alter system switch logfile;
alter system checkpoint;

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

5.3.2.4 主库添加standby log

主库用不着standby log，添加的目的是为了以后做切换的时候省事。

比redo log多加一组：

SQL> 
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;
alter database add standby logfile '+DATA' size 1024m;

5.3.3 备库重建standby log

5.3.3.1 查看redo日志组及大小

查看日志文件信息

SQL> select * from v$logfile;
SQL> select group#,thread#,round(bytes/1024/1024,2) size_mb,status from v$standby_log;

5.3.3.2 备库取消日志应用

查看是否有lag。

SQL> 
set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

取消日志应用

SQL> alter database recover managed standby database cancel;

5.3.3.3 修改日志管理模式为手动

SQL> alter system set standby_file_management='manual';

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      manual

5.3.3.4 备库删除备库standby log

添加两组临时standby log file。

alter database add standby logfile group 14 '+DATA' size 50m reuse;
alter database add standby logfile group 15 '+DATA' size 50m reuse;

在主库切日志，将active状态切到临时文件上，将所有旧的standby log的status刷到UNASSIGNED：

--主库操作
SQL> alter system switch logfile;

--备库查看
SQL> select group#,thread#,round(bytes/1024/1024,2) size_mb,status from v$standby_log;

删除旧的standby log file：

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;
alter database drop logfile group 13;

5.3.3.5 备库新建standby log

重新添加standby log，大小为1024m，7-13组。

SQL> 
alter database add standby logfile group 7 '+DATA' size 1024m reuse;
alter database add standby logfile group 8 '+DATA' size 1024m reuse;
alter database add standby logfile group 9 '+DATA' size 1024m reuse;
alter database add standby logfile group 10 '+DATA' size 1024m reuse;
alter database add standby logfile group 11 '+DATA' size 1024m reuse;
alter database add standby logfile group 12 '+DATA' size 1024m reuse;
alter database add standby logfile group 13 '+DATA' size 1024m reuse;

主库不断切日志，多切几次，将临时standby logfile切到UNASSIGNED状态，然后删除：

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

5.3.3.6 备库redo log

通常情况下，备库为只读模式，不对数据库进行修改，不会启用redo log files。

备库中修改日志文件也不太方便，因为adg备库在只读模式下，切不了日志，所以不好删除日志文件。

因此，redo log改不改都无所谓，不影响数据同步，可以在做切换的时候再进行修改。

5.3.3.7 打开实时应用日志和自动日志管理

完成以上动作后，adg环境的redo logfile和standby logfile就算替换完成了，接下来只需要恢复数据同步即可。

alter database recover managed standby database using current logfile disconnect;
alter system set standby_file_management='AUTO';

最后修改时间：2024-07-30 00:14:58

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

oracle（共23篇）

oracle上大分