Don't panic!

  

Cry about...
Oracle Troubleshooting

Database instance locks up

Symptom:

The apparent complete lock-up of the database. It is still possible to connect to the database, but any attempt to query it results in the application (or Oracle tool) locking up.

Possible Causes:

Alternately, if the problem is repeatable then consider monitoring Oracle memory usage (i.e. shared pool size and block buffer usage). See also trouble shooting notes for ‘Database instance failure’.

Remedy if out of archive log space:

Symptom:

The archive log space has been exhausted. Look at the drive to which archive logs are written. It will have either no space left or insufficient space to write the next archive log.

Remedies:

  • Create more space. Try emptying the wastebasket.
  • Delete all the archive logs. This will free up space, but means that an incremental backup of the database is no longer possible. The next database backup must therefore be a full one.

Remedy if shared pool exhausted:

Oracle uses a least-recently-used algorithm to determine what to remove from the shared pool. Accordingly, a database error caused by the shared pool being exhausted – with no obvious oracle error – is unlikely.

Symptoms:

  • Oracle instance dies. Application sees corresponding connection error but no obvious error indicating why the instance has failed.
  • Looking in the file ‘<SID>LCK0.TRC’ on the server shows the error “WARNING: out of shared memory while allocating lock” recorded at the end of the file. This indicates that shared pool is being exhausted because the shared pool is too small given the number of locks Oracle has been configured to use.

If the error is repeatable, the monitoring of available free memory in the shared pool will show the available free memory reducing to a few percent shortly before the instance goes down.

Remedy:

  • Increase the initialisation parameter SHARED_POOL_SIZE.
  • If the error was generated whilst Oracle was allocating a lock, try reducing the number of locks.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.