In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Tuesday, August 31, 2010

restore the server parameter file(spfile) from RMAN backup

1.Connect to target Database with RMAN.

i)If you have lost your spfile and dont have pfile then, follow the steps to recover spfile using RMAN.

RMAN>CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID=3148849783

2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT

Example:

3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;

To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;
RMAN> restore spfile to '/tmp/spfilejay.ora' from autobackup;

If you want to restore to a pfile then use,
RMAN> restore spfile to pfile '/tmp/initjay.ora';

4)Restore Spfile from control file autobackup;
RMAN> run
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> restore spfile from autobackup;
4> }

Note after restore spfile from RMAN shutdown and startup the database
else you will receive the error message
ORA-00205: error in identifying control file, check alert log for more info


1. Restore spfile from autobackup;
RMAN> set dbid=3148849783

executing command: SET DBID

RMAN> startup force nomount;

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

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes

RMAN> restore spfile from autobackup;

2. Restore spfile to non-default location
RMAN> restore spfile to '/tmp/spfilejay.ora' from autobackup;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: JAY
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100831
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-AUG-10

3. If you want to restore to a pfile from restored spfile

RMAN> restore spfile to pfile '/tmp/initjay.ora';

Starting restore at 31-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE to PFILE
output filename=/tmp/initjay.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/JAY_c-3148849783-20100831-00 tag=TAG20100831T174354
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 31-AUG-10

4.Restore spfile from Controlfile AutoBackup
RMAN> set dbid=3148849783

executing command: SET DBID

RMAN> startup force nomount;

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

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes

RMAN> run
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> restore spfile from autobackup; 4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20100831
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100831-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 31-AUG-10




Reference:
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

1 comment:

Kim said...

Hi,

Thank you for the posting and thank you for the good presentation.

Regards,
Kim Roddy
Video conferencing uk

 
Share/Bookmark