ServDoc_0310oracle

Code Index:



NAME

ServDoc_0310oracle - Documentation of your oracle databases


VERSION

$Id: ServDoc_0310oracle,v 1.9 2004/01/03 20:43:25 uherbst Exp $


SYNOPSIS

ServDoc_0310oracle [-h|help] [-v|version] [--debug ORA,intensity]


DESCRIPTION

ServDoc_0310oracle describes your oracle databases.

It depends on your well-documented oratab-file!

This will be documented for every instance in oratab, which is running:

This will be documented once for every oracle installation (that is: every ORACLE_HOME) in oratab


OPTIONS

-h|help
This help.

-v|version
Version

--debug ORA,intensity
Turn on Debugging for that module.

The debug feature for that module is named ``ORA''.


AUTHORS

Ulrich Herbst <Ulrich.Herbst@gmx.de>


#!/usr/bin/perl -w

#----------------------------------------------------------------------

# standard perl modules
use strict;                       # print error about unknown variables ...
use English;                      # long internal variable names;
use FindBin;                      # In which directory is ServDoc itself ?
                                  # There has to be the module and the lib dir!
use lib $FindBin::Bin. "/lib";    # Here are the ServDoc-perl-modules
use vars qw($options);

# our own perl modules
use ServDoc;

######################################################################
#
# Output relevant Variables
#
# Should we delete comments from init.ora in the output ?
#my $delcomments='^#.*$';       # delete comments
my $delcomments='';            # preserve comments
#
# Should we delete blank lines from init.ora in the output ?
my $delblanklines=0;           # 1: delete blank linkes
#                              # 0: don't delete blank lines
######################################################################

#----------------------------------------------------------------------

$options->{Version} = '$Id: ServDoc_0310oracle,v 1.9 2004/01/03 20:43:25 uherbst Exp $';

# We need the cmdline to call the modules with the same debug options.
$options->{cmdline} = join " ", @ARGV;

sub debug { ServDoc_debug( "ORA", $options, shift, shift ); }

$options = &process_cmdline($options);

my $h='Oracle DB%%+';

%{$options->{lang}->{en}}=
  (
   version       => $h. 'SID: %s%%+Installed Versions',
   version_short => 'select * from v$version',
   version_long  => '',

   sga           => $h . 'SID: %s%%+SGA',
   sga_short     => 'select * from v$sga',
   sga_long      => 'The SGA, the System Global Area, is the main memory' .
   ' consument on your database server. Here is listed, which part of the' .
   ' SGA uses how many memory',

   options1      => $h . 'SID: %s%%+Installed Options',
   options1_short=> 'select * from v$option',
   options1_long => '',

   options2      => $h . 'SID: %s%%+Installed Options(2nd)',
#   options2_short=>
   options2_long => '',

   init          => $h . 'SID: %s%%+init%s.ora',
#   init_short
   init_long     => '',
   ifile         => $h . 'SID: %s%%+Config include file',
   # ifile_short
   ifile_long    => 'You have in your init.ora file an option to '.
   'include this file to your configuration.',

   tssize        => $h . 'SID: %s%%+Tablespaces',
   tssize_short  => 'Get the information from dba_data_files and '.
   'dba_free_space',
   tssize_long   => '',

   ts_df         => $h . 'SID: %s%%+Tablespaces and Data Files',
   ts_df_short   => 'look at dba_data_files',
   ts_df_long    => '',

   redo          => $h . 'SID: %s%%+Redologs',
   redo_short    => 'look at v$logfile',
   redo_long     => '',

   param         => $h . 'SID: %s%%+DB Parameters',
   param_short   => 'look at v$parameter',
   param_long    => '',

   nls           => $h . 'SID: %s%%+DB NLS Parameters',
   nls_short     => 'look at nls_database_parameters',
   nls_long      => '',
  );
%{$options->{lang}->{de}}=
  (
   version       => $h. 'SID: %s%%+Installierte Versionen',
   version_short => 'select * from v$version',
   version_long  => '',

   sga           => $h . 'SID: %s%%+SGA',
   sga_short     => 'select * from v$sga',
   sga_long      => 'Die SGA (System Global Area) ist der gr&ouml;sste ' .
   'RAM-Verbraucher auf ihrem Server. Hier wird aufgelistet, welche '.
   'Teile der SGA wieviel RAM ben&ouml;tigen.',

   options1      => $h . 'SID: %s%%+Installierte Optionen',
   options1_short=> 'select * from v$option',
   options1_long => '',

   options2      => $h . 'SID: %s%%+Installierte Options(2.)',
#   options2_short=>
   options2_long => '',

   init          => $h . 'SID: %s%%+init%s.ora',
#   init_short
   init_long     => '',
   ifile         => $h . 'SID: %s%%+Konfigurations- Includedatei',
   # ifile_short
   ifile_long    => 'In Ihrer init.ora-Datei wird diese Datei mit der '.
   'ifile-Option eingebunden.',

   tssize        => $h . 'SID: %s%%+Tablespaces',
   tssize_short  => 'steht in dba_data_files und '.
   'dba_free_space',
   tssize_long   => '',

   ts_df         => $h . 'SID: %s%%+Tablespaces und Dateien',
   ts_df_short   => 'steht in dba_data_files',
   ts_df_long    => '',

   redo          => $h . 'SID: %s%%+Redologs',
   redo_short    => 'steht in v$logfile',
   redo_long     => '',

   param         => $h . 'SID: %s%%+DB Parameter',
   param_short   => 'steht in v$parameter',
   param_long    => '',

   nls           => $h . 'SID: %s%%+DB NLS Parameters',
   nls_short     => 'steht in nls_database_parameters',
   nls_long      => '',
  );

debug( 9, i18n_std('running',$0));

#----------------------------------------------------------------------

# Main

# Check, if we have a system with oracle installed
# On every oracle system (?) is a /etc/oratab-file.
my @oratab=read_oratab();
if (!@oratab) {exit 0;}

my $instance;
my $SQL;

# loop over every instance
foreach $instance (@oratab) {
  (my $sid, my $ora_home, my $dummy) = split /:/,$instance;
  debug(9,"SID: $sid  --  ORA_HOME: $ora_home\n");

  # First: Check if oracle is running (ora_smon_<SID>) and if we
  # are member of the dba-group (that is the group, ora_smon_<SID>
  # belongs to)

  ###use Data::Dumper;
  $dummy=get_proc_info( "ora_smon_$sid" );
  if (!defined($dummy)) {        # This instance doesn't run.
    debug(1,"$sid -- doesn't run\n");
    next;
  }

  # each %$dummy returns (PID, ref for the following hash).
  # -> (each %$dummy)[1] returns just the hashref
  my $sqlproc = (each %$dummy)[1];
  my $gid_match = are_we_GID( $sqlproc->{GID} );

  # With which uid should sqlplus run ?
  my $SQL_UID;
  if ($gid_match) {$SQL_UID=$UID;}
  elsif ($UID == 0) {$SQL_UID = $sqlproc->{UID}} # We have to su
  else {debug(1,"$sid -- we aren't root and we aren't member of the dba group");
	next;}

  # Versions
  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('version',$sid),
             i18n_mesg('version_short'),
             i18n_mesg('version_long'),
	     'select BANNER "Version" from v$version');

  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('sga',$sid),
             i18n_mesg('sga_short'),
             i18n_mesg('sga_long'),
	     'select * from v$sga');

  # Options
  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('options1',$sid),
             i18n_mesg('options1_short'),
             i18n_mesg('options1_long'),
	     "select * from v_\$option");

  if (-e "$ora_home/install/unix.rgs") {
    report_file(i18n_mesg('options2',$sid),
                i18n_std('filecontent',"$ora_home/install/unix.rgs"),
                i18n_mesg('options2_long'),
		"$ora_home/install/unix.rgs");
  }

  # Config-Files
  my $config= readfile("$ora_home/dbs/init$sid.ora");
  report_string(i18n_mesg('init',$sid,$sid),
                i18n_std('filecontent',"$ora_home/dbs/init$sid.ora"),
                i18n_mesg('init_long'),
		$config,
		delcomment    => $delcomments,
		delblanklines => $delblanklines,
	       );
  my @ifile = map {s/.*ifile\s*=\s*(.*)/$1/;$_}
              grep ! /^#/,
              split /\n/,$config;
  if ($#ifile > -1) {
    report_file(i18n_mesg('ifile'),
                i18n_std('filecontent',$ifile[0]),
                i18n_mesg('ifile_long'),
                $ifile[0]);
  }

  # Tablespaces
  $SQL = <<"  EOSQL";
    column file_name       heading 'File'       format a45
    column Tablespace_name heading 'Tablespace' FORMAT A15
    column SIZE            heading 'Size (MB)'  format 999999999
    column USED            heading 'Used (MB)'  format 999999999
    column PERCENT         heading '(%)'        format 999
    select tablespace_name,
           round(sum(total)/1024/1024) "SIZE",
           round((sum(total)-sum(free))/1024/1024) "USED",
           round((sum(total)-sum(free))/sum(total)*100) "PERCENT"
    from ( select tablespace_name,
                  0 total,
                  sum(bytes) free
           from dba_free_space
           group by tablespace_name
         union all
           select tablespace_name,
                  sum(bytes) total,
                  0 free
           from dba_data_files
           group by tablespace_name
         )
    group by tablespace_name
  EOSQL

  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('tssize',$sid),
             i18n_mesg('tssize_short'),
             i18n_mesg('tssize_long'),
	     $SQL,
	     tab=>"sql",
	    );

  # Tablespaces and Datafiles
  $SQL = <<"  EOSQL";
    column file_name       heading 'File'       format a45
    column Tablespace_name heading 'Tablespace' FORMAT A15
    column SIZE            heading 'Size (MB)'  format 999999999
    column USED            heading 'Used (MB)'  format 999999999
    column PERCENT         heading '%'          format 999
    column file_id         heading 'File#'      format 9999
    break on tablespace_name skip 1
    select t2.tablespace_name,
           t2.file_name,
           t2.file_id,
           t1.total2/1024/1024 "SIZE",
           (t1.total2-t1.free2)/1024/1024 "USED",
           round((t1.total2-t1.free2)/t1.total2*100) "PERCENT"
    from (
          select file_id,
                 sum(total1) total2,
                 sum(free1) free2
          from ( select
                        file_id,
	                0 total1,
	                sum(bytes) free1
                 from dba_free_space
                 group by file_id
             union all
                 select
                        file_id,
	                sum (bytes) total1,
	                0 free1
                 from dba_data_files
                 group by file_id
                )
          group by file_id
         ) t1,
         dba_data_files t2
    where t1.file_id = t2.file_id
    order by tablespace_name,t2.file_id
  EOSQL

  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('ts_df',$sid),
             i18n_mesg('ts_df_short'),
             i18n_mesg('ts_df_long'),
             $SQL,
	     tab=>"sql",
	    );

  # redo-logs
  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('redo',$sid),
             i18n_mesg('redo_short'),
             i18n_mesg('redo_long'),
	     'column "RL file" format A45
                            break on "RL group" skip 1
                            select a.group# "RL group",
                                          member   "RL file",
                                          round(bytes/1024/1024) "Size (MB)"
                            from v$logfile a, v$log b
                            where a.group#=b.group#
                            order by a.group#',
	     tab => "sql",
	    );

  # DB params
  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('param',$sid),
             i18n_mesg('param_short'),
             i18n_mesg('param_long'),
	     'select name, value, isdefault, description
                            from v$parameter
                            order by name',
	     tab => "sql",
	    );

  # NLS Params
  report_sql($sid,$ora_home,$SQL_UID,
             i18n_mesg('nls',$sid),
             i18n_mesg('nls_short'),
             i18n_mesg('nls_long'),
	     'select * from nls_database_parameters',
	     tab => "sql",
	    );

  # FIXME  print_user_roles($sid,$ora_home);
}

exit 0;

sub read_oratab {
  my $oratab_file = "/etc/oratab"; # correct for HP/UX, AIX, Linux

  return if (! -r $oratab_file);

  open (ORATAB,$oratab_file) or die "Couldn't open $oratab_file";
  my @rv;
  while (<ORATAB>) {
    chomp;                  # no newline
    s/#.*//;                # no comments
    s/^\s+//;               # no leading white
    s/\s+$//;               # no trailing white
    s/^\*.*//;		    # no stars
    next unless length;     # anything left?

    push @rv,$_;
  }
  close (ORATAB);
  return @rv;
}

sub report_sql {
  my $sid        = shift;
  my $ora_home   = shift;
  my $sql_uid    = shift;
  my $title      = shift;
  my $short_desc = shift;
  my $long_desc  = shift;

  my $select     = shift;

  my %options    = @_;
  my %newoptions = %options;
  if (defined($newoptions{tab}) &&
      $newoptions{tab} eq "sql") {
    $newoptions{tab} = "self";}

  report_string($title,
		$short_desc,
		$long_desc,
		oraselect($sid,$ora_home,$sql_uid,$select,%options),
		%newoptions);
}

sub oraselect {
  my $sid        = shift;
  my $ora_home   = shift;
  my $sql_uid    = shift;
  my $select     = shift;
  my %options    = @_;

  $ENV{ORACLE_HOME} = $ora_home;
  $ENV{ORACLE_SID}  = $sid;

  my $ADDTOPATH     = "$ENV{ORACLE_HOME}/bin/:";
  my $tmpfile       = "/tmp/oracle.$$.sql";
  my $SQLCMD        = 'sqlplus';

  if ($OSNAME =~ /MSWin32/) {
    $SQLCMD='plus80';
    $tmpfile='c:\TEMP\oracle.$$.sql';
    $ADDTOPATH="$ENV{ORACLE_HOME}\\bin;";
  }

  # PATH = ORACLE_HOME/bin:$PATH
  $ENV{PATH}=$ADDTOPATH . $ENV{PATH};

  # We don't use pipes to input sqlplus because that doesn't work for
  # windows.

  my $sqlheader = <<"  EOSQL";
   set serveroutput on
   set pagesize 9999
   set feedback off
   set linesize 10000
  EOSQL

  my $sqltable = "";
  if (defined($options{tab}) && $options{tab} eq "sql") {
    $sqltable = "set colsep '</td><td>'\n";
  }

  debug(9,"SQLString: $sqlheader\n$select");
  open TEMPFH, ">$tmpfile";
  print TEMPFH "$sqlheader\n";
  print TEMPFH "$sqltable\n";
  print TEMPFH "$select; \n";
  print TEMPFH "exit;\n";
  close TEMPFH;

  my $executestring;
  if ($OSNAME =~ /MSWin32/) {
    $executestring =
      "$SQLCMD -s '/ as sysdba' \@$tmpfile 2>&1";
  } else {
    $executestring =
      "$ora_home/bin/$SQLCMD -s \'/ as sysdba\' \@$tmpfile 2>&1";
  }

  if ($sql_uid != $UID) { # Try a su
    $executestring = "su " . scalar getpwuid($sql_uid) .
      " -c \"$executestring\"";
  }

  my $sqlout=do_cmd($executestring,'*',60);

  # We have to make a regular table from sql if asked
  if (defined($options{tab}) && $options{tab} eq "sql") {
    $sqlout =~ s{^(</td><td>|-|\s)*$}{}gm;
    $sqlout =~ s{\n\n}{\n}g;
    $sqlout =~ s{\n}{</td></tr><tr><td>}gm;

    $sqlout =~ s{\s+</td>}{</td>}g;
    $sqlout =~ s{<tr><td>$}{};
    $sqlout =~ s{^</td></tr>}{};
    $sqlout = "<table>$sqlout</table>\n";
  }

  unlink ($tmpfile);

  return $sqlout;
}