Top 5 Recent Articles
- Biography (1)
- Blog (27)
- Changes (1)
- Customers (1)
- Education (2)
- General Software (21)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Spatial (2)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (61)
- PostGIS (20)
- Press Releases (1)
- Source code (6)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (17)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- XML (5)
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:
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!).