How to identified issues with ADRCI

In oracle 19 as the previusly versions ADRCI (Automatic Diagnostic Repository Command Interpreter) its a great tool to solved issues and deal with Oracle Errors and Tracefiles

We could verified in the alert the oracle error :

Here, we will review quickly and easy and introduction to know the most basically commands to start.

I will start, as common with alert log file:

tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
Alert log evindecy

For years, Oracle has provided us with the possibility of reviewing in the alert log, the most critical movements such as the behavior of database engine processes.

Hands on

Now, we starting how to ADRCI we help us to undestand our issue.


oracle@DSORA02:~/JuanAndresMercado $ adrci

oracle@DSORA02:~/JuanAndresMercado $ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Apr 6 13:01:57 2020

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

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

The first step is to set the destination of all the database log files.

Lets go and start !

adrci> set home diag/rdbms/orcl/ORCL

They saved in the ADR home path , remember that we could have many databases in the same server, or exadata partitioning.

Path ComponentValue for Oracle Database
product_typerdbms
product_idDB_UNIQUE_NAME
instance_idSID
Oracle ADRCI Home Path

It possible find more information in the view V$DIAG_INFO, about the ADRCI home.


         1 Diag Enabled         TRUE                                                                  0
         1 ADR Base             /opt/oracle                                                           0
         1 ADR Home             /opt/oracle/diag/rdbms/orclcdb/ORCLCDB                                0
         1 Diag Trace           /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace                          0
         1 Diag Alert           /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/alert                          0
         1 Diag Incident        /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/incident                       0
         1 Diag Cdump           /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/cdump                          0
         1 Health Monitor       /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/hm                             0
         1 Default Trace File   /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_112          0
                                73.trc

         1 Active Problem Count 0                                                                     0
         1 Active Incident Coun 0                                                                     0
           t

         1 ORACLE_HOME          /opt/oracle/product/19c/dbhome_1                                      0

12 rows selected.

SQL>

The ARDCI commands we help us to analizating our problem detected in the alert log.

For example:

ADR_base/diag/rdbms/orclbi/orclbi/

With show problems we access to a major detail:


adrci> show problem

Saves errors that accumulate in traces and trace files.

ADR Home = /u01/app/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                                                  
-------------------- ----------------------------------------------------------- -------------------- --------------------------------------                        --
1                    ORA 700 [pga physmem limit]                                 94146                2020-04-06 11:45:58.954000 -03:00                             
2                    ORA 7445 [psstsat_set_attribute_info]                       94620                2020-04-06 12:48:06.071000 -03:00                             
2 rows fetched

With SHOW INCIDENTES we can view the records and saves errors that accumulate in traces and trace files.
We review the different errors that occurred and at what point in time.
This helps us to search more precisely for the one that corresponds to our case or to understand its evolution and how often it has occurred.


adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
1                    ORA 700 [pga physmem limit]                                 2019-11-21 15:59:44.293000 -03:00
2562                 ORA 700 [pga physmem limit]                                 2019-11-21 16:01:18.059000 -03:00
5123                 ORA 700 [pga physmem limit]                                 2019-11-21 16:01:51.995000 -03:00
7684                 ORA 700 [pga physmem limit]                                 2019-11-21 16:09:03.068000 -03:00
10245                ORA 700 [pga physmem limit]                                 2019-11-21 16:17:06.382000 -03:00
12806                ORA 700 [pga physmem limit]                                 2019-11-21 19:50:14.967000 -03:00
15367                ORA 700 [pga physmem limit]                                 2019-11-22 15:27:51.291000 -03:00
17928                ORA 700 [pga physmem limit]                                 2019-11-22 15:31:23.418000 -03:00
20489                ORA 700 [pga physmem limit]                                 2019-11-22 16:24:52.028000 -03:00
22090                ORA 700 [pga physmem limit]                                 2019-11-22 16:30:44.942000 -03:00
23691                ORA 700 [pga physmem limit]                                 2019-11-22 18:47:05.310000 -03:00
25292                ORA 700 [pga physmem limit]                                 2019-12-04 15:50:35.526000 -03:00
26893                ORA 700 [pga physmem limit]                                 2019-12-04 16:02:52.264000 -03:00
28494                ORA 700 [pga physmem limit]                                 2019-12-04 16:51:51.002000 -03:00
30095                ORA 700 [pga physmem limit]                                 2020-02-07 18:12:00.016000 -03:00
31696                ORA 700 [pga physmem limit]                                 2020-02-10 10:02:00.658000 -03:00
33297                ORA 700 [pga physmem limit]                                 2020-02-10 10:13:17.882000 -03:00
34898                ORA 700 [pga physmem limit]                                 2020-03-30 17:38:15.903000 -03:00
35387                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 16:59:08.355000 -03:00
35388                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 17:01:10.498000 -03:00
35395                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 17:39:31.955000 -03:00
35396                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 17:40:40.843000 -03:00
35397                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 18:09:15.702000 -03:00
35398                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 18:09:37.712000 -03:00
35427                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 18:45:51.493000 -03:00
35411                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 18:46:14.647000 -03:00
35419                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 18:48:01.547000 -03:00
35421                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 19:18:55.735000 -03:00
35379                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 19:20:19.840000 -03:00
35412                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 19:30:37.922000 -03:00
35413                ORA 7445 [psstsat_set_attribute_info]                       2020-04-01 19:32:00.499000 -03:00
35399                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 15:13:31.813000 -03:00
35422                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 15:14:54.510000 -03:00
35400                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:12:44.110000 -03:00
35401                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:13:57.686000 -03:00
35435                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:17:02.933000 -03:00
35355                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:18:18.033000 -03:00
36520                ORA 700 [pga physmem limit]                                 2020-04-02 16:34:27.800000 -03:00
38921                ORA 700 [pga physmem limit]                                 2020-04-02 16:37:14.310000 -03:00
41322                ORA 700 [pga physmem limit]                                 2020-04-02 16:38:56.830000 -03:00
41811                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:56:58.841000 -03:00
41812                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 16:58:07.284000 -03:00
45325                ORA 700 [pga physmem limit]                                 2020-04-02 17:01:13.215000 -03:00
45814                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 17:07:47.647000 -03:00
45806                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 17:09:02.368000 -03:00
45862                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 17:30:43.385000 -03:00
45807                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 17:30:50.156000 -03:00
45863                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 17:30:59.215000 -03:00
45811                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 18:13:29.582000 -03:00
45854                ORA 7445 [psstsat_set_attribute_info]                       2020-04-02 18:14:41.246000 -03:00
50 rows fetched (*** more available ***)

The detailed error, its possible explorer with the incident_id number.


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

ADR Home = /u01/app/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   45854
   STATUS                        ready
   CREATE_TIME                   2020-04-02 18:14:41.246000 -03:00
   PROBLEM_ID                    2
   CLOSE_TIME                    
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    psstsat_set_attribute_info
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x0
   ERROR_ARG4                    PC:0x116E3000
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    
   ERROR_ARG7                    
   ERROR_ARG8                    
   ERROR_ARG9                    
   ERROR_ARG10                   
   ERROR_ARG11                   
   ERROR_ARG12                   
   SIGNALLING_COMPONENT          PLSQL_Infrastructure
   SIGNALLING_SUBCOMPONENT       
   SUSPECT_COMPONENT             
   SUSPECT_SUBCOMPONENT          
   ECID                          
   IMPACTS                       0
   CON_UID                       2133857098
   PROBLEM_KEY                   ORA 7445 [psstsat_set_attribute_info]
   FIRST_INCIDENT                35387
   FIRSTINC_TIME                 2020-04-01 16:59:08.355000 -03:00
   LAST_INCIDENT                 94620
   LASTINC_TIME                  2020-04-06 12:48:06.071000 -03:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      SID
   KEY_VALUE                     524.59409
   KEY_NAME                      ProcId
   KEY_VALUE                     66.4
   KEY_NAME                      PQ
   KEY_VALUE                     (16777225, 1585862080)
   KEY_NAME                      Service
   KEY_VALUE                     orclpdb
   KEY_NAME                      Module
   KEY_VALUE                     Data Pump Worker
   KEY_NAME                      Action
   KEY_VALUE                     SYS_IMPORT_FULL_08
   KEY_NAME                      PdbName
   KEY_VALUE                     ORCLPDB
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@DSORA02.BUE299.COMAFI.COM.AR.16513_139659530539968
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/ORCL/incident/incdir_45854/ORCL_dw00_16513_i45854.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_dw00_16513.trc
1 row fetched