Messing up some setting with the system SPFILE can end us up with a database which does no longer start up. Unfortunately this can happens delayed as for it is possible to set new vales without making them active immediately.

Now a system SPFILE is no real editable file and therefore fixing it becomes difficult:

A typically problem which can occur leaving your database inoperable is specifying LOG_ARCHIVE_DEST within a Oracle Database Version 10g as for syntax has change in this version and it is easily done following good “old” behavior.

You will end up with an database complaining about:

SQL> startup mount force
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

Fixing this we have to startup the database with an alternative INIT.ORA file which we usually have from the database creation process. Don’t worry about it is recent or not as for our aim is just to get up the database up and running in mount status:
(if not at hand create an INIT.ORA file from scratch using an universal example)

SQL> startup mount pfile=’C:\oracle10g\admin\SID\pfile\init.ora’;
ORACLE instance started.

Total System Global Area 135339844 bytes
Fixed Size                  454468 bytes
Variable Size            109051904 bytes
Database Buffers          25165824 bytes
Redo Buffers                667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

Having the database up now again, we can create an editable INIT.ORA by reading the values within system SPFILE:

SQL> create pfile=’c:\temp\init.ora’ from spfile=’c:\oracle10g\database\spfileSID.ora’;
File created.

Now with the new editable INIT.ORA file containing all the actual configuration settings of our database (even the wrong one) we can go on fixing our issue and finally convert the INIT.ORA file again back into a system SPFILE:

SQL> create spfile=’c:\oracle92\database\spfileSID.ora’ from pfile=’c:\temp\init.ora’;
File created.

When we now shutdown the database and reopen it again it will start and open gain using the fixed SPFILE as before:

SQL> startup force
ORACLE instance started.

Total System Global Area 139534168 bytes
Fixed Size                  454488 bytes
Variable Size             113246208 bytes
…..