Oracle database configuration issues that cause downtime

May 18, 2012 by 2 Comments 

A well-designed high availability (HA) solution accounts for all these factors in preventing unplanned database downtime. One of the true challenges in designing a highly available (HA) solution is examining and addressing all the possible causes of downtime. It is important to consider causes of both unplanned and planned downtime. The diagram shown in the slide, classifies unplanned database failures.

Common Oracle database configuration issues cause downtime

Causes of unplanned database downtime

In this article I tried to list common database configuration issues that can affect Oracle database availability causing unplanned downtime. As an experienced DBA you should proactively diagnose and do preventive actions to avoid these types of situations in your database environment. Furthermore, from my experience even on database environments with high availability (HA) solutions (like clustering, storage mirroring, Oracle RAC and Data Guard, etc.) these cases still persists and negatively affect the database high availability (HA) at most.

Archive log destination is full

This issue can happen either on disk level or on the initialization parameter level.
Size your archive log destination properly. I recommend always having enough space to keep so many archive logs in an archive log destination to survive for at least one day without performing an archive log backup.

If the issue occurred, you still have a few options to fix it:

– Backup archive logs and delete those in the archive log destination to allow database log switches
– Add more space to the archive destination
– Dynamically change the log archive destination:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = (‘LOCATION=/oradata/ORCL/archive’);
– etc.

Tablespace or associated filesystem is full

Ensure proper configuration of your tablespaces and associated file systems / disks and think carefully about the database file extension strategy, database file options (like file autoextend), file system and tablespace monitoring, etc.

ORACLE_BASE file system / disk is full

ORACLE_BASE is the root of the Oracle Database directory tree. Usually it consists of one or more ORACLE_HOME directories where Oracle binaries are installed. How to prevent that issue:

–  Have a separate ORACLE_BASE file system and size it properly

–  Ensure that the Oracle mandatory and system auditing do not fill it in quickly

–  Be careful with default Data Pump dump location (ORACLE_HOME/rdbms/log/). Place it to a separate disk

–  In Oracle 11g keep track of file generation in Automatic Diagnostic Monitor (ADR) location that can also quickly fill the file system in.

When the issue occurs database may become non responsive. In such scenarios you should quickly react to free up the space or / and add more.

Database listener is down

Just restart it or fix the issues first

Database is in restricted or quiesced state

Be careful changing a database into restricted or quiesced state. And be aware that there are cases when somebody (a process, job or a system operation) can switch database in quiesced state. For example, DB is switched in quiescing for a short time when installing Oracle Enterprise Manager (OEM) Console using EMCA.

Database reaches certain parameter limits

Sometimes when a database reaches certain system limits, you have to increase those values quickly or even bounce the instance to make changes effective. Some common cases are below:

– processes – number of processes
– open_cursors – number of open cursors

Initialization parameter changes requirement

Think upfront in setting database initialization parameter right at the beginning, since not all of them can be adjusted online. 114 database parameters of 11gR2 are still not modifiable online on the instance level.

…read the second part of the article: Oracle database configuration issues that cause downtime – Part 2

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

2 Responses to “Oracle database configuration issues that cause downtime”
  1. Christian Klammer says:

    Hi, Kirill,

    Wenn Auditing in einem Dateisystem stattfindet, kann es entweder passieren, dass das Auditing-Dateisystem vollläuft oder es bei -ix-Dateisystemen die Anzahl der I-Nodes im Dateisystem erschöpft wird, bevor die Gesamtspeicherkapazität des Dateisystems erreicht wird.

    In jedem der genannten Fälle steht die Datenbank, weil die Auditingeinträge nicht weggschrieben werden.

    Christian

  2. Kirill Loifman says:

    Thanks Christian
    Indeed, you have to monitor your file system properly in case you use OS AUDITING, otherwise your DB can hang.
    — Kirill

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email