Any DBA who worked on oracle 8i knows where to find init[sid].ora.
The SPFile is located in the same folder just like the Pfile.
Except… It’s not manually managed. It’s managed by oracle. This is done to avoid human error in parameters.
There can be problems in parameters though. During the migration, a parameter (DB_BUFFER_SIZE), from the 8i era was left in the parameter file and was conflicting with another one (DB_CACHE_SIZE) in the parameter file.
When restarted, the database couldn’t even mount…
Don’t worry, when you get that error, there’ll be a list of conflicting parameters.
Usually, you should be able to get into OEM and remove the bad parameter then it would be possible to start again. But it was my luck and OEM was not working too.
There’s only one way out of it. It is possible to get back to the old 8i style and use an init.ora file which can be modified manually.
To do so, connect as sysdba on command line:
C:\SET ORACLE_SID = [Your Sid]
C:\SQLPLUS / as sysdba
SQL>CREATE PFILE=’C:\WHEREVER_YOU_WANT_IT_TO_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_IS\SPFILE[SID].ORA’
As it says, it will create the Pfile from the SPfile. Then it is manually manageable. When the culprit is removed, simply do the opposite thing:
SQL>CREATE SPFILE=’C:\[WHERE_IT_SHOULD_BE]\INIT[SID]’ FROM SPFILE=’C:\WHERE_IT_SHOULD_BE_TOO\SPFILE[SID].ORA’
This will create a new spfile from the modified init[sid].ora
Now all what is left, is to start the database
SQL>STARTUP
Hi Nicolas,
I could not get last create statement.
You have mentioned to create
SPFILE=INIT[SID] from SPFILE=SPFILE[SID].ora
and after that you are saying,
this will create a new spfile from init[sid].ora
But it is looking that init[sid].ora file will be created.
Or is it because of copy/paste from earlier command ?
There is an error in there.
You should read from PFILE to SPFILE or opposite.
6bSgp0 Hello! I’m Samuel Smith, i’m from Switqerland i and find your site really brilliant!
Hi,
I am Vijay, While creating spfile from pfile in oracle 8i i got an error like below:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
I try it after shutdown the database. I am unable to open sqlplus from command prompt. I am only able to connect from . How can i create spfile from pfile in this situation…. Can you give the solution for this….
Regards
Vijay
Hi.
Shared memory Real does not exist means that your database is not working at all.
Try run the command
SQL>Startup mount
then you’ll be able to start doing things with it.
If you can’t startup your DB then you have more than merely a SPFILE problem.
Nico
Awesome! This post saved my bacon
And what’s more important than bacon…