ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 2920|回复: 0

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

[复制链接]
认证徽章
论坛徽章:
10
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29目光如炬
日期:2016-10-30 22:00:00罗罗诺亚·索隆
日期:2016-10-17 12:26:14目光如炬
日期:2016-10-16 22:00:00火眼金睛
日期:2016-11-30 22:00:00目光如炬
日期:2017-01-22 22:00:00美羊羊
日期:2015-04-12 10:40:59火眼金睛
日期:2017-02-28 22:00:00
发表于 2016-12-20 23:06 | 显示全部楼层 |阅读模式
【故障处理】告警日志报“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  MOS1.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 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 <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'

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: <<<<<<<<<<<<<<<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
alter system set events '1565 trace name context off';
In the current example the trace file has the following information
Trace file output

*** 2014-12-01 19:26:44.771
*** SESSION ID15.63147) 2014-12-01 19:26:44.771
*** CLIENT ID) 2014-12-01 19:26:44.771
*** SERVICE NAMESYS$USERS) 2014-12-01 19:26:44.771
*** MODULE NAMEsqlplus@nracdb1 (TNS V1-V3)) 2014-12-01 19:26:44.771
*** CLIENT DRIVERSQL*PLUS) 2014-12-01 19:26:44.771
*** ACTION NAME) 2014-12-01 19:26:44.771
*** CONTAINER ID1) 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
From alert log


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.
REFERENCESBUG:18334406 - ORA-01565 ERROR ON THE TWO RAC NODES
BUG:9906253 - ORA-01565: UNABLE TO OPEN SPFILE EACH SIX HOURS


非常感谢各位朋友支持小麦苗。

1人打赏


您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表