前天早上北京一客户那边应用程序报错,无法连接数据库,以连接上的也无法做任何操作,但是没有一会,又恢复正常。
我从alter_orcl1.log文件中发现有如下报错信息产生:
Thread 1 advanced to log sequence 10017 (LGWR switch)ITPUB个人空间!N W BN)t
Current log# 1 seq# 10017 mem# 0: +ORCL_DATA1/orcl/onlinelog/group_1.261.702099307
x L.l o Z0~ m0 Current log# 1 seq# 10017 mem# 1: +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.259.702099307
b"U V2\ [/v0Mon Mar 1 09:11:07 2010
r y4j C |%m T*Y#|0Thread 1 cannot allocate new log, sequence 10018ITPUB个人空间8~4d&} n T)l({&v J$C
Checkpoint not complete
E!Z {1eu0 Current log# 1 seq# 10017 mem# 0: +ORCL_DATA1/orcl/onlinelog/group_1.261.702099307ITPUB个人空间7?N A6T$E,j
Current log# 1 seq# 10017 mem# 1: +FLASH_RECOVERY_AREA/orcl/onlinelog/group_1.259.702099307
由此信息可以看出是因为重做日志未归档完毕而照成的。
从日志信息中发现2个问题:
1.重做日志切换时间比较短,平均在1分钟左右
2。时常会看到上面提到的报错信息存在
鉴于此问题,可以考虑增加重做日志组或者大小。
客户这边的情况如表1,根据客户这边的情况,我们计划采取的方法为:
重做日志组 线程 大小 1 1 50M 2 2 50M 3 1 50M 4 2 50M表1
1.为2个节点分别增加重做日志组5,6,7,8,大小设置为200M
SQL>alter database add logfile thread 1 group 5 size 200m;
SQL>alter database add logfile thread 2 group 6 size 200m;
SQL>alter database add logfile thread 1 group 7 size 200m;
SQL>alter database add logfile thread 2 group 8 size 200m;
2.删除重做日志组
//节点1
SQL>alter system switch logfile;
//节点2
SQL>alter system switch logfile;
SQL>alter database drop logfile group 1;
SQL>alter database drop logfile group 2;
SQL>alter database drop logfile group 3;
SQL>alter database drop logfile group 4;
3.重建重做日志组1,2,3,4,大小设置为200M
SQL>alter database add logfile thread 1 group 1 size 200m;
SQL>alter database add logfile thread 2 group 2 size 200m;
SQL>alter database add logfile thread 1 group 3 size 200m;
SQL>alter database add logfile thread 2 group 4 size 200m;