Oracle 11g has introduced many new features to ease the DBA workload,
As there are many new features in oracle 11g, I will start with one new feature and will add on the many new features soon.
The new diagnosability infrastructure aim is
- Preventing problem
- To find Proactive detections of problems
- Limiting the damage caused by database problems
- Reducing the time it takes to diagnose problems
- Easier interaction with Oracle support
The new features introduced by Oracle Fault management
- Automatic Diagnostic Repository
- The support workbench
- The health monitor
- The Sql repair advisor
- The data recovery advisor
I will start off with Oracle 11g fault management–Automatic Diagnostic repository
Automatic Diagnostic repository
The automatic diagnostic repository (ADR) is a special storage facility that is located outside the database i.e in the file system.
- We can access the file with a command line utility (CMD) or Enterprise manager.ADR works based on the timely capture of the critical error information following a Database failure.
- A memory base trace system collects the diagnostic and stores it in ADR.ADR contains Diagnostic files like alert log, tracefile, dump files and core files and Health monitor reports.
- Please note in Oracle 11g not only the database, but ASM, CRS and other Oracle products and components store all their diagnostic data in ADR.
- For example in the environment with RAC and ASM storage, each of the database instances and ASM instances have their own directory under ADR.
- ADR uses a consistent diagnostic data format across all Oracle products and all instances thus making it easy for users and Oracle Support to correlate diagnostic data from multiple instances.
- ADR also replaces the traditional diagnostic directories such as the bdump and cdump.
The Structure of the ADR
The new initialization parameter DIAGNOSTIC_DEST can be used to set the location of ADR base.
Please note the location of the parameter above is optional and ,if we don’t specify the value above the Oracle will determine the default location of the ADR base:
- If we set the ORACLE_BASE environment variable, the database sets the diagnostic_dest parameter value to the ORACLE_BASE.
- Or else the database set the diagnostic_dest parameter value to $ORACLE_HOME/log
Example in my test database I set the value of ORACLE_BASE= /u01/app/oracle
[oracle@oraclelinux oracle]$ echo $ORACLE_BASE
/u01/app/oracle
SQL> show parameter diagnostic_dest
NAME TYPE VALUE
———————————— ———– ——————————
Diagnostic_dest string /u01/app/oracle
What is the difference between ADR_BASE and ADR home?
ADR base is the location we set with the help of diagnostic_dest parameter
ADR home represent the path of the ADR home for current database instance
ADR home subdirectories
Each database instance stores its diagnostic data in various subdirectories
- Alert-Oracle stores an alert log in the XML format
- Hm-Contains the checker run reports, which are generated by the new Health Monitor facility
- Incident-Contain incident report for the instance
- Trace-Contains the text-based alert log, similar to the traditional alert log file.
Viewing the ADR locations
We use the view V$DIAG_INFO view to see all the ADR locations for the ORACLE database instance
SQL> select * from v$diag_info;
INST_ID NAME VALUE
———- —————————— ————————————————————
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/prod/PROD
1 Diag Trace /u01/app/oracle/diag/rdbms/prod/PROD/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/prod/PROD/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/prod/PROD/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/prod/PROD/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/prod/PROD/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_5283.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
ADRCI
The ADR command interpreter (ADRCI) is a brand new command –line tool that is a key component of the new fault diagnosability infrastructure.ADRCI tool enables us to interact with the ADR from the command line:
Starting ADRCI
By default the homepath to ADR is null when we start ADRCI meaning that all ADR homes under the ADR base are current. Hence we need to set the ADR home for ADRCI
[oracle@oraclelinux ~]$ adrci
ADRCI: Release 11.1.0.6.0 – Beta on Tue Aug 10 00:40:20 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = “/u01/app/oracle”
adrci>
Once ADRCI is started, we can enter various ADR interactive commands at the ADRCI prompt. Once done we can leave by typing exit or quit.
To view the list of ADRCI command type help in the ADRCI prompt
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
“HELP EXTENDED” to see the list
adrci> help set homepath
Usage: SET HOMES | HOME| HOMEPATH <homepath_str1 homepath_str2 …>
Purathpose: Set the ADR homes to query in the current ADRCI session.
Setting the homepath for ADR
Step 1:show all the homes currently set for ADR
adrci> show homes
ADR Homes:
diag/rdbms/prod/PROD
diag/rdbms/prod/PRODUCTION
Step 2:changing the homepath to PRODUCTION
adrci> set homepath diag/rdbms/prod/PRODUCTION
adrci> show homes
ADR Homes:
diag/rdbms/prod/PRODUCTION
Running adrci using batch mode
We can run adrci command in batch mode too by using a script and invoking it from adrci
Script.txt
Set homepath diag/rdbms/prod/PRODUCTION
Show alert
In order to run the script enter the following command
Adrci script=script.txt
Viewing the ALERT log contents with ADRCI
There are two types of alert logs for each database instance in an Oracle Database 11g Release 1 Database
1. In Trace directory
2. In the Alert directory under the ADR
To view the alert log ,set the ADR homepath to the database instance we are interested in and then issue the command
SHOW ALERT or SHOW ALERT -TAIL
The show alert –tail commands shows the last few lines of the alert log
Example when database startup you can check against the alert log
adrci> show alert -tail
2010-08-10 18:40:00.111000 +08:00
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oradata/PROD/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
2010-08-10 18:40:04.812000 +08:00
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
2010-08-10 18:40:07.189000 +08:00
Opening with internal Resource Manager plan
2010-08-10 18:40:09.596000 +08:00
Starting background process SMCO
SMCO started with pid=23, OS
Starting background process FBDA
FBDA started with pid=24, OS
replication_dependency_tracking turned off (no async multimaster replication found)
2010-08-10 18:40:12.278000 +08:00
Starting background process QMNC
QMNC started with pid=25, OS
2010-08-10 18:40:38.981000 +08:00
Completed: ALTER DATABASE OPEN
adrci>
You can return to the ADRCI command prompt after issuing the show alert –tail command line by pressing CTL +C. You can also specify the number of lines to be shown and also spool the results of the command, just as you can in SQL*Plus.
Incidents and Problems
Oracle introduces two new diagnostic concepts in Oracle Database 11: problems and incidents.
These concepts are crucial to the understanding of the new fault diagnosability infrastructure
- Any critical error in the database is called a problem for example, a critical error such as the one manifested by the Oracle error ORA-4031 (unable to allocate more bytes of shared memory). A problem is identified by a problem key and includes Oracle error codes.
- A single occurrence of a problem is called an incident. Multiple occurrences of the same problem lead to multiple incidents. An incident is identified by a unique incident ID.
When the ADR track both problems and incident it displays in Enterprise manager
Each incident is tagged with a problem key that relates the incident to a problem.
ADR automatically creates a problem when the first incident of that problem key
occurs. It removes the problem metadata after the last incident with that problem
key is removed from the ADR.
The MMON background process is in charge of automatically purging expired
ADR data.
ADR uses what it refers to as a flood-controlled incident system, whereby it allows only a certain number of incidents under a problem to log diagnostic data.
Important
ADR allows 5 diagnostic Data dumps per hour for a single problem
Hence if the incident occur 20 times for a same incident type,we only have to report once for Oracle support.
The set of diagnostic data pertaining to an incident or incidents to a problem(or problems) is called an INCIDENT PACKAGE.
ADR follows a retention policy so it can limit the amount of diagnostic data it must store.The retention policy actually includes two different settings,
1. Metadata retention – default setting one year
2. Incident files and dump retention – default setting one month
To check an incident’s current status in ADR,type the command below
Adrci> show incident –mode detail
If there is no incident then the following status will appear
adrci> show incident -mode detail
ADR Home = /u01/app/oracle/diag/rdbms/prod/PROD:
*************************************************************************
0 rows fetched
Incident packaging services
In earlier version of Oracle,we had to manually gather diagnostic data from multiple sources to submit to Oracle Support when we want to notify them of a problem.
Oracle Database 11g provides a new feature called the incident packaging services(IPS).
IPS will let us automatically gather all diagnostic data pertaining to a critical error such as the trace files and dump files. Plus the new Health check reports,SQL test cases,and related information,and package the data into a ZIP file to send to Oracle Support
We manage the IPS through either new Support Workbench, or through ADRCI tool.
Packaging Incident with ADRCI
You can use incident package to transmit incident information to Oracle Support.
Creating a logical package
Before posting an incident report we need to create a logical package.
ADR stores Logical package as METADATA.
Adrci>ips create package
Created package 1 without any contents, correlation level typical
Adrci>
The above package is an empty package.
To create a non empty logical package issue the command below
Adrci>ips create package incident <incident number>
Created package 5 based on incident id 17060
Correlation level typical
Adrci>
You can add file to an existing package by using the following
Adrci>ips add file <file_name> package <package_number>
Note that you can add only those files that are located in the ADR directory hierarchy(under the ADR base)
Generating a physical Incident Package
Once we load the logical package with diagnostic data,its the time to create the physical package to send to Oracle support
adrci> ips generate package 1 in /u01/app/oracle/diag
Generated package 1 in file /u01/app/oracle/diag/IPSPKG_20100810231616_COM_1.zip, mode complete
Adrci>
The above command generates a physical package in the directory /u01/app/oracle/support from the logical package 1 that we created earlier.
Note the suffix COM_1 is the filename for the physical file.
Once we incorporate all the diagnostic data and are ready to transmit the physical package
adrci> ips finalize package 1
Finalized package 1
adrci>
even though have generated the file ,we need to send the file old-fashioned way.through Oracle support workbench.