数据库运维服务

 对客户DB环境进行巡检过程中,竟然发现其中一个RAC数据库的两个节点的ALERT日志均有错误,其中一个节点日志文件更是很快到达了2G,马上提起分析日志暴增的原因。初步查看发现,两个节点通过PL/SQL均不能连接,但是本机能正常登陆,而查询业务语句只有在节点1可以运行,但节点1也经常处于挂起状态。

错误日志:

节点1的ALERT日志:

Wed Jul 13 04:06:26 2011 

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=214 

System State dumped to trace file /u01/app/oracle/admin/port/udump/port1_ora_4668.trc 

Wed Jul 13 06:26:59 2011 

Errors in file /u01/app/oracle/admin/port/bdump/port1_j000_3593.trc: 

ORA-12012: error on auto execute of job 42780 

ORA-27468: "." is locked by another process 

Wed Jul 13 06:49:44 2011 

节点2的ALERT日志:

Tue Jul 12 22:57:19 2011 Thread 2 advanced to log sequence 6852 (LGWR switch) 

  Current log# 4 seq# 6852 mem# 0: +DATA/port/onlinelog/group_4.270.697238219 

  Current log# 4 seq# 6852 mem# 1: +DATA/port/onlinelog/group_4.271.697238221 

Wed Jul 13 01:51:41 2011 Wed Jul 13 01:51:41 2011 

ARCH: Archival stopped, error occurred. Will continue retrying 

Wed Jul 13 01:51:41 2011 

ORACLE Instance port2 - Archival Error 

Wed Jul 13 01:51:41 2011 

ORA-16038: log 4 sequence# 6852 cannot be archived 

ORA-00254: error in archive control string '' 

ORA-00312: online log 4 thread 2: '+DATA/port/onlinelog/group_4.270.697238219' 

ORA-00312: online log 4 thread 2: '+DATA/port/onlinelog/group_4.271.697238221' 

ORA-15173: entry 'archivelog' does not exist in directory 'port' 

从以上日志可以看出,故障发生时间在01:20:27-01:21:36之间,为什么这么说,因为在01:21:36时候已经出现“Checkpoint not complete”错误了,其实就是归档出了问题。并且要命的是PLSQL连接提示00257错误,这个错误经验性的让人想到是日志空间满了,事实上只要仔细看如上的“ORA-15173: entry ‘archivelog’ does not exist in directory ‘port’”错误和ORA-00254错误,应该可以定位故障是主库归档目录丢失的问题。

回忆最近的备份调整策略,由于磁盘使用空间日益增长,短期内无法扩展磁盘,于是把归档日志清理策略改成每日一清,并且每次彻底清理所有归档日志:

backup archivelog all format '/u01/archive/arch_%U'; 

delete noprompt archivelog until time 'sysdate -3'; 

delete noprompt archivelog until time 'sysdate -2'; 

delete noprompt archivelog until time 'sysdate -1'; 

delete noprompt archivelog until time 'sysdate -0'; 

来到这里,问题似乎跟这个sysdate-0有关,于是上网查了一下11G的确存在BUG4431215,该BUG明确表示,如采用这种’sysdate -0’归档清理时,会将目录一起清除掉,这个时候,我们不难发现更简便的方法,通过ASMCMD直接创建丢失的archivelog目录就可以了。

[oradba@oracle1 rmanbak]$ export ORACLE_SID=+ASM1 

[oradba@oracle1 rmanbak]$ asmcmd 

ASMCMD> cd data 

ASMCMD> mkdir archivelog 

ASMCMD> ls 

PORT/ 

archivelog/ 

总结:该BUG主要触发在ASM磁盘组,当命令"delete noprompt archivelog until time ‘sysdate -0’; 发出后,"删除所有归档日志之余,会将归档目录一起清理掉,所以该命令一定要谨慎处理,避免这种意外发生。