Can we start the Database with both the spfile and pfile lost??

Hi Data lovers,

Hope you guys are having a good time with your databases.

A scenario i came across this week while playing with one of my test Databases is, i lost both my spfile and pfiles.

so what to do in this case to bring my database up? i already had a blog post on recovering some of the parameters, at least, in such scenarios here.

Now i found another option to start my database, through RMAN.

Yes, RMAN can be used for starting the Oracle instances when we lost all the parameter files. It use default parameters and startup the instance.

Please see the exercise below.

[DB11203-11.2.0 @ mymachine : /home/oracle] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:27:13 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /11.2.0.3/dbs/spfileDB11203.or
                                                 a
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now we’re renaming the spfile and trying to start the database.

[DB11203-11.2.0 @ mymachine : /home/oracle] home
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3] cd dbs
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] ls
hc_DB11203.dat  init.ora  lkDB11203  orapwDB11203  spfileDB11203.ora
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] mv spfileDB11203.ora spfileDB11203.ora_

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:27:55 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/11.2.0.3/dbs/initDB11203.ora’
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now try with RMAN

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jun 12 19:28:20 2013

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

connected to target database (not started)

RMAN> startup

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/11.2.0.3/dbs/initDB11203.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/12/2013 19:29:46
ORA-00205: error in identifying control file, check alert log for more info

This error is because, as per default settings the Oracle instance expects control files to be under $ORACLE_HOME/dbs location. Now i identified the location of the control files and copied them to this location.

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] cd /u01/app/oracle/database/DB11203/
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203]
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] ls
control01.ctl  control02.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] cp control01.ctl control02.ctl /u01/app/oracle/product/11.2.0/11.2.0.3/dbs
[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] sql

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 12 19:33:00 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now try it again with RMAN

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jun 12 19:33:09 2013

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

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
database opened

Total System Global Area     939495424 bytes

Fixed Size                     2233960 bytes
Variable Size                268437912 bytes
Database Buffers             662700032 bytes
Redo Buffers                   6123520 bytes

RMAN> exit

Please feel free to comment/provide your feedback.

CSM

About these ads

2 Responses to Can we start the Database with both the spfile and pfile lost??

  1. Rakesh says:

    how did you identify the control files ?

    • csmdba says:

      There’s no procedure which can guarantee a 100% success but the possibilities may include, but not limited to,

      1). In my case i know the location of every file so it was so easy for me

      2). If you’re not sure about the location, you can search in your database server with ‘find’ command if you followed some kind of standard while creating your database. Standards like file should have an extension of ‘.ctl’ or starts with the name ‘ctrl’ or ‘control’ etc…

      3). If you have everything on ASM, it’s much easier to search.

      Again, all of these methods are like trail and error only. No guarantee that you’ll find the files for sure but you’ll definitely have a high probability.

      CSM

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

Follow

Get every new post delivered to your Inbox.

Join 332 other followers

%d bloggers like this: