# DBinfo.def: Collects Key Information about the Database
# $Id: DBinfo.def,v 1.35 2009/08/08 08:23:47 mschenke Exp $
# ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/DBinfo.def,v 1.35 2009/08/08 08:23:47 mschenke Exp $
#
# Change History
# 20090808  DEM  Improve ADR handling.

=head1 NAME

DBinfo - Collects Key RDBMS Information

=head1 DESCRIPTION

This module collects key RDBMS information that is required for
database-related modules.

The following macros are available:

=cut

# Make the module persistent and share macros
keep $KEEP_BLOCK,@SHARE_MACROS
var @SHARE_MACROS = ('find_base','find_dest','find_diag',\
                     'get_adr_base','get_adr_home','get_alert_name',\
                     'get_db_version','get_bdump','get_cdump','get_udump',\
                     'separator')

# Initialisation
keep $ADR_BASE,$ADR_HOME,$DB_FIND,$DB_INFO,$DB_VERSION,\
     $BDUMP_DIR,$CDUMP_DIR,$UDUMP_DIR
     
if !$DB_INFO
{var $DB_INFO = true

 # Define some variables that are shared between macros
 var $ADR_BASE
 var $ADR_HOME
 var $DB_FIND
 var $DB_VERSION
 var $BDUMP_DIR
 var $CDUMP_DIR
 var $UDUMP_DIR
}

# Define the separator macro
macro separator
{var $cur = $arg[0]
 import $TOC,$TOP,$TTL,@DBG,@HDR,@TTL,@TXT
 keep $cur,$sep,$TOC,$TOP,$TTL,@DBG,@HDR,@TTL,@TXT
 if $cur
 {if hasOutput(true)
   write $TOP
  var $sep = $cur
  if $DBG[$sep]
   debug $DBG[$sep]
  prefix
  {if !isCreated()
   {write $TTL
    write $TOC
   }
   if $TTL[$sep]
    write $TTL[$sep]
   if $TXT[$sep]
    write $TXT[$sep],'%BR% '
   if $HDR[$sep]
    write $HDR[$sep]
  }
  return 1
 }
 if getSqlMessage()
  write last,'%BR%'
 if hasOutput(true)
  write $TOP
 if isCreated()
  toc nvl($arg[2],2),':[[',getFile(),'][rda_report][',$arg[1],']]'
}

=head2 find_base()

Gets and returns the Oracle base directory.

=cut

macro find_base
{import $ORACLE_HOME

 debug ' Inside DBinfo, finding the Oracle base directory '
 var $ORACLE_BASE = undef

 if getSetting('DATABASE_INSTALLED')
 {set $sql
  {SELECT '|oracle_base|' || b.ksppstvl || '|'
  " FROM x$ksppi a,x$ksppsv b
  " WHERE a.indx = b.indx
  "   AND LOWER(a.ksppinm) LIKE '%oracle_base%';
  }
  if loadSql($sql)
   var $ORACLE_BASE = get_param('\|oracle_base\|')
 }
 
 if !?$ORACLE_BASE
 {if getEnv('ORACLE_BASE')
   var $ORACLE_BASE = last
  elsif isVms()
   var $ORACLE_BASE = getEnv('ORA_ROOT')
  else
  {var $ORACLE_BASE = dirname($ORACLE_HOME)
   while true
   {break testDir('d',catDir($ORACLE_BASE,'admin'))
    var $dir = dirname($ORACLE_BASE)
    break compare('eq',$dir,$ORACLE_BASE)
    var $ORACLE_BASE = $dir
   }
  }
 }
 return $ORACLE_BASE
}

=head2 find_dest([$pre])

Determines the background, core, and user destinations. It checks v$parameter
first. If the information is not available there, then the macro looks in pfile 
or in other known places.

On Oracle Database 11g and later, it retrieves the ADR base and home
directories.

=cut

# Define a macro to extract a parameter value
macro get_param
{import $ORACLE_HOME
 var $str = trim(field('\|',2,grepLastSql($arg[0],'f')),"'")
 var $str = replace($str,'\?',$ORACLE_HOME)
 var $str = replace($str,'\$ORACLE_HOME',$ORACLE_HOME)
 var $str = replace($str,'\${ORACLE_HOME}',$ORACLE_HOME)
 var $str = replace($str,'%ORACLE_HOME%',$ORACLE_HOME)
 return $str
}

# Get the dump directories
macro find_dest
{var ($pre) = @arg
 import $ORACLE_HOME,$ORACLE_SID,$DB_FIND,$BDUMP_DIR,$CDUMP_DIR,$UDUMP_DIR,\
        $DIAG_DIR,$ADR_BASE,$ADR_HOME,$MACHINE_NAME
 var $DB_FIND = true

 debug ' Inside DBinfo, finding dump destinations'
 set $sql
 {SELECT '|' || p.name || '|' || p.value || '|'
 " FROM v$parameter p
 " WHERE p.name LIKE '%_dump_dest'
 "    OR p.name = 'diagnostic_dest'
 "    OR p.name = 'spfile';
 "SELECT '|' || p.name || '|' || p.value || '|'
 " FROM v$diag_info p
 " WHERE p.name = 'ADR Base'
 "    OR p.name = 'ADR Home';
 "SELECT '|oracle_base|' || b.ksppstvl || '|'
 " FROM x$ksppi a,x$ksppsv b
 " WHERE a.indx = b.indx
 "   AND lower(a.ksppinm) LIKE '%oracle_base%';
 }

 if loadSql($sql)
 {var $ORACLE_BASE = get_param('\|oracle_base\|')
  var $BDUMP_DIR   = get_param('\|background_dump_dest\|')
  var $CDUMP_DIR   = get_param('\|core_dump_dest\|')
  var $UDUMP_DIR   = get_param('\|user_dump_dest\|')
  var $DIAG_DIR    = get_param('\|diagnostic_dest\|')
  var $ADR_BASE    = get_param('\|ADR Base\|')
  var $ADR_HOME    = get_param('\|ADR Home\|')
 }
 else
  var ($ORACLE_BASE,$BDUMP_DIR,$CDUMP_DIR,$UDUMP_DIR,$DIAG_DIR,\
       $ADR_BASE,$ADR_HOME) = ()

 #-----------------------------------------------------------------------------
 # If we do not have a dump directory yet, then it's because we either could
 # not read the vparameter outputfile, or we could not connect to the database.
 # Therefore, we should not have found the cdump or udump directories either.
 # Follow the same series of searches to get cdump and udump as we do for bdump.
 #-----------------------------------------------------------------------------
 if !$BDUMP_DIR
 {# Determine if pfile should be considered
  if grepLastSql($sql,'\|spfile\|','f')
   var $use = false
  elsif getSetting(concat($pre,'PFILE_LOCAL'))
  {var $fil = getSetting(concat($pre,'PFILE_LOCATION'))
   var $use = testFile('r',$fil)
  }
  else
   var $use = false

  # Look in pfile
  if $use
  {macro get_dump
   {var ($fil,$key) = @arg
    import $ORACLE_HOME
    var $str = trim(value(grepFile($fil,$key,'fi')),"'")
    var $str = replace($str,'\?',$ORACLE_HOME)
    var $str = replace($str,'\$ORACLE_HOME',$ORACLE_HOME)
    var $str = replace($str,'\${ORACLE_HOME}',$ORACLE_HOME)
    var $str = replace($str,'%ORACLE_HOME%',$ORACLE_HOME)
    return $str
   }

   # Check in the INIT.ORA file
   debug ' Inside DBinfo, looking for dump dir in ORACLE_HOME'
   var $BDUMP_DIR = get_dump($fil,'^[^#]*background_dump_dest')
   var $CDUMP_DIR = get_dump($fil,'^[^#]*core_dump_dest')
   var $UDUMP_DIR = get_dump($fil,'^[^#]*user_dump_dest')

   # If any directory is still empty, check for included file and scan it
   if !and($BDUMP_DIR,$CDUMP_DIR,$UDUMP_DIR)
   {var $treated{$fil} = 1

    # Treat included file, ignoring comments
    debug ' Inside DBinfo, grepping for IFILE/SPFILE for dump directories'
    while value(grepFile($fil,'^[^#]*(i|sp)file','fi'))
    {var $fil = trim(last)
     var $fil = replace($fil,"'",'',true)
     var $fil = replace($fil,'\?',$ORACLE_HOME)
     var $fil = replace($fil,'\$ORACLE_HOME',$ORACLE_HOME)
     var $fil = replace($fil,'\${ORACLE_HOME}',$ORACLE_HOME)
     var $fil = replace($fil,'%ORACLE_HOME%',$ORACLE_HOME)
     break exists($treated{$fil})
     if !$BDUMP_DIR
      var $BDUMP_DIR = get_dump($fil,'^[^#]*background_dump_dest')
     if !$CDUMP_DIR
      var $CDUMP_DIR = get_dump($fil,'^[^#]*core_dump_dest')
     if !$DDUMP_DIR
      var $UDUMP_DIR = get_dump($fil,'^[^#]*user_dump_dest')
     var $treated{$fil} = 1
    }
   }
  }
 }

 # Determine the ORACLE_BASE
 var @dir = ()
 if !?$ORACLE_BASE
 {if getEnv('ORACLE_BASE')
   var $ORACLE_BASE = last
  elsif isVms()
  {var $ORACLE_BASE = getEnv('ORA_ROOT')
   if getEnv('ORA_DUMP')
    var @dir = (last)
   elsif testDir('d',catDir($ORACLE_BASE,concat('db_',$ORACLE_SID),'trace'))
    var @dir = (lastDir())
  }
  else
  {var $ORACLE_BASE = dirname($ORACLE_HOME)
   while true
   {break testDir('d',catDir($ORACLE_BASE,'admin'))
    var $dir = dirname($ORACLE_BASE)
    break compare('eq',$dir,$ORACLE_BASE)
    var $ORACLE_BASE = $dir
   }
  }
 }

 # If we still do not have the directories, try some well known spots
 if !$BDUMP_DIR
 {debug ' Inside DBinfo, still no dump dir, look in basic spots'
  var $log = get_alert_name()
  loop $dir (catDir($ORACLE_BASE,'admin',$ORACLE_SID,'bdump'),\
             catDir($ORACLE_HOME,'rdbms','log'),\
             @dir)
  {if testFile('r',catFile($dir,$log))
   {var $BDUMP_DIR = $dir
    break
   }
  }
 }
 if !$CDUMP_DIR
 {debug ' Inside DBinfo, still no core dump dir, look in basic spots'
  loop $dir (catDir($ORACLE_BASE,'admin',$ORACLE_SID,'cdump'),\
             catDir($ORACLE_HOME,'dbs'),\
             @dir)
  {if testDir('d',$dir)
   {var $CDUMP_DIR = $dir
    break
   }
  }
 }
 if !$UDUMP_DIR
 {debug ' Inside DBinfo, still no user dump dir, look in basic spots'
  loop $dir (catDir($ORACLE_BASE,'admin',$ORACLE_SID,'udump'),\
             catDir($ORACLE_HOME,'rdbms','log'),\
             @dir)
  {if testDir('d',$dir)
   {var $UDUMP_DIR = $dir
    break
   }
  }
 }
 if !$DIAG_DIR
 {debug ' Inside DBinfo, still no diagnostic dir, look in basic spots'
  if testDir('d',catDir($ORACLE_BASE,'diag'))
   var $DIAG_DIR = lastDir()
 }
}

=head2 find_diag()

Finds the diagnostic directories.

=cut

macro find_diag
{import $DB_FIND,$DIAG_DIR,$ORACLE_SID
 if !$DB_FIND
  call find_dest()
 if $DIAG_DIR
 {loop $dir (grepDir($DIAG_DIR,concat('^',quote($ORACLE_SID),'$'),'ir'))
  {if and(testFile('fr',catFile($dir,'alert','log.xml')),\
          testDir('d',catDir($dir,'cdump')),\
          testDir('d',catDir($dir,'trace')))
    return $dir
  }
 }
 return undef
}

=head2 get_adr_base()

Gets and returns the ADR base directory.

=cut

macro get_adr_base
{import $DB_FIND,$ADR_BASE
 if !$DB_FIND
  call find_dest()
 return $ADR_BASE
}

=head2 get_adr_home()

Gets and returns the ADR home directory.

=cut

macro get_adr_home
{import $DB_FIND,$ADR_HOME
 if !$DB_FIND
  call find_dest()
 return $ADR_HOME
}

=head2 get_alert_name()

Gets the name of the alert log.

=cut

macro get_alert_name
{import $MACHINE_NAME,$ORACLE_SID

 if isVms()
  return concat($MACHINE_NAME,'_',$ORACLE_SID,'_alert.log')
 return concat('alert_',$ORACLE_SID,'.log')
}

=head2 get_bdump()

Gets and returns the background dump destination.

=cut

macro get_bdump
{import $DB_FIND,$BDUMP_DIR
 if !$DB_FIND
  call find_dest()
 return $BDUMP_DIR
}

=head2 get_cdump()

Gets and returns the core dump destination.

=cut

macro get_cdump
{import $DB_FIND,$CDUMP_DIR
 if !$DB_FIND
  call find_dest()
 return $CDUMP_DIR
}

=head2 get_udump()

Gets and returns the user dump destination.

=cut

macro get_udump
{import $DB_FIND,$UDUMP_DIR
 if !$DB_FIND
  call find_dest()
 return $UDUMP_DIR
}

=head2 get_db_version($flg,$dft)

Determines the database version. When the flag is set, it forces a new
detection but does not save the results. A default value can be provided as a
second argument.

=cut

# Determine which version the database says we are dealing.
macro get_db_version
{var ($flg,$ver) = @arg

 debug ' Inside DBinfo, gathering product versions'

 # Determine the version from a previous detection
 if !$flg
 {import $DB_VERSION
  if $DB_VERSION
   return $DB_VERSION
 }

 # Determine the database version
 set $sql
 {SELECT '|' || v.banner || '|'
 "  FROM v$version v;
 }
 call loadSql($sql)
 var ($lin) = grepLastSql('Oracle','f')
 var $ver = check($lin,'^\|Oracle7\s',  '7',\
                       '^\|Oracle8\s',  '80',\
                       '^\|Oracle8i\s', '81',\
                       '^\|Oracle9i\s', check($lin,'\s9\.2\.','92','90'),\
                       '^\|Oracle\s',   check($lin,'\s11\.2\.','112',\
                                                   '\s11\.1\.','111',\
                                                   '\s10\.2\.','102',\
                                                   '\s10\.1\.','101',\
                                                   '10'))

 # Save the database version for a next call
 if !$flg
  var $DB_VERSION = $ver

 # Return the database version
 return $ver
}

=head1 SEE ALSO

L<S010CFG.def|modules::S010CFG>, L<S200DB.def|modules::S200DB>,
L<S201DBA.def|modules::S201DBA>, L<S204LOG.def|modules::S204LOG>,
L<S205BR.def|modules::S205BR>, L<S230STC.def|modules::S230STC>,
L<S231STM.def|modules::S231STM>, L<S330SSO.def|modules::S330SSO>,
L<S391OWB.def|modules::S391OWB>, L<S392ODM.def|modules::S392ODM>,
L<S402ASM.def|modules::S402ASM>

=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