RMAN删除归档日志时将ASM上空目录同时删除

ASM上的bug还不少，在新建的RAC环境中碰到了这个bug。

在基于ASM的RAC环境中准备执行IMP导入操作，在导入操作执行之前，先对数据库进行了备份，随后备份了所有的归档日志并执行了删除操作。

然后执行导入操作，但是很快发现导入操作被HANG住了，检查数据库的会话发现，会话在等待归档的完成。而此时数据库的当前实例的在线日志已经写满，归档操作则一直没有完成。

SQL> SELECT SID, EVENT, PROGRAM
2 FROM V$SESSION
3 WHERE SID = 162;

SID EVENT PROGRAM
---------- ---------------------------------------- ----------------------------------
162 log file switch (archiving needed) imp@ser1 (TNS V1-V3)

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME, THREAD#
2 FROM V$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME THREAD#
--------------- ---------------- ----------
1 tradedb1 1

SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS
2 FROM V$LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 34 NO INACTIVE
2 1 36 NO INACTIVE
3 1 35 NO INACTIVE
4 1 37 NO CURRENT
5 2 13 YES INACTIVE
6 2 14 YES INACTIVE
7 2 15 NO INACTIVE
8 2 16 NO CURRENT

已选择8行。

手工执行归档则报错：

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT
*第 1 行出现错误:
ORA-16014: 日志 7 的序列号 15 未归档, 没有可用的目的地
ORA-00312: 联机日志 7 线程 2: '+MEMBER/tradedb/tradedb_redo2_3_1_1g'
ORA-00312: 联机日志 7 线程 2: '+MEMBER/tradedb/tradedb_redo2_3_2_1g'

检查alert文件，同样大量的归档错误信息：

Thu Aug 20 11:34:38 2009
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Aug 20 11:34:38 2009
ORACLE Instance tradedb1 - Archival Error
Thu Aug 20 11:34:38 2009
ORA-16038: log 4 sequence# 4 cannot be archived
ORA-00254: error in archive control string ''
ORA-00312: online log 4 thread 1: '+MEMBER/tradedb/tradedb_redo1_4_1_1g'
ORA-00312: online log 4 thread 1: '+MEMBER/tradedb/tradedb_redo1_4_2_1g'
ORA-15173: entry 'archivelog' does not exist in directory 'tradedb'
Thu Aug 20 11:34:38 2009
Errors in file /data/oracle/admin/tradedb/bdump/tradedb1_arc1_10016.trc:
ORA-16038: log 4 sequence# 4 cannot be archived
ORA-00254: error in archive control string ''
ORA-00312: online log 4 thread 1: '+MEMBER/tradedb/tradedb_redo1_4_1_1g'
ORA-00312: online log 4 thread 1: '+MEMBER/tradedb/tradedb_redo1_4_2_1g'
ORA-15173: entry 'archivelog' does not exist in directory 'tradedb'

问题显然是由于ASM上的归档路径archivelog不存在造成的。可是这个路径早在数据库建立的时候就手工建立了，而且以前归档并没有出现同样的问题。而最近只做过RMAN的备份操作，莫非是RMAN在删除归档的时候将目录同时删除了。

通过asmcmd手工添加目录后，通过RMAN执行BACKUP ARCHIVELOG ALL DELETE ALL INPUT的命令，检查是否会删除ASM上目录。

首先在节点2上连接asmcmd命令：

bash-3.00$ export ORACLE_SID=+ASM2
bash-3.00$ asmcmd
ASMCMD> cd member
ASMCMD> cd tradedb
ASMCMD> ls
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
archivelog/
control01.ctl
control02.ctl
control03.ctl
spfiletradedb.ora
tradedb_gpo_1_4g
tradedb_ndmain_1_4g
tradedb_ndmain_2_4g
tradedb_ndmain_3_4g
tradedb_ndmain_4_4g
tradedb_ndmain_5_4g
tradedb_redo1_1_1_1g
tradedb_redo1_1_2_1g
tradedb_redo1_2_1_1g
tradedb_redo1_2_2_1g
tradedb_redo1_3_1_1g
tradedb_redo1_3_2_1g
tradedb_redo1_4_1_1g
tradedb_redo1_4_2_1g
tradedb_redo2_1_1_1g
tradedb_redo2_1_2_1g
tradedb_redo2_2_1_1g
tradedb_redo2_2_2_1g
tradedb_redo2_3_1_1g
tradedb_redo2_3_2_1g
tradedb_redo2_4_1_1g
tradedb_redo2_4_2_1g
tradedb_sysaux_1_1g
tradedb_sysaux_2_1g
tradedb_system_1_4g
tradedb_temp_1_4g
tradedb_temp_2_4g
tradedb_temp_3_4g
tradedb_temp_4_4g
tradedb_temp_5_4g
tradedb_undotbs1_1_4g
tradedb_undotbs1_2_4g
tradedb_undotbs1_3_4g
tradedb_undotbs1_4_4g
tradedb_undotbs1_5_4g
tradedb_undotbs2_1_4g
tradedb_undotbs2_2_4g
tradedb_undotbs2_3_4g
tradedb_undotbs2_4_4g
tradedb_undotbs2_5_4g
tradedb_users_1_200m
tradedb_zhejiang_10_4g
tradedb_zhejiang_11_4g
tradedb_zhejiang_12_4g
tradedb_zhejiang_13_4g
tradedb_zhejiang_14_4g
tradedb_zhejiang_15_4g
tradedb_zhejiang_16_4g
tradedb_zhejiang_17_4g
tradedb_zhejiang_18_4g
tradedb_zhejiang_19_4g
tradedb_zhejiang_1_4g
tradedb_zhejiang_20_4g
tradedb_zhejiang_2_4g
tradedb_zhejiang_3_4g
tradedb_zhejiang_4_4g
tradedb_zhejiang_5_4g
tradedb_zhejiang_6_4g
tradedb_zhejiang_7_4g
tradedb_zhejiang_8_4g
tradedb_zhejiang_9_4g
ASMCMD> cd archivelog
ASMCMD> ls
1_31_694968965.dbf
1_32_694968965.dbf
2009_08_25/
2009_08_27/
2_13_694968965.dbf
ASMCMD> cd ..

可以看到，现在archivelog目录是存在的，下面用RMAN执行BACKUP ARCHIVELOG ALL DELETE ALL INPUT命令：

bash-3.00$ rman target /

恢复管理器: Release 10.2.0.3.0 - Production on 星期四 8月 27 23:40:34 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: TRADEDB (DBID=4181457554)

RMAN> run
2> {
3> allocate channel c1 device type disk format '/data/backup/%U';
4> backup archivelog all delete all input;
5> }

使用目标数据库控制文件替代恢复目录分配的通道: c1通道 c1: sid=320 实例=tradedb1 devtype=DISK

启动 backup 于 27-8月 -09当前日志已存档通道 c1: 正在启动存档日志备份集通道 c1: 正在指定备份集中的存档日志输入存档日志线程 =1 序列 =31 记录 ID=6046 时间戳=695832644输入存档日志线程 =1 序列 =32 记录 ID=6047 时间戳=696017068输入存档日志线程 =1 序列 =33 记录 ID=6050 时间戳=696037299输入存档日志线程 =2 序列 =13 记录 ID=6048 时间戳=696017368输入存档日志线程 =2 序列 =14 记录 ID=6049 时间戳=696037569通道 c1: 正在启动段 1 于 27-8月 -09通道 c1: 已完成段 1 于 27-8月 -09段句柄=/data/backup/agknpbtr_1_1 标记=TAG20090827T234146 注释=NONE通道 c1: 备份集已完成, 经过时间:00:00:26通道 c1: 正在删除存档日志存档日志文件名 =+MEMBER/tradedb/archivelog/1_31_694968965.dbf 记录 ID=6046 时间戳 =695832644存档日志文件名 =+MEMBER/tradedb/archivelog/1_32_694968965.dbf 记录 ID=6047 时间戳 =696017068存档日志文件名 =+MEMBER/tradedb/archivelog/1_33_694968965.dbf 记录 ID=6050 时间戳 =696037299存档日志文件名 =+MEMBER/tradedb/archivelog/2_13_694968965.dbf 记录 ID=6048 时间戳 =696017368存档日志文件名 =+MEMBER/tradedb/archivelog/2_14_694968965.dbf 记录 ID=6049 时间戳 =696037569完成 backup 于 27-8月 -09释放的通道: c1

现在回到节点2上的asmcmd命令，检查archivelog目录：

ASMCMD> ls
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
control01.ctl
control02.ctl
control03.ctl
spfiletradedb.ora
tradedb_gpo_1_4g
tradedb_ndmain_1_4g
tradedb_ndmain_2_4g
tradedb_ndmain_3_4g
tradedb_ndmain_4_4g
tradedb_ndmain_5_4g
tradedb_redo1_1_1_1g
tradedb_redo1_1_2_1g
tradedb_redo1_2_1_1g
tradedb_redo1_2_2_1g
tradedb_redo1_3_1_1g
tradedb_redo1_3_2_1g
tradedb_redo1_4_1_1g
tradedb_redo1_4_2_1g
tradedb_redo2_1_1_1g
tradedb_redo2_1_2_1g
tradedb_redo2_2_1_1g
tradedb_redo2_2_2_1g
tradedb_redo2_3_1_1g
tradedb_redo2_3_2_1g
tradedb_redo2_4_1_1g
tradedb_redo2_4_2_1g
tradedb_sysaux_1_1g
tradedb_sysaux_2_1g
tradedb_system_1_4g
tradedb_temp_1_4g
tradedb_temp_2_4g
tradedb_temp_3_4g
tradedb_temp_4_4g
tradedb_temp_5_4g
tradedb_undotbs1_1_4g
tradedb_undotbs1_2_4g
tradedb_undotbs1_3_4g
tradedb_undotbs1_4_4g
tradedb_undotbs1_5_4g
tradedb_undotbs2_1_4g
tradedb_undotbs2_2_4g
tradedb_undotbs2_3_4g
tradedb_undotbs2_4_4g
tradedb_undotbs2_5_4g
tradedb_users_1_200m
tradedb_zhejiang_10_4g
tradedb_zhejiang_11_4g
tradedb_zhejiang_12_4g
tradedb_zhejiang_13_4g
tradedb_zhejiang_14_4g
tradedb_zhejiang_15_4g
tradedb_zhejiang_16_4g
tradedb_zhejiang_17_4g
tradedb_zhejiang_18_4g
tradedb_zhejiang_19_4g
tradedb_zhejiang_1_4g
tradedb_zhejiang_20_4g
tradedb_zhejiang_2_4g
tradedb_zhejiang_3_4g
tradedb_zhejiang_4_4g
tradedb_zhejiang_5_4g
tradedb_zhejiang_6_4g
tradedb_zhejiang_7_4g
tradedb_zhejiang_8_4g
tradedb_zhejiang_9_4g
ASMCMD> ls archivelog
asmcmd: entry 'archivelog' does not exist in directory '+member/tradedb/'

显然RMAN在删除归档日志的同时删除空目录。

查询了metalink文档，发现果然是Oracle的bug，metalink文档Bug No. 4431215描述了这个问题。如果在ASM上建立的目录名称和SID前缀相同，则这个目录下的所有目录一旦为空，就会被ASM实例自动删除，不管这个目录是ASM自动建立，还是用户手工建立的。

解决这个问题的办法倒是很多，比如将归档目录设置到日志文件的目录中，或者改变主目录的名称使其和ORACLE_SID有所区别。还有更简单的办法，在归档目录下拷贝一个小文件，使得归档被删除后，归档目录不为空，从而避免路径被清除。

Oracle在10.2.0.4和11.1.0.6中fixed了这个bug。因此升级或打PATCH也是解决这个问题的一个方法。

比如在执行了下面的命令后，就可以确保archivelog目录不被删除了：

SQL> create pfile='/export/home/oracle/inittradedb1.ora' from spfile;

文件已创建。

SQL> create spfile='+MEMBER/tradedb/archivelog/spfile.ora' from pfile='/export/home/oracle/inittradedb1.ora';

文件已创建。