分析Oracle database alert log

1.本地alert log

# ora_err.rb
# usage: ruby ora_err.rb < alert.log > ora_err.txt
require 'parsedate'

delimiter = "\t" 
date_mask = '%Y-%m-%d %H:%M:%S'

months = { 'Jan'=>1, 'Feb'=>2, 'Mar'=>3, 'Apr'=>4, 'May'=>5, 'Jun'=>6,
           'Jul'=>7, 'Aug'=>8, 'Sep'=>9, 'Oct'=>10, 'Nov'=>11, 'Dec'=>12 }

class OraErr
  attr_reader :sqlcode, :begin_time, :sqlerrm
  def initialize(sqlcode,begin_time,sqlerrm)
    @sqlcode = sqlcode
    @begin_time = begin_time
    @sqlerrm = sqlerrm
  end
end   

this_log = nil
last_log = nil
this_time = nil

thisTime = nil

$stdin.each() { |line|

  next if $stdin.eof

  line.chomp!

  if line =~ /^(Sun|Mon|Tue|Wed|Thu|Fri|Sat)/ then

    thisTime = Time.local(*ParseDate.parsedate(line))

  elsif line =~ /^ORA-([0-9]+)/

    sqlcode = $1
    sqlerrm = line

    this_log = OraErr.new(sqlcode,thisTime,sqlerrm)

    if (last_log!=nil)

      # print the last log
      print \
          last_log.begin_time.strftime(date_mask),delimiter,
          last_log.sqlerrm,"\n" 

    end

    last_log = this_log
    this_log = nil

  end

}

2.遠端alert log

#scan_remote.rb
#usage:ruby scan_remote.rb > scan.txt
require 'rubygems'
require 'net/ssh'
require 'parsedate'

delimiter = "\t" 
date_mask = '%Y-%m-%d %H:%M:%S'

months = { 'Jan'=>1, 'Feb'=>2, 'Mar'=>3, 'Apr'=>4, 'May'=>5, 'Jun'=>6,
           'Jul'=>7, 'Aug'=>8, 'Sep'=>9, 'Oct'=>10, 'Nov'=>11, 'Dec'=>12 }

class OraErr
  attr_reader :sqlcode, :begin_time, :sqlerrm
  def initialize(sqlcode,begin_time,sqlerrm)
    @sqlcode = sqlcode
    @begin_time = begin_time
    @sqlerrm = sqlerrm
  end
end   

this_log = nil
last_log = nil
this_time = nil

thisTime = nil

Net::SSH.start('192.168.0.1', 'oracle',
                   :password=>'oraclepass') do |session|
  cmd = 'cat /orasys/admin/sid/bdump/alert_sid.log'
  #session.exec(cmd).each()
  session.open_channel do |channel|
    channel.on_data do |ch, data|
      data.each_line do |line|
        line.chomp!        
        if line =~ /^(Sun|Mon|Tue|Wed|Thu|Fri|Sat)/ then    
          begin
            thisTime = Time.local(*ParseDate.parsedate(line)) 
          rescue # 忽略無效日期
            next
          end    
          #print \
          #ParseDate.parsedate(line) ,line,"\n" 
        elsif line =~ /^ORA-([0-9]+)/
          sqlcode = $1
          sqlerrm = line
          this_log = OraErr.new(sqlcode,thisTime,sqlerrm)    
          if (last_log!=nil)      
            # print the last log
            print \
              last_log.begin_time.strftime(date_mask),delimiter,
              last_log.sqlerrm,"\n"    
          end    
          last_log = this_log
          this_log = nil    
        end # line  
      end # data
    end # channel
    channel.exec cmd
  end # session    
  session.loop
end #NET