Posts Tagged ‘oracle 11g’

Reading Alert Log

With Oracle 10g, external table was required to read alert log  but luckily Oracle 11g created a system table that can be used to access alert log from SQL:  X$DBGALERTEXT. Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows.

Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (as in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, another dump dest in 11g).

The database, Automatic Storage Management (ASM), Cluster Ready Services (CRS), and other Oracle products or components store all diagnostic data in the ADR.

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, ‘$ORACLE_HOME/log’

The location of an ADR home is given by the following path, which starts at the ADR base directory:


v$diag_info can be queried to check ADR locations, including ADR BASE and ADR home.

Within the ADR home directory are subdirectories:

alert       – XML formatted alertlog
trace      – Files and text alert.log file
cdump – core files

message_text can have multiple lines of ORA- errors and related tracing file information, but for monitoring only need first error code.

SELECT        record_id, substr(message_text, instr(message_text,'ORA-'),
              instr(message_text,chr(10),1,2)-instr(message_text,'ORA-') ) message_text
FROM          <strong>X$DBGALERTEXT</strong>
WHERE         originating_timestamp > sysdate-1
              AND message_text like '%ORA-%'
ORDER BY  record_id desc

Query to get errors for the past day:

<pre>SELECT  distinct    originating_timestamp,   message_text
WHERE   originating_timestamp > sysdate-1
AND     (  message_text = 'ORA-00600'  OR
           message_text like '%Fatal%'

Query to get errors for the last 10 minutes:

<pre>SELECT         rownum       “line”,    message_text “error”
WHERE          originating_timestamp > (sysdate – 10/1440)  AND
               message_text like ‘%ORA-%’
ORDER BY       originating_timestamp;


Oracle 11g Reading Alert Log Via SQL

Note: 1072547.1 – How To Edit, Read, and Query the Alert.Log

Oracle 11g Automatic Diagnostic Repository


Shutdown Hangup Scenario

If Shutdown normal or shutdown immediate hangs and you try to connect as sys, it would get you connected either as idle instance or simply connected which is in reality a fake connection. In a particular scenario, I changed ORACLE_HOME from EM and then issued simple “SHUTDOWN” command which took a long time to complete. I tried to issue “shutdown immediate” and “shutdown abort” commands and found following errors.

SQL*Plus: Release Production on Sat Mar 5 19:52:01 2011

Enter user-name: sys as sysdba
Enter password:
SQL>  shutdown immediate
ORA-24324: service handle not initialized
ORA-24323:  value not allowed
ORA-01090: shutdown in progress - connection is not  permitted

SQL> shutdown abort
ORA-01031: insufficient privileges

SQL> select instance from v$instance;
select instance from  v$instance
* ERROR at line 1: ORA-01012: not logged on
Process ID:  0
Session ID: 0 Serial number: 0

In this scenario it did “connect / as sysdba”, but in actually failed to connect. Then, when I
tried to shutdown abort, you got ORA-1031 cause you weren’t actually connected.
It’s interesting to note that if you connect as sysdba, and the connection is successful, you see something like:”Connected to:Oracle Database 10g Enterprise Edition Release – 64bit
Production With the Partitioning, OLAP and Data Mining options”If you fail to connect, cause process table is full, you’ll  get: “Connected.”
So, “Connected.” means you *failed* to connect!

ORA-01012: not logged on (check this link for another reason why Oracle shows “not logged on” error)


Although shutdow abort causes instance recovery but it becomes necessary if you want to accomplish a repetitive task at a given time in a specific time. You can combine it with restricted startup and then again shutdown with immediate option and better run it through a script.

# Source the db.env to set the Oracle environment

ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

shutdown abort;
startup restrict;
shutdown immediate;

Debugging a hung database

Starting with Oracle 11g,  SQL*Plus has a new option called “prelim” . This option is very useful for running oradebug and other utilities that do not require a real connection to the database.

C:\ACE> sqlplus –prelim



SQL> set _prelim on
SQL> connect / as sysdba

Run oradebug commands to diagnose a hung database issue using the

new hanganalyze utility:

SQL> oradebug hanganalyze

Wait for some time to identify process state changes.

SQL> oradebug hanganalyze

Open a separate SQL session and immediately generate a system state dump.

SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Further Reading:

Fix Oracle Hanging