Oracle database does not start up automatically!

Today I had to go in to configure multi-master replication between two Oracle databases as our Oracle replication expert was out at a client site. Well, to set up replication, I had to first connect to the database instances, and I found that one of the two instances was not starting up automatically even though the Windows service for that instance was started. I remembered that this was a problem that I had to run into a couple of years back but it took me a bit to figure out the core issue and resolve it. So I figured, I might as blog about it in case I run into it again a few years down the road and need to resolve the issue again.

First things first, I connected to the Oracle instance as a sysdba, and verified that I could start the instance up manually.

C:\Documents and Settings\Administrator>sqlplus "sys/sys_pwd@portal51 as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Jul 24 21:34:39 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  236003236 bytes
Fixed Size                   454564 bytes
Variable Size             209715200 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Once I started up the instance, I connected as a regular user and verified that I could connect. I then restarted the database instance Windows service, and found that once again the instance started up in idle mode. So, obviously something else was needed to have the instance start up automatically in the active mode. That turned out to be fairly straightforward. I opened up the registry editor, navigated to ‘HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0′, and found that the value of ‘ORA_PORTAL51_AUTOSTART’ (where PORTAL51 was our database SID) was FALSE. I changed that to TRUE, restarted the database instance, and sure enough the instance started up automatically!

PS: In case you are wondering, you did read it right. Yes, setting up multi-master Oracle replication does count as one of my skills – I had to pick it up many years when I could not get supposedly experienced Oracle DBAs to set it up in a timely enough fashion for my taste!