oracle 11g seems to die every 2 days, how can i debug what's happening?

Question

I installed oracle 11g on ubuntu 11.10, but it seems that every couple of days I'll get to work and the database isn't running but the listener is.

To fix it, all I have to do is login and start it up:
su - oracle
cd /path/to/oracle11g/bin
./sqlplus sys as sysdba
startup;
exit


and then everything's ok. Taking a look at the alert log at: /u01/app/oracle/diag/rdbms/mydb/mydb/alert/log.xml

It looks like the problem started here:

<msg time='2012-03-09T22:00:02.570+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiTuneSqlDrv:4182:2579917519' client_id='' type='NOTIFICATION'
 group='sqltune' level='16' module='DBMS_SCHEDULER'
 pid='4460'>
 <txt>Begin automatic SQL Tuning Advisor run for special tuning task  &quot;SYS_AUTO_SQL_TUNING_TASK&quot;
 </txt>
</msg>
<msg time='2012-03-09T22:00:31.273+01:00' org_id='oracle' comp_id='rdbms'
 msg_id='3646992671' type='INCIDENT_ERROR' group='Generic Internal Error'
 level='1' prob_key='ORA 600 [kgkprrpicknext1]' upstream_comp=''
 downstream_comp='' ecid='' errid='11150'
 detail_path='/u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_29268.trc  (incident=11150):
ORA-00600: internal error code, arguments: [kgkprrpicknext1], [18], [2], [], [], [], [], []
 </txt>
</msg>

Answer 1

Oracle has an alert log which should tell you when and why it shut down. Depending on how the database was configured, that will be under a directory identified by either the diagnostic_dest or background_dump_dest parameters, and will be called alert.xml or alert.log (in previous versions). If you're running it, you can get information from Enterprise Manager too.

Has the server it's running on rebooted as well? If so the listener may be configured to start automatically, but the database not; there's a flag in the oratab file that says whether each instance should start, e.g. when dbstart is run, which could be done from an init.d script on boot. You could try changing the flag for your database from N to Y to see if it stops you having to start it manually, at least.

If the server hasn't rebooted then the database may have crashed, in which case I'd expect to see errors in the alert log - search backwards, particularly for ORA-00600 errors which can be fatal, but also for anything close to the time it crashed.

---

Based on the ORA-600 in the alert log, this looks like it might be bug 6651027, which is related to the resource manager and is supposedly fixed in 11.1.0.7 and 11.2. If you have access to the Oracle Support website you could look at note 559251.1, and the patch set notes. You can also look at the trace file referenced in the alert log for more detail on exactly where it's failing.

If you're on an earlier release I'd recommend you patch up and see if the problem goes away. If it doesn't then you'll need to raise a support assistance request with Oracle I'm afraid. ORA-600s that aren't already fixed in a patch sometimes have a workaround, but often need Oracle's input.