Tags

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.

  1. 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.
  2. 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.
  3. 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.
  4. For example in the environment with RAC and ASM storage, each of the database instances and ASM instances have their own directory under ADR.
  5. 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.
  6. 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:

  1. If we set the ORACLE_BASE environment variable, the database sets the diagnostic_dest parameter value to the ORACLE_BASE.
  2. 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.