[—ATOC—]
[—TAG:h2—]
Introduction
Automatic Diagnostic Repository Command Interpreter (ADRCI) utility has been introduced in Oracle 11g to manage Oracle Trace files and diagnostic data. You can read previous article on ADR architecture here
You need to execute show homes to see current homes supported by adrci
adrci> show homes ADR Homes: diag/rdbms/snowy/snowy2 diag/rdbms/tintin/tintin1
You can verify the ORACLE_BASE by issuing show base
adrci> show base ADR base is "/home/oracle"
In case you try to set incorrect base , it errors out
adrci> set base /home/oragrid DIA-48447: The input path [/home/oragrid] does not contain any ADR homes
Most of adrci command work on single home, so need to set home first
adrci> set home diag/rdbms/tintin/tintin1
List tracefile with show tracefile
adrci> show tracefile -rt
In case you wish to see particular file
adrci> show tracefile %lgwr% -rt 03-JAN-13 14:28:29 diag/rdbms/tintin/tintin1/trace/tintin1_lgwr_18841.trc
Database Alert Log
To view alert log, you need to set correct home and issue show alert
adrci>set home diag/rdbms/tintin/tintin1 adrci>show alert
You can also use tail command to view last num lines or -f
show alert [-tail [num] [-f]]
Some examples
Last 100 lines
show alert -tail 100
Last 100 lines and it will wait and report any new lines (implements Linux tail -f )
show alert -tail 100 -f
Since this is XML based alert log, you can search for ORA- errors or time based alerts.e.g I am checking entries reported in last 1 hr
adrci> show alert -p "ORIGINATING_TIMESTAMP >systimestamp -1/24" -term
term option is used as this will display it on screen else this opens the output in editor
You can combine searches
adrci> show alert -p "ORIGINATING_TIMESTAMP between systimestamp -2/24 and systimestamp -1/24"
Similarly you can look for database alerts
show alert -p "MESSAGE_TEXT like '%ORA-%'"
We can also say that report ORA errors reported in last 7 days
adrci>show alert -p "MESSAGE_TEXT like '%ORA-%' and ORIGINATING_TIMESTAMP >systimestamp -7" -term
Incident and Problems
Problem – A problem is a critical error in the database
Incident – An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence.Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps.These settings are automatically defined and cannot be modified.
Problem Key – Every problem has a problem key, which is a text string that includes an error code (such as ORA 600) and in some cases, one or more error parameters. Two incidents are considered to have the same root cause if their problem keys match.Below example problem key is ORA 445
You can get information about current problems with show problem command
adrci> show problem ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 1 ORA 445 28012 2012-09-24 08:24:27.920000 +00:00 1 rows fetched
List incident detail
adrci> show incident -mode detail -p "incident_id=24129" ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* ********************************************************** INCIDENT INFO RECORD 1 ********************************************************** INCIDENT_ID 24129 STATUS ready CREATE_TIME 2012-09-24 03:09:56.962000 +00:00 PROBLEM_ID 1 CLOSE_TIME FLOOD_CONTROLLED none ERROR_FACILITY ORA ERROR_NUMBER 445 ERROR_ARG1 GCR0 ERROR_ARG2 120 ERROR_ARG3 ERROR_ARG4 ERROR_ARG5 ERROR_ARG6 ERROR_ARG7 ERROR_ARG8 ERROR_ARG9 ERROR_ARG10 ERROR_ARG11 ERROR_ARG12 SIGNALLING_COMPONENT background_proc SIGNALLING_SUBCOMPONENT SUSPECT_COMPONENT SUSPECT_SUBCOMPONENT ECID IMPACTS 0 PROBLEM_KEY ORA 445 FIRST_INCIDENT 24129 FIRSTINC_TIME 2012-09-24 03:09:56.962000 +00:00 LAST_INCIDENT 28012 LASTINC_TIME 2012-09-24 08:24:27.920000 +00:00 IMPACT1 0 IMPACT2 0 IMPACT3 0 IMPACT4 0 KEY_NAME ProcId KEY_VALUE 16.1 KEY_NAME Client ProcId KEY_VALUE [email protected]_47223367536176 KEY_NAME SID KEY_VALUE 4.1 OWNER_ID 1 INCIDENT_FILE /home/oracle/diag/rdbms/tintin/tintin1/trace/tintin1_lmhb_18832.trc OWNER_ID 1 INCIDENT_FILE /home/oracle/diag/rdbms/tintin/tintin1/incident/incdir_24129/tintin1_lmhb_18832_i24129.trc 1 rows fetched
You can create package this incident to upload to oracle support
adrci> IPS CREATE PACKAGE INCIDENT 24129 Created package 1 based on incident id 24129, correlation level typical adrci> IPS GENERATE PACKAGE 1 in /home/oracle Generated package 1 in file /home/oracle/ORA445_20130103054329_COM_1.zip, mode complete
Listener Home
Starting Oracle 11g, architecture for trace file and log files have changed. Alert.log and listener.log files are now XML based.
To check listener log in 11g, following steps need to be done
$adrci ADR base = "/home/oragrid/gridbase" adrci> show homes ADR Homes: diag/tnslsnr/oradbdev01/listener diag/asm/+asm/+ASM1 adrci> set home diag/tnslsnr/oradbdev01/listener
Check location of listener log with below command
adrci> show tracefile
diag/tnslsnr/oradbdev01/listener/trace/listener.log
There are various options to check listener log. Try anyone of following
adrci> show trace listener.log adrci> view listener.log adrci>show alert adrci>show alert -tail
“show alert” can be used to checks last 200 lines for TNS-12523 errors
adrci> show alert -p "message_text like '%TNS-12523%'" -tail 200
ADR Maintenance
ADR metadata is purged automatically as per default policies. To check current policy for home, use show control command
adrci> show control ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1489850811 720 8760 2012-01-13 06:49:37.719445 +00:00 2013-01-03 23:17:09.351760 +00:00 1 2 80 1 2012-01-13 06:49:37.719445 +00:00 1 rows fetched
You can directly query the metadata table to easily read the information
adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME -------------------- -------------------- ---------------------------------------- ---------------------------------------- 720 8760 2013-01-03 23:17:09.351760 +00:00 1 rows fetched
SHORTP_POLICY Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).
LONGP_POLICY Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).
As per Oracle docs, information about incidents and problems is subject to purging after one year, whereas the associated dump files (dumps) are subject to purging after only 30 days.
So shortp_policy determines retention of dump files whereas longp_policy is for metadata information for incidents and problems
Set policy to 7 days (7*24=168 hours)
adrci> set control (SHORTP_POLICY=168) adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME -------------------- -------------------- ---------------------------------------- ---------------------------------------- 168 8760 2013-01-03 23:17:09.351760 +00:00 1 rows fetched
You can manually purge the ADR repository by using purge command. You can use help purge to know all options
Below command will purge all files under alert directory older then 14400 mins i.e 10 days
adrci> purge -age 14400 -type ALERT
Before purge
oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/alert>ls -ltr|head total 305976 -rw-r----- 1 oracle dba 10485833 Mar 10 2012 log_1.xml -rw-r----- 1 oracle dba 10486021 Mar 17 2012 log_2.xml -rw-r----- 1 oracle dba 10485975 Mar 25 2012 log_3.xml -rw-r----- 1 oracle dba 10485979 Apr 1 2012 log_4.xml -rw-r----- 1 oracle dba 10485945 Apr 9 2012 log_5.xml -rw-r----- 1 oracle dba 10485931 Apr 16 2012 log_6.xml -rw-r----- 1 oracle dba 10485988 Apr 24 2012 log_7.xml -rw-r----- 1 oracle dba 10485883 May 1 2012 log_8.xml -rw-r----- 1 oracle dba 10485990 May 8 2012 log_9.xml
Post purge
oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/alert>ls -ltr|head total 8436 -rw-r----- 1 oracle dba 8620402 Jan 4 06:28 log.xml
This updates LAST_MANUPRG_TIME with current manual purge time
adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME -------------------- -------------------- ---------------------------------------- ---------------------------------------- 168 8760 2013-01-03 23:17:09.351760 +00:00 2013-01-04 06:31:08.229272 +00:00
Note that purge command doesn’t take care of purging text based alert_SID.log and listener.log. This still needs to be done manually or cron.
Using Help Commands
You can use help command to know the various commands
adrci>help
You can check additional commands by using help extended
adrci>help extended
To know more detail about command, type help command e.g
adrci>help show incident
Shortcuts
Below shortcut can be used to show alert log for current ORACLE_SID. I use this as Mac shortcut key 🙂
adrci exec="show homes"| egrep $ORACLE_SID | sed 's/^/ exec='set home / ; s/$/; show alert -tail -f '/" |xargs adrci
Recent Comments