Restore spfile through RMAN, here are some scenerios/examples.
There are some pre-requites, the database would need to be in nomount stage so you would need a pfile to start database in nomount. So one can use the following pfile change the following parameters (control_files, core_dump_dest, db_name, user_dump_dest, background_dump_dest) according to your environment.
After spfile is restored you would need to shutdown the database so Oracle starts with restored spfile.
$ cat initTEST.ora
*.background_dump_dest=’/u01/oradata/admin/TEST/bdump’
*.control_files=’/u01/oradata/TEST/control1.ora’,’/u01/oradata/TEST/control2.ora’,’/u01/oradata/TEST/control3.ora’
*.core_dump_dest=’/u01/oradata/admin/TEST/cdump’
*.db_block_size=8192
*.db_name=’TEST’
*.sga_max_size=209715200
*.sga_target=167772160
*.undo_management=’auto’
*.user_dump_dest=’/u01/oradata/admin/TEST/udump’
CONTROLFILE AUTOBACKUP explained here
1) In this scenerio there is a autobackup is present of spfile, database is no mount state so to use AUTOBACKUP DBID needs to be set before restoring spfile
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TEST (not mounted)
RMAN> set dbid 1992878807
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 28-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20101128
channel ORA_DISK_1: autobackup found: c-1992878807-20101128-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-NOV-10
In this scenerio there spfile is restored from autobackup, database is in nomount state and if the filename of backup piece is known so it can be passed to restore spfile
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 29 17:45:04 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TEST (not mounted)
— restoring spfile from a specific backup piece using full path or can just be the name
RMAN> restore spfile from '/u01/oracle/product/10.2.0/db_1/dbs/c-1992878807-20101128-00';
— RMAN> restore spfile from 'c-1992878807-20101128-00';
Starting restore at 28-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u01/oracle/product/10.2.0/db_1/dbs/c-1992878807-20101128-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-NOV-10
Scenerio where the SPFILE is already there and database was started with spfile, Oracle will not allow to overwrite the file unless “TO” clause is specified to overwite with a different name, RMAN will generate the following RMAN-06564 error
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TEST (not mounted)
RMAN> set dbid 1992878807
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 28-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/28/2010 15:06:34
RMAN-06564: must use the TO clause when the instance is started with SPFILE
Scenerio where there is no autobackup of control file so Oracle goes to last 7 days by default and does not find it
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TEST (not mounted)
RMAN> set dbid 1992878807
executing command: SET DBID
RMAN> restore spfile from autobackup;
If you want to tell RMAN to look for spfile more than 7 days so using maxdays RMAN will look for spfile from current date to currentday – maxdays “RMAN> restore spfile from autobackup maxdays 200;”
Starting restore at 28-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20101128
channel ORA_DISK_1: looking for autobackup on day: 20101127
channel ORA_DISK_1: looking for autobackup on day: 20101126
channel ORA_DISK_1: looking for autobackup on day: 20101125
channel ORA_DISK_1: looking for autobackup on day: 20101124
channel ORA_DISK_1: looking for autobackup on day: 20101123
channel ORA_DISK_1: looking for autobackup on day: 20101122
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 11/28/2010 15:08:30
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Restoring spfile but not connected to the target database it will return error RMAN-06171
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> set dbid 1992878807;
executing command: SET DBID
RMAN> restore spfile from autobackup;
Starting restore at 28-NOV-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/28/2010 15:08:17
RMAN-12010: automatic channel allocation initialization failed
RMAN-06171: not connected to target database
Scenerio where restroing spfile through autobackup and DBID is not set
$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 29 17:55:13 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TEST (not mounted)
RMAN> restore spfile from autobackup;
Starting restore at 29-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=39 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/29/2010 17:55:25
RMAN-06495: must explicitly specify DBID with SET DBID command
[…] 10. Different scenerios on restoring spfile – An Oracle Spin by … […]