ASM路径问题导致数据库不能正常启动 -- 报:ORA-03113: end-of-file on communication channel

时间:2024-01-14 13:29:56

环境描述:

操作系统版本:Red Hat Enterprise Linux Server release 6.5 (Santiago)

数据库版本:Oracle 11.2.0.4 RAC

场景描述:

Oracle RAC环境搭建完成之后,数据库可以启动到mounted状态,无法启动到open状态;其他的CRS集群服务、ASM服务状态正常!!!

处理过程:

[oracle@oracle01 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 12:48:00 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options SQL>
SQL> select open_mode from v$database; OPEN_MODE
--------------------
MOUNTED SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

++++++++++++++++++++++++++++++++++++++++++

再次尝试:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now 启动数据库一直停留在Database mounted.
最后报ORA-03113: end-of-file on communication channel

++++++++++++++++++++++++++++++++++++++++

查看告警日志,输出如下错误信息:

<txt>ORACLE Instance EBJDB1 - Archival Error
</txt>
</msg>
<msg time='2017-06-15T14:38:26.640+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='oracle01' host_addr='127.0.0.1' module=''
pid='7977'>
<txt>ORA-16014: log 1 sequence# 5 not archived, no available destinations
ORA-00312: online log 1 thread 1: &apos;+DATA/ebjdb/onlinelog/group_1.261.945309179&apos;
ORA-00312: online log 1 thread 1: &apos;+FRA/ebjdb/onlinelog/group_1.257.945309181&apos;
</txt>
</msg>
<msg time='2017-06-15T14:38:26.857+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='oracle01' host_addr='127.0.0.1' module=''
pid='7977'>
<txt>Archiver process freed from errors. No longer stopped
</txt>
</msg>

有戏!!!

初步分析是:日志不能正常归档,导致了数据库只能在mounted状态,在往open状态切换的时候,找不到相关的日志归档路径,导致数据库不能启动到open状态

++++++++++++++++++++++++++++++++++++++++

开始处理:

首先查看数据库当前启动状态:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

查看当前数据库的归档路径设置:

注:数据库在open状态下,可以通过如下命令查看归档信息,

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA/archivelog/
Oldest online log sequence 8
Next log sequence to archive 9
Current log sequence 9

数据库在mounted状态下的时候,需要通过查看参数文件来确认:

SQL> set linesize 200
SQL> set pagesize 100
SQL> show parameter log_ NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
commit_logging string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
enable_ddl_logging boolean FALSE
log_archive_config string
log_archive_dest string
.. .. .. .. .. .. .. .......
log_archive_dest_1 string LOCATION=+FRA/archivelog/
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 2379776
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
remote_login_passwordfile string EXCLUSIVE
sec_case_sensitive_logon boolean TRUE
sec_max_failed_login_attempts integer 10

可以看出归档日志的路径是:LOCATION=+FRA/archivelog/

+++++++++++++++++++++++++++++++++++
切换到oracle用户或者grid用户下,执行asmcmd查看磁盘组信息:

[grid@oracle01 ~]$ asmcmd
ASMCMD> ls
BAK/
CRS/
DATA/
FRA/
ASMCMD>
ASMCMD> cd FRA
ASMCMD> ls
EBJDB/
ASMCMD> 发现没有+FRA/archivelog目录,我们在+FRA目录下,创建archivelog目录:
ASMCMD> pwd
+FRA
ASMCMD> mkdir archivelog
archivelog/
ASMCMD> cd archivelog
ASMCMD> pwd
+FRA/archivelog
ASMCMD>

OK!!!!!!归档日志目录设置完毕!
++++++++++++++++++++++++++++++++++++++

回到sqlplus界面,

SQL> alter database open;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME     STATUS
---------------- ------------
EBJDB1 OPEN

查看集群服务状态:

[grid@oracle01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.BAK.dg
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.CRS.dg
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.DATA.dg
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.FRA.dg
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.LISTENER.lsnr
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.asm
ONLINE ONLINE oracle01 Started
ONLINE ONLINE oracle02 Started
ora.gsd
ONLINE OFFLINE oracle01
ONLINE OFFLINE oracle02
ora.net1.network
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.ons
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
ora.registry.acfs
ONLINE ONLINE oracle01
ONLINE ONLINE oracle02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oracle01
ora.cvu
1 ONLINE ONLINE oracle01
ora.db.db
1 ONLINE ONLINE oracle01 Open
2 ONLINE ONLINE oracle02 Open
ora.oracle01.vip
1 ONLINE ONLINE oracle01
ora.oracle02.vip
1 ONLINE ONLINE oracle02
ora.oc4j
1 ONLINE ONLINE oracle01
ora.scan1.vip
1 ONLINE ONLINE oracle01

OK!!! 焦急的心情稍许平静!!!!有木有!!!
网上查找各种资料的时候,心情特别难受,都开始怀疑人生了,对面的纯开发同事,人家一天啥事没有。。。。。这次问题虽然解决了,以后估计还会有相同的想法, 哈哈!!!

其他参考:
http://blog.csdn.net/tianlesoftware/article/details/6051672
http://blog.csdn.net/jingleli21/article/details/45888295
http://www.itpub.net/thread-1510308-1-1.html
https://wenku.baidu.com/view/755ddc25bcd126fff7050bdc.html
http://blog.chinaunix.net/uid-28813259-id-3987260.html