Wednesday, April 27, 2011

11g ADR – Viewing and Maintaining the alert log file

In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR.

The ADR is the Automatic Diagnostic Repository which is a file based hierarchical respository of data and information related the the database alert, trace and dump files, health monitor reports (new in 11g), network trace and log information, diagnostic data related to the 11g Incident Packaging Service etc.

The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or via the ADR command line interpreter adrci

The DIAGNOSTIC_DEST parameter identifies the location of the ADR and it defaults to the $ORACLE_HOME/log or $ORACLE_BASE if this environment variable has been set.

Let us assign this location the environment variable $ADR_DUMP.

The alert log is stored in both XML as well as standard text format in the ADR. In this example the database name is ‘apex’.

The XML alert.log is located at $ADR_DUMP/diag/rdbms/apex/apex/alert and is called ‘log.xml’. The current alert log contents will be found in the file ‘log.xml’ while the older entries are archived every 10 MB into files like ‘log_1.xml’, ‘log_2.xml’ and so on.

The standard text alert log file (alert_apex.log) is located at $ADR_DUMP/diag/rdbms/apex/apex/trace and is mantained for backward compatability purposes.

The view V$DIAG_INFO also has information on the location of the various components of the ADR.

SQL> col value format a50 SQL>  SELECT NAME,VALUE FROM v$diag_info   2   WHERE NAME LIKE '%Alert%';  NAME                 VALUE -------------------- -------------------------------------------------- Diag Alert           /u01/oracle/diag/rdbms/apex/apex/alert 

Using the ADR CLI to view Alert Log Information

The command ‘adrci’ will take us to the prompt.

apex:/u01/oracle/diag/rdbms/apex/apex/alert> adrci

ADRCI: Release 11.1.0.6.0 – Beta on Mon Aug 3 09:46:52 2009

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

ADR base = “/u01/oracle”
adrci>

Many of the adrci commands are very similar to the standard Unix vi commands. For example

SHOW ALERT -TAIL (to view the last 10 entries)

SHOW ALERT -TAIL 50 (to view the last 50 entries)

SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)

show alert -p “message_text like ‘%ORA-07445%’” (to show all occurences of the string ‘ORA-07445′ in the alert log.)

Purging Alert Log Content

The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.

The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.

So to purge all alert log entries older than 7 days the following command will be used:

adrci > purge -age 10080 -type ALERT

Mô hình oracle 11g

General Information
Server Directory Structure
Client Directory Structure
Client Structure (OEL) /home/oracle/oradiag_oracle/diag/clients/user_oracle/host_2309071977_76/

[oracle@perrito2 host_2309071977_76]$ ls
alert cdump indicent incpkg lck metadata stage sweep trace

Note: "When client side OCI or NET have an incident, an ADR directory is created in the user's home and the incident and dumps are located within. The root is named oradiag_. So finding this directory structure indicates some failure on the client side. The reason the ADR is created in the user's home directory and not in the Client Oracle home: permissions.

I was puzzled that my db server host had this directory even though I've never installed the Client there. Dev explained that "Every DB interaction uses OCI (SQLPLUS, etc.) from a client side." Thus you use it during install, for example, often without knowing it.
Queries set linesize 121
col value format a75

SELECT * FROM gv$diag_info;

ADRCI
Purpose The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:
  • Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
  • Viewing Health Monitor reports.
  • Packaging of 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.

ADR data is secured by operating system permissions on the ADR directories, hence there is no need to log in to ADRCI.
CREATE REPORT create report
SELECT run_id, name, check_name, run_mode, status, error_number
FROM gv$hm_run;

adrci> set homepath diag\rdbms\orabase\orabase

adrci> create report hm_run HM_RUN_1

adrci> show report hm_run hm_run_1
ECHO echo
ardci> echo on

ardci> echo off
EXIT exit
$ ardrci

adrci> exit
HELP help
adrci> help
HOST host
$ ardrci

adrci> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

-- then exits back to the command prompt
IPS CREATE PACKAGE INCIDENT ips create package incident
[CORRELATE ]
adrci> ips create package incident 2
IPS CREATE PACKAGE PROBLEM ips create package problem
[CORRELATE ]
adrci> ips create package problem ?
IPS CREATE PACKAGE PROBLEMKEY ips create package problemkey <'problem_key'>
[CORRELATE ]

ips create package problemkey <"problem_key">
[CORRELATE ]
adrci> ips create package problemkey '?'
adrci> ips create package problemkey "?"
IPS CREATE PACKAGE SECONDS ips create package seconds [CORRELATE ]
adrci> ips create package seconds 600 CORRELATE ALL
IPS CREATE PACKAGE TIME ips create package time <'start_timestamp'> TO <'end_timestamp'>
[CORRELATE ]
adrci> ips create package time '2008-01-01 00:00:00 -08:00' to '2008-01-02 23.59.59 -08:00'
IPS GENERATE PACKAGE ips generate package
adrci> ips generate package
IPS SHOW ips show incidents package pkg_id
adrci> show homes
adrci> set home diag\rdbms\orabase\orabase
adrci> ips show incident
IPS UNPACK ips unpack file file_name [into path]
adrci> ips unpack file /home/oracle/ORA603_20060906165316_COM_1.zip
into /tmp/newadr
PURGE purge [[-i {id1 | start_id end_id}] [-age mins]
[-type {ALERT | INCIDENT | TRACE | CDUMP | HM}]
adrci> purge -i 123 456

adrci> purge -age 60 -type incident
QUIT quit
$ ardrci

adrci> quit
RUN run script_name

@ script_name

@@ script_name
-- need to create a demo script here and post it

adrci> run uwadrscript
SET BASE set base base_str
adrci> set base /apps/oracle/product
SET BROWSER set browser browser_program
adrci> set browser firefox
SET CONTROL set control ( = value, ...)
adrci> set control (SHORTP_POLICY = 360)
SET ECHO set echo
adrci> set echo off
SET EDITOR set editor editor_program
adrci> set editor vi
SET HOME set home
adrci> show homes

adrci> set home diag\rdbms\orabase\orabase

adrci> show home
SET HOMES set homes , , ...
adrci> show homes

adrci> set homes diag\rdbms\orabase\orabase, diag\tnslsnr\perrito2\listener

adrci> show homes
SET HOMEPATH set homepath homepath_str1 [homepath_str2] [...]
adrci> show homepath

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show homepath
SET TERMOUT termout
adrci> set termout on

adrci> set termout off
SHOW ALERT show alert [-p predicate_string] [-tail [num] [-f]] [-term] [-file alert_file_name]
$ adrci exec="show homes; show incident"

adrci> show alert

Choose the alert log from the following homes to view:

1. diag\client\user_unknown\host_411310321_11
2. diag\rdbms\orabase\orabase
3. diag\tnslsnr\perrito2\listener
Q: to quit

Q

$ adrci

adrci> set home diag\rdbms\orabase\orabase

adrci> show alert

adrci> show alert 25
Tail the Alert Log -- open a terminal window
adrci> show home

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show alert -tail -f

-- to verify
SQL> ALTER SYSTEM SWITCH LOGFILE;
Show only lines with specific text -- open a terminal window
adrci> show home

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-7445%'";
SHOW BASE show base
adrci> show base

ADR base is "c:\oracle\product"
SHOW CONTROL show control
adrci> set homepath diag\rdbms\orabase\orabase

adrci> show control
SHOW HM_RUN show hm_run [-p predicate_string]
adrci> show hm_run
SHOW HOME show home
adrci> show home
SHOW HOMES show homes
adrci> show homes
SHOW HOMEPATH show homepath
adrci> show homepath
SHOW INCDIR show incdir [id | id_low id_high]
adrci> show incdir
SHOW INCIDENT show incident -p predicate_string [-mode {BASIC|BRIEF|DETAIL}]
[-orderby field1, field2, ...] [ASC|DSC]
adrci> show incident
SHOW PROBLEM show problem [-p predicate_string] [-last num | -all]
[-orderby field1, field2, ...] [ASC|DSC]]
adrci> show problem
SHOW REPORT show report report_type run_name
-- need to define a single home
conn / as sysdba

SELECT run_id, name, check_name, run_mode, status, error_number
FROM gv$hm_run;

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show report hm_run hm_run_1
SET TRACEFILE show tracefile [file1 file2 ...] [-rt | -t] [-i inc1 inc2 ...]
[-path path1 path2 ...]
adrci> show tracefile %mmon% -rt

adrci> show tracefile -i 1 4 -path diag/rdbms/orabase/orabase
SPOOL SPOOL filename [[APPEND] | [OFF]]
spool file_name_and_path []
adrci> spool /home/oracle/adr.log
adrci> show alert -term
adrci> spool off

Demo
ADRCI Demo SQL> conn / as sysdba

SQL> desc gv$hm_run

SQL> select run_id, name, check_name, run_mode, status, error_number
from gv$hm_run;

Wednesday, April 20, 2011

ORA-00824: Cannot Set sga_target Due to Existing Internal Settings

Question: I reset the memory structure, and now I cannot start the database. I'm getting the following error message:

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

How can I get rid of this ORA-00824 error and get my database back online?


Answer: To diagnose any error, you start by using the oerr utility to display the ORA-00824: cannot set sga_target due to existing internal settings error:

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

Cause: Unable to set sga_target due to current parameter settings.

Action: See alert log for more information.

The alert log will contain specific information on actions to take to rectify the condition causing the ORA-00824 error. Let's assume the alert.log contains the following message:

Cannot set sga_target with db_block_buffers set

The obvious answer is to not set db_block_buffers when using sga_target. Enabling automatic SGA Management by setting sga_target>0 in the same init.ora file when using the obsolete db_block_buffers parameter, pretty much guarantees an ORA-00824 error.

Either set sga_target=0 to disable SGA Management or remove the db_block_buffer paramenter from your spfile. Since the db_block_buffer parameter is obsolete, it makes sense to remove it. Attempting to edit the spfile directly with a text editor can cause a corruption issue. To safely remove the db_block_buffer parameter, do the following:

  1. Create a pfile from the spfile
  2. Remove the line containing the db_block_buffer parameter using a text editor
  3. Create a new spfile from the updated pfile
  4. Startup the database

The ORA-00824 should no longer be an issue.