|
How to restart a database after failed parameter change
Thursday November 20 2008 at 11:53
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!).
        
|
SpatialDB Advisor
Home
Comment [2]
yes. I do so all the way.
— an oracle dba · 5 June 2009, 11:50 · #
Perfect! Very thanks to you and your guide, very helpful for me!
— symon · 29 June 2009, 20:15 · #