oracle 恢复参数文件,Oracle参数文件(spfile)恢复

Oracle参数文件(spfile)恢复

参数文件的修改不会在重做日志里做记录，所以，不管是全部还是部分丢失，仅做restore操作，不存在recover动作

参数文件的丢失分为两类

1、参数文件丢失，实例crash

2、参数文件丢失，实例正常运行

1、模拟参数文件丢失且实例crash了

SQL> startup;

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittrsen.ora’

SQL> ! ps -ef | grep pmon_t=========》》实例关闭了

oracle 7528 7513 0 15:10 pts/1 00:00:00 /bin/bash -c ps -ef | grep pmon_t

oracle 7530 7528 0 15:10 pts/1 00:00:00 grep pmon_t

利用rman启动到nomount阶段，一般如果没有参数文件是无法启动的，rman是利用_dummy_instance隐藏参数来控制

因为restore参数文件需要在实例启动下才能做restore，现在rman通过隐藏参数方式启动一个实例名相同但db_name不同的实例，从而来避免无法获取备份集里的参数文件的信息

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/dbhome_1/dbs/inittrsen.ora’

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 398459784 bytes

Database Buffers 654311424 bytes

Redo Buffers 13905920 bytes

在alert日志里可以看到rman默认启动一个db_name为DUMMY的实例，其启动的sid名字还是trsen

System parameters with non-default values:

sga_target = 1G

compatible = “11.2.0.4.0”

_dummy_instance = TRUE

remote_login_passwordfile= “EXCLUSIVE”

db_name = “DUMMY”

Mon Dec 21 15:18:18 2015

PMON started with pid=2, OS id=8128

Mon Dec 21 15:18:18 2015

PSP0 started with pid=3, OS id=8130

Mon Dec 21 15:18:19 2015

SQL> !ps -ef | grep pmon_t

oracle 8128 1 0 15:18 ? 00:00:00 ora_pmon_trsen============》》》》

oracle 9334 9328 0 15:43 pts/1 00:00:00 /bin/bash -c ps -ef | grep pmon_t

oracle 9336 9334 0 15:43 pts/1 00:00:00 grep pmon_t

利用手工的方式从备份的参数文件里恢复spfile

RMAN> restore spfile from ‘/oradata/bk1/c-3731845208-20151218-15’;

Starting restore at 21-DEC-15

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oradata/bk1/c-3731845208-20151218-15

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 21-DEC-1

启动数据库实例

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 6.0663E+10 bytes

Fixed Size 2268032 bytes

Variable Size 5637145728 bytes

Database Buffers 5.4895E+10 bytes

Redo Buffers 128344064 bytes

Database mounted.

Database opened.

SQL> show parameter db_name;

NAME TYPE VALUE

———————————— ———– ——————————

db_name string trsen

若有恢复目录，可以利用恢复目录去做

[oracle@gpsdb admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 21-DEC-2015 16:08:59

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gpsdb)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production

Start Date 11-DEC-2015 14:19:59

Uptime 10 days 1 hr. 49 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/gpsdb/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gpsdb)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary…

Service “DUMMY” has 1 instance(s).

Instance “trsen”, status BLOCKED, has 1 handler(s) for this service…

Service “catadb” has 1 instance(s).

Instance “catadb”, status READY, has 1 handler(s) for this service…

The command completed successfully

[oracle@gpsdb admin]$ rman target sys/oracle catalog rcowner/oracle@catadb;

Recovery Manager: Release 11.2.0.4.0 – Production on Mon Dec 21 16:09:10 2015

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

connected to target database: DUMMY (not mounted)

connected to recovery catalog database

RMAN> restore spfile from autobackup;

Starting restore at 21-DEC-15

configuration for DISK channel 2 is ignored

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1576 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151221

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151220

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151219

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218

channel ORA_DISK_1: AUTOBACKUP found: /oradata/bk1/c-3731845208-20151218-15

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oradata/bk1/c-3731845208-20151218-15

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 21-DEC-15

2、参数文件丢失，但实例没有crash，可以采用：

RMAN> restore spfile to ‘/tmp/trsen_init_20151221.ora’ from autobackup;

Starting restore at 21-DEC-15

using channel ORA_DISK_1

recovery area destination: /oradata/db_recovery_dest

database name (or database unique name) used for search: TRSEN

channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151221

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151220

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151219

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218

channel ORA_DISK_1: AUTOBACKUP found: /oradata/bk1/c-3731845208-20151218-15

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oradata/bk1/c-3731845208-20151218-15

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 21-DEC-15