oracle 11g开始提供的命令行工具,可以查看数据库问题和相关事件,可以打包包括获取跟踪文件信息,数据库告警日志的详细信息以及其他配置/测试用例信息.
首先来看看Adrci的使用:
使用help可以获得帮助手册.
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
使用
ADRCI查看告警日志
adrci> show homes
ADR Homes:
diag/rdbms/leo11/leo11
adrci>show alert -tail
adrci> show alert -p "message_text like '%7445%'"
Choose the alert log from the following homes to view:
1: diag/rdbms/leo11/leo11
2: diag/tnslsnr/localhost/listener1
3: diag/tnslsnr/localhost/listener
Q: to quit
Please select option: 1
Output the results to file: /tmp/alert_4753_30863_leo11_1.ado
2012-03-17 07:53:16.484000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9477.trc (incident=137):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:55:26.425000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9591.trc (incident=1338):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:55:56.760000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9638.trc (incident=1362):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:56:12.950000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9664.trc (incident=1363):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:57:12.955000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9702.trc (incident=1364):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:58:13.414000 +08:00
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9731.trc (incident=1354):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
2012-03-17 07:59:16.517000 +08:00
问题和意外事件
drci> show problem
ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 7445 [nttaddr2bnd()+2284] 30918 2012-05-11 12:04:51.473000 +08:00
1 rows fetched
通过show incident可以看到那些方面受这个错误的影响:
adrci> show incident
ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
26715 ORA 7445 [nttaddr2bnd()+2284] 2012-04-17 20:24:27.317000 +08:00
26716 ORA 7445 [nttaddr2bnd()+2284] 2012-04-17 20:25:27.761000 +08:00
26717 ORA 7445 [nttaddr2bnd()+2284] 2012-04-17 20:26:28.374000 +08:00
26718 ORA 7445 [nttaddr2bnd()+2284] 2012-04-17 20:27:28.661000 +08:00
26719 ORA 7445 [nttaddr2bnd()+2284] 2012-04-17 20:28:29.102000 +08:00
28308 ORA 7445 [nttaddr2bnd()+2284] 2012-04-19 08:01:48.673000 +08:00
28380 ORA 7445 [nttaddr2bnd()+2284] 2012-04-19 08:02:23.557000 +08:00
28381 ORA 7445 [nttaddr2bnd()+2284] 2012-04-19 08:03:22.846000 +08:00
28382 ORA 7445 [nttaddr2bnd()+2284] 2012-04-19 08:04:23.246000 +08:00
28383 ORA 7445 [nttaddr2bnd()+2284] 2012-04-19 08:05:23.523000 +08:00
29573 ORA 7445 [nttaddr2bnd()+2284] 2012-04-20 08:11:55.732000 +08:00
29574 ORA 7445 [nttaddr2bnd()+2284] 2012-04-20 08:12:56.193000 +08:00
29575 ORA 7445 [nttaddr2bnd()+2284] 2012-04-20 08:13:56.659000 +08:00
29576 ORA 7445 [nttaddr2bnd()+2284] 2012-04-20 08:14:57.401000 +08:00
29577 ORA 7445 [nttaddr2bnd()+2284] 2012-04-20 08:15:57.831000 +08:00
29817 ORA 7445 [nttaddr2bnd()+2284] 2012-05-11 11:20:47.600000 +08:00
29897 ORA 7445 [nttaddr2bnd()+2284] 2012-05-11 11:21:34.716000 +08:00
29898 ORA 7445 [nttaddr2bnd()+2284] 2012-05-11 11:22:33.661000 +08:00
29899 ORA 7445 [nttaddr2bnd()+2284] 2012-05-11 11:23:34.013000 +08:00
29900 ORA 7445 [nttaddr2bnd()+2284] 2012-05-11 11:24:34.502000 +08:00
20 rows fetched
我们可以通过show tracefile命令得出与各个事件相关的信息:
adrci> show tracefile -I 29900
diag/rdbms/leo11/leo11/incident/incdir_29900/leo11_d000_4663_i29900.trc
adrci> show incident -mode detail -p "incident_id=29900"
ADR Home = /opt/app/oracle/diag/rdbms/leo11/leo11:
*************************************************************************
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 29900
STATUS ready
CREATE_TIME 2012-05-11 11:24:34.502000 +08:00
PROBLEM_ID 1
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 7445
ERROR_ARG1 nttaddr2bnd()+2284
ERROR_ARG2 SIGSEGV
ERROR_ARG3 ADDR:0x0
ERROR_ARG4 PC:0xA77C190
ERROR_ARG5 Address not mapped to object
ERROR_ARG6 <NULL>
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT <NULL>
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
PROBLEM_KEY ORA 7445 [nttaddr2bnd()+2284]
FIRST_INCIDENT 137
FIRSTINC_TIME 2012-03-17 07:53:16.484000 +08:00
LAST_INCIDENT 30921
LASTINC_TIME 2012-05-11 12:07:52.810000 +08:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME Client ProcId
KEY_VALUE oracle@localhost.4663_2418624
KEY_NAME ProcId
KEY_VALUE 27.5
OWNER_ID 1
INCIDENT_FILE /opt/app/oracle/diag/rdbms/leo11/leo11/incident/incdir_29900/leo11_d000_4663_i29900.trc
OWNER_ID 1
INCIDENT_FILE /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_4663.trc
1 rows fetched
意外事件打包服务
adrci> ips create package incident 29900
另外还有一个视图很牛,X$DBGALERTEXT,11g 开始引入了新的ADR,默认的Alert.log转换为LOG.XML的形式,默认存放在$ADR_HOME/alert&trace目录下。
SQL> desc X$DBGALERTEXT
Name Null? Type
----------------------- -------- ----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUE NUMBERNCE
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER
ORIGINATING_TIMESTAMP MESSAGE_GROUP PROBLEM_KEY
--------------------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-MAR-12 07.53.16.484 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9477.trc (incident=137):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.55.26.425 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9591.trc (incident=1338):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.55.56.760 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9638.trc (incident=1362):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.56.12.950 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9664.trc (incident=1363):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.57.12.955 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9702.trc (incident=1364):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.58.13.414 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9731.trc (incident=1354):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.59.16.517 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9809.trc (incident=2542):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 07.59.51.658 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9855.trc (incident=2590):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
17-MAR-12 08.00.00.506 AM +08:00 Access Violation ORA 7445 [nttaddr2bnd()+2284]
Errors in file /opt/app/oracle/diag/rdbms/leo11/leo11/trace/leo11_d000_9869.trc (incident=2591):
ORA-07445: exception encountered: core dump [nttaddr2bnd()+2284] [SIGSEGV] [ADDR:0x0] [PC:0xA77C190] [Address not mapped to object] []
9 rows selected.
查看最近2天内管理员执行过的DDL语句:
SQL> l
1 select originating_timestamp, message_text
2 from X$DBGALERTEXT
3 where originating_timestamp > sysdate - 2
4* and message_group = 'admin_ddl'
SQL> /
ORIGINATING_TIMESTAMP MESSAGE_TEXT
----------------------------------- ----------------------------------------
11-MAY-12 11.20.49.402 AM +08:00 ALTER DATABASE MOUNT
11-MAY-12 11.20.56.006 AM +08:00 Completed: ALTER DATABASE MOUNT
11-MAY-12 11.20.56.412 AM +08:00 ALTER DATABASE OPEN
11-MAY-12 11.21.26.701 AM +08:00 Completed: ALTER DATABASE OPEN