Current Oracle Spatial Blog Articles
I thought I would publish a simple set of steps that can be followed to restart an Oracle Database after a parameter change causes the database not to open.
Here is an example (on Windows). Let’s assume my database (gisdb) currenly has its compatible parameter set to 10.2.0.4. Because I want to test performance at a previous version, we accidently do this:
After restarting the database, it refuses connections with the following error:
How do I find out what is happening and fix it?
1. Check $ORACLE_HOME/rdbms/oradim.log
Here we found (close to the bottom):
This was caused by the compatible parameter change.
But how do we fix this if we cannot log in to the database and execute another alter system command??
First, open up a copy of sqlplus in nolog mode and connect as sysdba to the instance as follows:
Now create a pfile (editable) from the (binary, uneditable) spfile as follows:
Find the pfile (C:\oracle\product\10.2.0\db_1\database\INITgisdb.ORA), open it using your favourite text editor, find and change the compatible parameter back to the original value:
Now, go back to sqlplus and create an spfile from the modified pfile:
Oops, of course, we have to shutdown the instance to release the spfile as follows:
Now, try and create the spfile:
And, finally, startup the database and check the value of the compatible parameter:
OK, we have successfully changed the parameter, we can no go ahead and moount and open the database:
I hope this is useful to someone (I am glad I have logically set out the steps as I have managed to stop the database from opening before after experimenting with parameter settings and, because I am not a full-time DBA, I have to try and remember what I did last time – no longer!).