How to Troubleshoot with Your Oracle 12c Database Logs
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:
$ADR_BASE/diag/rdbms/DATABASE NAME/DATABASE SID
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; NAME VALUE ----------- --------------------------------- 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 /u01/app/oracle/diag/rdbms/dev12c/dev12c/trace/dev12c_ora_23293.trc 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.
|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 log.xml $ 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 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions 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))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.121)(PORT=21165)) * 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=192.168.2.121) (CID=(PROGRAM=C:\Program?Files\Quest?Software\TOAD\TOAD.exe) (HOST=LPT-MPYLE)(USER=mpyle))) * (ADDRESS=(PROTOCOL=tcp) (HOST=192.168.2.170) (PORT=3108)) * establish * 192.168.2.121 * 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.