年前曾修改sga过大导致数据库无法启动,随后使用pfile启动并使用
create spfile='+DATA' from pfile;创建spfile,因为是垃圾的win系统,所以也没有再重启,反正会不定时的死机重启。
除夕当天接到电话,库起不来了,登陆上去直接查了下crs_stat -t
create spfile='+DATA' from pfile;创建spfile,因为是垃圾的win系统,所以也没有再重启,反正会不定时的死机重启。
除夕当天接到电话,库起不来了,登陆上去直接查了下crs_stat -t
E:\oracle\product\10.2.0\crs\BIN>crs_stat -t
名称 类型 目标 状态 主机
------------------------------------------------------------
ora.sxdb.db application ONLINE OFFLINE
ora....b1.inst
application ONLINE OFFLINE
ora....b2.inst application ONLINE OFFLINE
ora....b3.inst application ONLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE sxdb1
ora....B1.lsnr application ONLINE ONLINE sxdb1
ora.sxdb1.gsd application ONLINE ONLINE sxdb1
ora.sxdb1.ons application ONLINE ONLINE sxdb1
ora.sxdb1.vip application ONLINE ONLINE sxdb1
ora....SM2.asm application ONLINE ONLINE sxdb2
ora....B2.lsnr application ONLINE ONLINE sxdb2
ora.sxdb2.gsd application ONLINE ONLINE sxdb2
ora.sxdb2.ons application ONLINE ONLINE sxdb2
ora.sxdb2.vip application ONLINE ONLINE sxdb2
ora....SM3.asm application ONLINE ONLINE sxdb3
ora....B3.lsnr application ONLINE ONLINE sxdb3
ora.sxdb3.gsd application ONLINE ONLINE sxdb3
ora.sxdb3.ons application ONLINE ONLINE sxdb3
ora.sxdb3.vip application ONLINE ONLINE sxdb3
3个节点都没有启动,于是随便登录一个节点进去手动startup,给出的提示是spfile不存在
set oracle_sid=+asm1
asmcmd
登录进asm查了下,发现在+DATA/sxdb/parameterfile/下有spfile,但启动时报错给出的位置和文件名为:+DATA/sxdb/spfilesxdb.ora
于是手动mkalias一个连接 mkalias spfilesxdb.ora +data/sxdb/parameterfile/spfile.273.123235423
再次手动startup,结果报错
ORA-01565: error in identifying file '+data/sxdb/spfilesxdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +data/sxdb/spfilesxdb.ora
ORA-01000: maximum open cursors exceeded
修改pfile的open_cursor,然后重新创建spfile,这次直接指定位置
create spfile='+DATA/sxdb/spfilesxdb.ora' from pfile;这样会自动创建alias,方便些。
还是同样报错,反复修改多次,无论open_cursor改到多大都是同样报错,认为问题不在这里
查询srvctl config database -d sxdb -a 发现crs里存的spfile文件路径没有问题。开始怀疑是spfile文件问题,于是反向把spfile弄出来
create pfile='e:\new.ora' from spfile='+data/sxdb/spfilesxdb.ora';
打开new.ora结果发现里面只有一行
*.spfile='+data/sxdb/spfilesxdb.ora’
没有metalink,上网求助别人,最后发现是个bug
ORA-01565 on database STARTUP due to failure to open SPFILE [ID 419509.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Symptoms
During the database startup using spfile, the following errors are raised:
SQL> startup
ORA-01565 : error in identifying file '+DATA/SDEPROD/spfileSDEPROD.ora'
ORA-17503 : ksfdopn:2 Failed to open file +DATA/SDEPROD/spfileSDEPROD.ora
ORA-01000 : maximum open cursors exceeded
Changes
Created a pfile, modified the file, started up the database using pfile, re-created spfile from pfile.
Cause
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='
Additional information on pfile and spfile creation can be found in:
Note 249664.1:Pfile vs SPfile
And
Note 162491.1:Startup of an Oracle Instance using SPFile or Init.ora Parameter File
References
NOTE:249664.1 - Pfile vs SPfile
NOTE:162491.1 - Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File
10.2.0.1正好有这个问题,创建spfile的时候需要指定pfile的位置,否则就会这样,于是重新创建
create spfile='+data/sxdb/spfilesxdb.ora' from pfile='e:\pfile.ora';
再次启动,一切正常,蛋疼
3个节点都没有启动,于是随便登录一个节点进去手动startup,给出的提示是spfile不存在
set oracle_sid=+asm1
asmcmd
登录进asm查了下,发现在+DATA/sxdb/parameterfile/下有spfile,但启动时报错给出的位置和文件名为:+DATA/sxdb/spfilesxdb.ora
于是手动mkalias一个连接 mkalias spfilesxdb.ora +data/sxdb/parameterfile/spfile.273.123235423
再次手动startup,结果报错
ORA-01565: error in identifying file '+data/sxdb/spfilesxdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +data/sxdb/spfilesxdb.ora
ORA-01000: maximum open cursors exceeded
修改pfile的open_cursor,然后重新创建spfile,这次直接指定位置
create spfile='+DATA/sxdb/spfilesxdb.ora' from pfile;这样会自动创建alias,方便些。
还是同样报错,反复修改多次,无论open_cursor改到多大都是同样报错,认为问题不在这里
查询srvctl config database -d sxdb -a 发现crs里存的spfile文件路径没有问题。开始怀疑是spfile文件问题,于是反向把spfile弄出来
create pfile='e:\new.ora' from spfile='+data/sxdb/spfilesxdb.ora';
打开new.ora结果发现里面只有一行
*.spfile='+data/sxdb/spfilesxdb.ora’
没有metalink,上网求助别人,最后发现是个bug
ORA-01565 on database STARTUP due to failure to open SPFILE [ID 419509.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Symptoms
During the database startup using spfile, the following errors are raised:
SQL> startup
ORA-01565 : error in identifying file '+DATA/SDEPROD/spfileSDEPROD.ora'
ORA-17503 : ksfdopn:2 Failed to open file +DATA/SDEPROD/spfileSDEPROD.ora
ORA-01000 : maximum open cursors exceeded
Changes
Created a pfile, modified the file, started up the database using pfile, re-created spfile from pfile.
Cause
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='
Additional information on pfile and spfile creation can be found in:
Note 249664.1:Pfile vs SPfile
And
Note 162491.1:Startup of an Oracle Instance using SPFile or Init.ora Parameter File
References
NOTE:249664.1 - Pfile vs SPfile
NOTE:162491.1 - Startup of an Oracle 9i, 10g, 11g Instance using SPFile or Init.ora Parameter File
10.2.0.1正好有这个问题,创建spfile的时候需要指定pfile的位置,否则就会这样,于是重新创建
create spfile='+data/sxdb/spfilesxdb.ora' from pfile='e:\pfile.ora';
再次启动,一切正常,蛋疼
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25680865/viewspace-715272/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25680865/viewspace-715272/