SPFILE Recovery

Database Version: 10.1.0.5.0
OS: Windows XP
Archive log ENABLE
Backup Method : RMAN


For SPFILE backup we have to configure below parameters

C:\>set ORACLE_SID=orcl

C:\>rman target=sys

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1155117244)



RMAN> configure controlfile autobackup on;



using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Note: with above configuration whenever database backup taken that time automatically SPFILE backup generated.


Eg:

Starting Control File and SPFILE Autobackup at 12-AUG-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2007_0
8_12\O1_MF_S_630440652_3CY60FMB_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 12-AUG-07

How to restore SPFILE backup incase of SPFILE lost

C:\>del c:\oracle\product\10.1.0\db_1\database\SPFILEORCL.ORA

C:\>del c:\oracle\product\10.1.0\db_1\database\initorcl.ora

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE
\INITORCL.ORA'

C:\>rman target=sys

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

target database Password:
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.1.0\DB_1\DATABASE
\INITORCL.ORA'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area 79691776 bytes

Fixed Size 788076 bytes
Variable Size 69990804 bytes
Database Buffers 8388608 bytes
Redo Buffers 524288 bytes


must explicitly specify DBID with SET DBID command



You can check DBID from $ORACLE_HOME/flash_recovery_area/autobackup/here_autobackup_file_name_with_db_id.BKP





RMAN> restore spfile from autobackup db_recovery_file_dest='c:\oracle\product\10
.1.0\flash_recovery_area' db_name='orcl';



Starting restore at 12-AUG-07
using channel ORA_DISK_1

recovery area destination: c:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AR
EA\ORCL\AUTOBACKUP\2007_08_12\O1_MF_S_630440652_3CY60FMB_.BKP
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 12-AUG-07


Login to SQL prompt and give below command


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.