【故障處理】告警日誌報“ORA-01565 Unable To open Spfile”
【故障處理】告警日誌報“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中，檔案“<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'
1.3 MOS
1.3.1 Grid Infrastructure 12.1.0.2 ORA-01565 Unable To open Spfile (文件 ID 1970979.1)
| Symptoms |
| Cause |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
SYMPTOMS
Newly created database using dbca, seeing the following in database alert.log frequently:
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:
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
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 <DB_HOME>/dbs/init<ORACLE_SID>.ora which isn't being used in 12.1.0.2:
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 laterInformation 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
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
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: <<<<<<<<<<<<<<<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
ORA-01565: Unable to open Spfile +DATA/racdb1/spfileracdb1.ora.
Mon Dec 01 19:26:49 2014
Errors in file /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/racdb1_ora_3233.trc:
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'
After the event has occurred, the tracing can be disabled as follows
In the current example the trace file has the following information
*** 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 -----
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'
----- 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)
Thu Oct 30 10:55:26 2014
RECOMMENDATION:
Thu Oct 30 10:55:26 2014
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
Thu Oct 30 10:55:26 2014
**********************************************************************
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.
REFERENCES
BUG:18334406 - ORA-01565 ERROR ON THE TWO RAC NODESBUG:9906253 - ORA-01565: UNABLE TO OPEN SPFILE EACH SIX HOURS
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2131070/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/6204654.html
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(642808185)，註明新增緣由
● 於 2016-11-28 10:00 ~ 2016-11-30 22:00 在農行完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr，免費學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2131070/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 告警日誌alert過大的處理Oracle
- 【故障處理】修改SPFILE無許可權
- 【spfile】修改及重置spfile實操 plus 故障處理一則
- nginx日誌處理Nginx
- 【ELK】elastalert 日誌告警AST
- 告警日誌介紹
- kkjcre1p: unable to spawn jobq slave process故障處理
- PHP日誌處理類PHP
- Too many open files報錯處理
- weblogic日誌報錯too many open filesWeb
- DATAGUARD主庫重做日誌不能同步至備庫 alert日中報ORA-12170故障處理
- 資料庫使用者不能登入 alert日誌報ORA-04031故障分析處理資料庫
- 歸檔日誌滿導致的資料庫掛起故障處理資料庫
- orbeon form 的日誌處理ORBORM
- shell日誌顏色處理
- RAC的告警日誌檔案
- 告警日誌出現kewastUnPackStats資訊AST
- 【故障處理】ORA-1688: unable to extend table AUDSYS.AUD$UNIFIEDNifi
- oracle 中 alert 報警日誌過大的處理方法Oracle
- 完美解決xhost +報錯： unable to open display
- 資料庫alert日誌報ORA-03137 TTC 協議內部錯誤故障分析處理資料庫協議
- Weblogic BEA-141281 unable to get file lock, will retry 故障處理Web
- DATAGUARD中手工處理日誌GAP
- node錯誤處理與日誌
- logstash kafka output 日誌處理Kafka
- oracle alert日誌亂碼處理Oracle
- strom打造日誌處理系統
- Db2 日誌處理二DB2
- 快速實現釘釘告警通知、處理告警、關閉告警
- Oracle告警日誌ora-04030Oracle
- oracle12告警日誌檔案?Oracle
- 使用外部表管理Oracle 告警日誌Oracle
- apache日誌匯入oracle(日誌經過python處理)ApacheOraclePython
- 歸檔日誌滿導致的資料庫掛起故障處理【轉載】資料庫
- SQL Server日誌檔案總結及日誌滿的處理SQLServer
- 如何在zuul上做日誌處理Zuul
- 搭建node服務(1)：日誌處理
- 指令碼處理iOS的Crash日誌指令碼iOS