Automatic Diagnostic Repository Command Interpreter
In addition to the excellent (For RAC) TFA utility – the ADRCI has been around for some time…
You can use ADRCI assist in diagnosis of events and collecting the necessary data for analysis and debugging for Oracle support. It can be configured easily to work with multiple installed Oracle homes or against the currently set Oracle home path. This is important as the ADRCI can gather diagnostic data from multiple instance homes or focus on working against one singly set home or component.
To see the available homepaths, you can use the show command below and set the appropriate single homepath if necessary.
adrci> show homes ADR Homes: diag/rdbms/sandprd/SANDPRD diag/clients/user_oracle/host_822129311_82 diag/tnslsnr/oralinux7/listener_1521 diag/tnslsnr/oralinux7/listener diag/diagtool/user_oracle/adrci_822129311_82 adrci> set homepath diag/rdbms/sandprd/SANDPRD adrci> adrci> show homes ADR Homes: diag/rdbms/sandprd/SANDPRD
You can perform a range of functions from within ADRCI , such as useful things like creating reports, packaging up the details of incidents/problems encountered for Oracle support diagnosis, viewing alert logs and trace files along with some not so useful features
such as echoing text to stdout (For the purposes of using adrci scripts). I’m not entirely sure why this is an ADRCI thing but it exists nonetheless.
You invoke the ADRCI CLI utility as per once your environment variables have been set.
[oracle@oralinux7 ~]$ adrci ADRCI: Release 12.1.0.2.0 - Production on Fri Dec 30 19:52:07 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle" adrci>
Health Monitor Reports:
You can view all available Health Monitor run records via ADRCI if you enter the ‘show hm_run’ command.
********************************************************** HM RUN RECORD 163 ********************************************************** RUN_ID 8779 RUN_NAME HM_RUN_8779 CHECK_NAME DB Structure Integrity Check NAME_ID 2 MODE 2 START_TIME 2016-09-26 02:48:49.069581 +01:00 RESUME_TIME <NULL> END_TIME 2016-09-26 02:48:49.582055 +01:00 MODIFIED_TIME 2016-09-26 02:48:49.582055 +01:00 TIMEOUT 0 FLAGS 0 STATUS 5 SRC_INCIDENT_ID 0 NUM_INCIDENTS 0 ERR_NUMBER 0 REPORT_FILE <NULL>
From here, you can locate the run record of the Health Monitor execution you would like to investigate and generate a report.
adrci> create report hm_run HM_RUN_8779 adrci> adrci> show report hm_run HM_RUN_8779 <?xml version="1.0" encoding="US-ASCII"?> <HM-REPORT REPORT_ID="HM_RUN_8779"> <TITLE>HM Report: HM_RUN_8779</TITLE> <RUN_INFO> <CHECK_NAME>DB Structure Integrity Check</CHECK_NAME> <RUN_ID>8779</RUN_ID> <RUN_NAME>HM_RUN_8779</RUN_NAME> <RUN_MODE>REACTIVE</RUN_MODE> <RUN_STATUS>COMPLETED</RUN_STATUS> <RUN_ERROR_NUM>0</RUN_ERROR_NUM> <SOURCE_INCIDENT_ID>0</SOURCE_INCIDENT_ID> <NUM_INCIDENTS_CREATED>0</NUM_INCIDENTS_CREATED> <RUN_START_TIME>2016-09-26 02:48:49.069581 +01:00</RUN_START_TIME> <RUN_END_TIME>2016-09-26 02:48:49.582055 +01:00</RUN_END_TIME> </RUN_INFO> <RUN_PARAMETERS/> <RUN-FINDINGS/> </HM-REPORT> adrci>
Viewing the Alert log:
Viewing the alert log form ADRCI is a useful touch and I use it often. It can be invoked by using the syntax below and opens the alert log in your default editor.
*’set editor’ will allow you to change the default editor within adrci.
adrci> show alert
adrci> show alert -tail 2016-12-30 19:40:04.265000 +00:00 Errors in file /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_j001_72239.trc: ORA-12012: error on auto execute of job "APEX_050000"."ORACLE_APEX_MAIL_QUEUE" ORA-04063: package body "APEX_050000.WWV_FLOW_MAIL" has errors 2016-12-30 19:45:04.278000 +00:00
Viewing Trace files
For deeper diagnosis, you can refer to the trace files mentioned in an incident or problem or the alert log above and view this via ADRCI; for example…
adrci> show trace /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_j001_101550.trc /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_j001_101550.trc ---------------------------------------------------------- LEVEL PAYLOAD ----- ------------------------------------------------------------------------------------------------------------------------------------------------ Trace file /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_j001_101550.trc Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options ORACLE_HOME = /u01/app/oracle/product/12102 System name: Linux Node name: oralinux7 Release: 3.8.13-118.4.1.el7uek.x86_64 Version: #2 SMP Tue Mar 8 10:22:24 PST 2016 Machine: x86_64 Instance name: SANDPRD Redo thread mounted by this instance: 1 Oracle process number: 46 Unix process pid: 101550, image: oracle@oralinux7 (J001) <<<<< Output truncated >>>>
Packaging up problems/incidents for an SR:
IPS is the Incident packaging service. Its allows you to gather and collate the incident diagnostics related to a particular problem/incident and zip these up for ease of collection and submission to Oracle Support.
You can display the listing of historical incidents or problems using the appropriate show command syntax.
adrci> show problem ADR Home = /u01/app/oracle/diag/rdbms/sandprd/SANDPRD: *********************************************************************** PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 3 ORA 1578 110649 2016-07-09 03:20:21.758000 +01:00 2 ORA 4031 148883 2016-09-22 05:46:43.098000 +01:00 1 ORA 700 [kskvmstatact: excessive swapping observed] 165764 2016-12-29 22:04:40.419000 +00:00 3 rows fetched
A ‘show incident’ will display all present incident records which may be a long listing of events.
There are various methods for then grouping incident numbers or problem. A problem in this scenario may consist of multiple incidents occurring on a repeating basis but essentially specific to the same problem.
You can however drill down into incident IDs by using the show syntax below for example.
adrci> show incident -mode detail -p "incident_id=165764" ADR Home = /u01/app/oracle/diag/rdbms/sandprd/SANDPRD: ************************************************************************* ********************************************************** INCIDENT INFO RECORD 1 ********************************************************** INCIDENT_ID 165764 STATUS ready CREATE_TIME 2016-12-29 22:04:40.419000 +00:00 PROBLEM_ID 1 CLOSE_TIME <NULL> FLOOD_CONTROLLED none ERROR_FACILITY ORA ERROR_NUMBER 700 ERROR_ARG1 kskvmstatact: excessive swapping observed ERROR_ARG2 <NULL> ERROR_ARG3 <NULL> ERROR_ARG4 <NULL> ERROR_ARG5 <NULL> ERROR_ARG6 <NULL> ERROR_ARG7 <NULL> ERROR_ARG8 <NULL> ERROR_ARG9 <NULL> ERROR_ARG10 <NULL> ERROR_ARG11 <NULL> ERROR_ARG12 <NULL> SIGNALLING_COMPONENT VOS SIGNALLING_SUBCOMPONENT <NULL> SUSPECT_COMPONENT <NULL> SUSPECT_SUBCOMPONENT <NULL> ECID <NULL> IMPACTS 0 PROBLEM_KEY ORA 700 [kskvmstatact: excessive swapping observed] FIRST_INCIDENT 14473 FIRSTINC_TIME 2016-03-19 23:48:46.222000 +00:00 LAST_INCIDENT 165764 LASTINC_TIME 2016-12-29 22:04:40.419000 +00:00 IMPACT1 0 IMPACT2 0 IMPACT3 0 IMPACT4 0 KEY_NAME Client ProcId KEY_VALUE oracle@oralinux7.76118_140660454380160 KEY_NAME SID KEY_VALUE 9.23553 KEY_NAME Service KEY_VALUE SYS$BACKGROUND KEY_NAME ProcId KEY_VALUE 9.1 OWNER_ID 1 INCIDENT_FILE /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/trace/SANDPRD_dbrm_76118.trc OWNER_ID 1 INCIDENT_FILE /u01/app/oracle/diag/rdbms/sandprd/SANDPRD/incident/incdir_165764/SANDPRD_dbrm_76118_i165764.trc 1 rows fetched adrci>
You can generate a package or zip output for Oracle support by means of the IPS commands below pertaining to particular incidents or problems. Here is an example specific to the problem key#1 displayed in the output of the ‘show problem’ command.
adrci> ips create package problem 1 Created package 1 based on problem id 1, correlation level typical adrci>
This has not yet created your output zip file for transfer but has prepped the information for generation. When creating your zip output file, you specify the package identifier as noted from the output above and provide the destination output directory location also.
adrci> ips generate package 1 in "/tmp" Generated package 1 in file /tmp/ORA700ksk_20161230203045_COM_1.zip, mode complete adrci> adrci> host "ls -lrt /tmp/ORA700ksk_20161230203045_COM_1.zip" -rw-r--r--. 1 oracle oinstall 24020161 Dec 30 20:32 /tmp/ORA700ksk_20161230203045_COM_1.zip
And the above displays your packaged up “problem” zipfile for analysis by support.
Alternatively, you can create a packaged output by specifying an individual incident if that is preferred also.
adrci> ips create package incident 165764 Created package 2 based on incident id 165764, correlation level typical adrci> ips generate package 2 in "/tmp" Generated package 2 in file /tmp/ORA700ksk_20161230203545_COM_1.zip, mode complete adrci>
You can also view the information and contained files within a package directly from ADRCI if you wish:
adrci> ips show package 2 DETAILS FOR PACKAGE 2: PACKAGE_ID 2 PACKAGE_NAME ORA700ksk_20161230203545 PACKAGE_DESCRIPTION DRIVING_PROBLEM 1 DRIVING_PROBLEM_KEY ORA 700 [kskvmstatact: excessive swapping observed] DRIVING_INCIDENT 165764 DRIVING_INCIDENT_TIME 2016-12-29 22:04:40.419000 +00:00 STATUS Generated (4) CORRELATION_LEVEL Typical (2) PROBLEMS 1 main problems, 1 correlated problems INCIDENTS 1 main incidents, 16 correlated incidents INCLUDED_FILES 350 SEQUENCES Last 1, last full 1, last base 0 UNPACKED FALSE CREATE_TIME 2016-12-30 20:35:45.772045 +00:00 UPDATE_TIME 2016-12-30 20:35:47.037214 +00:00 BEGIN_TIME N/A END_TIME N/A FLAGS 0 adrci>
And to list all the files included in the package….
adrci> ips show files package 2 FILE_ID 1 FILE_LOCATION <ADR_HOME>/incident/incdir_14473 FILE_NAME SANDPRD_dbrm_5067_i14473.trc LAST_SEQUENCE 1 EXCLUDE Included FILE_ID 2 FILE_LOCATION <ADR_HOME>/incident/incdir_14473 FILE_NAME SANDPRD_dbrm_5067_i14473.trm LAST_SEQUENCE 1 EXCLUDE Included <<<< Output truncated >>>>
Note: Before taking this newly created package zip file and sending it on its merry way to Oracle for further troubleshooting, you should finalize the package which, per the documentation, correlates additional trace files, alert logs and so on and adds these into the package.
adrci> ips finalize package 2 Finalized package 2 adrci>
tooniedba - Oracle DB related stuff and more... 