spfile参数文件丢失及恢复方案

 

spfile参数文件丢失及恢复方案

 

下面文章源自官方文档，结合官方文档来实验一下spfile参数文件丢失及恢复方案

本次演示的操作系统：Rhel6.3；  Oracle版本：Oracle11gR2

 

Recovering a Lostor Damaged Server Parameter File

If your serverparameter file (SPFILE) becomes lost or corrupted, the current instance

may fail, or thenext attempt at starting the database instance may fail. There are

several ways torecover the SPFILE:

1、 If the instance is running, issue the following command to re-create the SPFILE

from the current values of initialization parameters in memory:

CREATE SPFILE FROM MEMORY;

This command creates the SPFILE with the default name and in the default

location. You can also create the SPFILE with a new name or in a specified

location.

 

实验如下：

1)查看spfile参数文件路径：
SQL> show parameter spfile;
NAME                 TYPE        VALUE
----------------------------------------------- ------------------------------
spfile                  string     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora

 
2)删除spfile参数文件
[oracle@pri dbs]$rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora

 
3)尝试修改spfile里的内容，提示报错，spfile不存在
SQL>  alter system set processes=300 scope=spfile;
 alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-01565: error inidentifying file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora'
ORA-27037: unableto obtain file status
Linux-x86_64 Error:2: No such file or directory
Additionalinformation: 3

 
4)按照文档中说明，直接从内存中创建一个spfile参数文件，但也提示有问题，不能创建一个现在正在被实例使用的spfile(应该是创建的默认spfile和现在正在使用的spfile目录相同，名称相同)
SQL> CREATE SPFILE FROM MEMORY;
CREATE SPFILE FROMMEMORY
*
ERROR at line 1:
ORA-32002: cannotcreate SPFILE already being used by the instance

 
5)那就创建一个不同名称的spfile，然后再改成正确的spfile名称
SQL> create spfile ='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile_tmp.ora' from memory;
File created.

 
把刚才创建的spfile改成正确的名称spfile<sid>.ora
[oracle@pri dbs]$mv spfile_tmp.ora   spfileorclpri.ora


6)再次修改spfile的内容，修改成功
SQL>  alter system set processes=300 scope=spfile;
System altered.

 

2、 If you have a valid text initialization parameter file (PFILE), re-create the SPFILE

from the PFILE with the following statement:

CREATE SPFILE FROM PFILE;

This command assumes that the PFILE isin the default location and has the

default name. See"Creating a Server Parameter File"on page 2-34 for the

command syntax touse when the PFILE isnot in the default location or has a

nondefault name.

 

实验如下：(说明本次实验是重启数据库的方式，在spfile丢失的情况下，会用pfile启动，然后再用pfile来重新创建spfile)

1)查看spfile参数文件路径：
SQL> show parameter spfile;
NAME                 TYPE        VALUE
----------------------------------------------- ------------------------------
spfile               string     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora

 
2)删除spfile参数文件
[oracle@pri dbs]$rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora


3)尝试修改spfile里的内容，提示报错，spfile不存在

SQL>  alter system set processes=300 scope=spfile;
 alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-01565: error inidentifying file
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora'
ORA-27037: unableto obtain file status
Linux-x86_64 Error:2: No such file or directory
Additionalinformation: 3

 
4)重启数据库
SQL> shutdown immediate;
Database closed.
Databasedismounted.
ORACLE instanceshut down.

SQL> startup
ORACLE instancestarted.
Total System GlobalArea  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             490736760 bytes
DatabaseBuffers          335544320 bytes
Redo Buffers                2392064 bytes
Database mounted.
Database opened.

 
5)查看当前spfile参数文件，因为value为空，所以是以pfile启动的
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
----------------------------------------------- ------------------------------
spfile                               string

 
6)以pfile来重新创建spfile
SQL> create spfile from pfile;
File created.

 
7)再次重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instanceshut down.

SQL> startup
ORACLE instancestarted.
Total System GlobalArea  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             490736760 bytes
DatabaseBuffers          335544320 bytes
Redo Buffers                2392064 bytes
Database mounted.
Database opened.

 
8)查看spfile参数文件，value有值，说明数据库是以spfile参数文件启动的
SQL> show parameter spfile;
NAME                 TYPE        VALUE
----------------------------------------------- ------------------------------
spfile               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora


9)再次修改spfile的内容，修改成功
SQL>  alter system set processes=300 scope=spfile;
System altered.

 
附：数据库启动 参数文件的查找顺序：spfile<sid>.ora—>spfile.ora—>init<sid>.ora

 

3、Restore the SPFILE from backup.

See "BackingUp the Server Parameter File"on page 2-38 for more information.

 

实验如下：(这里演示用rman备份spfile，删除spfile，然后使其恢复的过程)

1)rman备份spfile参数文件
RMAN> backup spfile;
Starting backup at10-MAR-16
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=37 device type=DISK
channel ORA_DISK_1:starting full datafile backup set
channel ORA_DISK_1:specifying datafile(s) in backup set
including currentSPFILE in backup set
channel ORA_DISK_1:starting piece 1 at 10-MAR-16
channel ORA_DISK_1:finished piece 1 at 10-MAR-16
piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01r04pd4_1_1tag=TAG20160310T135235 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:00:02
Finished backup at10-MAR-16

 
2)删除spfile和pfile
[oracle@pridbs]$  rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora
[oracle@pridbs]$  rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorclpri.ora

 
3)启动数据库，报错，无法找到参数文件，
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: couldnot open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorclpri.ora'

 

4)需要手工建立一个pfile参数文件，然后让数据库启动到nomount状态，rman才可以连接到数据库(pfile是文本模式，可以直接打开编辑；而spfile是二进制文件，无法直接编辑，所以这里编辑了pfile文件)
[oracle@pri dbs]$vi pfile_tmp.ora
db_name=orclpri


让数据库启动到nomount状态
SQL>  startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/pfile_tmp.ora';
ORACLE instance started.
Total System GlobalArea  217157632 bytes
Fixed Size                  2251816 bytes
Variable Size             159384536 bytes
DatabaseBuffers           50331648 bytes
Redo Buffers                5189632 bytes

 
5)使用rman恢复刚才备份的spfile
RMAN> restore spfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01r04pd4_1_1';
Starting restore at10-MAR-16
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=20 device type=DISK
channel ORA_DISK_1:restoring spfile from AUTOBACKUP/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01r04pd4_1_1
channel ORA_DISK_1:SPFILE restore from AUTOBACKUP complete
Finished restore at10-MAR-16

 
6)查看一下目录，出现了spfile文件，恢复成功
[oracle@pri dbs]$ll spfileorclpri.ora
-rw-r----- 1 oracleoinstall 3584 Mar 10 14:02 spfileorclpri.ora

 
7)重启数据库
SQL> shutdown immediate
ORA-01507: databasenot mounted
ORACLE instanceshut down.

SQL> startup
ORACLE instance started.
Total System GlobalArea  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             490736760 bytes
DatabaseBuffers          335544320 bytes
Redo Buffers                2392064 bytes
Database mounted.
Database opened.

 
8)现在是以恢复的spfile参数文件启动的
SQL> show parameter spfile;
NAME                 TYPE        VALUE
----------------------------------------------- ------------------------------
spfile               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorclpri.ora

 

4、 If none of the previous methods arepossible in your situation, perform these steps:

1. Create a text initialization parameter file (PFILE) from the parameter value

listings in the alert log.

When an instance starts up, the initialization parameters used for startup are

written to the alert log. You can copy and paste this section from the text

version of the alert log (without XML tags) into a new PFILE.

See "Viewingthe Alert Log"on page 9-21 for more information.

2. Create the SPFILE from the PFILE.

See "Creatinga Server Parameter File"on page 2-34 for instructions.

 

实验如下：当数据库启动时，在alert_<sid>.log 日志文件中有数据库的参数信息，可以用此参数创建一个pfile来启动数据库(具体可以自己实验一下)

[oracle@pri trace]$tail -500 alert_orclpri.log

……

System parameterswith non-default values:
  processes                = 150
  memory_target            = 796M
  control_files            ="/u01/app/oracle/oradata/orclpri/control01.ctl"
  control_files            ="/u01/app/oracle/oradata/orclpri/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  log_archive_dest_1       = "LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclpri"
  log_archive_dest_2       = "SERVICE=orclsty OPTIONAL LGWRSYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orclsty"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  fal_client               = "orclpri"
  fal_server               = "orclsty"
  log_archive_config       ="DG_CONFIG=(orclpri,orclsty)"
  log_archive_format       = "%t_%s_%r.arc"
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile="EXCLUSIVE"
  db_domain                = ""
  audit_file_dest          ="/u01/app/oracle/admin/orclpri/adump"
  audit_trail              = "DB"
  db_name                  = "orclpri"
  db_unique_name           = "orclpri"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"

……

 

附：alert_<sid>.log 日志文件路径：
SQL> select * from v$diag_info where name like '%Alert%';
   INST_ID   NAME           VALUE
---------------------------------------------------------------
  1          Diag Alert      /u01/app/oracle/diag/rdbms/orclpri/orclpri/alert