Different scenerios on restoring spfile

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s