How to Troubleshoot with Your Oracle 12c Database Logs - dummies

How to Troubleshoot with Your Oracle 12c Database Logs

By Chris Ruel, Michael Wessler

You need to dive into the Oracle 12c logs themselves. Each database has a set of directories where key log, trace, and dump files are stored.

Database log infrastructure in Oracle 12c

Using log files to diagnose a problem is often a daily task for the DBA so knowing how Oracle manages this critical resource is important. Oracle log file structure and management are referred to as the Automatic Diagnostic Repository (ADR). ADR provides these log management capabilities:

  • Integrated log management not just for the database but other Oracle products. Currently, Automatic Storage Management and listener also write to the new log infrastructure.

  • Event logging in terms of incidents with included diagnostic data and stored in zip files that can be reviewed and sent to Oracle Support. The idea is to better compartmentalize error events and neatly package them so they can be sent directly to Oracle Support.

  • Incident flood control to intelligently limit the creation and size of trace files. If an event repeats at an extreme rate above a defined threshold, only the occurrence of the event is logged.

It’s important to know where the key diagnostic files are located:

  • The location for the diagnostic subdirectories (diag) is the ADR_BASE and is typically under the ORACLE_BASE.

  • The location of the base for the log subdirectories is the ADR_HOME and is defined by database parameter DIAGNOSTIC_DEST.

  • The ADR_HOME is beneath the ADR_BASE location and is under the database SID directory.

The structure for ADR_HOME for databases follows:


For example, here’s the following structure for the dev12c database:

$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c
alert hm    incpkg lck metadata    metadata_pv sweep
cdump incident ir   log metadata_dgif stage    trace

The ADR_HOME location is the full path up to and including the second reference to dev12c.

You see this same information neatly stored within the database and can be queried via V$DIAG_INFO:

SQL> select name, value from v$diag_info;
-----------  ---------------------------------
Diag Enabled     TRUE
ADR Base       /u01/app/oracle
ADR Home       /u01/app/oracle/diag/rdbms/dev12c/dev12c
Diag Trace      /u01/app/oracle/diag/rdbms/dev12c/dev12c/trace
Diag Alert      /u01/app/oracle/diag/rdbms/dev12c/dev12c/alert
Diag Incident     /u01/app/oracle/diag/rdbms/dev12c/dev12c/incident
Diag Cdump      /u01/app/oracle/diag/rdbms/dev12c/dev12cl/cdump
Health Monitor    /u01/app/oracle/diag/rdbms/dev12c/dev12c/hm
Default Trace File
Active Problem Count 0
Active Incident Count 0
11 rows selected.

Within each database directory are subdirectories where different files are stored. Here is each primary directory and its purpose.

Directory Purpose
alert Stores very important XML-formatted alert log for database
cdump Core dump location of memory stack when a process fails
incident Subdirectories relating to individual events or incidents
trace Trace and dump files for background and user processes; also
contains text formatted alert log
This is a listing of each directory:
$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c
alert hm    incpkg lck metadata    metadata_pv sweep
cdump incident ir   log metadata_dgif stage    trace
oralinux1> ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c/alert
$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c/cdump
$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c/incident
$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c/trace
alert_dev12c.log    dev12c_m000_23037.trc dev12c_ora_6593.trm
dev12c_aqpc_24310.trc dev12c_m000_23037.trm dev12c_ora_760.trc

These directories can get cluttered with many files and eat up disk space.

Have a process to clean up the trace, cdump, and incident directories so they don’t fill up your disk and are easier to manage.

Trace and dump files in Oracle 12c

When a problem event occurs (such as a failed process or failed memory allocation), log files for that event are written into the trace directory.

The format for the log filename is SID_process name_process ID.trc:

$ ls $ORACLE_BASE/diag/rdbms/dev12c/dev12c/trace
alert_dev12c.log    dev12c_m000_23037.trc dev12c_ora_6593.trm
dev12c_aqpc_24310.trc dev12c_m000_23037.trm dev12c_ora_760.trc

Here are the contents of a trace file:

Trace file /u01/app/oracle/diag/rdbms/dev12c/dev12c/trace/dev12c_dbw0_24263.trc
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ORACLE_HOME = /u01/app/oracle/product/12.1.0
System name:  Linux
Node name:   oralinux1
Release:    2.6.39-400.17.2.el6uek.x86_64
Version:    #1 SMP Wed Mar 13 12:31:05 PDT 2013
Machine:    x86_64
Instance name: dev12c
Redo thread mounted by this instance: 0 <none>
Oracle process number: 11
Unix process pid: 24263, image: oracle@oralinux1 (DBW0)
*** 2013-07-19 13:21:21.458
*** CLIENT ID:() 2013-07-19 13:21:21.458
*** SERVICE NAME:() 2013-07-19 13:21:21.458
*** MODULE NAME:() 2013-07-19 13:21:21.458
*** ACTION NAME:() 2013-07-19 13:21:21.458
2013-07-19 13:21:21.458540 :kjcipctxinit(): (pid|psn)=(11|1): initialised and
linked pctx 0x125d93038 into process list

The difference between a dump and a trace file: A trace is an ongoing log of a problem event. The dump is the one-time dumping of information into a file for a problem event. As a Database Administrator (DBA), you should review these text files to diagnose what is occurring.

If a process crashes, a core dump can be created in the cdump directory. This is a binary trace file of the memory process and its contents at the time of the crash. Although many people consider these to be “hands-off” for a DBA to review, that isn’t necessarily the case.

The Linux and UNIX strings command can show the printable text of a binary file. If you opt to upload the core file to any support organization, you may want to review it first for username and password combinations because they are sometimes present in clear text in these files.

Listener log in Oracle 12c

You may have to track connections into the database via the listener. Every time a connection to the database occurs, that event (or failure) is stored in the listener log.

Knowing where listener logs are generated is important to troubleshooting. You can find the listener log under the ADR_BASE/diag/tnslsnr directory tree. (In this example, it’s /u01/app/oracle/diag/tnslsnr/oralinux1/listener/trace/listener.log.)

Here’s a sample log entry:

02-AUG-2013 17:38:48 * (CONNECT_DATA=(SERVICE_NAME=dev12c)
  (CID=(PROGRAM=sqlplus)(HOST=oralinux1)(USER=oracle))) *
  * establish * dev12c * 0

Key information is the time, host, and program for the incoming connections.

Here’s a Dell TOAD software utility user connecting, most likely from a user’s workstation:

02-AUG-2013 11:57:45 * (CONNECT_DATA=(SERVICE_NAME=
  (HOST= (PORT=3108))
  * establish * * 12514

Finally, an error is occurring:

TNS-12514: TNS:listener does not currently know of service
      requested in connect descriptor

You can search for the Transport Network Substrate (TNS) error code in the listener log to see what errors are occurring. This search is useful because there will (hopefully) be far more connections than errors and, like the alert logs, the listener log can grow large.