ORA-00257错误的解决方法(RHEL4+RAC+ASM)

今天早上，登录数据库就报以下错误

$ sqlplus lijie/lijie;
 
SQL*Plus: Release 10.2.0.1.0 - Production ON Sat Nov 24 10:57:48 2007
 
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
 
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
 
SQL> SELECT * FROM v$log;
 
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS  FIRST_CHANGE# FIRST_TIM
---------- ---------- -------  -----  ----- -------- ---         --------
1        1       17    52428800 2       NO INACTIVE 1532387    23-NOV-07
2        1       18    52428800 2       NO INACTIVE 1533430    23-NOV-07
3        2       15    52428800 2       NO INACTIVE 1530271    23-NOV-07
4        2       14    52428800 2       YES INACTIVE 1526605    23-NOV-07
5        1       19    52428800 2       NO CURRENT   1553194    23-NOV-07
6        2       16    52428800 2       NO CURRENT    1533467   23-NOV-07

注意：发现很多日志都没有归档

查看报警日志文件，发现有日志不能归档的错误信息。

$ tail -100 alert_devdb1.log

其中一部分

#######################################################################
Errors in file /u01/app/oracle/admin/devdb/bdump/devdb1_arc1_30745.trc:
ORA-16038: log 1 sequence# 17 cannot be archived
ORA-19504: failed to create file “”
ORA-00312: online log 1 thread 1: ‘+DG1/devdb/onlinelog/group_1.261.639177695′
ORA-00312: online log 1 thread 1: ‘+RECOVERYDEST/devdb/onlinelog/group_1.257.639177701′
Sat Nov 24 13:23:10 2007
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 17 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Nov 24 13:23:10 2007
ORACLE Instance devdb1 – Archival Error
Sat Nov 24 13:23:10 2007
ORA-16014: log 1 sequence# 17 not archived, no available destinations
ORA-00312: online log 1 thread 1: ‘+DG1/devdb/onlinelog/group_1.261.639177695′
ORA-00312: online log 1 thread 1: ‘+RECOVERYDEST/devdb/onlinelog/group_1.257.639177701′
###########################################################################

查看归档路径

SQL> archive log list
DATABASE log mode Archive Mode
Automatic archival Enabled
Archive destination +DG1/devdb/
Oldest online log sequence 19
Next log sequence TO archive 21
Current log sequence 21

使用ASMCMD命令(具体命令，可以使用help)

ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 512 4096 1048576 6134 4 0 2 0 DG1/
MOUNTED EXTERN N N 512 4096 1048576 2047 1029 0 1029 0 RECOVERYDE
 
发现可用空间只有4M
 
解决办法:
 
1、改变归档路径
2、删除部分归档，释放空间
 
我使用第二种方法
 
ASMCMD> ls
2007_11_22/
2007_11_23/
2007_11_24/
ASMCMD> rm -rf 2007_11_22/
ASMCMD> ls
2007_11_23/
2007_11_24/
ASMCMD> rm -rf 2007_11_23/
ASMCMD> ls
2007_11_24/
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED NORMAL N N 512 4096 1048576 6134 1936 0 968 0 DG1/
MOUNTED EXTERN N N 512 4096 1048576 2047 1029 0 1029 0 RECOVERYDEST/
 
删除两天的归档，空间得到释放
 
$ export ORACLE_SID=devdb1
$ sqlplus lijie/lijie
 
SQL*Plus: Release 10.2.0.1.0 - Production ON Sat Nov 24 13:29:22 2007
 
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
WITH the Partitioning, Real Application Clusters, OLAP AND DATA Mining options
 
SQL> SELECT * FROM v$log;
 
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------------
1        1       21    52428800 2       NO CURRENT   1684214      24-NOV-07
2        1       20    52428800 2       YES INACTIVE 1605211      24-NOV-07
3        2       18    52428800 2       NO CURRENT   1673244      24-NOV-07
4        2       17    52428800 2       YES INACTIVE 1630973      24-NOV-07
5        1       19    52428800 2       YES INACTIVE 1553194      23-NOV-07
6        2       16    52428800 2       YES INACTIVE 1533467      23-NOV-07