版本：
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版本
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804' version='1'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
host_addr='192.168.99.99' pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804'>
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中增加如下信息：
level='16' host_id='BJM' host_addr='192.168.99.99'
pid='2804' version='1'>
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-701388/ ，如需转载，请注明出处，否则将追究法律责任。
