1. spfile --> pfile --> spfile （手工写一个pfile)
2. 从警告日志复制 --> pfile --> spfile #在11g可以这样做
3. 通过备份还原
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
关闭数据库
SQL> show parameter spfile;
删除参数文件
$ su - grid
$ asmcmd
ASMCMD> rm -rf +DATA/orcl/spfileorcl.ora
启动数据库报错
[oracle@oracleEDU ~]$ sqlplus as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 29 06:34:34 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
ORA-06512: at line 4
解决办法
找警告日志文件
$ cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/
vi alert_orcl.log
从后往前找正确的参数信息
vi home/orcle/a.sql
processes = 150
spfile = "+DATA/orcl/spfileorcl.ora"
memory_target = 512M
control_files = "+DATA/orcl/controlfile/current.262.951534451"
control_files = "+FRA/orcl/controlfile/current.256.951534455"
control_files = "/u01/app/oracle/control03.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+fra"
db_recovery_file_dest_size= 3882M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
audit_sys_operations = TRUE
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
utl_file_dir = "/tmp"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
管理员登录
先从Pfile启动测试是否正确
sqlplus as sysdba
startup pfile='/home/oracle/a.sql';
成功了 就可以把pfile转为spfile
SQL> create spfile ='+data/orcl/spfileorcl.ora' from pfile='/home/oracle/a.sql';
File created.
检查
SQL> show parameter spfile ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
检查是否有SPFILE的备份
自动备份(控制文件自动备份）或者手工备份（backup spfile)
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 80.00K DISK 00:00:00 31-AUG-17
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20170831T031752
Piece Name: +FRA/orcl/backupset/2017_08_31/nnsnf0_tag20170831t031752_0.361.953435873
SPFILE Included: Modification time: 31-AUG-17
SPFILE db_unique_name: ORCL
如果快速恢复区是共享（多个数据库使用） 如果数据库不在MOUNT状态或者OPEN状态 在此场景下在恢复前指定数据库的DBID
查找DBID：
$ rman target
connected to target database: ORCL (DBID=1479315187) #这里
或：
SQL> select dbid from v$database;
DBID
----------
1479315187
或：在审计文件里找
模拟故障：删除参数文件
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
su - grid
asmcmd
rm +DATA/orcl/spfileorcl.ora
启动报错找不到参数文件：
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora
ORA-15173: entry 'spfileorcl.ora' does not exist in directory 'orcl'
ORA-06512: at line 4
有备份，通过备份还原：
$ rman target
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 22 23:01:21 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started) #实例没有起来
RMAN> startup nomount;
#根基系统默认参数文件启动，没有读取你的参数文件，该模式恢复用
这个命令只能在rman输入，这个实例做恢复用，用来恢复参数文件，恢复后，需要重新从你恢复的参数文件启动
starting Oracle instance without parameter file for retrieval of spfile #启动了实例
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2211448 bytes
Variable Size 88080776 bytes
Database Buffers 62914560 bytes
Redo Buffers 5455872 bytes
restore spfile from autobackup ; #失败了
Starting restore at 31-AUG-17
RMAN> restore spfile from autobackup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2017 03:46:06
RMAN-06495: must explicitly specify DBID with SET DBID command
#失败了，需要制定DBID
RMAN> set dbid=1479315187
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170831
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170830
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170829
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2017 03:47:35
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
没有找到自动备份的控制文件，那么就手工去找
RMAN> restore spfile from '+FRA/orcl/backupset/2017_08_31/nnsnf0_tag20170831t031752_0.361.953435873';
Starting restore at 31-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/orcl/backupset/2017_08_31/nnsnf0_tag20170831t031752_0.361.953435873
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 31-AUG-17
如果不想手工找
告诉RMAN你的数据库快速恢复区在哪，数据库名 （控制文件自动备份生产的参数文件备份存在快速恢复区 fra）
RMAN> set dbid=1479315187
RMAN> startup nomount
RMAN> restore spfile from autobackup db_name=orcl db_recovery_file_dest='+FRA';
停止数据库，重启（使用恢复的参数文件）。
