Appendix J. Troubleshooting ORA-* Errors

This section focuses on using the command line tool, Automatic Diagnostic Repository Command Interpreter (ADRCI), to troubleshoot Oracle database related errors. ADRCI was introduced in Oracle Database 11g in order to help users diagnose errors within their Oracle database environments and provide health reports if an issue should occur. The following example shows how one could troubleshoot an Oracle database instance error using the ADRCI tool.

Note

The following steps are intended to produce an ORA-07445 error that can be troubleshooted using the ADRCI tool. Do not attempt on a Oracle Database Production environment. The following is for demonstration purposes only and intended only to show how to troubleshoot ORA-* related errors using the ADRCI tool.

  1. In order to create an ORA-07445 error, an essential Oracle process is killed via the following commands: 

    $ ps -A --format pid,args | grep ora_dbrm | grep -v grep
8480 ora_dbrm_cdb

$ kill -SEGV 8480

  2. Export the ORACLE_HOME 

    $ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

  3. Start the ADRCI command tool via the command: 

    $ $ORACLE_HOME/bin/adrci

ADRCI: Release 12.2.0.1.0 - Production on Wed Sep 6 16:01:02 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci>

  4. At the ADRCI prompt , show Oracle Home’s available via the command: 

    adrci> show home
ADR Homes:
diag/rdbms/cdb/cdb
    Note

    If more than one Oracle Home is available, one must specify a particular Oracle Database Home. AN example on how to set to a particular Oracle Database Home is as follows: 

    adcri> set home diag/rdbms/cdb/cdb

  5. At the ADRCI prompt run the following command to see the last 50 entries in the alert log: 

    adrci> show alert -tail 50
    Note

    The above step is to view the alert log and check for errors. However, the following commands simplify the process of viewing problems with the Oracle deployment.

  6. Within the ADRCI, there are two key terms to be aware of, problem and incident. An incident is a particular time when a problem occurred. For example, it is possible for an Oracle process to crash at different times with the same ORA-07445. The multiple occurences of the crash are incidents, while the problem is still the ORA-07445 error. In order to view the problem, the following ADRCI command needs to be run. 

    adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/cdb/cdb:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 7445 [semtimedop]                                       61553                2017-09-06 15:59:10.480000 +00:00
1 row fetched

  7. In order to view how many incidents, the following ADRCI command must be run. In this example, I only have one incident in which the ORA-07445 problem occurred. 

    adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/cdb/cdb:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
61553                ORA 7445 [semtimedop]                                       2017-09-06 15:59:10.480000 +00:00
1 row fetched

  8. In order to view the incident in more detail, run the following: 

    adrci> show incident -mode detail -p "incident_id=61553"

ADR Home = /u01/app/oracle/diag/rdbms/cdb/cdb:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   61553
   STATUS                        ready
   CREATE_TIME                   2017-09-06 15:59:10.480000 +00:00
   PROBLEM_ID                    1
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    semtimedop
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0xD43100001DC3
   ERROR_ARG4                    PC:0x7FEA674FABDA
   ERROR_ARG5                    unknown code
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          <NULL>
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   CON_UID                       1
   PROBLEM_KEY                   ORA 7445 [semtimedop]
   FIRST_INCIDENT                61553
   FIRSTINC_TIME                 2017-09-06 15:59:10.480000 +00:00
   LAST_INCIDENT                 61553
   LASTINC_TIME                  2017-09-06 15:59:10.480000 +00:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     14.1
   KEY_NAME                      Service
   KEY_VALUE                     SYS$BACKGROUND
   KEY_NAME                      PdbName
   KEY_VALUE                     CDB$ROOT
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@oracle1.e2e.bos.redhat.com.8480_140644811120448
   KEY_NAME                      SID/u01/app/oracle/diag/rdbms/cdb/cdb/incident/incdir_61553/cdb_dbrm_8480_i61553.trc
   KEY_VALUE                     1695.17513
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_dbrm_8480.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/cdb/cdb/incident/incdir_61553/cdb_dbrm_8480_i61553.trc
1 row fetched
    Note

    The two parameters of important here are the PROBLEM_ID and INCIDENT_FILE.

  9. The incident file can be examined further via: 

    adrci> show trace /u01/app/oracle/diag/rdbms/cdb/cdb/incident/incdir_61553/cdb_dbrm_8480_i61553.trc
Output the results to file: /tmp/utsout_46828_14046_2.ado
/bin/bash: adrci: command not found

  10. Open the /tmp/utsout_46828_14046_2.ado with an editor such as vi. 

    /u01/app/oracle/diag/rdbms/cdb/cdb/incident/incdir_61553/cdb_dbrm_8480_i61553.trc
----------------------------------------------------------
LEVEL PAYLOAD
----- ------------------------------------------------------------------------------------------------------------------------------------------------
      Dump file /u01/app/oracle/diag/rdbms/cdb/cdb/incident/incdir_61553/cdb_dbrm_8480_i61553.trc
      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
      Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
      ORACLE_HOME:    /u01/app/oracle/product/12.2.0/dbhome_1
      System name:      Linux
      Node name:        oracle1.e2e.bos.redhat.com
      Release:  3.10.0-693.1.1.el7.x86_64
      Version:  #1 SMP Thu Aug 3 08:15:31 EDT 2017
      Machine:  x86_64
      Instance name: cdb
      Redo thread mounted by this instance: 1
      Oracle process number: 14
      Unix process pid: 8480, image: oracle@oracle1.e2e.bos.redhat.com (DBRM)


      *** 2017-09-06T15:59:10.488444+00:00
      *** SESSION ID:(1695.17513) 2017-09-06T15:59:10.488494+00:00
      *** CLIENT ID:() 2017-09-06T15:59:10.488508+00:00
      *** SERVICE NAME:(SYS$BACKGROUND) 2017-09-06T15:59:10.488521+00:00
      *** MODULE NAME:() 2017-09-06T15:59:10.488534+00:00
      *** ACTION NAME:() 2017-09-06T15:59:10.488546+00:00
      *** CLIENT DRIVER:() 2017-09-06T15:59:10.488557+00:00
      *** CONTAINER ID:(1) 2017-09-06T15:59:10.488570+00:00

[ ... Output Abbreviated ... ]

  11. While this concludes how to examine trace files that pertain to a particular ORA error using ADRCI; if the issue cannot be solved by the end user, the ADRCI provides the Incident Packaging Service (IPS) tool to zip the necessary trace files based on the problem. It can be then sent to support for further debugging. To create the appropriate zip file, use the following commands: 

    adrci> ips create package problem 1 correlate all
Created package 1 based on problem id 1, correlation level all

adrci> ips generate package 1 in "/home/oracle"
Generated package 1 in file /home/oracle/ORA7445se_20170906161951_COM_1.zip, mode complete
    Note

    Problem 1 is the Problem_ID found in a previous step. Package 1 is the package ID captured from the ips create output command.

    For more information about ADRCI visit: http://docs.oracle.com/database/122/SUTIL/oracle-ADR-command-interpreter-ADRCI.htm