Oracle ADRCI Command Interpreter

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

The next step is to see what SHOW INCIDENT displays. In the output below, a CREATE_TIME column is not shown due to editing/page size limitations.

adrci> set homepath diag\rdbms\db11\db11

adrci> show incident

ADR Home = c:\app\ora11g\diag\rdbms\db11\db11:
********************************************************

INCIDENT_ID PROBLEM_KEY
-------------------- -----------------------------------
8545 ORA 600 [kcidr_io_check_common_6]

1 rows fetched

Note that the database did not and does not have to be opened or have an instance running, for that matter, in order to use ADRCI.

Pretending that one is clueless about what caused this error, or realistically, had it been a true ORA-00600 or ORA-07445 error that often times mandate getting assistance from Oracle Support Services, it is now time to generate a package and finalize it before uploading into a Service Request on MOSC.

To recap the overall status of the process: the tool (ADRCI) has been identified, there is a problem (an ORA-00600 error), and there is an incident (only the critical error, not the user one, but an incident can have more than one error associated with it).

The problem has an identifier or problem key, which generally speaking is the error number and the error message. One needs to start a session and choose the relevant ADR Home. So look at some other useful commands along the way to creating the package.

Use the "show home" command to see which ADR homes are available, followed by the "set homepath" as necessary. Note that more than one home can be involved in an incident. Only the rdbms home need apply in this example for viewing a specific file.

adrci> set homepath diag\rdbms\db11\db11
adrci> show home
ADR Homes:
diag\rdbms\db11\db11

With a specific home set, use ADRCI to view the tail of the XML-based alert log file. The output will have the XML stripped off. The syntax is:

show alert –tail [options]

The options are quite similar to UNIX. Using "tail –f" in an ADRCI session, one is able to view new entries into the log file as they occur. As an experiment, try using "show alert –tail –f" in an ADRCI session, and then in another session, switch the logfile via "alter system switch logfile" and the "Thread 1 advanced…" output appears in the ADRCI's session window.

Despite what Oracle's documentation says, using Control-C to end the live monitoring will not only end the tailing of the file, it will also terminate the ADRCI session.

Even more intuitive regarding available commands is spooling a session to a file. SPOOL ON <path/filename> and SPOOL OFF do the trick.

If it is known that there is a tracefile, or if one simply wants to view all tracefiles, the intuitive command of "show tracefile" will support that need. Yet as is well known, not all trace files are germane with respect to an incident.

Since there is an incident number, are there any trace files associated with it?

adrci> show tracefile -i 8545
diag\rdbms\db11\db11\incident\incdir_8545\db11_m000_4844_i8545.trc

Yes, as luck has it, there is a trace file. Remember, that is luck only in the sense that one was needed for this example, but lucky in real life, no. What are the details about this incident?

ADRCI will list incident reports in a brief or detail mode, and also by a specific incident number in case there is more than one in the repository. Looking at the detailed output for this incident, the following comes up.

adrci> show incident -mode detail

ADR Home = c:\app\ora11g\diag\rdbms\db11\db11:

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

INCIDENT INFO RECORD 1

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

    INCIDENT_ID                   8545
   STATUS                        ready
   CREATE_TIME                   2008-09-20 18:40:36.762000 -06:00
   PROBLEM_ID                    1
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  600
   ERROR_ARG1                    kcidr_io_check_common_6
   ERROR_ARG2                    4
   ERROR_ARG3                    C:\APP\ORA11G\ORADATA\DB11\USERS01.DBF
   ERROR_ARG4                    8192
   ERROR_ARG5                    2
   ERROR_ARG6                    4
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   SIGNALLING_COMPONENT          <NULL>
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 600 [kcidr_io_check_common_6]
   FIRST_INCIDENT                8545
   FIRSTINC_TIME                 2015-09-20 18:40:36.762000 -06:00
   LAST_INCIDENT                 8545
   LASTINC_TIME                  2015-09-20 18:40:36.762000 -06:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     18.2
   KEY_NAME                      Client ProcId
   KEY_VALUE                     ORACLE.EXE.1116_4844
   KEY_NAME                      SID
   KEY_VALUE                     154.5
   OWNER_ID                      1
   INCIDENT_FILE      c:\app\ora11g\diag\rdbms\db11\db11\trace\db11_m000_4844.trc
   OWNER_ID                      1
   INCIDENT_FILE                 c:\app\ora11g\diag\rdbms\db11\db11\incident\incdir_8545\db11_m000_4844_i8545.trc

1 rows fetched

NOTE: Rampant author Laurent Schneider has some additional insight into creating an Oracle Automatic Diagnostic Repository (ADR).

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).

Buy direct from the publisher and save 30%!

��