This content has been marked as final. Show 21 replies
Check that weather you using spfile or not.
SQL> show user;
USER is "SYS"
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/product/102/dbs/spfilesyslog.ora
If your spfile parameter returns a value then your database has started with spfile.
Otherwise log in as sysdba
then create a spfile from your pfile.
Shutdown and startup the database by spfile.
SQL > create spfile from pfile;
You will find it on $ORACLE_HOME/dbs/init<sid>.ora
Total Questions: *29 (29 unresolved)*
Mark your helpful post as correct/helpful
$>create spfile from pfile
$>create spfile from pfile=/oracle/dbs/initsid.ora
it always uses spfile.
SQL>SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Initial file type" FROM sys.v_$parameter WHERE name = 'spfile';
I have done same but it starts sometime but after take pfile.
Oracle expects the spfile in $ORACLE_HOME/dbs
It will first search for the pfile, if the pfile is not there, it will use the spfile.
The trick is to have an 1-line pfile
spfile=<name of your spfiile>
Obviously this is documented.
Senior Oracle DBA
After creating the spfile.
1. shutdown immediate
It will always use the spfile.
Normally, the database uses SPFILE<SID>.ora in priority.
So, you may check that the SPFILE exists in *$ORACLE_HOME/dbs* (for UNIX) or ORACLE_HOME\database (for Windows).
Once the SPFILE is created, you can always move the old init<SID>.ora to another directory, so as to be sure that the SPFILE is the only parameter file used.
However, you should always keep the init<SID>.ora in a safe place, as a Backup of the SPFILE.
Please find enclosed, a link about PFILE/SPFILE:
Hope this help.
Edited by: Lubiez Jean-Valentin on May 9, 2010 2:51 PM
No its not like that in our case I have done same many time.Right now database is started from pfile whereas spfile is located there(dbs).
I did try to validate it using rman command to check and also it gave below error.
RMAN> backup validate check logical spfile;
Starting backup at 09-MAY-10
using channel ORA_SBT_TAPE_1
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of backup command at 05/09/2010 13:15:40
RMAN-06062: can not backup SPFILE because the instance was not started with SPFILE
Oracle expects the spfile in $ORACLE_HOME/dbs.It will first search for the pfile, if the pfile is not there, it will use the spfile.
--spfile and pfile are located at $ORACLE_HOME/dbs, As per your update i have to move pfile at some other location?
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
If Windows post results from command below
If *NIX post results from command below
env | sort
if you choose to not answer my requests, then I choose to ignore your problem
$ env | sort
MAILMSG=[YOU HAVE NEW MAIL]
COPY lines below & PASTE into terminal window
ls -ltr $ORACLE_HOME/dbs
COPY commands & results then PASTE all back here
Check your database startup script.
A default "STARTUP" command would look for the spfile / pfile in $ORACLE_HOME/dbs.
However, if the script issues a "STARTUP PFILE=..location_of_file" then it would ignore the default locations and would ignore the presence of an spfile. It would use the PFILE specified in the startup command.
Hemant K Chitale