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

yes. I do so all the way.

— an oracle dba Jun 5, 11:50 am #
Perfect! Very thanks to you and your guide, very helpful for me!

— symon Jun 29, 08:15 pm #

<<Fixing failed spatial indexes after import using data pump >>How to extract elements from the result of an sdo_intersection of two polygons.

Name		Remember
E-mail
http://
Message
	Textile Help