Oracle 11g rac + Dataguard 环境调整 redo log 大小

睿思达DBA_WGX

已于 2025-01-05 19:57:48 修改

阅读量2.1k

点赞数 49

分类专栏： Oracle体系结构 Oracle 数据运维文章标签： oracle 数据库

于 2025-01-04 17:45:59 首次发布

本文链接：https://blog.csdn.net/weixin_44377973/article/details/144932948

版权

Oracle 11g rac + Dataguard 环境调整 redo log 大小

一、问题的产生

客户的生产库为 Oracle 11g rac双节点集群，并且配置有Dataguard，近期业务出现卡顿，检查发现日志文件异常，系统一共配置6组redo log（1号线程与2号线程各3组，每个日志文件的大小为100MB）与8组standby log（1号线程与2号线程各4组）。

1、查看日志文件、日志组信息

（1）日志文件信息

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE MEMBER   IS_
-----------------------------------------------------------------------------------
	 1	   ONLINE  +DATA/hisdb/onlinelog/redo01.log  NO
	 2	   ONLINE  +DATA/hisdb/onlinelog/redo02.log  NO
	 4	   ONLINE  +DATA/hisdb/onlinelog/redo04.log  NO
	 5	   ONLINE  +DATA/hisdb/onlinelog/redo05.log  NO
	 6	   ONLINE  +DATA/hisdb/onlinelog/redo06.log  NO
	 3	   ONLINE  +DATA/hisdb/onlinelog/redo03.log  NO
	 7	   STANDBY  +DATA/hisdb/onlinelog/group_7.446.1121009477   NO
	 8	   STANDBY  +DATA/hisdb/onlinelog/group_8.447.1121009483   NO
	 9	   STANDBY  +DATA/hisdb/onlinelog/group_9.448.1121009489   NO
	10	   STANDBY  +DATA/hisdb/onlinelog/group_10.449.1121009493  NO
	11	   STANDBY  +DATA/hisdb/onlinelog/group_11.450.1121009499  NO
	12	   STANDBY  +DATA/hisdb/onlinelog/group_12.451.1121009507  NO
	13	   STANDBY  +DATA/hisdb/onlinelog/group_13.452.1121009507  NO
	14	   STANDBY  +DATA/hisdb/onlinelog/group_14.453.1121009507  NO
14 rows selected.

（2）日志组信息

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

GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS  FIRST_CHANGE# FIRST_TIME
------------- ---------------------- ---------- ---------- ---------- ---------- --- ----------
	 1	    2	  114460	100	     1 YES ACTIVE    9057709636 03-JAN-25
	 2	    2	  114461	100	     1 YES ACTIVE    9057751045 03-JAN-25
	 3	    2	  114462	100	     1 NO  CURRENT   9057800557 03-JAN-25
	 4	    1	  324121	100	     1 YES ACTIVE    9057794541 03-JAN-25
	 5	    1	  324122	100	     1 NO  CURRENT   9057807196 03-JAN-25
	 6	    1	  324120	100	     1 YES ACTIVE   9057790272 03-JAN-25
6 rows selected.

（3）standby log信息

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

    GROUP#    THREAD#	 SIZE_MB STATUS
---------- ---------- ---------- ----------
	 7	    1	     100 UNASSIGNED
	 8	    1	     100 UNASSIGNED
	 9	    1	     100 UNASSIGNED
	10	    1	     100 UNASSIGNED
	11	    2	     100 UNASSIGNED
	12	    2	     100 UNASSIGNED
	13	    2	     100 UNASSIGNED
	14	    2	     100 UNASSIGNED
8 rows selected.

2、日志切换较快出现的问题

频繁的日志切换会增加CPU和I/O负载，因为每次切换都需要更新控制文件和数据字典，并且产生新的归档。

日志组循环写满以后，LGWR进程要覆盖先前的日志文件，如果未完成归档会导致无法切换，出现等待，数据库将陷于停顿状态，直到要覆盖的日志文件完成归档。

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

理想性状态下，平均一个小时切换2-4次较为合理。

使用以下脚本可以查看日志每小时切换次数，平均每小时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