RMAN备份与恢复之spfile恢复

实验环境

• 操作系统 Redhat5.4 x86
• 数据库版本 oracle 11gR2 (11.2.0.1.0)
• 实验前已经做了RMAN全量备份包括controlfile、spfile

实验模拟

SPFILE文集损坏或丢失

案例模拟

[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 8 00:19:30 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  /u01/app/oracle/product/11.2.0
                         /dbhome_1/dbs/spfilePROD.ora
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 dbs]$ rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora
[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 8 00:19:55 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

案例恢复

[oracle@node1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon May 8 00:34:59 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/08/2017 00:35:09
ORA-00205: error in identifying control file, check alert log for more info

--尝试从autobackup中恢复：
RMAN> restore spfile from autobackup;

Starting restore at 08-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170508
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170507
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170506
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170505
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170504
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170503
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170502
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 05/08/2017 00:36:53
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

--从指定备份集中恢复：
RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/PROD/autobackup/2017_05_08/o1_mf_s_943401816_djykbthr_.bkp';

Starting restore at 08-MAY-17
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD/autobackup/2017_05_08/o1_mf_s_943401816_djykbthr_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-MAY-17

RMAN> exit

Recovery Manager complete.

[oracle@node1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 8 00:38:07 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         683675300 bytes
Database Buffers      146800640 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> 

如果以上方法还是无法恢复spfile，则可以通过查看alert日志文件，根据日志文件中的启动信息编辑一个pfile文件，然后由pfile文件创建spfile文件出来。