Friday, July 27, 2007

How to check alert.log in Oracle 11g

Virag Sharma

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool.
This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,

Package incident and problem information into a zip file for send to Oracle Support. Etc.

No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data is secured only by operating system permissions on the ADR directories.

adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

adrci> show alert -tail 100 -f ( Similar to tail -100f )

Too list all the "ORA-" error run following command

show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"


In 11g alert file is saved in 2 location, one is in alert directory ( in XML format log.xml ) and
old style alert file in trace directory. Both these directory reside inside ADR HOME. ADR (
Automatic Diagnostic Repository ) is a file system based directory structure outside of the database, So you can have access of diagnostic data even database is down.

New initialization parameter
DIAGNOSTIC_DEST decide where to store ADR data ( i.e diagnostic data). In oracle 11g parameter background_dump_dest, core_dump_dest, user_dump_dest is replaced by DIAGNOSTIC_DEST. Even you set background_dump_dest, core_dump_dest, user_dump_dest it not going to used i.e. 11g ignore these parameters.

SQL> show parameter diagnostic_dest

--------------- --------------------------- -------------------------

diagnostic_dest string /u01/app/oracle

More Oracle Database 11g New Features

  1. Oracle SQL REPLAY

  2. IPS ( Incident Packaging Service)
  3. Automatic Diagnostic Repository (ADR)

  4. Database Replay [Click for Detail]

TAG = > Oracle 11g New Features , Oracle 11g tips and tricks , Oracle Database 11g New Features for DBA , Oracle Database 11g New Features


  1. Where will be the trace of control file saved in this case?

  2. How can we diffrentiate that which one is the trace file and which is error alert?

  3. can u explain me difference between trace file and alert log file and location of both files....