How to restart a database after failed parameter change

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:

 SQL> ALTER SYSTEM SET compatible='10.2.0.1' scope=spfile;

After restarting the database, it refuses connections with the following error:

 ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

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):

 Tue Nov 18 09:05:55 2008
 C:\oracle\product\10.2.0\db_1\bin\oradim.exe -startup -sid gisdb -usrpwd * -log oradim.log -nocheck 0
 Tue Nov 18 09:05:57 2008
 ORA-00201: control file version 10.2.0.3.0 incompatible with ORACLE version 10.2.0.1.0
 ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\GISDB\CONTROL01.CTL'

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:

 C:\Documents and Settings\Simon>sqlplus /nolog

 SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 18 09:39:19 2008

 Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

 SQL> connect / as sysdba
 Connected.

Now create a pfile (editable) from the (binary, uneditable) spfile as follows:

 SQL> create pfile from spfile;

 File created.

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:

 *.compatible='10.2.0.3.0'

Now, go back to sqlplus and create an spfile from the modified pfile:

 SQL> create spfile from pfile;
 create spfile from pfile
 *
 ERROR at line 1:
 ORA-32002: cannot create SPFILE already being used by the instance

Oops, of course, we have to shutdown the instance to release the spfile as follows:

 SQL> shutdown immediate
 ORA-01507: database not mounted

 ORACLE instance shut down.

Now, try and create the spfile:

 SQL> create spfile from pfile;

 File created.

And, finally, startup the database and check the value of the compatible parameter:

 SQL> startup nomount;
 ORACLE instance started.

 Total System Global Area   775946240 bytes
 Fixed Size                   1299096 bytes
 Variable Size              423628136 bytes
 Database Buffers           343932928 bytes
 Redo Buffers                 7086080 bytes

 SQL> show parameters compatible

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ---------------------
 compatible                           string      10.2.0.3.0

OK, we have successfully changed the parameter, we can no go ahead and moount and open the database:

 SQL> alter database mount;

 Database altered.

 SQL> alter database open;

 Database altered.

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!).