Category Archives: Tuning

Using ADRCI Properly

The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data and is part of the fault diagnosability infrastructure introduced in Oracle 11g. ADRCI enables you to:

  • View diagnostic data within the Automatic Diagnostic Repository (ADR).
  • View Health Monitor reports.
  • Package incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

Invoke ADRCI as the Oracle OS user:

> adrci
ADRCI: Release - Production on Fri Jan 17 22:38:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"

adrci> show home
ADR Homes:

Set the home
adrci> set home diag/rdbms/orcl/ORCL
To check the alert log use the following option.

adrci> show alert -tail -f
2014-01-21 10:20:23.418000 -05:00
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1176
Successful close of redo thread 1
Completed: alter database close normal
alter database dismount
Completed: alter database dismount
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
2014-01-21 10:20:24.852000 -05:00
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Stopping background process VKTM:
2014-01-21 10:20:27.036000 -05:00
Instance shutdown complete

adrci> show alert -tail 


adrci> show alert 

To check for severe problems in database.

adrci> show problem

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [723]                                               3807                 2007-06-18 21:35:47.862114 -07:00
1                    ORA 29770                                                   24129                2011-10-21 19:08:24.097000 +00:00
2 rows fetched

To check for incedents related to problems.

adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
----------------- ------------------------- ---------------------------------
3808              ORA 603                   2007-06-18 21:35:49.322161 -07:00
3807              ORA 600 [723]             2007-06-18 21:35:47.862114 -07:00
3805              ORA 600 [723]             2007-06-18 21:35:25.012579 -07:00
3804              ORA 1578                  2007-06-18 21:35:08.483156 -07:00
4 rows fetched

To get more details on the incident execute the following

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

adrci> show trace <trace_file_location>

This statement lists the names of all trace files related to incident number 1681:

adrci> SHOW TRACEFILE -I 1681

adrci> show control

ADR Home = /opt/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
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3783208084           720                  8760                 2013-12-16 19:28:24.827055 +00:00        2014-01-17 21:09:07.527157 +00:00        2014-01-17 10:15:21.303696 +00:00        1                    2                    80                   1                    2013-12-16 19:28:24.827055 +00:00
1 rows fetched

All the tracefiles will stay for 30 days (720 hours),
but all the incident files would be kept for one year (8760 hours) by default.
This can be changed by the following command

adrci> set control (SHORTP_POLICY = 48)
adrci> set control (LONGP_POLICY = 48)

Purging manually
Files can be purged manually by the following commands. The value "2880" is in minutes.

adrci> purge -age 2880 -type incident
adrci> purge -age 2880 -type ALERT
adrci> purge -age 2880 -type TRACE
adrci> purge -age 2880 -type CDUMP
adrci> purge -age 2880 -type HM

Creation of Packages & ZIP files to send to Oracle Support

adrci> ips create package problem 1 correlate all

Created package 2 based on problem id 1, correlation level all 

#First create a logical package.

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/, mode complete

#Now create a zip file from the logical package

Working with AWR Reports

AWR is the best tool available to identify performance issues on your database. It has several tools to help you identify performance issues and all the information is displayed in a single HTML page. AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM.

Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). AWR runs by default and Oracle states that it does not add a noticeable level of overhead. A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository.

For more details on its usage, please ckick HERE