Oracle Database Requires Manual Restart Occasionally

Question

Let me begin by saying my Oracle knowledge is minimal.

We have a proprietary application that stores data into a Oracle 9.2 database, which works great. I only work with this software and thus Oracle every 3 months or so. The problem is, and it seems due to inactivity, I'll have to startup the database/instance using the following commands:

conn sys as sysdba
<enter password>
startup

If I'm only away from using the server for a couple of weeks, I don't have to do this. I was just curious what the deal was? It's not really that big a deal, but is it something I can fix, or do I even want to fix it? Perhaps it's a security thing? Where is this inactivity setting, if that's what it is?

I'm mostly interested in understanding, so the more information, the better. Thanks!

Answer 1

Oracle would not shut itself down due to inactivity. Are you sure the machine hasn't been rebooted and you don't have the OS starting Oracle on reboot?

You can check the Oracle alert log for details on why and when the database shutdown. In 9i I believe the default is in:

/u01/app/oracle/admin/<sid>/bdump/alert_<sid>.log

When you see Oracle shutting down in the log, you can note the time and then look in the system log (Linux: /var/log/messages) to see if it corresponds to a system shutdown.

Hope this helps you track down the issue.

Edit based on feedback:

It's interesting that you say the server has been rebooted since 2007 and there is nothing in the alert log of the Oracle server shutting down. That indicates to me you're either looking at the wrong/old log file or that Oracle isn't shutting down cleanly on an OS shutdown.

Couple of questions:

• Are you certain that the Oracle database is open when you startup the machine?
  
  • If you login to sqlplus '/ as sysdba', you'll get a "Connected to an idle instance"
    if the db isn't open.
• Are you seeing db file recovery when opening the database?