spfile丢失的处理方法

SPFILE为二进制文件，如果被破坏了，数据库无法启动，那该怎么办？

一．一般方法解决

1.有备份或init{ORACLE_SID}.ora文件，那使用即可

2.SPFILE文件的内容很少，在unix平台上只需用strings命令就可以提取出其中的文本

    strings spfile{ORACLE_SID}.ora

   在windows平台上用写字板打开文件，copy出其中的文本即可

   将文本放在init{ORACLE_SID}.ora文件中就可以打开数据库

3.还可以从警告日志文件中获得相关的内容，警告日志内容大致如下

Starting up ORACLE RDBMS Version:10.2.0.1.0.

System parameters with non-default values:

  processes                = 150

  sga_target               = 167772160

  control_files            = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

  db_block_size            = 8192

  compatible               =10.2.0.1.0

  db_file_multiblock_read_count= 16

  db_recovery_file_dest    = C:\oracle\product\10.2.0\flash_recovery_area

  db_recovery_file_dest_size= 2147483648

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  remote_login_passwordfile= EXCLUSIVE

  db_domain                =

  job_queue_processes      = 10

  audit_file_dest          = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP

  background_dump_dest     = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP

  user_dump_dest           = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

  core_dump_dest           = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP

  db_name                  = orcl

  open_cursors             = 300

  pga_aggregate_target     = 16777216

PMON started with pid=2, OS id=2188

PSP0 started with pid=3, OS id=2192

 

从中我们可以很容易的创建一个pfile，再通过create spfile from pfile来创建一个spfile

二．Rman解决

想通过rman解决得先通过rman备份过spfile文件。

1.打开rman，提示找不到spfile文件

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release10.2.0.1.0 - Production on Mon Dec 9 17:07:24 2013

Copyright (c) 1982, 2005, 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 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITORCL.ORA'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2002752 bytes

Variable Size                 67109056 bytes

Database Buffers              88080384 bytes

Redo Buffers                   2191360 bytes

2．指定dbid

RMAN> set dbid=1354439025

executing command: SET DBID

不指定会报错

RMAN-00571:==========================================================

RMAN-00569:============= ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of restore command at 12/09/2013 17:11:33

RMAN-06495: must explicitly specify DBID with SET DBID command

3．通过autobackup或指定文件恢复

RMAN> restore spfile from autobackup;

Starting restore at 09-DEC-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20131209

channel ORA_DISK_1: looking for autobackup on day: 20131208

channel ORA_DISK_1: looking for autobackup on day: 20131207

channel ORA_DISK_1: looking for autobackup on day: 20131206

channel ORA_DISK_1: looking for autobackup on day: 20131205

channel ORA_DISK_1: looking for autobackup on day: 20131204

channel ORA_DISK_1: looking for autobackup on day: 20131203

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 12/09/2013 17:11:56

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

因为自动备份中没有spfile的备份文件，所以失败，通过指定文件备份

RMAN> restore spfile from 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2013_12_09\O1_

MF_NNSNF_TAG20131209T164551_9BC0T0TM_.BKP';

 

Starting restore at 09-DEC-13

using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\201

3_12_09\O1_MF_NNSNF_TAG20131209T164551_9BC0T0TM_.BKP

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 09-DEC-13---------------------------成功

4.关闭数据库，重新打开如果startup不能打开，则可先设置dbid

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> set dbid=1354439025

 

RMAN> startup

connected to target database (not started)

Oracle instance started

database mounted

database opened

Total System Global Area     167772160 bytes

Fixed Size                     2002816 bytes

Variable Size                104857728 bytes

Database Buffers              58720256 bytes

Redo Buffers                   2191360 bytes

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29014732/viewspace-773688/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29014732/viewspace-773688/