# TLoraddc.def: Oracle Database Diagnostics Collector # $Id: TLoraddc.def,v 1.26 2009/08/05 12:39:01 mschenke Exp $ # ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/TLoraddc.def,v 1.26 2009/08/05 12:39:01 mschenke Exp $ # # Change History # 20090715 LDE Modify BG process table for 11g. =head1 NAME TLoraddc - Oracle Database Diagnostics Collector =head1 DESCRIPTION Oracle Product Support may request a variety of diagnostics from SYSTEMSTATE dumps to trace files generated by operating system commands, in situations where a database is encountering outages or severe loss of service. Oracle Database Diagnostics Collector (ORADDC) is designed to make the collection of database diagnostics a more streamlined process. ORADDC has the following capabilities: =over 3 =item * It can generate the following dumps and collect the corresponding trace files: =over 3 =item o Hang Analyze Dump =item o Shared Pool Heap Dump =item o Library Cache Dump =item o O/S Command Tracing (where F, F or F are available) =item o Process Error Stack =item o Process State Dump =item o Process Stack Trace (where F is available) =item o Process Open Files (where F is available) =item o Process Dynamic Libraries (where F is available) =item o Row Cache (Dictionary Cache) Dump =item o System State Dump =item o 10046 Trace =back =item * It can produce the following reports: =over 3 =item o Active Session History (requires licenses) =item o Event Histogram (requires Wait events) =item o Locking/blocking contention =item o Shared Pool memory chunk allocation (when a heap dump is requested) =item o Session Wait Events =back =back In addition, ORADDC is able to do the following: =over 3 =item * Run requested diagnostics as multiple requests in parallel as background processes, which helps for reducing the collection time. =item * Name a list of background processes to trace and to dump. =item * Specify one or more Oracle server processes by any one of the following methods: =over 3 =item o A list of database session identifiers =item o A list of listener names (UNIX only) =item o A list of Oracle process identifiers =item o A list of operating system process identifiers =item o Sessions from a specified username =item o Sessions with entries in C =item o Sessions waiting on specified wait events (limited to 10 per C) =back =item * Keep track of trace/dump/process stack/report files produced as well has being able to determine process dumps caused indirectly by requesting a hang analyze dump. It adds a summary of dump, report, and trace files to the execution log. =item * Inspect a produced hang analyze dump and display a list of sessions in a 'hang' state as well as sessions that are blocking other sessions. It writes these details in the execution log. It lists any process dumps caused by the C request also. =back By default system/process dumps are performed several times with a two-minutes interval in between. You can override the default time interval by a time specified in seconds. ORADDC has the ability (if requested) to pre-connect to the database and sleep until told to wake up before taking the specified diagnostics. If 10046 or operating system traces were requested, ORADDC waits for all non-trace tasks (dumps, reports) to complete. It then waits by default for thirty seconds and terminates all tracing activity. You can override the default time period by a time specified in seconds. =head1 USAGE This tool can be used in two ways: =over 3 =item a) Runs interactively. It requests the user to enter the required information. -vdT oraddc =item b) Runs from the command line. The input can be given in the command line using the following syntax: -vdT oraddc:[:[:...]] =back It supports the following commands: =over 6 =item B< A > Perform a collection =item B< B > Perform a collection under the control of wake-up commands =item B< C > Wake up ORADCC =item B< D > Remove a result set =item B< E > Check if ORADDC is still alive =item B< F > Auto wake up ORADCC =back =head2 ORADDC Diagnostic Collection You can specify the collection details interactively or from the command line with the following syntax: -vdT oraddc:A:[,...]:[/...]:[,...]:[,...]:[, ,,,...] CnamE,...> is a comma-separated list of background process names for which reports are requested. It supports following names: C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C

, C, C, C

, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C, C. Names that are shorter than four characters regroup several processes. For example, C matches C, C, ... With CmetE>, you can specify additional process identifiers by one of the following methods: =over 6 =item B< B > Blocking sessions =item B< E > Sessions waiting on the wait events provided as argument =item B< L > Listener names (list provided as argument) =item B< O > Oracle process identifiers (list provided as argument) =item B< P > Process identifiers (list provided as argument) =item B< S > Session identifiers (list provided as argument) =item B< U > Sessions for the user provided as argument =item B< - > None =back Use a comma-separated format to specify the information like the process or system identifiers, or the wait event numbers. Be careful on the number of sessions logged in as a specified user. Ct1E,...> is a comma-separated list of traces or dumps that ORADDC must collect for the specified processes. It supports following entries: =over 6 =item B< E > Process Error Stack =item B< F > Process Open Files =item B< L > Process Dynamic Libraries =item B< O > Operating System Trace =item B< P > Process State Dump =item B< S > Process Stack =item B< T > 10046 Trace =back Operating system traces and process stacks are depending on command availability. Usually, you cannot execute them simultaneously. Ct2E,...> is a comma-separated list of traces or dumps that ORADDC must collect. It supports following entries: =over 6 =item B< A > Hang Analyze Dump =item B< C > Locking Contention Report =item B< D > Active Session History Dump =item B< E > Event Histogram (requires Wait events) =item B< H > Shared Pool (Heap) Dump =item B< L > Library Cache Dump =item B< R > Row Cache Dump =item B< S > System State Dump =item B< W > Session Wait Events Report =back For example, -vdT oraddc:A:smon:B:T:S -vdT oraddc:A::E/125,137 -vdT oraddc:A::U/scott:T,S:A,H,S:3 -vdT oraddc:A::::C,L,R =head2 ORADDC Delayed Diagnostic Collection ORADDC supports same collection capabilities in wait mode as the direct mode and uses a similar syntax: -vdT oraddc:B:[,...]:[/...]:[,...]:[,...]:[, ,,,...] It waits for a wake-up command before proceeding with gathering diagnostics: The wake-up can be done in two ways: =over 3 =item o Wake-up manually. -T oraddc:C =item o Wake-up automatically when the database is no longer responding. -T oraddc:F:[,] The default values for CsleepE> and CtimeoutE> are respectively 60 seconds and 120 seconds. =back This is useful when a pre-connection is required because fresh connections to the database are not possible. When ORADDC runs in delayed mode, you can test if it is still alive by using one of the following commands (or its equivalent interactive form): -T oraddc:E -T oraddc:E: Oracle recommends a default delay of 30 seconds. If you decide to override this, do not use a value less than 10 seconds. This is because ORADDC sleeps in 5 second cycles. Therefore, anything approaching 5 seconds may risk a false result if the system is busy. You can specify a flag as an extra argument to get the list of processes that are waiting for a wake-up. -T oraddc:E::1 -T oraddc:E::1 The delay mechanism is based on the current Oracle system identifier (SID). You can specify an alternative SID, using the following: -T -e ORACLE_SID= oraddc:C -T -e ORACLE_SID= oraddc:E -T -e ORACLE_SID= oraddc:F =head2 ORADDC Result Management With the C command, you can remove all files from a specified ORADDC run. In interactive mode, you can select the result set to remove from the list of existing result sets. ORADDC removes it only after confirmation. You can use the following command also: -T oraddc:D: The time stamp should be in YYYYMMDDhhmm format. =head2 Other Relevant Settings The following settings influence the database connections used to collect the diagnostic information: =over 3 =item * ORACLE_SID =item * SQL_LOGIN =item * SQL_SYSDBA =item * SQL_PRELIM =back ORADDC supports the RDA extended SID formats. Nevertheless, collecting trace files requires a local database. =cut # Initialisation var $ORACLE_HOME = getSetting('ORACLE_HOME', getEnv('ORACLE_HOME','')) var $ORACLE_SID = getSetting('ORACLE_SID', getEnv('ORACLE_SID', '')) var $ORADDC_DIR = getEnv('RDA_ORADDC', 'oraddc') var $RPT_DIRECTORY = getSetting('RPT_DIRECTORY','output') var $RPT_GROUP = getSetting('RPT_GROUP', 'RDA') var ($DDC_ASH,$DDC_EVT_SLP,$DDC_PID,$DDC_SLP,$DDC_TRC_SLP,$DDC_USR) = () var (@DDC_PID,%DDC_BGP,%DDC_DMP,%DDC_PRC,%DDC_SQL,$WAIT) = () # Define the validation tables var %tb_bgp = ('abmr', 'abmr',\ 'acfs', 'acfs',\ 'acms', 'acms',\ 'ap', 'ap\w{2}',\ 'ap[00-zz]', 'ap\w{2}',\ 'arb', 'arb\w',\ 'arb[0-a]', 'arb\w',\ 'arc', 'arc\w',\ 'arc[0-t]', 'arc\w',\ 'asmb', 'asmb',\ 'as', 'as\w{2}',\ 'as[00-zz]', 'as\w{2}',\ 'bmrn', 'bmrn',\ 'b', 'b00\d',\ 'b[000-004]','b00\d',\ 'cjq', 'cjq\d',\ 'cjq[0-9]', 'cjq\d',\ 'ckpt', 'ckpt',\ 'cp', 'cp\w{2}',\ 'cp[00-zz]', 'cp\w{2}',\ 'cs', 'cs\w{2}',\ 'cs[00-zz]', 'cs\w{2}',\ 'ctwr', 'ctwr',\ 'd', 'd\d{3}',\ 'd[000-999]','d\d{3}',\ 'dbrm', 'dbrm',\ 'dbw', 'dbw\w',\ 'dbw[0-j]', 'dbw\w',\ 'dbwr', 'dbwr',\ 'dia', 'dia\d',\ 'dia[0-9]', 'dia\d',\ 'diag', 'diag',\ 'dm', 'dm\d{2}',\ 'dm[00-99]', 'dm\d{2}',\ 'dmon', 'dmon',\ 'dr', 'dr\d{2}',\ 'dr[00-99]', 'dr\d{2}',\ 'dskm', 'dskm',\ 'dw', 'dw\d{2}',\ 'dw[00-99]', 'dw\d{2}',\ 'e', 'e\d{3}',\ 'e[000-999]','e\d{3}',\ 'emn', 'emn0',\ 'emn0', 'emn0',\ 'emnc', 'emnc',\ 'emon', 'emon',\ 'fbda', 'fbda',\ 'fmon', 'fmon',\ 'fsfp', 'fsfp',\ 'gen', 'gen0',\ 'gen0', 'gen0',\ 'gmon', 'gmon',\ 'gtx', 'gtx\w',\ 'gtx[0-j]', 'gtx\w',\ 'i', 'i\d{3}',\ 'i[000-999]','i\d{3}',\ 'insv', 'insv',\ 'j', 'j\d{3}',\ 'j[000-999]','j\d{3}',\ 'kate', 'kate',\ 'l', 'l\d{3}',\ 'l[000-999]','l\d{3}',\ 'lck', 'lck\d',\ 'lck[0-9]', 'lck\d',\ 'lgwr', 'lgwr',\ 'lmd', 'lmd\d',\ 'lmd[0-9]', 'lmd\d',\ 'lmhb', 'lmhb',\ 'lmon', 'lmon',\ 'lms', 'lms\w',\ 'lms[0-z]', 'lms\w',\ 'lns', 'lns\w',\ 'lns[0-j]', 'lns\w',\ 'lnsv', 'lnsv',\ 'lsp', 'lsp\d',\ 'lsp[0-2]', 'lsp\d',\ 'lsnv', 'lsnv',\ 'm', 'm\d{3}',\ 'm[000-999]','m\d{3}',\ 'mark', 'mark',\ 'mman', 'mman',\ 'mmnl', 'mmnl',\ 'mmon', 'mmon',\ 'mrp', 'mrp0',\ 'mrp0', 'mrp0',\ 'ms', 'ms\d{2}',\ 'ms[00-99]', 'ms\d{2}',\ 'n', 'n\d{3}',\ 'n[000-999]','n\d{3}',\ 'nsa', 'nsa\w',\ 'nsa[1-v]', 'nsa\w',\ 'nss', 'nss\w',\ 'nss[1-v]', 'nss\w',\ 'nsv', 'nsv\w',\ 'nsv[0-u]', 'nsa\w',\ 'o', 'o\d{3}',\ 'o[000-999]','o\d{3}',\ 'ocf[0-9]', 'ocf\d',\ 'ofsc', 'ofsc',\ 'orb', 'orb\d',\ 'orb[0-9]', 'orb\d',\ 'osmb', 'osmb',\ 'p', 'p\d{3}',\ 'p[000-999]','p\d{3}',\ 'ping', 'ping',\ 'pmon', 'pmon',\ 'p', 'p\d{3}',\ 'p[000-999]','p\d{3}',\ 'psp', 'psp\d',\ 'psp[0-9]', 'psp\d',\ 'q', 'q\d{3}',\ 'q[000-999]','q\d{3}',\ 'qmn', 'qmn\d',\ 'qmn[0-9]', 'qmn\d',\ 'qmnc', 'qmnc',\ 'r', 'r\d{3}',\ 'r[000-999]','r\d{3}',\ 'rbal', 'rbal',\ 'rcbg', 'rcbg',\ 'reco', 'reco',\ 'rms', 'rms\d',\ 'rms[0-9]', 'rms\d',\ 'rp', 'rp\d{2}',\ 'rp[00-99]', 'rp\d{2}',\ 'rsm', 'rsm\d',\ 'rsm[0-9]', 'rsm\d',\ 'rsmn', 'rsmn',\ 'rvwr', 'rvwr',\ 's', 's\d{3}',\ 's[000-999]','s\d{3}',\ 'smc', 'smc\d',\ 'smc[0-9]', 'smc\d',\ 'smco', 'smco',\ 'smon', 'smon',\ 'tem', 'tem\d',\ 'tem[0-9]', 'tem\d',\ 'trwr', 'trwr',\ 'v', 'v\d{3}',\ 'v[000-999]','v\d{3}',\ 'vbg', 'vbg\d',\ 'vbg[0-9]', 'vbg\d',\ 'vdbg', 'vdbg',\ 'vkrm', 'vkrm',\ 'vktm', 'vktm',\ 'w', 'w\d{3}',\ 'w[000-999]','w\d{3}',\ 'wmon', 'wmon',\ 'x', 'x\d{3}',\ 'x[000-999]','x\d{3}') var %tb_dmp = ('A','Hang Analyze Dump',\ 'C','Locking Contention Report',\ 'D','Active Session History Dump',\ 'E','Event Histogram',\ 'H','Shared Pool (Heap) Dump',\ 'L','Library Cache Dump',\ 'R','Row Cache Dump',\ 'S','System State Dump',\ 'W','Session Wait Events Report') var %tb_prc = ('E','Process Error Stack',\ 'F','Process Open Files',\ 'L','Process Dynamic Libraries',\ 'O','Operating System Trace',\ 'P','Process State Dump',\ 'S','Process Stack',\ 'T','10046 Trace') # Validate the execution context if !and($ORACLE_HOME,$ORACLE_SID) die 'ORACLE environment has not been set (ORACLE_HOME, ORACLE_SID, etc)' # Define macros macro auto_wakeup {var ($slp,$max) = @arg import $ORACLE_SID,$RPT_DIRECTORY,$RPT_GROUP # Set the passwords call set_passwords() # Test the database connection if testSql() {echo 'ERROR: Cannot connect to the database' echo getSqlMessage() return } # Loop until wake-up or database hangs debug 'Checking the database response ...' var $nam = concat($RPT_GROUP,'_TMP_ddcup_',field(':',-1,$ORACLE_SID),'.tmp') set $sql {SELECT 'RDA' " FROM sys.dual; } call setSqlTimeout($max) while !testFile('f',catFile($RPT_DIRECTORY,$nam)) {if !grepSql($sql,'^RDA$','f') {debug 'Wake up ORADDC processes due to a database timeout ...' output ON,$nam write close return } sleep $slp } } macro set_passwords {import $ORACLE_SID debug 'Setting the current login ...' if !defined(getSetting('ORACLE_SID')) call setSqlSid($ORACLE_SID) var $log = setSqlLogin() loop $key (grepSetting('^SQL_PASSWORD_')) {if match($key,'^SQL_PASSWORD__([\+\w]+)__(.*)$') {var ($sid,$usr) = (last) var $sid = replace($sid,'plus','+',true) } else var ($usr,$sid) = (substr($key,13)) call setSqlLogin($usr,unpack('u',getSetting($key)),$sid) } loop $key (grepSetting('^SQL_PASS_\d+$')) {var ($usr,$sid) = split('\@',getSetting(replace($key,'_PASS_','_USER_')),2) call setSqlLogin($usr,unpack('u',getSetting($key)),$sid) } if defined(getSetting('SQL_LOGIN')) call setSqlLogin($log) else call setSqlLogin('','','',true) } # Treat the command if getSetting('TST_ARGS') {var $DDC_CMD = last var ($cmd,$arg,$pid,$prc,$dmp,$cst) = split(':',$DDC_CMD) var ($DDC_SLP,$DDC_TRC_SLP,$DDC_ASH,$DDC_EVT_SLP) = split(',',$cst) if match($cmd,'^(A|(B))$') {var (undef,$WAIT) = last # Parse the background process list if $arg {loop $nam (split(',',$arg)) {var $nam = lc($nam) if missing($tb_bgp{$nam}) die 'Invalid background process name (',$nam,')' var $DDC_BGP{$nam} = $tb_bgp{$nam} } } # Parse the process selection directives var ($DDC_PID,$str) = split('\/',$pid) if compare('eq',nvl($DDC_PID,''),'') var $DDC_PID = '-' elsif match($DDC_PID,'^[EOPS]$') {if !length($str) die 'Missing identifiers' if !match($str,'^\d+(,\d+)*$') die 'Invalid identifier list' var @DDC_PID = split(',',$str) } elsif compare('eq',$DDC_PID,'L') {if !length($str) die 'Missing listener names' if !match($str,'^\S+$') die 'Invalid listener names list' var @DDC_PID = split(',',$str) } elsif compare('eq',$DDC_PID,'U') {if !length($str) die 'Missing user name' if match($str,'[^\w\-\$]') die 'Invalid user name' var $DDC_USR = $str } elsif !match($DDC_PID,'^[\-B]$') die 'Invalid process selection method (',$DDC_PID,')' # Parse the process report list if $prc {loop $opt (split(',',$prc)) {if missing($tb_prc{$opt}) die 'Invalid process report (',$opt,')' var $DDC_PRC{$opt} = $tb_prc{$opt} } } elsif or(compare('ne',$DDC_PID,'-'),scalar(%DDC_BGP)) var $DDC_PRC{'T'} = $tb_prc{'T'} # Parse the dump list if $dmp {loop $opt (split(',',$dmp)) {if missing($tb_dmp{$opt}) {if testFile('fr',catFile($ORADDC_DIR,$opt)) var $DDC_SQL{lastFile()} = concat('Custom Script ',$opt) else die 'Invalid dump (',$opt,')' } var $DDC_DMP{$opt} = $tb_dmp{$opt} } } # Validate the time constants if !and(isNumber($DDC_ASH),expr('>',$DDC_ASH,0)) var $DDC_ASH = 15 if !and(isNumber($DDC_EVT_SLP),expr('>',$DDC_EVT_SLP,0)) var $DDC_EVT_SLP = 900 if !and(isNumber($DDC_SLP),expr('>',$DDC_SLP,0)) var $DDC_SLP = 120 if !and(isNumber($DDC_TRC_SLP),expr('>',$DDC_TRC_SLP,0)) var $DDC_TRC_SLP = 30 # Execute the command call set_passwords() run DDCrun() } elsif compare('eq',$cmd,'C') {output ON,concat($RPT_GROUP,'_TMP_ddcup_',field(':',-1,$ORACLE_SID),'.tmp') write close } elsif compare('eq',$cmd,'D') {if and(match($arg,'^\d{10}$'),\ testFile('fr',catFile($RPT_DIRECTORY,'extern',\ concat($RPT_GROUP,'_DDC_',$arg,'_log.txt')))) call purge('E',concat('DDC_',$arg,'_'),-1,0) } elsif compare('eq',$cmd,'E') run DDCstat(cond(isNumber($arg),$arg,30),$pid) elsif compare('eq',$cmd,'F') {var ($slp,$max) = split(',',$arg) if !and(isNumber($slp),expr('>',$slp,0)) var $slp = 60 if !and(isNumber($max),expr('>',$max,0)) var $max = 120 call auto_wakeup($slp,$max) } } else {call requestSetting('TLoraddc') var $cmd = getSetting('DDC_COMMAND','A') if match($cmd,'^(A|(B))$') {var (undef,$WAIT) = last # Get the request details if getSetting('DDC_BGP_LIST') {loop $nam (split(',',last)) {next missing($tb_bgp{$nam}) var $DDC_BGP{$nam} = $tb_bgp{$nam} } } # Parse the process selection directives var $DDC_PID = getSetting('DDC_PID_MENU','-') var $val = getSetting('DDC_PID_LIST') if defined($val) var @DDC_PID = split(',',$val) var $DDC_USR = getSetting('DDC_USR') if getSetting('DDC_PRC_RPT') {loop $opt (split(',',last)) var $DDC_PRC{$opt} = $tb_prc{$opt} } elsif or(compare('ne',$DDC_PID,'-'),scalar(%DDC_BGP)) var $DDC_PRC{'T'} = $tb_prc{'T'} if getSetting('DDC_DMP') {loop $opt (split(',',last)) {if or(compare('ne',$opt,'D'),getSetting('DDC_ASH_USE')) var $DDC_DMP{$opt} = $tb_dmp{$opt} } } # Get the time constants var $DDC_ASH = getSetting('DDC_ASH_DURATION',15) var $DDC_EVT_SLP = getSetting('DDC_EVENT_SLEEP',900) var $DDC_SLP = getSetting('DDC_SLEEP',120) var $DDC_TRC_SLP = getSetting('DDC_TRACE_SLEEP',30) # Generate the argument string var $DDC_CMD = join(':',$cmd,\ join(',',keys(%DDC_BGP)),\ join('/',$DDC_PID,join(',',$DDC_USR,@DDC_PID)),\ join(',',keys(%DDC_PRC)),\ join(',',keys(%DDC_DMP)),\ join(',',$DDC_SLP,$DDC_TRC_SLP,$DDC_ASH,$DDC_EVT_SLP)) # Execute the command call set_passwords() run DDCrun() } elsif compare('eq',$cmd,'C') {output ON,concat($RPT_GROUP,'_TMP_ddcup_',field(':',-1,$ORACLE_SID),'.tmp') write close } elsif compare('eq',$cmd,'D') {if getSetting('DDC_DELETE') call purge('E',concat('DDC_',last,'_'),-1,0) } elsif compare('eq',$cmd,'E') run DDCstat(getSetting('DDC_TOLERANCE',30)) elsif compare('eq',$cmd,'F') call auto_wakeup(getSetting('DDC_AUTO_SLEEP',60),\ getSetting('DDC_AUTO_TIMEOUT',120)) } =head1 WARNING For some of the operations, this tool can have negative affects on system performance. If you are not sure about the options you intend to use, you should ensure that you are working under the guidance of Oracle Support. This tool parallelizes its tasks. It launches one background process per report and per specified process. Therefore, it requires a Perl version where 'fork' is implemented and not disabled in the setup. =head1 SEE ALSO See also L, L, L. =begin credits =over 10 =item RDA 4.11: Clive Bostock. =item RDA 4.12: Clive Bostock. =item RDA 4.15: Clive Bostock. =item RDA 4.16: Clive Bostock. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2009, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut