恢复参数文件spfile

Oracle启动的时候，一定会去查找一个参数文件，如果如下参数文件均未找到，数据库将无法启动，Oracle 查找参数文件的顺序如下：

spfile<sid>.ora---pfile<sid>.ora----init<sid>.ora

sid是你自己的数据实例名，参数文件保存路径在$ORACLE_HOME/dbs

如下，通过两种方式恢复参数文件

比较简单的一种方法，通过Alert日志恢复，如果你知道你的alert日志路径，跳过如下步骤：

oracle@linux:~/product/10.2.0/db_1/dbs> ls *.ora
initdw.ora  init.ora
oracle@linux:~/product/10.2.0/db_1/dbs>

一个参数文件都没有，我们尝试启动数据库

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/product/10.2.0/db_1/dbs/initzxin.ora'
SQL>

提示找不到参数文件

首先在$ORACLE_HOME/dbs下创建一个init<sid>.ora的参数文件，内容如下

oracle@linux:~/product/10.2.0/db_1/dbs> cat initzxin.ora
db_name='zxin'
oracle@linux:~/product/10.2.0/db_1/dbs>

其中的zxin为你自己的实例名，然后，我们启动数据库到nomount状态：

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2082496 bytes
Variable Size             113248576 bytes
Database Buffers           50331648 bytes
Redo Buffers                6303744 bytes
SQL>

查一下alert日志的路径,如下红框内的路径即为alert日志的路径:

进入alert日志所在目录，查看alert日志:

oracle@linux:~/data/admin/zxin/bdump> vi alert_zxin.log

拷贝出红框中的内容，在$ORACLE_HOME/dbs下新建一个文件pfile<SID>.ora,把复制的内容贴进去,特别需要注意的是，所有数值和路径需要用单引号括起来，如下:

oracle@linux:~/product/10.2.0/db_1> vi pfilezxin.ora

然后使用pfile启动数据库:

SQL> startup pfile='/home/oracle/product/10.2.0/db_1/dbs/pfilezxin.ora';
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                  2083848 bytes
Variable Size             352322552 bytes
Database Buffers         1006632960 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL>

拷贝pfile<SID>.ora 为init<SID>.ora

oracle@linux:~/product/10.2.0/db_1/dbs> cp pfilezxin.ora initzxin.ora
oracle@linux:~/product/10.2.0/db_1/dbs>

然后通过pfile创建spfile

SQL> create spfile from pfile;

File created.

SQL>

重启数据库:

另一种方法，通过RMAN恢复参数文件:

oracle@linux:~> rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Oct 20 16:53:55 2016

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

connected to target database: ZXIN (DBID=1579813944)

查看有控制文件的备份集:

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
24      Incr 2  6.80M      DISK        00:00:01     2016.10.17 17:15:09
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20161017T171501
        Piece Name: /home/oracle/product/10.2.0/db_1/dbs/0prijp8s_1_1
  Control File Included: Ckp SCN: 358638       Ckp time: 2016.10.17 17:15:08

RMAN>

模拟参数文件丢失:

oracle@linux:~/product/10.2.0/db_1/dbs> rm -rf spfilezxin.ora
oracle@linux:~/product/10.2.0/db_1/dbs>

恢复参数文件：

停止数据库:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

RMAN> startup nomount;

Oracle instance started

Total System Global Area    1375731712 bytes

Fixed Size                     2083848 bytes
Variable Size                352322552 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  14692352 bytes

RMAN> restore spfile from '/home/oracle/product/10.2.0/db_1/dbs/0prijp8s_1_1';

Starting restore at 2016.10.20 16:57:41
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: autobackup found: /home/oracle/product/10.2.0/db_1/dbs/0prijp8s_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2016.10.20 16:57:43

RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

database opened

RMAN>

重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                  2083848 bytes
Variable Size             385876984 bytes
Database Buffers          973078528 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/oracle/product/10.2.0/db
                                                 _1/dbs/spfilezxin.ora
SQL>