转载

使用rman恢复参数文件

当我们的数据库实例的参数文件丢失了，怎么样把它找回呢？如下

在恢复的过程中，可能要用到dbid，所以下面有两种方法可以查看dbid

方法一：查询v$database视图

SQL> select dbid from v$database;

DBID

----------

1446008355

方法二：用rman连接上已将启动的数据库实例

[oracle@potato ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 10 14:52:57 2017

connected to target database: ORCL (DBID=1446008355)

开始模拟丢失

[oracle@potato dbs]$ mv spfileorcl.ora spfileorcl.oraa

[oracle@potato dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 14:54:31 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force;

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

使用rman连接进行恢复

[oracle@potato ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 10 14:55:44 2017

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/11.2.0/db_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1343612 bytes

Variable Size 75501444 bytes

Database Buffers 79691776 bytes

Redo Buffers 2482176 bytes

若是我们不指定dbid用自动备份进行恢复，会报以下错误

RMAN> restore spfile from autobackup;

Starting restore at 10-FEB-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

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

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

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

RMAN-03002: failure of restore command at 02/10/2017 15:05:06

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

指定了dbid，再进行恢复

RMAN> SET DBID=1446008355;

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 10-FEB-17

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170210

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170209

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170208

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170207

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170206

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170205

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170204

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 02/10/2017 15:05:59

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

这里提示没有找到7日内的自动备份，这是因为我的库里面没有自动备份。

注：若是你的库里存在spfile自动备份，在这一步就可以把库还原成功了。

所以我们只能通过直接指定备份片的方式恢复。

注：使用直接指定路径恢复的方法是可以不指定dbid的

将spfile恢复到原来位置

RMAN> restore spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs' from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2017_02_10/o1_mf_s_935591112_d9tq6bpj_.bkp';

Starting restore at 10-FEB-17

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2017_02_10/o1_mf_s_935591112_d9tq6bpj_.bkp

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

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

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

RMAN-03002: failure of restore command at 02/10/2017 14:43:55

ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2017_02_10/o1_mf_s_935591112_d9tq6bpj_.bkp

ORA-32015: unable to restore SPFILE

ORA-27038: created file already exists

Additional information: 1

发现报错了，提示文件已经存在，那是因为我们通过rman将库暂时启动到nomonut状态，此时系统认为spfile是存在的。

那我们只能把它还原到其他位置。

RMAN> restore spfile to '/home/oracle/spfileorcl.ora' from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2017_02_10/o1_mf_s_935591112_d9tq6bpj_.bkp';

Starting restore at 10-FEB-17

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2017_02_10/o1_mf_s_935591112_d9tq6bpj_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 10-FEB-17

然后把文件copy到正确路径

[oracle@potato dbs]$ cp /home/oracle/spfileorcl.ora /u01/app/oracle/product/11.2.0/db_1/dbs

将数据库open

[oracle@potato dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 14:45:39 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production