# TLoraddc.def: Oracle Database Diagnostics Collector # $Id: TLoraddc.def,v 1.11 2008/02/25 13:52:17 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/TLoraddc.def,v 1.11 2008/02/25 13:52:17 mschenke Exp $ # # Change History # 20080222 MSC Improve the documentation. =head1 NAME TLoraddc - Oracle Database Diagnostics Collector =head1 DESCRIPTION In situations where a database is suffering outages or severe loss of service, it may be that Oracle Product Support may request a variety of diagnostics from SYSTEMSTATE dumps to trace files generated by operating system commands. 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 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 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. That 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 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 preconnect 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 approximately thirty seconds and terminates all tracing activity. =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 =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, and 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 arguments =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< 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< 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: -T oraddc:C This is useful when a preconnect is required in situations where 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 of 30 seconds is recommended. 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 =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 timestamp 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 $RPT_DIRECTORY = getSetting('RPT_DIRECTORY','output') var $RPT_GROUP = getSetting('RPT_GROUP', 'RDA') var ($DDC_PID,$DDC_SLP,$DDC_USR,@DDC_BGP,@DDC_PID,%DDC_PRC,%DDC_DMP,$WAIT) = () # Define the validation tables var %tb_bgp = ('ckpt',1,\ 'lgwr',1,\ 'lmon',1,\ 'mman',1,\ 'mmnl',1,\ 'mmon',1,\ 'pmon',1,\ 'qmnc',1,\ 'rbal',1,\ 'reco',1,\ 'smon',1) var %tb_dmp = ('A','Hang Analyze Dump',\ 'C','Locking Contention Report',\ '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',\ '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)' # Treat the command if getSetting('TST_ARGS') {var $DDC_CMD = last var ($cmd,$arg,$pid,$prc,$dmp,$DDC_SLP) = split(':',$DDC_CMD) if match($cmd,'^(A|(B))$') {var (undef,$WAIT) = last # Parse the background process list if $arg {loop $nam (split(',',$arg)) {if missing($tb_bgp{$nam}) die 'Invalid background process name (',$nam,')' var @DDC_BGP = (@DDC_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,'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}) die 'Invalid dump (',$opt,')' var $DDC_DMP{$opt} = $tb_dmp{$opt} } } # Validate the sleep time if !and(isNumber($DDC_SLP),expr('>',$DDC_SLP,0)) var $DDC_SLP = 120 # Execute the command 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) } 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') var @DDC_BGP = split(',',last) # 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)) var $DDC_DMP{$opt} = $tb_dmp{$opt} } # Get the sleep time var $DDC_SLP = getSetting('DDC_SLEEP',120) # Generate the argument string var $DDC_CMD = join(':',$cmd,\ join(',',@DDC_BGP),\ join('/',$DDC_PID,join(',',$DDC_USR,@DDC_PID)),\ join(',',keys(%DDC_PRC)),\ join(',',keys(%DDC_DMP)),\ $DDC_SLP) # Execute the command 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)) } =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. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, 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