Oracle database configuration issues that cause downtime
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.
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’);
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