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.
