We all are aware of spfile and pfile in Oracle. Oracle requires an initialization file to define the attributes and characteristics of the starting instance and the connecting sessions there of. We are aware of the Initialization parameter file (also referred as init.ora file or PFILE). This file holds the setup parameters that define the attribute of the instance that is being started. Administrators can control, tune and optimize an instance by setting and modifying the initialization parameters in this file.
Some parameters can be dynamically modified to affect the present instance, while others require the instance to be brought down so that changes can take effect. This remains the same when using PFILE or SPFILE. A simple search on the net will reveal a lot of information regarding PFILE and SPFILE.
There are 2 types of parameter files, namely :
1. pfile (parameter file) – older way [ not recommended by Oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]
spfile was introduced starting from Oracle 9i, until that time text-based pfile was used to store database initialization parameters.
What happens if someone has deleted your spfile or you have modified it in depth that you are now unable to even startup your database.
Your only option is to restore your spfile and this step is easy if you have not forgotten to set up your RMAN environment to use the AUTOBACKUP feature:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Because I’m using the flash recovery area, this setup will use it as default location where to save the controlfile and spfile.
Every time you backup your database or change your database structure (add/drop a tablespace or datafile and so on) RMAN will automatically save your current controlfile and spfile.
It’s important to know where your autobackup is saved because when you begin to restore your spfile, RMAN will open the database in NOMOUNT mode and without the spfile it won’t have any possibilities to know where the flash recovery area is located.
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 autobackups 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
Once the update is done, Follow the same process of upgrading agents.
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp