Thursday, April 10, 2008

How to Start your database with non default spfile

All of you know that whenever it is invoked startup of a database then

1)First search for a spfile on it's default location named spfile$ORACLE_SID.ora

2)If not found then it searches for spfile.ora on the default location

3)If also not found then it search pfile on the default location.

But it is also not found then error arises.

We all know then we can easily startup with a pfile, like
startup pfile='Pfilename';
But there is no parameter like, startup spfile=.......;
So if my spfile is not in the default location then how I can specify the location of spfile.
The solution is ,

a)Create a blank Pfile.
b)Put the location of the non default spfile inside the blank Pfile with parameter spfile=location.
c)startup the database with the Pfile.

The example below will clear the problem.
1)Create no-default spfile.
SQL> create spfile='/export/home/oracle/myspfile.txt' from pfile;
File created.

2)Create a Pfile that will location SPfile.

SQL> !vi /export/home/oracle/pfile.txt
SPFILE=/export/home/oracle/myspfile.txt

3)Shutdown the database.
4)Start the database with the pfile.
SQL> startup pfile=/export/home/oracle/pfile.txt
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

5)Ensure that the database is started with no-default spfile.

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /export/home/oracle/myspfile.txt

No comments:

Post a Comment