【故障处理】告警日志报“ORA

云深处

【故障处理】告警日志报“ORA-01565 Unable To open Spfile”

1.1  BLOG文档结构图

1.2  故障分析及解决过程
1.2.1  故障环境介绍

项目	source db
db 类型	RAC
db version	12.1.0.2.0
db 存储	ASM
OS版本及kernel版本	SuSE Linux Enterprise Server（SLES 11） 64位

1.2.2  故障发生现象及报错信息
客户的12.1.0.2的RAC库告警日志报ORA-01565: Unable To open Spfile的错误，其中一个节点在每天凌晨3点多，另外一个节点在凌晨1点多。

1.2.3  故障分析及解决过程
根据MOS How to troubleshoot ORA-01565 being reported in alert log (文档 ID 1950208.1)查询出来是由于$ORACLE_HOME/dbs/init$ORACLE_SID和OCR 中的配置（srvctl config db -d racdb1）查询出来的结果不一致导致的。
解决：将两者配置修改为一致即可。
可以设置trace事件来追踪该问题，生成trace后再关闭该跟踪事件：
alter system set events '1565 trace name errorstack level 10';
alter system set events '1565 trace name context off';

另外，在12.1.0.2的RAC中，文件“/dbs/init.ora”不再使用：
The only reference to the incorrect spfile name is in /dbs/init.ora which isn't being used in 12.1.0.2:

$ cat initeaipprd1.ora
SPFILE='+DATA/eaipprd/spfileeaipprd.ora'

1.3  MOS
1.3.1  Grid Infrastructure 12.1.0.2 ORA-01565 Unable To open Spfile (文档 ID 1970979.1)


In this Document

Symptoms

Cause

Solution

References

APPLIES TO:
　　Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

SYMPTOMS
Newly created database using dbca, seeing the following in database alert.log frequently:
　　Wed Nov 19 10:00:40 2014
ORA-01565: Unable to open Spfile +DATA/eaipprd/spfileeaipprd.ora.
Wed Nov 19 10:00:40 2014
ORA-01565: Unable to open Spfile +DATA/eaipprd/spfileeaipprd.ora.
Wed Nov 19 10:00:41 2014
The spfile doesn't exist, the spfile in the OCR for the database is correct:
　　$ srvctl config database -d eaipprd
Database unique name: eaipprd
Database name: eaipprd
Oracle home: /oracle/oracle/product/12.1.0.2_eaip
Oracle user: oracle
Spfile: +DATA/EAIPPRD/PARAMETERFILE/spfile.279.861715841
　　SQL> show parameter spfile;
　　NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/EAIPPRD/PARAMETERFILE/spfile.279.861715841
　　ASMCMD [+DATA/EAIPPRD/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE NOV 20 07:00:00 Y spfile.279.861715841
The only reference to the incorrect spfile name is in /dbs/init.ora which isn't being used in 12.1.0.2:
　　$ cat initeaipprd1.ora
SPFILE='+DATA/eaipprd/spfileeaipprd.ora'
  
CAUSE
The issue was investigated in multiple bugs:
BUG 20133332 - FREQUENT ALERT.LOG MSG: ORA-01565: UNABLE TO OPEN SPFILE +DATA/EAIPPRD/SPFILEEAI
BUG 19064439 - ORA-01565: UNABLE TO OPEN SPFILE ON AN IDLE SYSTEM - FOR 1 SECOND
BUG 20025790 - EM CAUSES ORA-1565 TO BE GENERATED DUE TO DBCA ISSUE
The exact cause wasn't determined.


SOLUTION
After applied 12.1.0.2 GI PSU2, the issue stopped.
　　


1.3.2  How to troubleshoot ORA-01565 being reported in alert log (文档 ID 1950208.1)

  
In this Document

Goal

Solution

References

APPLIES TO:
　　Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

GOAL
Troubleshooting "ORA-01565: Unable to open Spfile"
SOLUTION
When "ORA-01565: Unable to open Spfile" is being reported in the instance alert log, then some process is trying to access the spfile but referring to the incorrect location

　　Database Instance alert log reports the following errors


　　Wed Nov 19 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Wed Nov 19 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Wed Nov 19 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Wed Nov 19 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Wed Nov 19 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
　　

In a situation where the error is not generating any trace files we need to set the following event at the database level

　　alter system set events '1565 trace name errorstack level 10';

Once the above event is set we can see messages as follows in the alert log when the issue occurs again

　　From alert log

Mon Dec 01 19:26:44 2014
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Mon Dec 01 19:26:44 2014
Errors in file /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/racdb1_ora_3106.trc: