【故障處理】告警日志報“ORA-01565 Unable To open Spfile”
項目
source db
db 類型
RAC
db version
12.1.0.2.0
db 存儲
ASM
OS版本及kernel版本
SuSE Linux Enterprise Server（SLES 11） 64位
客戶的12.1.0.2的RAC庫告警日志報ORA-01565: Unable To open Spfile的錯誤，其中一個節點在每天淩晨3點多，另外一個節點在淩晨1點多。
根據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中，檔案“<DB_HOME>/dbs/init<ORACLE_SID>.ora”不再使用：
The only reference to the incorrect spfile name is in <DB_HOME>/dbs/init<ORACLE_SID>.ora which isn't being used in 12.1.0.2:
$ cat initeaipprd1.ora
SPFILE='+DATA/eaipprd/spfileeaipprd.ora'
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">In this Document</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175552931127059&id=1970979.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_171#SYMPTOM" target="_blank">Symptoms</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175552931127059&id=1970979.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_171#CAUSE" target="_blank">Cause</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175552931127059&id=1970979.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_171#FIX" target="_blank">Solution</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175552931127059&id=1970979.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_171#REF" target="_blank">References</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank"></a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">Oracle Database - Enterprise Edition - Version 12.1.0.2 and later Information in this document applies to any platform.</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">Newly created database using dbca, seeing the following in database alert.log frequently:</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">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</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">The spfile doesn't exist, the spfile in the OCR for the database is correct:</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">$ 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</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">SQL> show parameter spfile;</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/EAIPPRD/PARAMETERFILE/spfile.279.861715841</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">ASMCMD [+DATA/EAIPPRD/PARAMETERFILE] > ls -l Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE NOV 20 07:00:00 Y spfile.279.861715841</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">The only reference to the incorrect spfile name is in <DB_HOME>/dbs/init<ORACLE_SID>.ora which isn't being used in 12.1.0.2:</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">$ cat initeaipprd1.ora SPFILE='+DATA/eaipprd/spfileeaipprd.ora'</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank"> </a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">The issue was investigated in multiple bugs: </a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">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</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">The exact cause wasn't determined.</a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank"> </a>
<a href="http://images2015.cnblogs.com/blog/646850/201612/646850-20161220224303323-574158245.png" target="_blank">After applied 12.1.0.2 GI PSU2, the issue stopped.</a>
In this Document
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175209581680993&id=1950208.1&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_4#GOAL" target="_blank">Goal</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175209581680993&id=1950208.1&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_4#FIX" target="_blank">Solution</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=175209581680993&id=1950208.1&_afrWindowMode=0&_adf.ctrl-state=5da12j3e9_4#REF" target="_blank">References</a>
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.
Troubleshooting "ORA-01565: Unable to open Spfile"
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.
In a situation where the error is not generating any trace files we need to set the following event at the database level
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
Errors in file /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/racdb1_ora_3106.trc: <<<<<<<<<<<<<<<Trace files for the event are getting generated
ORA-01565: error in identifying file '+DATA/racdb1/spfileracdb1.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/racdb1/spfileracdb1.ora
ORA-15173: entry 'spfileracdb1.ora' does not exist in directory 'racdb1'
Mon Dec 01 19:26:48 2014
Dumping diagnostic data in directory=[cdmp_20141201192648], requested by (instance=1, osid=3106), summary=[abnormal process termination].
Mon Dec 01 19:26:49 2014
Errors in file /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/racdb1_ora_3233.trc:
After the event has occurred, the tracing can be disabled as follows
In the current example the trace file has the following information
Trace file output
*** 2014-12-01 19:26:44.771
*** SESSION ID:(15.63147) 2014-12-01 19:26:44.771
*** CLIENT ID:() 2014-12-01 19:26:44.771
*** SERVICE NAME:(SYS$USERS) 2014-12-01 19:26:44.771
*** MODULE NAME:(sqlplus@nracdb1 (TNS V1-V3)) 2014-12-01 19:26:44.771
*** CLIENT DRIVER:(SQL*PLUS) 2014-12-01 19:26:44.771
*** ACTION NAME:() 2014-12-01 19:26:44.771
*** CONTAINER ID:(1) 2014-12-01 19:26:44.771
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=10, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=37hr89tuy952y) -----
CREATE PFILE='/tmp/RUwOs966FJ' FROM SPFILE='+DATA/racdb1/spfileracdb1.ora'
The actual SPFILE location of the database can be checked from the alert log or database configuration
Thu Oct 30 10:55:26 2014
Starting ORACLE instance (normal) (OS id: 10576)
RECOMMENDATION:
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 4
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =101
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1
System name: Linux
Node name: lo03dox3
Release: 2.6.32-431.29.2.el6.x86_64
Version: #1 SMP Sun Jul 27 15:55:46 EDT 2014
Machine: x86_64
Using parameter settings in server-side spfile +DATA/spfileracdb1.ora
From the config output
srvctl config database -d racdb1
Database unique name: racdb1
Database name: racdb1
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/spfileracdb1.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: FRA,DATA
Services: test1, test2
OSDBA group:
OSOPER group:
Database instance: racdb1
From the above it is clear that the actual location of the spfile is "+DATA/spfileracdb1.ora" however the process is trying to access the spfile from "+DATA/racdb1/spfileracdb1.ora"
In such a situation the reference of the spfile needs to be corrected by the process.
本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1884539，如需轉載請自行聯系原作者