rac模式下如何处理spfile文件参数故障（没有备份pfile）

spfile参数故障处理

---

今天在修改rac环境下修改spfile参数，报错

ORA-01261: Parameter db_create_file_dest destination string cannot be translated

但是我又没有备份pfile,数据库无法启动了，于是想到下面一招
将asm磁盘上的spfile拷出来

---

[root@momdb1 grid]# strings spfile.268.919525555> momdb.ora[root@momdb1 grid]# lscore.1159   core.13161  DesktopDownloads  Music Pictures  spfile.268.919525555  Templatescore.12918  core.14397  Documents  momdb.ora  oradiag_grid  Publicspfilemomdb.ora   Videos[root@momdb1 grid]# cat momdb.ora momdb2.__db_cache_size=3288334336momdb1.__db_cache_size=3288334336momdb2.__java_pool_size=16777216momdb1.__java_pool_size=16777216momdb2.__large_pool_size=16777216momdb1.__large_pool_size=16777216momdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentmomdb2.__pga_aggregate_target=2147483648momdb1.__pga_aggregate_target=2147483648momdb2.__sga_target=4194304000momdb1.__sga_target=4194304000momdb2.__shared_io_pool_size=0momdb1.__shared_io_pool_size=0momdb2.__shared_pool_size=838860800momdb1.__shared_pool_size=838860800momdb2.__streams_pool_size=0momdb1.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/momdb/adump'*.audit_trail='db'*.cluster_database=true*.compatible='11.2.0.0.0'*.control_files='+RACDATA/momdb/control01.ctl','+RACDATA/momdb/control02.ctl'*.db_block_size=8192*.db_create_file_dest='+RACDATA/momdb/'*.db_domain=''*.db_name='momdb'*.db_recovery_file_dest='+RACFRA'*.db_recovery_file_dest_size=42919264*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=momdbXDB)'momdb2.instance_number=2momdb1.instance_number=1*.log_archive_dest_1='location=+racdata/arch'*.open_cursors=300*.pga_aggregate_target=2147483648*.processes=1000*.remote_listener='momdb-scan.tops.com:1521'*.remote_login_passwordfile='exclusive'*.sessions=1105*.sga_max_size=4194304000*.sga_target=4194304000momdb2.thread=2momdb1.thread=1*.undo_retention=10800momdb1.undo_tablespace='UNDOTBS1'momdb2.undo_tablespace='UNDOTBS2'

---

将文件内容追加到Pfile中后，需要检查文本文件中的格式。

见证奇迹的时刻到了

[oracle@momdb1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 18 17:32:34 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup pfile='/home/oracle/momdb.ora'ORACLE instance started.Total System Global Area 4175568896 bytesFixed Size      2234960 bytesVariable Size         872416688 bytesDatabase Buffers     3288334336 bytesRedo Buffers           12582912 bytesDatabase mounted.Database opened.SQL> 

---

但是执行以下命令报错

SQL> create spfile from pfile  2  /File created.SQL> show parameter spfileNAME                 TYPE    VALUE------------------------------------ ----------- ------------------------------spfile               stringSQL> startup forceORA-01261: Parameter db_create_file_dest destination string cannot be translated

看来，spfile中的内容并没有得到更改

但是可以通过以下方式修改

SQL> create spfile='+RACDATA' from pfile  2  /File created.

删除以前的spfile重新生成

ASMCMD> cd parame*ASMCMD> lsspfile.268.919525555spfile.483.922902205ASMCMD> ls -lType   Redund  Striped  Time Sys  NamePARAMETERFILE  MIRROR  COARSE   SEP 18 17:00:00  Yspfile.268.919525555PARAMETERFILE  MIRROR  COARSE   SEP 18 17:00:00  Yspfile.483.922902205ASMCMD> rm -rf spfile.268.919525555ASMCMD> lsspfile.483.922902205ASMCMD> rm -rf  spfile.483.922902205ASMCMD> pwd+racdata/momdb/PARAMETERFILEASMCMD> lsspfile.483.922902427

再次启动时

SQL> startupORA-01078: failure in processing system parametersORA-01565: error in identifying file '+RACDATA/momdb/spfilemomdb.ora'ORA-17503: ksfdopn:2 Failed to open file +RACDATA/momdb/spfilemomdb.oraORA-15056: additional error messageORA-17503: ksfdopn:2 Failed to open file +RACDATA/momdb/spfilemomdb.oraORA-15173: entry 'spfilemomdb.ora' does not exist in directory 'momdb'ORA-06512: at line 4

还是无法启动，oracle似乎并没有去找我们生成的文件啊

其实是上面建spfile的方法有问题

create spfile='+RACDATA/momdb/spfilemomdb.ora' from pfile

此时再去查看文件
ASMCMD> pwd
+racdata/momdb
ASMCMD> ls spfi*
spfilemomdb.ora

这其实是一个链接文件

文件的实际位置

spfilemomdb.ora => +RACDATA/MOMDB/PARAMETERFILE/spfile.484.922906555

再次启动数据库

[oracle@momdb2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 18 18:59:08 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORA-01506: missing or illegal database nameSQL> show parameter db_nameORA-01034: ORACLE not availableProcess ID: 0Session ID: 0 Serial number: 0

还是有报错
查一下这个报错

[oracle@momdb2 ~]$ oerr ora 0150601506, 00000, "missing or illegal database name"// *Cause:  No db_name INIT.ORA aprameter was specified.// *Action:  The database name must be given in the db_name//  INIT.ORA parameter.[oracle@momdb2 ~]$ 

纯属鸡肋，不过大致知道什么原因了

这其实是在rac环境下建spfile的注意事项很多

SQL> create spfile='+RACDATA/momdb/spfilemomdb.ora' from pfile='/home/oracle/momdb.ora'  2  /File created.

再次启动

[oracle@momdb2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 ****Production on Sun Sep 18 19:04:57 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 4175568896 bytesFixed Size      2234960 bytesVariable Size         872416688 bytesDatabase Buffers     3288334336 bytesRedo Buffers           12582912 bytesDatabase mounted.Database opened.SQL> 

最后重启看下

[grid@momdb1 ~]$ srvctl stop instance -d momdb -i momdb1,momdb2 -o immediate[grid@momdb1 ~]$ srvctl start  instance -d momdb -i momdb1,momdb2 [grid@momdb1 ~]$ 

---

问题解决

---

此处是一个大原则，在ORACLE DBA的日常工作，如果需要修改Spfile，一定要事先备份出Pfile，确保万无一失！切记！