# TLora600.ctl: Diagnoses ORA-600 Oracle Internal Errors
# $Id: TLora600.ctl,v 1.12 2015/05/29 06:11:26 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/TLora600.ctl,v 1.12 2015/05/29 06:11:26 RDA Exp $
#
# Change History
# 20150527  KRA  Improve the documentation.

=head1 NAME

DB:TLora600 - Diagnoses ORA-600 Oracle Internal Errors

=head1 DESCRIPTION

ORA-600 errors are raised from the kernel code of the Oracle Database software
when an internal inconsistency is detected or an unexpected condition is
met. These types of error conditions are not necessarily bugs because the
error condition may be caused by problems with the operating system, lack of
resources, hardware failures, and so on. Whenever an ORA-600 error is raised,
a trace file is generated in C<USER_DUMP_DEST> or C<BACKGROUND_DUMP_DEST>
depending on whether the error was caught in a user or a background
process. The error is written in the alert log with the name of the trace
file also. This trace file contains vital information about the factors that
led to the error condition.

=head1 REQUIREMENTS

Must have a valid Oracle Database trace file.

=head1 USAGE

This tool takes the trace file as input and can be used in two ways:

=over 3

=item a)

Runs through RDA setup. It requests the user to input the absolute path of
the trace file.

 <rda> -vT ora600

 <sdci> -v run ora600

=item b)

Runs from the command line. The input can be given in the command line using
the following syntax:

 <rda> -vT ora600:absolute_path_of_trace_file

 <sdci> -v run ora600 absolute_path_of_trace_file

=back

The tool analyzes the trace file, generates reports, and displays key
information about the results. The reports can be viewed in the C<External
Data Collections> submenu of the final RDA output package also. If it is not
disabled, you can refresh that section by the following command:

 <rda> -vCRP LOAD

 <sdci> -v collect -RP LOAD

The following reports can be produced:

=head2 Summary Report

Obtains the ORA-600 arguments, call stack details, current SQL statement, the
corresponding My Oracle Support document, and the heap error summary.

=head2 Header Report

Displays the header of the input trace file.

=head2 Heap Report

For a heap dump error, it obtains the heap dump details, corrupted extent
details, and memory dump details.

=cut

use Log


options 'p:z:'

section tool

echo tput('bold'),'Diagnosing ORA-600 Oracle internal errors ...',tput('off')

# Initialization
var $MULTI = ${RUN.REQUEST.B_ORA600_MULTI:true}

var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'

# Define the analysis macro
macro treat_error
{var ($top,$ign,$cln,$ver,$typ,$pos,$min,$max,\@err,\@lim,\@det) = @arg
 import $TOC,$TOP
 keep $det,$TOC,$TOP

 # Prepare the heap report
 var @hep = ()
 incr $det

 # Analyze the ORA-600/ORA-7445 errors and collect 5 arguments
 prefix
 {if !isCreated()
  {write '---+!! Assessment Summary'
   write $TOC
  }
  write '---++ Findings for ','ORA-',$typ,' [',$arg,']'
  write '|*Finding*|*Value*|'
  echo
 }
 loop $err (@err)
 {var ($cod,$arg) = split('\s+\133',$err,2)
  var ($cod) = match($cod,'ORA-0+(600|7445)')
  var (undef,@nxt) = split('\133',$arg)
  var $arg = field('\135',0,$arg)
  var ($cnt,$oth) = (5)
  loop $nxt (@nxt)
  {break !$cnt
   decr $cnt
   if field('\135',0,$nxt)
    var $oth = join(', ',$oth,last)
  }
  write '|Error Code|',$cod,'|'
  echo 'Error Code: ',$cod
  if $arg
  {write '|First&nbsp;Argument|``',$arg,'`` |'
   echo "- First Argument:  \001",$arg
  }
  if $oth
  {write '|Other&nbsp;Arguments|``',$oth,'`` ||'
   echo "- Other Arguments: \001",$oth
  }
 }
 unprefix

 # Get the current SQL statement
 var $sql = undef
 call setPos('TRC',$pos)
 if grepBuffer('TRC','Current SQL statement','ifr',undef,$min,$max)
 {while getLine('TRC')
  {var $lin = trim(chomp(last))
   next !$lin
   break match($lin,'^(---|\*\*\*)')
   var $sql = join('%BR%',$sql,$lin)
  }
 }

 # Analyze the stack trace
 var $pat = '- Call Stack Trace'
 call setPos('TRC',$pos)
 if or(match($lin,$pat),grepBuffer('TRC',$pat,'ifr',undef,$min,$max))
 {# Skip some lines to get the start of the stack trace
  call grepBuffer('TRC','------------','fr',undef,$min,$max)

  # Parse the stack dump
  var ($cur,@stk) = ('')
  var $ref = nvl(field('[\(\[\+\:]',0,$arg),'')
  while getLine('TRC')
  {var $lin = chomp(last)
   next match($lin,'^(\s|Cannot find symbol)')
   break match($lin,'-----')
   var ($lin,$chr) = split('\s+',substr($lin,0,22),2)
   if $chr
   {var $cur = nvl(field('[\(\[\+\:]',0,$cur),'')
    if or(@stk,compare('eq',$cur,$ref),not(match($cur,$ign)))
     call push(@stk,$cur)
    var $cur = ''
   }
   var $cur = concat($cur,$lin)
  }
  if $cur
  {var $cur = nvl(field('[\(\[\+\:]',0,$cur),'')
   if or(@stk,compare('eq',$cur,$ref),not(match($cur,$ign)))
    call push(@stk,$cur)
  }
 }

 # Complete summary along with stack trace details
 if @stk
 {var (@tbl,@bot,$prv) = last
  var @ovw = splice(@tbl,0,${RUN.REQUEST.N_STK_COUNT:4})
  while ?$fct = pop(@tbl)
  {next or(compare('eq',$cur = substr($fct,0,3),$prv),\
           and(not(@bot),match($fct,$cln)))
   call unshift(@bot,$fct)
   var $prv = $cur
  }
  if @bot
   call push(@ovw,'...',@bot)
  write '|Call Stack Overview|``',join('%BR%',@ovw),'`` |'
  if ${RUN.REQUEST.B_STK_DETAILS}
   write '|Call Stack|``',join('%BR%',@stk),'`` |'
 }
 if $sql
  write '|Current SQL Statement|``',$sql,'``|'
 else
  write '|Current SQL Statement|No Current SQL statement found in trace file|'

 # Heap Error Analysis
 var ($off,$pos) = (0)
 var $pat = concat('Internal heap ERROR ',verbatim($arg),' ')
 loop $lin (@det)
 {incr $off
  next !match($lin,$pat)
  var $pos = field(':',0,$lin)
  break
 }
 if $pos
 {var $lim = $lim[$off]
  call setPos('TRC',$pos)
  var (undef,$adr) = match($lin,'addr=(0x)?(\S+)')
  if $adr
  {var ($com,$typ,@ext) = ()

   # Start the heap report
   suspend summary
   report concat('heap',$det)
   write '---+!! Assessment Details / Heap Error Analysis'
   write '---### ',match($lin,'\*\s*([^\*]+)\*')
   write $TOC

   # Extract the heap dump description
   var ($dmp) = grepBuffer('TRC','HEAP DUMP','fr',undef,0,$lim)
   var ($nam) = match($dmp,'name="([^"]*)"')
   write '---++ Heap "',$nam,'"'
   write '<verbatim>'
   write $dmp
   while getLine('TRC')
   {var $lin = chomp(last)
    break match($lin,'^(\*\*\*|EXTENT)')
    write $lin
   }
   write '</verbatim>'
   write $TOP

   # Extract chunk details
   if match($lin,'^EXTENT')
   {macro get_details
     return match($arg[0],'sz=\s+\d+\s+(.*?)\s*"([^"]*)')

    # Search for the corresponding extent
    var ($off,$pat,@ext) = (0,concat('\b(0x)?0*',$adr,'\b'),$lin)
    while getLine('TRC')
    {var $lin = chomp(last)
     break match($lin,'^(Total|\*\*\*)')
     if match($lin,'^EXTENT')
     {break $off
      var (@ext) = ($lin)
      next
     }
     if match($lin,$pat)
     {var $off = scalar(@ext)
      var ($typ,$com) = get_details($lin)
     }
     call push(@ext,$lin)
    }

    # Treat the extent
    if $off
    {# Resolve type for adjacent lines
     if match($ext[$off],' sz= ')
     {if !defined($typ)
       var ($typ,$com) = get_details($ext[expr('-',$off,1)])
      if !defined($typ)
       var ($typ,$com) = get_details($ext[expr('+',$off,1)])
      var $ttl = '---++ Chunk Comments around Chunk '
     }
     else
      var $ttl = '---++ Extent Referring to 0x'

     # Report it
     write $ttl,$adr
     write '<verbatim>'
     loop $ext (@ext)
      write $ext
     write '</verbatim>'
     write $TOP
    }
    else
     var @ext = ()
   }

   # Extract memory dump details around the corrupted memory address
   call setPos('TRC',$pos)
   if grepBuffer('TRC',\
     concat('(Dump of memory around addr |Argument\/Register addr=)(0x)?0*',\
            $adr,'\b'),\
     'fr',undef,0,$lim)
   {var ($lin) = (last)
    write '---++ Memory Dump around 0x',$adr
    write '<verbatim>'
    write $lin
    while getLine('TRC')
    {var $lin = chomp(last)
     break match($lin,\
                 '(^\*{3}|Argument\/Register addr=|End of Call Stack Trace)')
     write $lin
    }
    write '</verbatim>'
    write $TOP
   }

   # Render the heap report and add findings in the summary report
   var $hep = renderFile()
   call push(@hep,$hep)
   resume summary
   if $MULTI
    write '|Heap&nbsp;Error&nbsp;Address|[[',basename($hep),\
          '][_blank][0x',$adr,']] (Click Link for details)|'
   if scalar(@ext)
    write '|Corrupted Extent|',$ext[0],'|'
   if $typ
    write '|Chunk Type|',trim($typ),'|'
   if $com
    write '|Sub-Heap Comment|',trim($com),'|'
  }
 }

 # Extract My Oracle Support document details
 echo 'Applicable My Oracle Support document: '
 write '---++!! Applicable My Oracle Support Document(s)'
 prefix
  write '|*Document*|*Title*|'
 loop $xml (xmlFind($top,concat(\
   'ora600/argno id="1"/argument value="^',verbatim(lc($arg)),'$"/note')))
 {var $not = xmlData($xml)
  var $ttl = xmlData(xmlFind($top,concat(\
    'metalink/note docid="^',verbatim($not),'$"')))
  var $fnd = 'F'
  write '|[[https://support&#46;oracle&#46;com/rs?type=doc&amp;id=',$not,\
        '][_blank][',$not,']]|',$ttl,' |'
  echo "  ",$not," (\001",$ttl,")"
 }
 if !hasOutput(true)
 {var $fnd = 'M'
  write '**Note:** \
         No My Oracle Support documents are available for this error.%BR%'
  echo '  No My Oracle Support documents are available for this error'
 }

 # Include My Oracle Support Argument and Stack search
 prefix
 {write '---++!! My Oracle Support Search'
  write '|*Search*|'
 }
 if $arg
 {var $url = concat('https://support&#46;oracle&#46;com/\
                     rs?type=search&amp;sources=ALLSOURCES&amp;term=',\
                    encode($arg,true))
  write '|[[',$url,'][_blank][Argument Search]]|'
 }
 if grep(@stk,$ign,'v')
 {var @qry = last
  var @qry = splice(@qry,0,4)
  var $url = concat('https://support&#46;oracle&#46;com/\
                     rs?type=search&amp;sources=ALLSOURCES&amp;term=',\
                    encode(join('%20',concat('ORA-',$typ),last),true))
  write '|[[',$url,'][_blank][Stack Search: ',join(',',@qry),']]|'
 }
 if hasOutput(true)
  write '**Note:** You must be already logged on to My Oracle Support to \
         use these links%BR%'
 write $TOP

 # Indicate the execution and its success in the event log
 call log('DB','DB:TLora600','TRC',$fnd,$arg,$ver)

 # Return the heap report name when applied
 return @hep
}

# Set the abbreviation
call setAbbr('DB_ORA600_')

# Obtain the input trace file
if $arg[0]
 var $fil = last
else
{call requestInput('TLora600')
 var $fil = ${RUN.REQUEST.F_TRACE}
}

# Treat the file
if isArchived($opt{'z'})
 call selectIndex(last,$opt{'p'})
if !length($fil)
 echo 'Missing input file'
elsif !createBuffer('TRC','V',$fil)
 echo 'The input file does not exist or is not readable'
else
{# Set a report prefix and initiate the summary report
 var ($pid) = match($fil,'(\d+)\.trc$',true)
 call setPrefix(concat('p',$pid))
 report summary

 # Treat the file when errors are found
 if grepBuffer('TRC','(Internal heap ERROR|^ORA-0(0600|7445):)','o')
 {var ($max,@hit,@hdr,@hep,$ign) = (inputLine('TRC'),last)

  # Extract the header information
  call setPos('TRC')
  while getLine('TRC')
  {var $lin = chomp(last)
   break match($lin,'^\s*$')
   if expr('>',inputLine(),24)
   {echo 'The trace file might be corrupt. Check it and try again'
    return
   }
   call push(@hdr,$lin)

   # Determine the Oracle version
   next $ver
   if match($lin,'^(Oracle(7|8i?|9i)?)\s')
    var $ver = check($lin,'^Oracle7\s',  '7',\
                          '^Oracle8\s',  '8.0',\
                          '^Oracle8i\s', '8.1',\
                          '^Oracle9i\s', check($lin,'\s9\.2\.','9.2','9.0'),\
                          '^Oracle\s',   check($lin,'\s10\.2\.','10.2',\
                                                    '\s10\.1\.','10.1',\
                                                    '10'))
  }

  # Load the data file
  var $loc = catFile(${CUR.D_DIRECTORY},'TLora600.txt')
  var $top = xmlLoadFile($loc)
  loop $xml (xmlFind($top,'stkign/func'))
   var $ign = join('|',$ign,verbatim(xmlData($xml)))
  var $ign = join('|',$ign,'$')
  var $ign = concat('^(',$ign,')')
  loop $xml (xmlFind($top,'stkcln/func'))
   var $cln = join('|',$cln,verbatim(xmlData($xml)))
  var $cln = concat('^(',$cln,')')

  # Determine the trace regions and treat the errors
  var (@det,@err,@lim) = ()
  loop $lin (@hit)
  {if match($lin,'^((\d*)\|\d+):ORA-0+(600|7445):')
   {var ($off,$num,$cod) = (last)

    # Check for continuous errors
    if $flg
    {incr $nxt
     if expr('==',$num,$nxt)
     {if and(compare('eq',$typ,'7445'),compare('eq',$cod,'600'))
       var ($typ,@err) = ('7445&amp;600',@err,$lin)
      next
     }
     call push(@lim,$min)
     call push(@hep,\
       treat_error($top,$ign,$cln,$ver,$typ,$pos,$min,$num,\@err,\@lim,\@det))
     var (@det,@lim) = ()
    }

    # Open the next region
    var ($flg,$min,$nxt,$pos,$typ,@err) = (true,$num,$num,$off,$cod,$lin)
   }
   else
   {var $off = field('\|',0,$lin)
    if $flg
    {call push(@lim,$min)
     var ($flg,@hep,@det,@lim) = (false,@hep,\
       treat_error($top,$ign,$cln,$ver,$typ,$pos,$min,$off,\@err,\@lim,\@det))
    }
    call push(@det,$lin)
    call push(@lim,$off)
   }
  }
  if $flg
  {call push(@lim,$min)
   call push(@hep,\
     treat_error($top,$ign,$cln,$ver,$typ,$pos,$min,$max,\@err,\@lim,\@det))
  }
 }

 # Close the buffer
 call deleteBuffer('TRC')

 # Produce the header report and display the generated files
 if isCreated(true)
 {echo
  call getGroupFile('D_CWD',renderFile())
  echo 'Summary file: ',last
  echo 'Related Files:'

  report header
  write '---+ Trace File Header Information'
  write '---## Information Taken from ',encode($fil)
  write '<verbatim>'
  loop $lin (@hdr)
   write $lin
  write '</verbatim>'
  call getGroupFile('D_CWD',renderFile())
  echo '- Trace Header File: ',last

  loop $hep (@hep)
   echo '- Heap Summary File: ',$hep
 }
 else
  echo 'The trace file does not contain any ORA-600 or ORA-7445 error'
}

=head1 SEE ALSO

L<RDA:DCload|collect::RDA:DCload>

=begin credits

=over 10

=item RDA 4.6:  Grant Hayden.

=item RDA 4.7:  Grant Hayden.

=item RDA 4.18: Grant Hayden.

=item RDA 4.24: Takeyoshi Sasaki.

=item RDA 4.30: Grant Hayden.

=item RDA 8.03: Girisha Madegowda.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut