Oracle日志信息收集1.doc

Oracle日志信息收集针对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 166589 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 admindrwxr-x- 2 oracle oinstall 96 May 15 14:22 evmddrwxr-x- 2 root oinstall 96 May 15 14:22 crsddrwxr-x- 5 oracle oinstall 8192 May 15 14:38 cssddrwxrwxr-t 5 oracle oinstall 8192 May 21 17:17 racg-rw-rw-r- 1 root oinstall 7750 Jun 3 16:46 alerthp101.logdrwxr-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$ lltotal 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.log4. 故障发生阶段的ash报告（时间段越短越好，一般在5-30分钟之间）和一个小时的awr报告：具体操作如下：生成awr report执行：SQL ?/rdbms/admin/awrrpt.sqlCurrent Instance DB Id DB Name Inst Num Instance - - - - 1939003356 TEST 1 test1 Specify the Report TypeWould you like an HTML report, or a plain text report?Enter html for an HTML report, or text for plain textDefaults to htmlEnter value for report_type: htmlType Specified: html （此处建议输入html） Specify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1 （输入多少天以来，例子输入是1天）Listing the last 30 days of Completed Snapshots SnapInstance 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 1Specify the Begin and End Snapshot IdsEnter value for begin_snap: 4 （输入快照ID）Begin Snapshot Id specified: 1Enter value for end_snap: 5 （输入快照ID）End Snapshot Id specified: 5Specify the Report NameThe default report file name is awrrpt_1_4_5.html. To use this name,press to continue, otherwise enter an alternative.Enter value for report_name: awrrpt_test1_20071209.html （输入报告名） Ash报告生成：SQL ?/rdbms/admin/ashrpt.sqlCurrent Instance DB Id DB Name Inst Num Instance- - - - 1952945692 TEST 1 test1Specify the Report TypeEnter html for an HTML report, or text for plain textDefaults to htmlEnter value for report_type: htmlType 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 hp101Defaults to current databaseUsing database id: 1952945692Defaults to current instanceUsing instance number: 1ASH Samples in this Workload Repository schemaOldest ASH sample available: 08-Jun-08 23:00:33 11000 mins in the pastLatest ASH sample available: 16-Jun-08 14:20:02 0 mins in the pastSpecify the timeframe to generate the ASH reportEnter 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 minsEnter value for begin_time: 06/16/08 13:10:00 （输入报告开始时间，输入的时候注意时间格式 月/日/年）Report begin time specified: 06/16/08 13:10:00Enter duration in minutes starting from begin time:Defaults to SYSDATE - begin_timePress Enter to analyze till current timeEnter value for duration: 10 （输入时间长度，我这里输入的是10分钟）Report duration specified: 10 Using 16-Jun-08 13:10:00 as report begin timeUsing 16-Jun-08 13:20:00 as report end timeSpecify 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 usageDefaults 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 n