RAC无法使用spfile启动

LDean

windows2003 数据库版本10.2.0.1使用asm 3节点rac
之前曾修改过sga，修改之前创建的pfile，重启后因sga过大而无法启动（win 1.7G问题），改用pfile启动，一切正常
使用create spfile='+data/sfdb/spfilesfdb.ora' from pfile创建pfile后重新启动，crs_start -all报
CRS-0215:无法启动资源‘ora.sfdb.sfdb1.inst
CRS-0215:无法启动资源‘ora.sfdb.sfdb2.inst
CRS-0215:无法启动资源‘ora.sfdb.sfdb3.inst
手动startup单个节点，报
ORA-01565: error in identifying file '+data/sfDB/spfilesfDB.ora'
ORA-17503: ksfdopn:2 Failed to open file +data/sfDB/spfilesfDB.ora
ORA-01000: maximum open cursors exceeded
修改pfile的open_cursor，改多大都是一样错误，后反向创建pfile，发现写入asm的spfile只有一行*.spfile='+data/sfdb/spfilesfdb.ora’
现在只能使用pfile启动，无法使用spfile，win服务器又那么不稳定，死机重启库就挂了，麻烦帮忙解决一下，谢谢

Maclean Liu(刘相兵

action plan:

srvctl  config database -d sfDB -a
cat pfile

export ORACLE_SID=+ASM1

asmcmd

cd data/sfDB

ls -l

Maclean Liu(刘相兵

猜测的原因：

create spfile='+data/sfdb/spfilesfdb.ora' from pfile

ORA-01565: error in identifying file '+data/sfDB/spfilesfDB.ora'



'+data/sfdb/spfilesfdb.ora'  != '+data/sfDB/spfilesfDB.ora'

低级错误

LDean

Liu你之前那篇blog我已经看过，也查过了

LDean

是windows系统
srvctl  config database -d sfDB -a 显示的也是正确的位置，asmcmd里也查过有这个文件

Maclean Liu(刘相兵

诊断问题的时候不要 写自己的观点， 只描述事实。

把 输出贴出来

Maclean Liu(刘相兵

ODM Data:

1. ORA-01565 on database STARTUP due to failure to open SPFILE [ID 419509.1]
   
2. 
   
3. 
   
4. Applies to:
   
5. Oracle Server - Enterprise Edition - Version: 10.2.0.1
   
6. This problem can occur on any platform.
   
7. Symptoms
   
8. 
   
9. During the database startup using spfile, the following errors are raised:
   
10. 
    
11. SQL> startup
    
12. ORA-01565 : error in identifying file '+DATA/SDEPROD/spfileSDEPROD.ora'
    
13. ORA-17503 : ksfdopn:2 Failed to open file +DATA/SDEPROD/spfileSDEPROD.ora
    
14. ORA-01000 : maximum open cursors exceeded
    
15. Changes
    
16. Created a pfile, modified the file, started up the database using pfile, re-created spfile from pfile.
    
17. Cause
    
18. When creating the spfile from pfile, the full path of the pfile is not specified.
    
19. Solution
    
20. 
    
21. To create the spfile from pfile use the Full path as follows:
    
22. 
    
23. create spfile='<fully qualified name of spfile' from pfile='<fully qualified name of pfile>';
    
24. Additional information on pfile and spfile creation can be found in:
    
25. 
    
26. Note 249664.1:Pfile vs SPfile
    
27. And
    
28. Note 162491.1:Startup of an Oracle Instance using SPFile or Init.ora Parameter File
    
29. 
    
30. 
    
31. 
    
32. 
    
33. References
    
34. NOTE:249664.1 - Pfile vs SPfile
    
35. NOTE:162491.1 - Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File

复制代码

Maclean Liu(刘相兵

When creating the spfile from pfile, the full path of the pfile is not specified.
Solution

To create the spfile from pfile use the Full path as follows:

create spfile='<fully qualified name of spfile' from pfile='<fully qualified name of pfile>';


使用 pfile 的完全路径 重建spfile ， 引起该问题的看来是10.2.0.1 的一个bug 。

同时建议你升级到10.2.0.5 , 10.2.0.1 问题较多 。

Maclean Liu(刘相兵

找出ASM 下 spfile server parameter 文件位置的脚本如下：

1. --- listspfiles.sql
   
2. --- Purpose: Sample script to list spfiles kept in ASM instance
   
3. --- Usage: This should be run against an ASM instance,
   
4. --- not a database instance.
   
5. ---
   
6. --- cut here --%<----%<----%<----%<----%<----%<--
   
7. 
   
8. --list all spfiles
   
9. 
   
10. set lines 120
    
11. col full_path for a110
    
12. SELECT full_path, dir, sys
    
13. FROM
    
14. (SELECT
    
15. CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
    
16. dir, sys FROM
    
17. (SELECT g.name gname,
    
18. a.parent_index pindex, a.name aname,
    
19. a.reference_index rindex, a.ALIAS_DIRECTORY dir,
    
20. a.SYSTEM_CREATED sys
    
21. FROM v$asm_alias a, v$asm_diskgroup g
    
22. WHERE a.group_number = g.group_number)
    
23. START WITH (MOD(pindex, POWER(2, 24))) = 0
    
24. CONNECT BY PRIOR rindex = pindex
    
25. ORDER BY dir desc, full_path asc)
    
26. WHERE UPPER(full_path) LIKE '%SPFILE%'
    
27. /
    
28. 
    
29. ---
    
30. --- cut here --%<----%<----%<----%<----%<----%<--

复制代码

LDean

非常感觉，指定pfile路径后问题解决