In oracle 19 the previously version ADRCI (Automatic Diagnostic Repository Command Interpreter) is a great tool to solve issues and deal with Oracle Errors and Trace files
We could verify in the alert the oracle error :
Here, we will review quickly and easily and introduction to know the most basic commands to start.
I will start, as common with an alert log file:
tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
For years, Oracle has provided us with the possibility of reviewing in the alert log, the most critical movements such as the behaviour of database engine processes.
Hands-on
Now, we start how to ADRCI we help us to understand 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.
Let’s go and start!
adrci> set home diag/rdbms/orcl/ORCL
They are saved in the ADR home path, remember that we could have many databases in the same server or Exadata partitioning.
| Path Component | Value for Oracle Database |
|---|---|
| product_type | rdbms |
| product_id | DB_UNIQUE_NAME |
| instance_id | SID |
It is possible to 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 help us to analyse the 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 INCIDENCES 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 is a 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
