adrci: A survival guide for the DBA

Starting with 11gR1, we have a new way to deal with Oracle Errors & Tracefiles: There is now a special command line utility dedicated for that purpose called adrci (Automatic Diagnostic Repository Command Interpreter). This posting is intended to show you the (in my view) essential commands, a DBA ought to know in order to use it. We will look at

  1. Viewing the alert.log
  2. The relation between incident & problem
  3. Creation of Packages & ZIP files to send to Oracle Support
  4. Managing, especially purging tracefiles

I will at first create a problem. Don’t do that with your Production Database! Especially: Never do DML on dictionary tables!

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:25:06 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter diagnostic

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest              string     /u01/app/oracle

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table t (n number);

Table created.

SQL> select object_id from user_objects;

 OBJECT_ID
----------
     75719

SQL> connect / as sysdba
Connected.
SQL> update tab$ set cols=2 where obj#=75719;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> connect adam/adam
Connected.
SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2236
Session ID: 29 Serial number: 9

I flushed the Shared Pool to get the Data Dictionary Cache empty. Else the select may not crash the session as it did. Imagine the user calls me now on the phone. Our first idea as an experienced DBA: We look at the alert.log! Right so. Please notice that we now have two different kinds of the alert.log.

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

1. Viewing the alert.log

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert This version of the alert.log is accessed by adrci:

[oracle@uhesse ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 10:20:08 2011

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

ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/tnslsnr/uhesse/listener
diag/rdbms/orcl/orcl

Please notice that we have different ADR Homes. In my case only two, because I am not using Grid Infrastructure on this Demo System, else there would be another one. I specify my Database Home first. Then I look at the alert.log. Good news if you are on Windows: Finally, you can tail -f your alert.log also :-)

adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail -f
2011-06-01 10:16:35.337000 +02:00
db_recovery_file_dest_size of 4032 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=21, OS id=2204
2011-06-01 10:18:42.668000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc  (incident=6153):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:18:47.518000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601101847], requested by (instance=1, osid=2236), summary=[incident=6153].
2011-06-01 10:18:48.727000 +02:00
Sweep [inc][6153]: completed
Sweep [inc2][6153]: completed

2. The relation between Incident & Problem

You see the incident was recorded in the alert.log. And it tells you “Use ADRCI or Support Workbench to package the incident.” We will soon see how to do that. First I’d like to explain the relation between incident and problem: An incident is the concrete occurrence of a problem. In other words: The same problem may have multiple incidents. To show that, I will open another terminal and do again a select against the table t, while still tailing the alert log from the first session.

Second terminal:

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:21:52 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t where n=42;
select * from t where n=42
                         *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2299
Session ID: 36 Serial number: 11

First terminal:

2011-06-01 10:21:31.367000 +02:00
Starting background process SMCO
SMCO started with pid=19, OS id=2268
2011-06-01 10:22:08.781000 +02:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc  (incident=6201):
ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2011-06-01 10:22:11.135000 +02:00
Dumping diagnostic data in directory=[cdmp_20110601102211], requested by (instance=1, osid=2299), summary=[incident=6201].
2011-06-01 10:22:13.370000 +02:00
Sweep [inc][6201]: completed
Sweep [inc2][6201]: completed

I have seen the second incident recorded. I exit out of the tail -f with CTRL+C and continue:

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 7445 [qcstda()+702]                                     6201                 2011-06-01 10:22:08.924000 +02:00       
1 rows fetched

So I have one problem with the ID 1 and the last incident occurred at 10:22. Are there more?

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              
-------------------- ----------------------------------------------------------- ----------------------------------------
6153                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:18:42.995000 +02:00       
6201                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:22:08.924000 +02:00       
2 rows fetched

I want to see some more detail about the incidents:

adrci> show incident -mode detail -p "incident_id=6201"

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   6201
   STATUS                        ready
   CREATE_TIME                   2011-06-01 10:22:08.924000 +02:00
   PROBLEM_ID                    1
   CLOSE_TIME                   
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    qcstda()+702
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x0
   ERROR_ARG4                    PC:0x90D891A
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                   
   ERROR_ARG7                   
   ERROR_ARG8                   
   ERROR_ARG9                   
   ERROR_ARG10                  
   ERROR_ARG11                  
   ERROR_ARG12                  
   SIGNALLING_COMPONENT          SQL_Parser
   SIGNALLING_SUBCOMPONENT      
   SUSPECT_COMPONENT            
   SUSPECT_SUBCOMPONENT         
   ECID                         
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [qcstda()+702]
   FIRST_INCIDENT                6153
   FIRSTINC_TIME                 2011-06-01 10:18:42.995000 +02:00
   LAST_INCIDENT                 6201
   LASTINC_TIME                  2011-06-01 10:22:08.924000 +02:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     25.3
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@uhesse (TNS V1-V3).2299_140262306875136
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1306916528)
   KEY_NAME                      SID
   KEY_VALUE                     36.11
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
1 rows fetched

I want to look at the incident tracefile mentioned above:

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 ----------------------------------------------------------
 LEVEL PAYLOAD
 ----- ------------------------------------------------------------------------------------------------------------------------------------------------
 Dump file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
 System name:      Linux
 Node name:        uhesse
 Release:  2.6.32-100.28.5.el6.x86_64
 Version:  #1 SMP Wed Feb 2 18:40:23 EST 2011
 Machine:  x86_64
 Instance name: orcl
 Redo thread mounted by this instance: 1
 Oracle process number: 25
 Unix process pid: 2299, image: oracle@uhesse (TNS V1-V3)
*** 2011-06-01 10:22:08.929
 *** SESSION ID:(36.11) 2011-06-01 10:22:08.929
 *** CLIENT ID:() 2011-06-01 10:22:08.929
 *** SERVICE NAME:(SYS$USERS) 2011-06-01 10:22:08.929
 *** MODULE NAME:(SQL*Plus) 2011-06-01 10:22:08.929
 *** ACTION NAME:() 2011-06-01 10:22:08.929
Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
 1>     ***** Error Stack *****
 ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []
 1<     ***** Error Stack *****
 1>     ***** Dump for incident 6201 (ORA 7445 [qcstda()+702]) *****
 2>      ***** Beginning of Customized Incident Dump(s) *****
 2>      ***** Beginning of Customized Incident Dump(s) *****
 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]
 Registers:
 %rax: 0x0000000000000000 %rbx: 0x00007f915c77f0e0 %rcx: 0x0000000000000007
 %rdx: 0x0000000000000000 %rdi: 0x00007f915c77be98 %rsi: 0x0000000000000000
 %rsp: 0x00007fffc65178e0 %rbp: 0x00007fffc6517960  %r8: 0x0000000000000028
 %r9: 0x0000000000002000 %r10: 0x00000000093849c0 %r11: 0x0000000000000168
 %r12: 0x00007f915c77ade8 %r13: 0x000000008edbb178 %r14: 0x00007f915c777da0
 %r15: 0x00007f915c77ae28 %rip: 0x00000000090d891a %efl: 0x0000000000010246
 qcstda()+686 (0x90d890a) mov -0x40(%rbp),%rdi
 qcstda()+690 (0x90d890e) mov %rdx,0x18(%rbx)
 qcstda()+694 (0x90d8912) mov 0x60(%r15),%rsi
 qcstda()+698 (0x90d8916) mov %ecx,0x8(%r15)
 > qcstda()+702 (0x90d891a) mov %ecx,(%rsi)
 qcstda()+704 (0x90d891c) mov 0x78(%rdi),%rdx
 qcstda()+708 (0x90d8920) test %rdx,%rdx
 qcstda()+711 (0x90d8923) jnz 0x90d8d03
 qcstda()+717 (0x90d8929) mov -0x70(%rbp),%rdi
*** 2011-06-01 10:22:08.963
 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
 3>       ***** Current SQL Statement for this session (sql_id=8r222qucmawdt) *****
          select * from t where n=42
 3<       ***** current_sql_statement ***** 3 

3. Creation of Packages & ZIP files to send to Oracle Support

I may not be able to solve the problem myself. Oracle Support will help me with that one. I gather all the required information with a method called “Incident Packaging Service” (IPS):

adrci> ips create package problem 1 correlate all
 Created package 2 based on problem id 1, correlation level all

This did not yet create a ZIP file and is therefore referred to as “Logical Package”. The ZIP file is generated from the Logical Package that was created:

adrci> ips generate package 2 in "/home/oracle"  
Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, mode complete

4. Managing, especially purging of tracefiles

Now to the management of tracefiles. You may notice that 11g creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:

adrci> show tracefile -rt
 01-JUN-11 10:31:48  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
 01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
 01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log
 01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
 01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
 01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
 01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
 01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
 01-JUN-11 09:17:19  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
 01-JUN-11 09:16:44  diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
 01-JUN-11 09:16:30  diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
 01-JUN-11 09:16:19  diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
 01-JUN-11 09:16:16  diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
 01-JUN-11 09:16:14  diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
 30-MAY-11 14:07:02  diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
 30-MAY-11 11:15:30  diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
 30-MAY-11 11:00:01  diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
 30-MAY-11 10:56:58  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
 30-MAY-11 10:56:20  diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
 30-MAY-11 10:56:06  diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
 30-MAY-11 10:55:58  diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
 30-MAY-11 10:55:55  diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
 30-MAY-11 10:55:53  diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc
 27-MAY-11 10:53:25  diag/rdbms/orcl/orcl/trace/orcl_mmon_8589.trc
 27-MAY-11 10:17:05  diag/rdbms/orcl/orcl/trace/orcl_ora_11390.trc
 27-MAY-11 09:26:41  diag/rdbms/orcl/orcl/trace/orcl_ora_10739.trc
 27-MAY-11 09:23:53  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8573.trc
 27-MAY-11 09:22:58  diag/rdbms/orcl/orcl/trace/orcl_ora_8687.trc
 27-MAY-11 09:22:54  diag/rdbms/orcl/orcl/trace/orcl_mman_8577.trc
 27-MAY-11 09:22:50  diag/rdbms/orcl/orcl/trace/orcl_vktm_8565.trc
 27-MAY-11 09:22:48  diag/rdbms/orcl/orcl/trace/orcl_ora_8516.trc
 27-MAY-11 09:22:44  diag/rdbms/orcl/orcl/trace/orcl_ora_8515.trc
 27-MAY-11 09:22:44  diag/rdbms/orcl/orcl/trace/orcl_vktm_8347.trc
 27-MAY-11 09:21:24  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8355.trc
 27-MAY-11 09:20:29  diag/rdbms/orcl/orcl/trace/orcl_ora_8470.trc
 27-MAY-11 09:20:28  diag/rdbms/orcl/orcl/trace/orcl_mmon_8371.trc
 27-MAY-11 09:20:28  diag/rdbms/orcl/orcl/trace/orcl_ora_8381.trc
 27-MAY-11 09:20:26  diag/rdbms/orcl/orcl/trace/orcl_mman_8359.trc
 27-MAY-11 09:20:20  diag/rdbms/orcl/orcl/trace/orcl_ora_8299.trc
 27-MAY-11 09:20:15  diag/rdbms/orcl/orcl/trace/orcl_ora_8297.trc
 27-MAY-11 09:20:15  diag/rdbms/orcl/orcl/trace/orcl_vktm_8096.trc
 27-MAY-11 09:20:07  diag/rdbms/orcl/orcl/trace/orcl_ora_8296.trc
 27-MAY-11 09:19:42  diag/rdbms/orcl/orcl/trace/orcl_ora_8285.trc
 27-MAY-11 09:19:33  diag/rdbms/orcl/orcl/trace/orcl_dm00_8271.trc
 27-MAY-11 09:19:33  diag/rdbms/orcl/orcl/trace/orcl_dw00_8273.trc
 27-MAY-11 09:19:11  diag/rdbms/orcl/orcl/trace/orcl_dbrm_8104.trc
 27-MAY-11 09:18:53  diag/rdbms/orcl/orcl/trace/orcl_ora_8267.trc
 27-MAY-11 09:18:33  diag/rdbms/orcl/orcl/trace/orcl_j001_8237.trc
 27-MAY-11 09:18:26  diag/rdbms/orcl/orcl/trace/orcl_mmon_8219.trc
 27-MAY-11 09:18:23  diag/rdbms/orcl/orcl/trace/orcl_ora_8231.trc
 27-MAY-11 09:18:22  diag/rdbms/orcl/orcl/trace/orcl_cjq0_8229.trc
 27-MAY-11 09:18:16  diag/rdbms/orcl/orcl/trace/orcl_ora_8131.trc
 27-MAY-11 09:18:14  diag/rdbms/orcl/orcl/trace/orcl_m000_8223.trc
 27-MAY-11 09:18:13  diag/rdbms/orcl/orcl/trace/orcl_mman_8108.trc
 27-MAY-11 09:18:05  diag/rdbms/orcl/orcl/trace/orcl_ora_8048.trc
 27-MAY-11 09:17:59  diag/rdbms/orcl/orcl/trace/orcl_vktm_7920.trc
 27-MAY-11 09:17:59  diag/rdbms/orcl/orcl/trace/orcl_ora_8046.trc
 27-MAY-11 09:17:00  diag/rdbms/orcl/orcl/trace/orcl_mman_7932.trc
 27-MAY-11 09:16:56  diag/rdbms/orcl/orcl/trace/orcl_ora_7954.trc
 27-MAY-11 09:16:51  diag/rdbms/orcl/orcl/trace/orcl_ora_7871.trc

I have already got some tracefiles. How long are they going to stay?

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           720                  8760                 2011-05-27 10:16:46.997118 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00       
1 rows fetched

The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy with for example:

adrci> set control (SHORTP_POLICY = 360)

adrci> set control (LONGP_POLICY = 2190)

adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           360                  2190                 2011-06-01 11:42:17.208064 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00
1 rows fetched

Also, we may want to purge tracefiles manually. Following command will manually purge all tracefiles older than 2 days (2880 minutes):

adrci> purge -age 2880 -type trace
adrci> show tracefile -rt
   01-JUN-11 10:46:54  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc
   01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc
   01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log
   01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc
   01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc
   01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc
   01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc
   01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc
   01-JUN-11 09:17:19  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc
   01-JUN-11 09:16:44  diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc
   01-JUN-11 09:16:30  diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc
   01-JUN-11 09:16:19  diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc
   01-JUN-11 09:16:16  diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc
   01-JUN-11 09:16:14  diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc
   30-MAY-11 14:07:02  diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc
   30-MAY-11 11:15:30  diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc
   30-MAY-11 11:00:01  diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc
   30-MAY-11 10:56:58  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc
   30-MAY-11 10:56:20  diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc
   30-MAY-11 10:56:06  diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc
   30-MAY-11 10:55:58  diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc
   30-MAY-11 10:55:55  diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc
   30-MAY-11 10:55:53  diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc

Conclusion: With adrci, we have a new and efficient utility to deal with Oracle Errors – especially for collecting information to send them to Oracle Support. This functionality is called Incident Packaging Service. 11g is generating lots of tracefiles. We can control the purging policy of them with adrci. Finally, we can now tail -f our alert.log from any OS.

Recommend this posting: In case you found this post useful, why not recommend it to your Twitter Followers? Look how easy I’ve made that for you: Just click here and a Twitter Status Box will open with the (fully customizable) text you see when you hold the cursor above the link. Thank you :)

About these ads

, ,

  1. #1 by Asif Momen on June 1, 2011 - 12:39

    Precisely written and worth reading :)

  2. #2 by LewisC on June 1, 2011 - 13:56

    Uwe,

    Nice post. Concise and very useful. Thanks.

    LewisC

  3. #3 by Uwe Hesse on June 1, 2011 - 21:41

    Thank you Asif and Lewis for your nice feedback! I really appreciate it :-)

    Lewis,
    for some reason the built-in spam detector thought your comment was spam, so I had to approve it manually. Sorry for that.

  4. #4 by Sadeq on June 2, 2011 - 00:09

    Thank you for this info, I learnt today something new that really helps me especially I have incident today and i was tracing it manule, now i have this tool which will help me a lots.

  5. #5 by mdinh on June 2, 2011 - 03:50

    Wanted to supplement your post with mine “Alert Log Monitoring using ADRCI” http://j.mp/kvGK3g

  6. #6 by Anand on June 2, 2011 - 09:12

    Hi Uwe,

    Nice and simple again :)

    Anand

  7. #7 by Uwe Hesse on June 3, 2011 - 08:50

    @mdinh, thanks for the supplement! Reminds me to add to my disclaimer that I am not responsible for any comments content/link :-)
    Sadeq & Anand,
    thank you guys for the nice feedback! Glad if you found the posting helpful :-)

  8. #8 by Rochak on June 9, 2011 - 11:45

    Perfect

  9. #9 by Rafi on June 9, 2011 - 15:58

    Hi Uwe,
    Came to know about many concepts.Thanks very much.

    Best regards,

    Rafi.

  10. #10 by Uwe Hesse on June 9, 2011 - 16:13

    Hi Rafi,
    thank YOU! You’re welcome :-)

  11. #11 by neeraj on June 10, 2011 - 14:04

    It is very useful and nice post.
    all the best…….

  12. #12 by sunil on June 14, 2011 - 04:48

    too good.. :)

  13. #13 by Uwe Hesse on June 15, 2011 - 17:40

    Thank you, Neeraj and Sunil!

  14. #14 by Ramanoudjame TIROUMALAI on June 17, 2011 - 15:53

    Hi Uwe,

    I’m dealing with 11g R2 since more than three months. This article has provided me something new and easy for maintenance tasks on databases.

    Thanks a lot.

    For further information about adrci, I went through Chapter 16 of Oracle Utilities on the following link.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e16536.pdf

    Regards,
    Tirou

  15. #15 by paul.mcmanus@maxima.co.uk on June 17, 2011 - 16:05

    Hi

    Anyone have issues with the autmatic and manual purge of the trace subdirectory not working as expected?

    One of our servers has entries under the trace subdirectories for a number of databases going back to October 2010.

    show tracefile -rt only shows some of the tracefile history (last couple of weeks) and performing a manual ADRCI purge will remove the files listed in show tracefile output but no further back than that so we have to manually delete the tracefiles using OS commands

  16. #16 by Krish Sridhara on July 28, 2011 - 18:10

    Hi Uwe,
    Your post on Exadata and adrci (my favorite resolution, rca tool in 11g) is simply superb.
    I have one question though similar to purging the trace files, can the listener.log be purged or is there any other to do logrotate.

    I highly appreciate your response.

    Thanks and Regards,

  17. #17 by Uwe Hesse on July 29, 2011 - 04:40

    Krish,
    presently, it seems not to be possible to purge the listener.log with adrci. You may still use logrotate until that works with adrci also.

  18. #18 by Krish Sridhara on August 3, 2011 - 18:30

    Hi Uwe,
    I highly appreciate your response.
    I wish that could be done.
    Thanks and Regards,
    Ram

  19. #19 by Pavel Ermakov on September 2, 2011 - 10:03

    Hi
    Good post!Thanks!

    Regards,
    Pavel.

  20. #20 by Greg Cook on September 2, 2011 - 10:26

    Nice post Uwe.

    If you didn’t know already Fusion Middleware also uses ADR for the creation of incidents and storage of associated diagnostic dumps. I’ve posted an introductory article on it here:

    http://blogs.oracle.com/fmwdiag/entry/introduction_to_fmw_diagnostic

  21. #21 by shrikant rao on September 3, 2011 - 09:20

    Hi Uwe,

    As usual great pleasure reading your article and this one was a nice post.

    Shrikant.

  22. #22 by Uwe Hesse on September 3, 2011 - 09:28

    Pavel, Shrikant: Thank you for the nice feedback! Very much appreciated :-)

    Greg,
    I must admit that I don’t know much about Fusion Middleware – that includes the presence of ADR there. Thank you for the information!

  23. #23 by neerajvasudeva on September 6, 2011 - 06:34

    very nicely documented.

  24. #24 by Mark Heckler on September 6, 2011 - 19:35

    Well-stated and nicely illustrated, as always. Thanks, Uwe!

  25. #25 by Uwe Hesse on September 6, 2011 - 21:19

    Thank you, Neeraj and Mark! Very much appreciated :-)

  26. #26 by Mubeenubeen on September 18, 2011 - 09:19

    excellentent article again. very clear & very useful.

    thanks for great work here.

  27. #27 by Uwe Hesse on September 20, 2011 - 18:53

    Thanks for the nice feedback! You’re welcome :-)

  28. #28 by Ram on February 5, 2012 - 18:19

    Hi

    can you please explain the awr report

  29. #29 by Uwe Hesse on February 7, 2012 - 13:26

    Ram, there is no direct relation to AWR reports here.

  30. #30 by Phil on February 15, 2012 - 17:24

    Thanks for the info, used it today to upload a package to Oracle.

  31. #31 by Uwe Hesse on February 21, 2012 - 20:19

    You’re welcome, Phil, thanks for stopping by :-)

  32. #32 by Saurabh Gupta on February 22, 2012 - 08:03

    Great tip to monitor trace files

  33. #33 by jaygee on February 22, 2012 - 13:22

    Have you ever come across the error “DIA-48110: error encountered while attempting to remove a file, DIA-48190: OS unlink system call failure…………”???

  34. #34 by Uwe Hesse on February 22, 2012 - 18:49

    Saurabh Gupta, thanks for the nice comment!

    Jaygee, no, I did not encounter that, but I recall that error mentioned in some other Blog where they created files with reserved names manually in the diag subfolders.

  35. #35 by Vijay Dwivedi on February 26, 2012 - 08:38

    Very nicely written article, any idea how to change Alert Log file location. I am using Windows 2008 R2 as OS for 11gR2.

  36. #36 by newbie01oracle on March 29, 2012 - 01:07

    Hi

    Absolutely love all your posts. Would be nice if there is printer friendly version though :(-

  37. #37 by Pankaj on May 23, 2012 - 15:21

    Hi,

    Nice post… :)

    Thanks
    Pankaj Gupta

  38. #38 by Uwe Hesse on June 1, 2012 - 08:43

    Pankaj, thanks for leaving this nice comment :)

  39. #39 by Samir on July 29, 2012 - 16:33

    A little late on reading this, Excellent post, I really wonder how and when you people get all the time to keep up with this ever evolving technology

  40. #40 by Narayan on July 30, 2012 - 07:14

    Hello Uwe

    Its really helpful for analysis

    Regards
    Narayan

  41. #41 by Uwe Hesse on July 30, 2012 - 17:21

    Samir, thank you! Fortunately, it’s part of my job to keep up with Oracle Database New Features :)
    Narayan, you’re welcome :)

  42. #42 by Sam on July 31, 2012 - 05:06

    This post is really useful.

  43. #43 by Uwe Hesse on August 2, 2012 - 09:11

    Thank you, Sam, for leaving the nice feedback :)

  44. #44 by Amit Sanghvi on September 12, 2012 - 06:06

    Hey Uwe, I find your posts very helpful. Please keep writing more of them. They make the topics interesting and simple to understand.
    – Amit

  45. #45 by sai on September 29, 2012 - 07:26

    Hi uwe,

    Please guide me how to start the Oracle Tuning.

    Regards,
    Sai

  46. #46 by Reddy on October 8, 2012 - 12:25

    Very nice post Sir.. Thank you

  47. #47 by Uwe Hesse on October 9, 2012 - 21:31

    You’re welcome :-)

  48. #48 by Yasir on October 15, 2012 - 07:45

    Exellent!! You explain complex things in simpler ways..

  49. #49 by sendtoshailesh on October 16, 2012 - 13:18

    nice explanation !!

    Keep posting. This is helpful for lot more than DBAs !!

  50. #50 by Uwe Hesse on October 23, 2012 - 16:49

    Thank you guys :)

  51. #51 by Daz on November 12, 2012 - 03:49

    Thanks Ewe, exactly what I needed, brief, clear and concise.

  52. #52 by Uwe Hesse on November 12, 2012 - 09:36

    It’s Uwe, and my pleasure :)

  53. #53 by vivien on November 14, 2012 - 16:57

    Really great explanation and clear. I’m myself SENIOR Oracle Instructor and DBA. And it’s not the only one from you.
    I’v read a lot of interesting doc from your blog.
    THANK YOU for ALL !!!!!!!!!!!

  54. #54 by Brajesh on December 17, 2012 - 14:54

    Uwe… Not sure how to convey my satisfaction after reading this blog
    Nothing else,but to the point… You shaved my few hours and frustration reading oracle doc.
    Once again thanks

  55. #55 by Uwe Hesse on December 19, 2012 - 09:37

    Vivien and Brajesh, thank you very much for taking the effort to leave such a nice feedback! I really appreciate it :)

  56. #56 by Ahmet on January 3, 2013 - 13:44

    Hi Uwe, thanks for great post.

  57. #57 by Uwe Hesse on January 6, 2013 - 10:58

    Ahmet, you’re welcome :)

  58. #58 by priyadarshani on January 18, 2013 - 09:03

    Hi UWE ,
    it was very helpful for me as I was not knowing anything about ADRCI
    suddenly I face issue of that and I was blank
    but you save me
    thank you so much

    priyadarshani

  59. #59 by Uwe Hesse on January 20, 2013 - 11:33

    Thank you for leaving this nice feedback then! You’re welcome :)

  60. #60 by Zubair on February 8, 2013 - 01:03

    Really great explanation very helpful!!!!!!!!!!!!!!!!!!
    Thank You

  61. #61 by Sparkle on May 22, 2013 - 00:38

    Thank you, I use your page as my goto reference when my syntax fails me.

  62. #62 by Uwe Hesse on May 27, 2013 - 08:20

    Thank YOU for sharing this – I hope others may follow your example ;-)

  63. #63 by Harini on June 18, 2013 - 01:39

    Very nice – Thank you so much for your post. it saved a lot of time.

  64. #64 by Anil Panda on June 26, 2013 - 12:04

    Awesome explanation with detailed examples :) !!

  65. #65 by Gerrit Haase on November 29, 2013 - 15:48

    Is there an extended version of this howto available, which is also talking about log_X.xml files? Are these treated the same way than “trace” files, and what about trace/alertORCL.log and trace/listener.log which are not rotatetd, is there an option or will there be an option to administer these with ADRCI?

  66. #66 by Jijo on January 7, 2014 - 18:48

    Hi Uwe , Nice post.Thank you so much…

  67. #67 by Mauricio Miret on January 10, 2014 - 17:32

    Very useful! Nice, concise and well explained.

  68. #68 by CLETO S. FERNANDES on February 11, 2014 - 22:16

    Uwe some !!! Thanks a lot !!!

  1. Log Buffer #224, A Carnival of the Vanities for DBAs | The Pythian Blog
  2. adrci: A survival guide for the DBA (via The Oracle Instructor) « Little bit technical
  3. My adrci posting is published in the OU EMEA Newsletter « The Oracle Instructor
  4. Usn's IT Blog » Oracle: Manage the lifetime of trace files et al. with ADRCI (purge interval)
  5. Using the ADRCI utility with Oracle Weblogic « Jan van Zoggel
  6. Merry Christmas & A Happy New Year 2012! « The Oracle Instructor
  7. Merry Christmas & A Happy New Year 2012! | Oracle Administrators Blog - by Aman Sood
  8. ADRCI and listener purging « ORAganism
  9. ADRCI Commands « Oracle VUE
  10. Top 10 postings in 2012 « The Oracle Instructor
  11. Very nice Oracle adrci demo | MY DBA Notes
  12. Do you know “adrci”? | sanjay bali

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,635 other followers

%d bloggers like this: