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]# ls
core.1159   core.13161  DesktopDownloads  Music Pictures  spfile.268.919525555  Templates
core.12918  core.14397  Documents  momdb.ora  oradiag_grid  Publicspfilemomdb.ora   Videos
[root@momdb1 grid]# cat momdb.ora 
momdb2.__db_cache_size=3288334336
momdb1.__db_cache_size=3288334336
momdb2.__java_pool_size=16777216
momdb1.__java_pool_size=16777216
momdb2.__large_pool_size=16777216
momdb1.__large_pool_size=16777216
momdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
momdb2.__pga_aggregate_target=2147483648
momdb1.__pga_aggregate_target=2147483648
momdb2.__sga_target=4194304000
momdb1.__sga_target=4194304000
momdb2.__shared_io_pool_size=0
momdb1.__shared_io_pool_size=0
momdb2.__shared_pool_size=838860800
momdb1.__shared_pool_size=838860800
momdb2.__streams_pool_size=0
momdb1.__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=2
momdb1.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=4194304000
momdb2.thread=2
momdb1.thread=1
*.undo_retention=10800
momdb1.undo_tablespace='UNDOTBS1'
momdb2.undo_tablespace='UNDOTBS2'

---

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

见证奇迹的时刻到了

[oracle@momdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 18 17:32:34 2016

Copyright (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 bytes
Fixed Size      2234960 bytes
Variable Size         872416688 bytes
Database Buffers     3288334336 bytes
Redo Buffers           12582912 bytes
Database mounted.
Database opened.
SQL> 

---

但是执行以下命令报错

SQL> create spfile from pfile
  2  /

File created.

SQL> show parameter spfile

NAME                 TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile               string
SQL> startup force
ORA-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> ls
spfile.268.919525555
spfile.483.922902205
ASMCMD> ls -l
Type   Redund  Striped  Time Sys  Name
PARAMETERFILE  MIRROR  COARSE   SEP 18 17:00:00  Yspfile.268.919525555
PARAMETERFILE  MIRROR  COARSE   SEP 18 17:00:00  Yspfile.483.922902205
ASMCMD> rm -rf spfile.268.919525555
ASMCMD> ls
spfile.483.922902205
ASMCMD> rm -rf  spfile.483.922902205
ASMCMD> pwd
+racdata/momdb/PARAMETERFILE
ASMCMD> ls
spfile.483.922902427

再次启动时

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+RACDATA/momdb/spfilemomdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +RACDATA/momdb/spfilemomdb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +RACDATA/momdb/spfilemomdb.ora
ORA-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 sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 18 18:59:08 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01506: missing or illegal database name
SQL> show parameter db_name
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

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

[oracle@momdb2 ~]$ oerr ora 01506
01506, 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 sysdba

SQL*Plus: Release 11.2.0.3.0 ****Production on Sun Sep 18 19:04:57 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size      2234960 bytes
Variable Size         872416688 bytes
Database Buffers     3288334336 bytes
Redo Buffers           12582912 bytes
Database 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，确保万无一失！切记！