针对Oracle数据库出现的各种问题，为了全方位的定位问题，维护部遇到客户oracle异常问题时，一例要求客户提供如下日志信息：
1.$ORACLE_BASE/admin/$SID/bdump, $ORACLE_BASE/admin/$SID/udump 当天的日志
如：今天是6月16日，/u01/app/oracle/admin/test/bdump目录下：
-rw-r----- 1 oracle oinstall 205370 Jun 13 10:59 alert_test1.log
-rw-r----- 1 oracle oinstall 8153 Jun 13 10:59 test1_lgwr_16208.trc
-rw-r----- 1 oracle oinstall 2791 Jun 13 23:20 test1_m000_25387.trc
-rw-r----- 1 oracle oinstall 2791 Jun 14 23:21 test1_m000_20972.trc
-rw-r----- 1 oracle oinstall 2790 Jun 15 23:22 test1_m000_14356.trc
-rw-r----- 1 oracle oinstall 1665 Jun 16 10:23 test1_lmd0_16196.trc
/u01/app/oracle/admin/test/udump 目录下当天的日志文件。
2. crs log目录下crs的相关日志信息：
如：/u01/app/oracle/product/10.2.0/crs/log/hp101
drwxr-x--- 2 oracle oinstall 96 May 15 14:21 admin
drwxr-x--- 2 oracle oinstall 96 May 15 14:22 evmd
drwxr-x--- 2 root oinstall 96 May 15 14:22 crsd
drwxr-x--- 5 oracle oinstall 8192 May 15 14:38 cssd
drwxrwxr-t 5 oracle oinstall 8192 May 21 17:17 racg
-rw-rw-r-- 1 root oinstall 7750 Jun 3 16:46 alerthp101.log
drwxr-x--- 2 oracle oinstall 114688 Jun 16 14:06 client
这些目录下当天的日志文件。
3. $ORACLE_HOME/network/log当天的日志信息：
/u01/app/oracle/product/10.2.0/db/network/log
$ ll
total 249696
-rw-r--r-- 1 oracle oinstall 126028387 Jun 16 14:14 listener.log
-rw-r--r-- 1 oracle oinstall 1743147 May 22 10:15 listener_hp101.log
-rw-r--r-- 1 oracle oinstall 3902 Jun 5 21:26 sqlnet.log
4. 故障发生阶段的ash报告（时间段越短越好，一般在5-30分钟之间）
和一个小时的awr报告：
具体操作如下：
生成awr report执行：
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1939003356 TEST 1 test1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html （此处建议输入html）
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing specifying a number lists all completed snapshots. Enter value for num_days: 1 （输入多少天以来，例子输入是1天） Listing the last 30 days of Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- test1 TEST 1 09 12月 2007 17:26 1 2 09 12月 2007 17:35 1 3 09 12月 2007 17:35 1 4 09 12月 2007 17:35 1 5 09 12月 2007 17:35 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 4 （输入快照ID） Begin Snapshot Id specified: 1 Enter value for end_snap: 5 （输入快照ID） End Snapshot Id specified: 5 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_4_5.html. To use this name, press Enter value for report_name: awrrpt_test1_20071209.html （输入报告名） Ash报告生成： SQL> @?/rdbms/admin/ashrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1952945692 TEST 1 test1 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html Type Specified: html （此处建议输入html） Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ 1952945692 2 TEST test2 hp102 * 1952945692 1 TEST test1 hp101 Defaults to current database Using database id: 1952945692 Defaults to current instance Using instance number: 1 ASH Samples in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Oldest ASH sample available: 08-Jun-08 23:00:33 [ 11000 mins in the past] Latest ASH sample available: 16-Jun-08 14:20:02 [ 0 mins in the past] Specify the timeframe to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter begin time for report: -- Valid input formats: -- To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 -- To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Defaults to -15 mins Enter value for begin_time: 06/16/08 13:10:00 （输入报告开始时间，输入的时候注意时间格式 月/日/年） Report begin time specified: 06/16/08 13:10:00 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: 10 （输入时间长度，我这里输入的是10分钟） Report duration specified: 10 Using 16-Jun-08 13:10:00 as report begin time Using 16-Jun-08 13:20:00 as report end time Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- In the 'Activity Over Time' section of the ASH report, -- the analysis period is divided into smaller slots -- and top wait events are reported in each of those slots. -- Default: -- The analysis period will be automatically split upto 10 slots -- complying to a minimum slot width of -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY. Specify Slot Width in seconds to use in the 'Activity Over Time' section: Defaults to a value as explained above: Slot Width specified: Specify Report Targets (using ashrpti.sql) to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- ASH Report can accept "Report Targets -- like a particular SQL statement, or a particular SESSION, -- to generate the report on. If one or more report targets are -- specified, then the data used to generate the report will only be -- the ASH samples that pertain to ALL the specified report targets. -- Default: -- If none of the report targets are specified, -- then the target defaults to all activity in the database instance. Specify SESSION_ID (eg: from V$SESSION.SID) report target: Defaults to NULL: SESSION report target specified: Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: Defaults to NULL: (% and _ wildcards allowed) SQL report target specified: Specify WATI_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target specified: Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target: Defaults to NULL: SERVICE report target specified: Specify MODULE name (eg: from V$SESSION.MODULE) report target: Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified: Specify ACTION name (eg: from V$SESSION.ACTION) report target: Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified: Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target: Defaults to NULL: (% and _ wildcards allowed) CLIENT_ID report target specified: Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target: Defaults to NULL: (% and _ wildcards allowed) PLSQL_ENTRY report target specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is ashrpt_1_0616_1320.html. To use this name, press Enter value for report_name: /home/oracle/ash0616_1.html （输入报告名称） 生成的报告会保存在/home/oracle/ 目录下。下载本文
