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.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

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