Some useful ADRCI commands

The ADR (Automatic Diagnostic Repository) and its command-line tool adrci was introduced in Oracle11g and is still available.

I still find it incredibly useful when troubleshooting problems, particulary ORA-600 errors which needs to be sent to Oracle Support.

An ADR may contain file from many homes, or components. You need to set the correct home before you start working with ADR:

show homes
set homepath 

exampel:

adrci> show homes
ADR Homes:
diag/tnslsnr/zus15d-0110/listener_pzhhtet2
diag/tnslsnr/zus15d-0110/listener_pzhhtet1
diag/rdbms/pzhhtet1/PZHHTET1
diag/rdbms/pzhhtet2/PZHHTET2

adrci> set homepath diag/rdbms/pzhhtet1/PZHHTET1
adrci> show home
ADR Homes:
diag/rdbms/pzhhtet1/PZHHTET1

help show tracefile
show tracefile 

order by timestamp:

show tracefile -t 

order by reversed timestamp:

show tracefile -rt

filter on tracefile name:

show tracefile %m00% -rt

alert log operations

show alert
show alert -tail -f
show alert -tail 50

show alert -p "module_id='DBMS_SCHEDULER'"
show alert -p "module_id != 'DBMS_SCHEDULER'"
show alert -p "module_id LIKE '%SCHEDULER%'"

If you want to extract the Streams related statements from the alert log, you would use:

show alert -p "message_text like '%STREAM%'"

Show all messages related to a group. For example, a startup:

show alert -p "MESSAGE_GROUP LIKE 'startup'"

spool 
spool off

show incident
show incident -mode basic 
show incident -mode detail
show incident -mode detail -p "incident_id=incident_ID;"
show incident -last 5

create a package from an incident:

adrci> ips create package incident 1697
Created package 1 based on incident id 1697, correlation level typical

view its contents:

adrci> ips show files package 1
   FILE_ID                1
   FILE_LOCATION          /incident/incdir_1697
   FILE_NAME              +ASM1_lmhb_15352_i1697.trc
   LAST_SEQUENCE          0
   EXCLUDE                Included

   FILE_ID                2
   FILE_LOCATION          /incident/incdir_1697
   FILE_NAME              +ASM1_lmhb_15352_i1697.trm
   LAST_SEQUENCE          0
   EXCLUDE                Included

   FILE_ID                3
   FILE_LOCATION          /trace
   FILE_NAME              +ASM1_lmhb_15352.trc
   LAST_SEQUENCE          0
   EXCLUDE                Included

   FILE_ID                4
   FILE_LOCATION          /trace
   FILE_NAME              +ASM1_lmhb_15352.trm
   LAST_SEQUENCE          0
   EXCLUDE                Included

generate zip-files on disk:

adrci> ips generate package 1 in /cs/oracle/grid/packages
Generated package 1 in file /cs/oracle/grid/packages/ORA29770_20100427133326_COM_1.zip, mode complete

To remove old incidents, use

purge -age 2 -type incident

where -age 2 means older than two days.

Automatic purging:

adrci> purge -age 2880 -type trace

--> will automatically remove all trace files older than 2880 minutes (2 days) type "help purge" on the adrci prompt for more options.

Other examples:

purge -age 129600 -type alert
purge -age 129600 -type incident
purge -age 129600 -type cdump
purge -age 129600 -type stage
purge -age 129600 -type sweep
purge -age 129600 -type hm

129600 = 90 days 43200 = 30 days or

purge -age 2880 -type trace
purge -age 2880 -type alert
purge -age 2880 -type cdump
purge -age 2880 -type stage
purge -age 2880 -type sweep
purge -age 2880 -type hm

you can create a batch file and run all ov the above in one bulk:

adrci script=adrci_settings.cmd

You can spool from within an adrci script:

spool myfile.log

There are two policies in place; the LONGP_POLICY and the SHORTP_POLICY. Events such as incidents will fall into the LONGP policy and trace files and dumps will fall into the SHORTP policy. You can configure the policies using the commands below, which set the short and long retention periods to 3 months and 6 months, respectively. Note that set control accepts the time in hours

show control
set control (SHORTP_POLICY = 2160)
set control (LONGP_POLICY = 4320)

Run in batch mode using the "exec" instruction"

adrci exec="command1; command2 ... command n"

How to use adrci to show all messages related to shutdown and startup of a database

These commands will give you all messages related to a shutdown and a startup of an oracle database, respectively:

adrci> show alert -p "MESSAGE_GROUP LIKE 'shutdown'"
adrci> show alert -p "MESSAGE_GROUP LIKE 'startup'"

Example output for a shutdown:

Instance shutdown complete (OS id: 4058731)
2022-03-10 18:00:18.042000 +01:00
License high water mark = 863
2022-03-10 18:00:19.115000 +01:00
Instance shutdown complete (OS id: 3611031)
2022-03-10 18:00:33.286000 +01:00
Shutting down instance: further logons disabled
2022-03-10 18:00:36.357000 +01:00
License high water mark = 1
Shutting down archive processes
Archiving is disabled
2022-03-10 18:00:37.519000 +01:00
Shutting down archive processes
Archiving is disabled
2022-03-10 18:00:43.753000 +01:00
Instance shutdown complete (OS id: 3611306)

For the startup sequence, adrci will show you all the details of the startup, in other words what you normally see if you put a tail on the alert log. This is a rather lengthy output, so I am shorting it down. It should be familiar to most DBAs:

2022-03-10 18:00:22.413000 +01:00
Oracle instance running with ODM in PGA: Oracle Direct NFS ODM Library Version 6.0
2022-03-10 18:00:25.600000 +01:00
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is enabled
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0.
ORACLE_HOME:    /sw/oracle/product/19c
System name:    Linux
Node name:      myserver.mydomain.com
Release:        4.18.0-348.12.2.el8_5.x86_64
Version:        #1 SMP Mon Jan 17 07:06:06 EST 2022
Machine:        x86_64
Using parameter settings in server-side spfile /sw/oracle/product/19c/dbs/spfiletestdb01.ora
System parameters with non-default values:
etc etc

Since the output is very long, if you simply want to check for an indication of a database startup you could use this command instead:

adrci> show alert -p "message_text like '%Starting ORACLE instance%'"

which will give you a shorter list to examine, and in my case showing that the database was restarted Jan 28th and March 10th:

2022-02-28 07:46:54.505000 +01:00
Starting ORACLE instance (restrict) (OS id: 4058595)
2022-02-28 07:47:39.567000 +01:00
Starting ORACLE instance (normal) (OS id: 3300)
2022-03-10 18:00:22.421000 +01:00
Starting ORACLE instance (restrict) (OS id: 3611160)
2022-03-10 18:02:06.831000 +01:00
Starting ORACLE instance (normal) (OS id: 3429)

How to use the purge command in adrci

In Version 11gR1 Oracle introduced the Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:

adrci> purge -age 48 -type trace

Other examples (2160 hrs = 90 days):

purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :

adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):

set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched

Remove incidents that are older than 90 days:

adrci> purge -age 2160 -type incident

Another check of the incident list shows that the number has been reduced:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

The emcli utility

If you're working in a large Enterprise and are using Cloud Control, you will have access to a neat utility called emcli.

emcli is short for "Enterprise Manager Command Line Interface" and is documented here.

In short, emcli can be used to perform scripted jobs on hosts, or on databases running on hosts, that are registered in your Cloud Control 12c.

The jobs you create and execute will show up in Cloud Control under "Enterprise"-->"Jobs"-->"Job Activity"

In this particular case, I wanted to use the

adrci

utility to set new retention policies.
Instead of logging into each and every oracle 11g databaser server, I used emcli to loop through the different groups that our Cloud Control Administrator had created.

To change target from a specific host to a group, exchange the -target to a valid group name, followed by the keyword "group". For example

-targets="prod_db_servers:group"

First, make sure you're logged into emcli:

emcli login -username=sysman -password=mypassword
Login successful

You can query the targets usable by emcli by using "get_targets" like this:

oracle@myomsserver:[OMS]# emcli get_targets |grep -i myserver1

If you have a large enterprise with many targets, you may need to add "-limit_rows" to the "get_targets" command in order to accommodate a larger list, since "get_targets" will automatically limit the rows extracted to 2000:

oracle@myomsserver: [OMS]# emcli get_targets -limit_rows=5000 |grep -i myserver2

The following command will execute a host command of type "file", and the actual file is pointed out by the -input_file flag:

emcli execute_hostcmd -cmd="/bin/sh -s" -osscript="FILE" -input_file="FILE:/u01/app/scripts/change_adrci_policy.sh" -credential_set_name="HostCredsNormal" -targets="myhost.mydomain.no:host"

The file "change_adrci_policy.sh":

# Oric Consulting AS
# Vegard Kasa
# Version 2.0
#!/usr/bin/ksh
# Special coding required for AIX hosts, as there are many different flavours of setting the environment...
#

echo "Hostname: " `hostname`
echo "Running as user: " `whoami`
export NUMDB=`cat /etc/oratab | egrep -v "\#|\*" | sed  '/^$/d' | wc -l | tr -d ' '`
export OS=`uname -s`

 
echo "There are $NUMDB database(s) on this server."
# Loop through the databases extracted from /etc/oratab
for x in $(cat /etc/oratab | grep -v "#" | grep -v "*" | awk -F":" '{print $1}' | sed  '/^$/d'); do
  case $OS in
     'Linux') echo "This is Linux, sourcing .bash_profile...";
              . ~/.bash_profile;
               # set the ORACLE_SID, and make sure LIBPATH is set
              export ORACLE_SID=$x;
              export LIBPATH=$ORACLE_HOME/lib;
              # PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
              # and Oracle Agent home binaries.
              export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/local/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/drift:/home/oracle/bin;
              echo "adrci exec=\"spool /home/oracle/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
              chmod 755 /home/oracle/find_adr_home.sh;
              /home/oracle/find_adr_home.sh;
              export ADR_HOME=`cat /home/oracle/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
        'AIX') echo "This is AIX...";
               echo "Now checking for oracle11g installations..."
               USEDSHELL=`cat /etc/passwd | grep ora11g | cut -f7 -d":"`
               # The user ora11g is not found
               if [ -z "${USEDSHELL}" ]; then
                echo "Oracle user \"ora11g\" does not exist on server `hostname`"
                break;
               # The user ora11g is indeed found in /etc/passwd
               else
                if [ "${USEDSHELL}" = "/usr/bin/bash" ]; then
                  # We found that ora11g is using bash shell. Source .bash_profile
                  echo "Sourcing .bash_profile in this environment..."
                  . ~/.bash_profile
                 else
                   # We found that ora11g is using a shell <> from bash. Source .profile
                   echo "Sourcing .profile in this environment..."
                   . ~/.profile;
                   IAM=`whoami`
                   # We have sourced .profile and the user turns out to be ora10g
                   if [ "${IAM}" = "ora10g" ]; then
                     echo "Oracle installation under user \"ora10g\" found."
                     echo "Only Oracle11g is applicable for adrci purging. Exiting."
                     break;
                   fi
                fi
               fi
               # set the ORACLE_SID, and make sure LIBPATH is set
               export ORACLE_SID=$x;
               export LIBPATH=$ORACLE_HOME/lib
               # PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
               # and Oracle Agent home binaries.
               export PATH=$ORACLE_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/ora11g/bin:/usr/bin/X11:/sbin:/usr/java131/bin:/usr/vac/bin:/usr/local/bin;
               # create an executable file on the target server
               echo "adrci exec=\"spool /home/ora11g/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
               chmod 755 /home/ora11g/find_adr_home.sh;
               # execute the file
               /home/ora11g/find_adr_home.sh;
               # grep for the ADR home in the log file produced by the execution above. Look for rdbms home
               # Then set the ADR_HOME to the path reflecting the rdbms instance
               export ADR_HOME=`cat /home/ora11g/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
  esac
  # Show the current settings (for logging purposes)
  echo "ORACLE_SID is " $ORACLE_SID
  echo "ORACLE_HOME is: " $ORACLE_HOME
  echo "PATH: " $PATH
  echo "LIBPATH: " $LIBPATH
  echo "adrci located in " `which adrci`
  echo "ADR HOME selected: " $ADR_HOME
  # finally, execute the set control statements in adrci against the rdbms home
  adrci exec="set home $ADR_HOME;show control;set control \(shortp_policy = 336\);set control\(longp_policy=2160\);show control;quit"
done
exit 0

Great feature in adrci for searching in the alert log

A potentially very timesaving feature in Oracle's adrci is the ability to search in the alert log for specific text, as I had to do to find when a specific parameter was set:

adrci

 ADRCI: Release 11.2.0.4.0 - Production on On Jun 11 12:03:34 2014

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

 ADR base = "/u01/oracle/admin/proddb01/diagnostic"
 adrci> show homes
 ADR Homes:
 diag/rdbms/proddb01/proddb01
 adrci> show alert -p "message_text like '%event%'"

 ADR Home = /u01/oracle/admin/proddb01/diagnostic/diag/rdbms/proddb01/proddb01:
 *************************************************************************
 Output the results to file: /tmp/alert_3343326_1_proddb01_1.ado
 "/tmp/alert_3343326_1_proddb01_1.ado" 44 lines, 2408 characters
 2013-08-25 14:35:46.344000 +02:00
 One of the following events caused this:
 2014-01-02 10:56:34.311000 +01:00
 OS Pid: 8455160 executed alter system set events '10852 trace name context forever, level 16384'
 2014-01-02 10:56:47.555000 +01:00
 ALTER SYSTEM SET event='10852 trace name context forever, level 16384' SCOPE=SPFILE;
 2014-01-10 00:43:02.945000 +01:00
   event                    = "10852 trace name context forever, level 16384"
 2014-01-31 19:32:59.471000 +01:00
   event                    = "10852 trace name context forever, level 16384"
 2014-02-01 09:12:59.653000 +01:00
   event                    = "10852 trace name context forever, level 16384"
 CLOSE: Active sessions prevent database close operation
 2014-02-01 18:10:54.100000 +01:00
   event                    = "10852 trace name context forever, level 16384"
 2014-06-10 19:38:42.536000 +02:00
 ALTER SYSTEM SET event='10852 trace name context forever, level 16384 off' SCOPE=SPFILE;
 2014-06-10 19:43:12.770000 +02:00
   event                    = "10852 trace name context off"

Without much effort I was able to find that the parameter was set 02.01.2014, and switched off 10.06.2014.

How to use adrci to tail the alert log

oracle@myhost:[PRODDB01]# adrci

ADRCI: Release 11.2.0.4.0 - Production on Wed May 28 15:06:44 2014

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

ADR base = "/u01/oracle"

Display which homes that exists, and set the homelocation to point to the database's home:

adrci> show homes

ADR Homes:
diag/rdbms/PRODDB01/PRODDB01
diag/tnslsnr/myhost/listener
adrci> set home diag/rdbms/PRODDB01/PRODDB01

Tail the log:

adrci> show alert -tail -f

Other handy comments related to the alert log:

show alert
show alert -tail 50
show alert -p "module_id='DBMS_SCHEDULER'"
show alert -p "module_id != 'DBMS_SCHEDULER'"
show alert -p "module_id LIKE '%SCHEDULER%'"

If you want to extract the Streams related statements from the alert log, you would use:

show alert -p "message_text like '%STREAM%'"

See also this post for searching in adr using adrci