Oracle 11g alert文件的变化(后台报警日志)
刚刚加班写的
一．首先介绍2个新名词
Automatic Diagnostic Repository (ADR)—
一个存放数据库诊断日志、跟踪文件的目录，称作ADR base，对应初始化参数DIAGNOSTIC_DEST，如果设置了ORACLE_BASE环境变量，
DIAGNOSTIC_DEST等于ORACLE_BASE，如果没有设置ORACLE_BASE，则等与ORACLE_HOME/log。
PHP code:
SQL> show parameter DIAGNOSTIC
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /home/oracle
......
ADRCI Command-Line Utility 命令行工具
使用该工具查看日志，跟踪信息，查看健康报告。
二．一个比较大的变化：aelrt xml file
在oracle 11g中，alert文件的信息是以xml的文件格式存在的，另外提供了普通文本格式的alert文件。
这两份log文件的位置分别是V$DIAG_INFO中的Diag Alert 和Diag Trace 对应的目录。PHP code:
1* SELECT * FROM V$DIAG_INFO
INST_ID NAME VALUE
---------- ------------------------- ------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /home/oracle
1 ADR Home /home/oracle/diag/rdbms/orcl/orcl
1 Diag Trace /home/oracle/diag/rdbms/orcl/orcl/trace
1 Diag Alert /home/oracle/diag/rdbms/orcl/orcl/alert
1 Diag Incident /home/oracle/diag/rdbms/orcl/orcl/incident
1 Diag Cdump /home/oracle/diag/rdbms/orcl/orcl/cdump
1 Health Monitor /home/oracle/diag/rdbms/orcl/orcl/hm
1 Default Trace File /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12624.trc
1 Active Problem Count 0
1 Active Incident Count &nb
这个是xml格式的alert log：PHP code:
[oracle@ractest ~]$ cd /home/oracle/diag/rdbms/orcl/orcl/alert
[oracle@ractest alert]$ ls -l
total 148
-rw-r----- 1 oracle dba 139355 Jul 3 08:21 log.xml
[oracle@ractest alert]$ tail log.xml
<txt>QMNC started with pid=20, OS id=8646
</txt>
</msg>
<msg time='2007-07-03T08:21:31.911+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:2925:2802784106' type='NOTIFICATION' group='admin_ddl'
level='16' host_id='ractest' host_addr='10.200.15.180'
module='sqlplus@ractest (TNS V1-V3)' pid='8640'>
<txt>Completed: ALTER DATABASE OPEN
</txt>
</msg>
[oracle@ractest alert]$
这是文本格式的alert：PHP code:
[oracle@ractest trace]$ cd /home/oracle/diag/rdbms/orcl/orcl/trace
[oracle@ractest trace]$ ls -l al*
-rw-r----- 1 oracle dba 27988 Jul 3 08:21 alert_orcl.
SQL> create table test as select * from dba_objects where rownum <50;
Table created.
SQL> insert into test select * from dba_objects where rownum < 10 ;
9 rows created.
SQL> commit
2 /
Commit complete.
SQL> delete from test where rownum < 30 ;
29 rows deleted.
SQL> commit;
Commit complete.
SQL> drop table test ;
Table dropped.
对应的alert信息：
Sat Jun 30 06:00:02 2007
Sat Jun 30 06:00:02 2007
Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 304868
Sat Jun 30 08:51:24 2007
create table test as select * from dba_objects where rownum <50
Sat Jun 30 08:51:49 2007
drop table test
Sat Jun 30 08:52:03 2007
.......
三．查看alert文件的方法：
1.使用Enterprise Manager，进入Oracle database home page，在Related Links点击Alert Log Contents.
见图：
2.使用vi等文本编辑alert_SID.log （text-only alert log, without the XML tags）
3.使用工具adrci：PHP code:
[oracle@ractest ~]$ which adrci
/home/oracle/11g/bin/adrci
[oracle@ractest ~]$ adrci
ADRCI: Release 11.1.0.4.0 - Beta on Tue Jul 3 09:02:14 2007
Copyright (c) 1982, 2006, Oracle. All rights reserved.
ADR base = "/home/oracle"
adrci>>help show alert
Usage: SHOW ALERT [-p <predicate_string>] [-tail [num]] [-v]
[-file <alert_file_name>]
…………
Examples:
show alert
show alert -p "message_text like '%incident%'"
show alert -tail 20
adrci>>show alert -tail 10
ADR Home = /home/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
2007-07-03 08:21:31.039000 +08:00
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Starting background process SMCO
SMCO started with pid=18, OS id=8642
Opening with internal Resource Manager plan
Starting background process FBAR
FBAR started with pid=19, OS id=8644
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=20, OS id=8646
Completed: ALTER DATABASE OPEN
......
关于这个工具，我会再单独写一篇介绍它的具体用法。
