automatic diagnostic repository
11g使用diagnostic_dest,user dest,background dest,core dest都 不使用了
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /home/oracle
默认为$ORACLE_BASE,若无oracle_base,defualt oracle_home/log
[oracle@vm11g ~]$ echo $ORACLE_BASE
/home/oracle
受一个隐藏参数控制
SQL> SQL> column value format a25
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
SQL> 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
3 4 SQL> SQL> /
Enter value for par: oracle_base
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%oracle_base%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
__oracle_base /home/oracle
ORACLE_BASE
SQL> select * from v$diag_info
2 ;
INST_ID NAME VALUE
---------- ------------------------------ -------------------------
1 Diag Enabled TRUE
1 ADR Base /home/oracle
1 ADR Home /home/oracle/diag/rdbms/x
h/xh
1 Diag Trace /home/oracle/diag/rdbms/x ~~~对应log版 alert
h/xh/trace
1 Diag Alert /home/oracle/diag/rdbms/x ~~~~~~~对应xml版的alert
h/xh/alert
INST_ID NAME VALUE
---------- ------------------------------ -------------------------
1 Diag Incident /home/oracle/diag/rdbms/x
h/xh/incident
1 Diag Cdump /home/oracle/diag/rdbms/x
h/xh/cdump
1 Health Monitor /home/oracle/diag/rdbms/x
h/xh/hm
1 Default Trace File /home/oracle/diag/rdbms/x
h/xh/trace/xh_ora_3747.tr
INST_ID NAME VALUE
---------- ------------------------------ -------------------------
c
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
#针对11g查找当前trace非常简单
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
Diag Trace :alert .log ,backup dump,user dump位置
Diag Alert :html版本alert
新的管理工具adrci
[oracle@vm11g trace]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Thu Jan 28 18:56:43 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/home/oracle"
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> show alert -tail 10 (返回不只10 rows 是个bug)
2010-01-28 18:48:06.573000 +08:00
Starting background process SMCO
FBDA started with pid=21, OS id=3753
SMCO started with pid=22, OS id=3755
replication_dependency_tracking turned off (no async multimaster replication found)
2010-01-28 18:48:08.285000 +08:00
Starting background process QMNC
QMNC started with pid=23, OS id=3757
2010-01-28 18:48:16.067000 +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.
2010-01-28 18:48:29.019000 +08:00
Completed: ALTER DATABASE OPEN
2010-01-28 18:52:59.819000 +08:00
Starting background process CJQ0
CJQ0 started with pid=26, OS id=3782
adrci> show alert -p "message_text like '%ALTER DATABASE OPEN%'"
ADR Home = /home/oracle/diag/rdbms/xh/xh:
*************************************************************************
Output the results to file: /tmp/alert_3834_30864_xh_12.ado
2009-12-10 10:55:58.272000 +08:00
ALTER DATABASE OPEN
2009-12-10 10:56:11.540000 +08:00
Completed: ALTER DATABASE OPEN
2009-12-13 11:06:07.124000 +08:00
ALTER DATABASE OPEN
2009-12-13 11:06:38.655000 +08:00
Completed: ALTER DATABASE OPEN
2010-01-02 20:07:42.074000 +08:00
ALTER DATABASE OPEN
2010-01-02 20:08:19.057000 +08:00
Completed: ALTER DATABASE OPEN
2010-01-03 20:32:42.902000 +08:0
adrci> help show alert
Usage: SHOW ALERT [-p ] [-term]
[ [-tail [num] [-f]] | [-file ] ]
Purpose: Show alert messages.
Options:
[-p ]: The predicate string must be double quoted.
The fields in the predicate are the fields:
ORIGINATING_TIMESTAMP timestamp
NORMALIZED_TIMESTAMP timestamp
ORGANIZATION_ID text(65)
COMPONENT_ID text(65)
HOST_ID text(65)
HOST_ADDRESS text(17)
MESSAGE_TYPE number
MESSAGE_LEVEL number
MESSAGE_ID text(65)
MESSAGE_GROUP text(65)
CLIENT_ID text(65)
MODULE_ID text(65)
PROCESS_ID text(33)
THREAD_ID text(65)
USER_ID text(65)
INSTANCE_ID text(65)
DETAILED_LOCATION text(161)
UPSTREAM_COMP_ID text(101)
DOWNSTREAM_COMP_ID text(101)
EXECUTION_CONTEXT_ID text(101)
EXECUTION_CONTEXT_SEQUENCE number
ERROR_INSTANCE_ID number
ERROR_INSTANCE_SEQUENCE number
MESSAGE_TEXT text(2049)
MESSAGE_ARGUMENTS text(129)
SUPPLEMENTAL_ATTRIBUTES text(129)
SUPPLEMENTAL_DETAILS text(129)
PROBLEM_KEY text(65)
[-tail [num] [-f]]: Output last part of the alert messages and
output latest messages as the alert log grows. If num is not specified,
the last 10 messages are displayed. If "-f" is specified, new data
will append at the end as new alert messages are generated.
[-term]: Direct results to terminal. If this option is not specified,
the results will be open in an editor.
By default, it will open in emacs, but "set editor" can be used
to set other editors.
[-file ]: Allow users to specify an alert file which
may not be in ADR. must be specified with full path.
Note that this option cannot be used with the -tail option
Examples:
show alert
show alert -p "message_text like '%incident%'"
show alert -tail 20
现在sr提供错误信息有时候要求对这个错误打包,下面是打包步骤
1. With 11g ORACLE_HOME and PATH set,from the OS prompt enter
%]adrci
adrci tool is started.
2.
adrci> show home
diag/rdbms/v11/V11 (example)
This will list all the ADR homes. Note the home path for current database or instance
3. set the home path (for exmple)
adrci> set homepath diag/rdbms/v11/V11
.
4.
adrci> show problem
This will list the most recent problems encountered by the database.
The Oracle Error (i.e. ORA-600, ORA-7445, ORA-1578) you are experiencing will be listed.
You will also see a PROBLEM_ID , Problem_key and Date associated with the problem
5. Package the trace files
adrci> ips pack problem in /tmp
Package will be generated in /tmp.
6. Upload this package to the SR .
生产库中实际使用
[oracle@dmk01 ~]$ adrci
ADRCI: Release 11.1.0.7.0 - Production on Wed Mar 31 09:44:46 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/oracle/app"
adrci> show home
ADR Homes:
diag/tnslsnr/dmk01/listener
diag/tnslsnr/dmk01/listener_dmk01
diag/clients/user_oracle/host_1580021934_11
diag/rdbms/rac/rac1
diag/asm/+asm/+ASM1
adrci> set the home path diag/rdbms/rac/rac1
DIA-48470: Unknown "SET" command
adrci> set the homepath diag/rdbms/rac/rac1
DIA-48470: Unknown "SET" command
adrci> set homepath diag/rdbms/rac/rac1
adrci> show problem -p "problem_key like '%7445%'"
ADR Home = /oracle/app/diag/rdbms/rac/rac1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1 ORA 7445 [kewa_dump_time_diff()+157] 64001 2009-12-11 11:44:35.126712 +08:00
1 rows fetched
adrci> ips pack problem 1 in /home/oracle
Generated package 1 in file /home/oracle/IPSPKG_20100331094626_COM_1.zip, mode complete
可以看到产生一个zip,upload这个zip既可
adrci> adrci> exit
[oracle@dmk01 ~]$ adrci
ADRCI: Release 11.1.0.7.0 - Production on Wed Mar 31 09:47:16 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/oracle/app"
adrci> set homepath diag/rdbms/rac/rac1
#查看 这个路径下alert中严重错误
adrci> show INCIDENT
ADR Home = /oracle/app/diag/rdbms/rac/rac1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
329084 ORA 603 2010-03-29 16:12:43.248755 +08:00
329083 ORA 603 2010-03-29 15:17:23.805185 +08:00
329076 ORA 603 2010-03-29 13:41:31.220666 +08:00
329075 ORA 603 2010-03-29 13:27:02.896547 +08:00
329067 ORA 603 2010-03-29 13:48:42.511807 +08:00
329044 ORA 603 2010-03-29 15:24:40.457620 +08:00
329043 ORA 603 2010-03-29 15:09:46.440418 +08:00
329035 ORA 603 2010-03-29 15:46:57.058043 +08:00
329003 ORA 603 2010-03-29 10:26:18.633057 +08:00
328987 ORA 603 2010-03-29 13:34:07.274823 +08:00
328982 ORA 603 2010-03-29 16:34:48.038176 +08:00
328981 ORA 603 2010-03-29 16:20:22.204018 +08:00
328980 ORA 603 2010-03-29 16:05:48.559722 +08:00
328979 ORA 603 2010-03-29 15:02:47.497143 +08:00
328899 ORA 603 2010-03-29 16:27:21.459434 +08:00
328891 ORA 603 2010-03-29 11:16:33.664016 +08:00
328844 ORA 603 2010-03-29 12:56:22.053915 +08:00
328843 ORA 603 2010-03-29 12:48:17.282289 +08:00
328828 ORA 603 2010-03-29 13:55:32.173735 +08:00
328813 ORA 603 2010-03-29 12:41:01.904272 +08:00
328812 ORA 603 2010-03-29 12:25:59.796220 +08:00
328811 ORA 603 2010-03-29 10:10:22.159886 +08:00
328803 ORA 603 2010-03-29 12:32:52.899523 +08:00
328636 ORA 603 2010-03-28 10:34:31.857401 +08:00
328635 ORA 603 2010-03-27 10:39:26.528660 +08:00
312634 ORA 603 2010-03-26 10:54:45.841685 +08:00
265121 ORA 603 2010-03-23 17:14:30.139817 +08:00
265032 ORA 603 2010-03-23 16:45:06.178141 +08:00
265025 ORA 603 2010-03-23 17:49:27.096376 +08:00
265024 ORA 603 2010-03-23 17:06:50.186866 +08:00
264993 ORA 603 2010-03-23 16:51:59.852377 +08:00
264956 ORA 603 2010-03-23 18:10:07.781480 +08:00
264955 ORA 603 2010-03-23 16:59:58.233656 +08:00
264954 ORA 603 2010-03-23 16:08:20.279849 +08:00
264953 ORA 603 2010-03-23 15:47:16.122075 +08:00
264952 ORA 603 2010-03-23 15:39:59.277703 +08:00
264928 ORA 603 2010-03-23 16:01:34.681364 +08:00
264921 ORA 603 2010-03-23 18:02:59.816676 +08:00
264920 ORA 603 2010-03-22 11:21:57.952662 +08:00
264906 ORA 603 2010-03-23 17:56:17.599535 +08:00
264890 ORA 603 2010-03-23 18:17:52.615167 +08:00
264888 ORA 603 2010-03-23 15:53:56.521197 +08:00
264801 ORA 603 2010-03-22 11:29:19.143686 +08:00
264800 ORA 603 2010-03-20 19:02:01.089399 +08:00
264584 ORA 603 2010-03-20 18:54:00.567620 +08:00
216909 ORA 603 2010-03-19 10:05:20.006221 +08:00
216869 ORA 603 2010-03-18 12:15:50.006790 +08:00
216581 ORA 603 2010-03-18 12:08:44.359526 +08:00
185510 ORA 600 [kksSearchChildList1] 2010-03-15 09:43:30.940769 +08:00
185478 ORA 600 [kksSearchChildList1] 2010-03-15 09:43:30.947698 +08:00
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-630872/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-630872/