Using DML with ADRCI

The Oracle Database has some useful debugging and diagnostics features. One such utility called the Automatic Diagnostics Repository Command Interpreter or ADRCI, enables database and system administrators to view and analyze information including trace and log files.

In addition to the usual ADR commands, you can also issue DML-like statements. These statements are limited to the diagnostics information found in the ADR. Let's take a look at how to use ADRCI to issue DML.

Please note: DML statement issued via ADRCI don't require a termination character such as the semicolon. Also, the statements have to be on a single line.

The following DML commands are defined in ADRCI version 12.1.0.2.0
SELECT
UPDATE*
DELETE*
INSERT*
UPDATE*
QUERY*

To get a list of the commands, use the help keyword in ADRCI.

You may notice that only the SELECT statement is shown by default. To see the list of additional commands, type help extended.

Note: Commands proceeded by an asterisk are restricted for Oracle internal use.

Let's look at the simplest case using SELECT. This works just as you would expect. But instead of directly querying a table or view, you query relations, which corresponds to views in the database. To quickly get a list of relations, list the files in the ADR_BASE/ADR_HOME/metadata directory.

Display the ADR_BASE using the show base command
adrci> show base
ADR base is "/u01/app/oracle"
Display the ADR_HOME using show home
adrci> show home
ADR Homes:
diag/rdbms/rac12db/rac12db1
diag/rdbms/marvel/marvel1
diag/rdbms/dwh/dwh1
diag/clients/user_oracle/host_1616732858_82
diag/clients/user_oracle/host_1616732858_80
The relations corresponding to the marvel1 home would be located in /u01app/oracle/diag/rdbms/marvel/marvel1/metadata

adrci> host

[oracle@rac12c01 ~]$ ls /u01/app/oracle/diag/rdbms/marvel/marvel1/metadata

ADR_CONTROL.ams DFW_CONFIG_CAPTURE.ams HM_MESSAGE.ams INC_METER_SUMMARY.ams IPS_REMOTE_PACKAGE.ams

ADR_INTERNAL.mif DFW_CONFIG_ITEM.ams HM_RECOMMENDATION.ams IPS_CONFIGURATION.ams PICKLEERR.ams

ADR_INVALIDATION.ams DFW_PATCH_CAPTURE.ams HM_RUN.ams IPS_FILE_COPY_LOG.ams PROBLEM.ams

AMS_XACTION.ams DFW_PATCH_ITEM.ams INCCKEY.ams IPS_FILE_METADATA.ams SWEEPERR.ams

DDE_USER_ACTION.ams EM_DIAG_JOB.ams INCIDENT.ams IPS_PACKAGE.ams VIEW.ams

DDE_USER_ACTION_DEF.ams EM_TARGET_INFO.ams INCIDENT_FILE.ams IPS_PACKAGE_FILE.ams VIEWCOL.ams

DDE_USER_ACTION_PARAMETER.ams EM_USER_ACTIVITY.ams INC_METER_CONFIG.ams IPS_PACKAGE_HISTORY.ams

DDE_USER_ACTION_PARAMETER_DEF.ams HM_FDG_SET.ams INC_METER_IMPT_DEF.ams IPS_PACKAGE_INCIDENT.ams

DDE_USER_INCIDENT_ACTION_MAP.ams HM_FINDING.ams INC_METER_INFO.ams IPS_PACKAGE_UNPACK_HISTORY.ams

DDE_USER_INCIDENT_TYPE.ams HM_INFO.ams INC_METER_PK_IMPTS.ams  IPS_PROGRESS_LOG.ams

Here you'll see all the relations that can be accessed via the ADRCI SELECT command. You can query the PROBLEM relation to see errors such as "ORA-600" that have been generated by the database.
Before running the commands, you should set your ADR HOME using set home if you have multiple homes defined.

adrci> set home marvel1
Next, describe the relation just as you would using SQL

adrci> DESCRIBE problem
Name Type NULL?
----------------------------- --------------- -----------
PROBLEM_ID number
PROBLEM_KEY text(550)
FIRST_INCIDENT number
FIRSTINC_TIME timestamp
LAST_INCIDENT number
LASTINC_TIME timestamp
IMPACT1 number
IMPACT2 number
IMPACT3 number
IMPACT4 number
SERVICE_REQUEST text(64)
BUG_NUMBER text(64)
So putting this together, we can find the problem_key for an ORA 603 error message as follows.

adrci> SELECT problem_id,problem_key,first_incident FROM problem WHERE problem_key like '%603'
ADR Home = /u01/app/oracle/diag/rdbms/marvel/marvel1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY FIRST_INCIDENT
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
2 ORA 603 24097
1 rows fetched

In looking at the command above, you may realize that you can receive the same output using the show problem command.

adrci> show problem -p "problem_key like '%603%'"

ADR Home = /u01/app/oracle/diag/rdbms/marvel/marvel1:

*************************************************************************

PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME

-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------

2                    ORA 603                                                     24206                2017-05-19 20:59:40.951000 -05:00

1 rows fetched

This is just one example of using DML-like features in ADRCI. Now, why would you want to do that? It might be easier to learn if you're new to ADRCI but only familiar and comfortable with SQL statements. No need to memorize new commands and syntax.