版本：
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

1,Oracle 11g引入了Automatic Diagnostic Repository (ADR),用於管理系統診斷資訊.
增加了新的檢視:
select name,value from v$diag_info;
Diag Enabled TRUE
ADR Base c:\oracle
ADR Home c:\oracle\diag\rdbms\ora11\ora11
Diag Trace c:\oracle\diag\rdbms\ora11\ora11\trace
Diag Alert c:\oracle\diag\rdbms\ora11\ora11\alert
Diag Incident c:\oracle\diag\rdbms\ora11\ora11\incident
Diag Cdump c:\oracle\diag\rdbms\ora11\ora11\cdump
Health Monitor c:\oracle\diag\rdbms\ora11\ora11\hm
Default Trace File c:\oracle\diag\rdbms\ora11\ora11\trace\ora11_ora_652.trc
Active Problem Count 0
Active Incident Count 0

alert log存在兩種格式,分別在Diag Trace和Diag Alert下。

2,alert檔案的兩種格式,內容一致;xml格式包含更多格式化資訊,可供ADRCI,X$DBGALERTEXT分析查詢,分別見3,4部分說明。
2.1文字格式
--alert_ora11.log
Thu Aug 27 10:50:33 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\oracle\product\11.1.0\db_1\RDBMS
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled

2.2xml格式
--log.xml版本
msg_id='opistr_real:871:3971575317' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804' version='1'>
Starting ORACLE instance (normal)

msg_id='ksunfy:13399:2937430291' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
LICENSE_MAX_SESSION = 0

msg_id='ksunfy:13400:4207019197' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
LICENSE_SESSIONS_WARNING = 0

type='UNKNOWN' level='16' host_id='BJM'
host_addr='192.168.99.99' pid='2804'>
Shared memory segment for instance monitoring created

msg_id='kcsnfy:323:968333812' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
Picked latch-free SCN scheme 2

msg_id='kcrrdini:15230:1211400554' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
Using LOG_ARCHIVE_DEST_1 parameter default value as C:\oracle\product\11.1.0\db_1\RDBMS

msg_id='kcrrdini:15230:1211400554' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

msg_id='ktunfy:2044:3053135360' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
Autotune of undo retention is turned on.

msg_id='ktinfy:1466:1526000287' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
IMODE=BR

msg_id='ktinfy:1474:3929296192' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
ILAT =18

msg_id='kzunfy:1914:2892522327' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
LICENSE_MAX_USERS = 0

msg_id='kzanfy:998:1161496215' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
SYS auditing is disabled

3,使用adrci讀取警告日誌
3.1互動模式
adrci
adrci> show homes
ADR Homes:
diag\clients\user_mahong\host_3130016397_11
...
adrci> set homepath diag\rdbms\ora11\ora11
adrci> show alert -tail 10
2011-07-05 13:55:01.237000 +08:00
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
...
adrci>set editor notepad
--查詢ORA錯誤
adrci> show alert -p "MESSAGE_TEXT LIKE '%ORA-%'"
--跟蹤最近輸出
adrci> show alert -tail -f

3.2批次模式
3.2.1執行命令
adrci exec="set homepath diag\\rdbms\\ora11\\ora11;show alert -tail 20;"

3.2.2執行指令碼
adrci_script.txt
set homepath diag\rdbms\ora11\ora11;show alert -tail 20
adrci script=adrci_script.txt

4使用sql讀取alert log：
以前版本中,可以使用External Table透過SQL來讀取alert log。
4.1在11g中，可以使用X$DBGALERTEXT讀取alert log。
測試：在log.xml中增加如下資訊：
msg_id='opistr_real:871:3971575317' type='NOTIFICATION' group='startup'
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804' version='1'>
test

select indx,message_text from X$DBGALERTEXT;
0 test
這表明X$DBGALERTEXT透過讀取解析log.xml獲取的。

4.2X$DBGDIREXT檢視包括所有目錄、檔案資訊:
測試：如果在diagnostic_dest下diag路徑下建立test目錄和test.txt,查詢結果：
SELECT indx, physical_path, physical_file, lvl, TYPE
FROM x$dbgdirext
WHERE creation_time > trunc(SYSDATE);
567 c:\oracle\diag test 0 1
568 c:\oracle\diag test.txt 0 2

Oracle11g_alert_log