Today we are going to learn about recovery with rman for loss of spfile. As we know that RMAN can be used either with or without a recovery catalog.

A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations.

Generally, a skillful DBA would urge that the Enterprise Manager instance schema and RMAN catalog schema be placed in the same utility database on a server distinct from the main servers. The RMAN schema generally only requires 15 megabyte per year per database backed up.

It’s completely different when you are using a recovery catalog because it knows exactly where the latest available autobackup is located.
If you are not using a recovery catalog, in order to restore the spfile you have also to set your database identifier (DBID).
When your database is open you can obtain that information querying the V$DATABASE view, but now your database can’t even mount.
My approach is to preserve and send me by email the RMAN log of every backup I take: in that log you can extract the database identifier you need.
Indeed every time you connect to your database through RMAN you can see that value:

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Aug 27 08:06:12 2012

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

connected to target database: ORCL (DBID=1229390655)

RMAN>

Currently I’m using an spfile located in the default location ($ORACLE_HOME/dbs)

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora

On the default parameter file location I have the following parameter files:

[oracle@localhost dbs]$ ll *.ora
-rw-r--r-- 1 oracle oracle 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oracle 621 Oct 2 2010 initorcl.ora
-rw-r----- 1 oracle oracle 3584 Aug 27 07:28 spfileorcl.ora

I simulate a deletion of my parameter files:

[oracle@localhost dbs]$ mv init.ora init.ora.20120828_020500.bck
[oracle@localhost dbs]$ mv initorcl.ora initorcl.ora.20120828_020500.bck
[oracle@localhost dbs]$ mv spfileorcl.ora spfileorcl.ora.20120828_020500.bck

Now let’s see what it happens when I try to startup the database.

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 28 02:08:33 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

We have to restore our spfile.

Let’s begin. Start RMAN:

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 28 02:14:02 2012

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

connected to target database (not started)

Force RMAN to start an instance without a parameter file. You have to use the FORCE option:

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

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

Total System Global Area 159019008 bytes

Fixed Size 1342848 bytes
Variable Size 79692416 bytes
Database Buffers 75497472 bytes
Redo Buffers 2486272 bytes
Now you have to set your DBID:
RMAN> set DBID 1229390655;

executing command: SET DBID

Let’s see first what it happens when I specify the restore spfile from autobackup command and the autobackups are not saved on the default location ($ORACLE_HOME/dbs)

RMAN> restore spfile from autobackup;

Starting restore at 28-08-2012 02:18:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120828
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120827
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120826
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120825
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120824
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120823
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120822
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 08/28/2012 02:18:54
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN is not able to know where the autobacks are. It doesn’t know if I used a flash recovery area and where eventually that FRA was located.
So I have to explicitly tell RMAN where autobackup is located and which to use. To identify the autobackup I need to locate my flash recovery area and go through the autobackup directory

[oracle@localhost dbs]$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup
[oracle@localhost autobackup]$ ll -lrt
total 20
drwxrwx--- 2 oracle oracle 4096 Jul 17 09:05 2012_07_17
drwxrwx--- 2 oracle oracle 4096 Jul 21 08:44 2012_07_21
drwxrwx--- 2 oracle oracle 4096 Jul 25 07:53 2012_07_25
drwxrwx--- 2 oracle oracle 4096 Jul 26 06:59 2012_07_26
drwxrwx--- 2 oracle oracle 4096 Aug 21 07:57 2012_08_21

The latest autobackup is located in the 2012_08_21 directory:

[oracle@localhost autobackup]$ ls -lrth 2012_08_21
total 19M
-rw-rw---- 1 oracle oracle 9.5M Aug 21 07:42 o1_mf_s_791883766_8377m7v2_.bkp
-rw-rw---- 1 oracle oracle 9.5M Aug 21 07:57 o1_mf_s_791884673_8378hktd_.bkp

I will use the o1_mf_s_791884673_8378hktd_.bkp autobackup to restore the spfile.

RMAN> restore spfile from '/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp';

Starting restore at 28-08-2012 03:18:57
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-08-2012 03:18:58

Now I can bounce the instance and successfully open the database.

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area 456146944 bytes

Fixed Size 1344840 bytes
Variable Size 352324280 bytes
Database Buffers 96468992 bytes
Redo Buffers 6008832 bytes

As you can see there’s a new spfile on the default parameter file location

[oracle@localhost dbs]$ ls -lrt *.ora
-rw-rw---- 1 oracle oracle 3584 Aug 28 02:56 spfileorcl.ora

According to the “Oracle Database Backup and Recovery Reference 11g Release 2 (11.2)” manual on page 3-40 (or n.248 of that pdf) if you look at “Table 3-10 RESTORE … FROM AUTOBACKUP”.

You can also do not set the DBID when you specify RECOVERY AREA and DB_NAME on the restore command, that is:

[oracle@localhost dbs]$ rm spfileorcl.ora
[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 28 03:44:23 2012

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

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'

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

Total System Global Area 159019008 bytes

Fixed Size 1342848 bytes
Variable Size 79692416 bytes
Database Buffers 75497472 bytes
Redo Buffers 2486272 bytes

RMAN> restore spfile from autobackup recovery area='/home/oracle/app/oracle/flash_recovery_area' db_name=orcl;

Starting restore at 28-08-2012 03:45:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_08_21/o1_mf_s_791884673_8378hktd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-08-2012 03:45:55

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area 456146944 bytes

Fixed Size 1344840 bytes
Variable Size 352324280 bytes
Database Buffers 96468992 bytes
Redo Buffers 6008832 bytes

We hope this information is valuable for you.

Source

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.