问题11：参数文件spfile丢失的情况下，如何恢复？

目的：利用RMAN在spfile、init.ora都丢失的情况下进行spfile的恢复
前提：
1.知道当前数据库的DBID，通过以下的命令进行查询
SQL> select dbid from v$database;
DBID
----------
269906945
2.已经开启控制文件的自动备份
1）进入rman控制台
$ rman target /
2）通过以下的命令开启控制文件自动备份
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
3.已经利用rman对数据库进行了全库的备份。如下：
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat Sep 13 09:51:41 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: RADIUS (DBID=269906945)
RMAN> backup database format '/u01/backup/backup_full/full_%d_%T_%s_%p.bak';
Starting backup at 13-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/radius/system01.dbf
input datafile fno=00003 name=/u01/oradata/radius/sysaux01.dbf
input datafile fno=00005 name=/u01/oradata/radius/example01.dbf
input datafile fno=00002 name=/u01/oradata/radius/undotbs01.dbf
input datafile fno=00004 name=/u01/oradata/radius/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
channel ORA_DISK_1: finished piece 1 at 13-SEP-14
piece handle=/u01/backup/backup_full/full_RADIUS_20140913_46_1.bak tag=TAG20140913T095227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 13-SEP-14
Starting Control File and SPFILE Autobackup at 13-SEP-14
piece handle=/u01/app/oracle/flash_recovery_area/RADIUS/autobackup/2014_09_13/o1_mf_s_858160412_b178wwko_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-14

下面针对spfile丢失的两种情况，进行spfile的恢复：
A.在启动实例时，发现spfile已经丢失
B.在数据库运行的过程，当对某些参数在spfile中进行修改时，发现spfile丢失

A.在启动实例时，发现spfile已经丢失
1.启动实例
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 13 10:12:26 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initradius.ora'
【报错】
1）不能加载系统参数
2）不能找到这个initradius.ora参数文件
【说明】
Oracle在启动实例的时候，首先找到$ORACLE_HOME/dbs/spfileradius.ora即二进制的参数文件，如果不存在，就会通过该目录下的initradius.ora文件来启动实例，如果该参数文件也不存在，那么就会报错即找不到能够加载系统参数的参数文件
2.利用rman及自动备份的控制文件和参数文件的备份集，将spfile恢复
1）使用rman启动实例到nomount状态。rman能够在没有参数文件的情况下，将实例启动到nomount状态
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sat Sep 13 10:14:49 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initradius.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes
2)设置dbid
RMAN> set dbid 269906945
executing command: SET DBID
3)利用restore命令，从控制文件和spfile的自动备份集中恢复spfile
RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/RADIUS/autobackup/2014_09_13/o1_mf_s_858160412_b178wwko_.bkp';
Starting restore at 13-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/RADIUS/autobackup/2014_09_13/o1_mf_s_858160412_b178wwko_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 13-SEP-14
4)进入$ORACLE_HOME/dbs目录，查看spfileradius.ora是否已经恢复回来
$ ls -l /u01/app/oracle/product/10.2.0/db_1/dbs/
total 6960
-rw-rw---- 1 oracle oinstall 1544 Sep 13 10:14 hc_radius.dat
-rw-r--r-- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-rw---- 1 oracle oinstall 24 Sep 3 14:10 lkRADIUS
-rw-r----- 1 oracle oinstall 1536 Sep 13 07:51 orapwradius
-rw-r----- 1 oracle oinstall 7061504 Sep 13 09:53 snapcf_radius.f
-rw-r----- 1 oracle oinstall 3584 Sep 13 10:18 spfileradius.ora
【说明】：spfile已经恢复回来了
5）关闭实例
SQL> shutdown immediate
6）重新启动数据库
SQL> startup
或者
在rman控制台进行关闭实例，启动数据库的操作
RMAN>shutdown immediate;
RMAN>startup;

如果控制文件自动备份不在默认的路径下，而是如下所示的情况：
CONFIGURE CONTROLFILE AUTOBACKUP FORMATFOR DEVICE TYPE DISK TO '/tmp/%F';
备份数据库的过程如下：
RMAN> backup database format '/u01/backup/backup_full/full_%d_%T_%s_%p.bak';
Starting backup at 13-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/radius/system01.dbf
input datafile fno=00003 name=/u01/oradata/radius/sysaux01.dbf
input datafile fno=00005 name=/u01/oradata/radius/example01.dbf
input datafile fno=00002 name=/u01/oradata/radius/undotbs01.dbf
input datafile fno=00004 name=/u01/oradata/radius/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-SEP-14
channel ORA_DISK_1: finished piece 1 at 13-SEP-14
piece handle=/u01/backup/backup_full/full_RADIUS_20140913_48_1.bak tag=TAG20140913T113604 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 13-SEP-14
Starting Control File and SPFILE Autobackup at 13-SEP-14
piece handle=/tmp/c-269906945-20140913-07 comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-14
当SPFILE文件丢失时，对其恢复的过程如下：
RMAN> restore spfile from '/tmp/c-269906945-20140913-07';
Starting restore at 13-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /tmp/c-269906945-20140913-07
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 13-SEP-14
【说明】
在利用restore命令进行spfile的恢复时，指明from的路径如：
from '/tmp/c-269906945-20140913-07';
查看spfileradius.ora文件是否恢复回来：
$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
$ ls -l
total 6960
-rw-rw---- 1 oracle oinstall 1544 Sep 13 11:42 hc_radius.dat
-rw-r--r-- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-rw---- 1 oracle oinstall 24 Sep 3 14:10 lkRADIUS
-rw-r----- 1 oracle oinstall 1536 Sep 13 07:51 orapwradius
-rw-r----- 1 oracle oinstall 7061504 Sep 13 11:37 snapcf_radius.f
-rw-r----- 1 oracle oinstall 3584 Sep 13 11:42 spfileradius.ora

B.在数据库运行的过程中，参数文件spfile丢失
当对某一个参数进行修改的时候出现以下的报错信息：
SQL> alter system set log_archive_dest_2='location=/u01/app/oracle' scope=spfile;
alter system set log_archive_dest_2='location=/u01/app/oracle' scope=spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/product/10.2.0/db_1/dbs/spfileradius.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
或者以下的报错：
SQL> alter system set log_archive_dest_2='location=/u01/app/oracle' scope=spfile;
alter system set log_archive_dest_2='location=/u01/app/oracle' scope=spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/product/10.2.0/db_1/dbs/spfileradius.ora'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
【说明】
spfile丢失或者被损坏，因为spfile是个二进制文件，如果用文本编辑器vi将其打开，就会有可能将这个文件损坏。

直接利用rman的restore命令恢复spfile，不需要进行关闭实例和设置DBID
RMAN> restore spfile to '/tmp/spfileradius.ora' from '/tmp/c-269906945-20140913-07';
Starting restore at 13-SEP-14
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /tmp/c-269906945-20140913-07
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 13-SEP-14
【注意】如果数据库是用spfile文件启动的，要恢复到其他位置，在copy到原位置即可
cp /tmp/spfileorcl.ora $ORACLE_HOME/dbs
即在恢复的命令中必须要有：TO 子句即to '/tmp/spfileradius.ora'
再次对这个参数进行修改成功：
SQL> alter system set log_archive_dest_2='location=/u01/app/oracle' scope=spfile;
System altered.

【总结】：
1.在数据库的运行过程中，要对spfile进行备份，即转换pfile;
SQL> create pfile from spfile;
2.在进行pfile对spfile备份之后，同样需要换个目录再次对spfile进行备份
SQL>create pfile='/tmp/initradius.ora' from spfile;
3.在对任何的参数修改之前都要对参数文件进行备份，这样在操作失败之后，可以有回退的方法。

4.最好是利用脚本进行备份，同时在备份完数据库，归档之后，最好将当前的控制文件进行备份，即可以通过备份的控制文件所在的备份集进行恢复控制文件。

--END--