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

来源：互联网发布：c语言编程病毒编辑：程序博客网时间：2024/06/05 04:18

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

$ 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-072        1       18    52428800 2       NO INACTIVE 1533430    23-NOV-073        2       15    52428800 2       NO INACTIVE 1530271    23-NOV-074        2       14    52428800 2       YES INACTIVE 1526605    23-NOV-075        1       19    52428800 2       NO CURRENT   1553194    23-NOV-076        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 listDATABASE log mode Archive ModeAutomatic archival EnabledArchive destination +DG1/devdb/Oldest online log sequence 19Next log sequence TO archive 21Current log sequence 21

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

ASMCMD> lsdgState Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks NameMOUNTED 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> ls2007_11_22/2007_11_23/2007_11_24/ASMCMD> rm -rf 2007_11_22/ASMCMD> ls2007_11_23/2007_11_24/ASMCMD> rm -rf 2007_11_23/ASMCMD> ls2007_11_24/ASMCMD> lsdgState Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks NameMOUNTED 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 - ProductionWITH 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-072        1       20    52428800 2       YES INACTIVE 1605211      24-NOV-073        2       18    52428800 2       NO CURRENT   1673244      24-NOV-074        2       17    52428800 2       YES INACTIVE 1630973      24-NOV-075        1       19    52428800 2       YES INACTIVE 1553194      23-NOV-076        2       16    52428800 2       YES INACTIVE 1533467      23-NOV-07