ServDoc_0310oracle - Documentation of your oracle databases
$Id: ServDoc_0310oracle,v 1.9 2004/01/03 20:43:25 uherbst Exp $
ServDoc_0310oracle [-h|help] [-v|version] [--debug ORA,intensity]
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
The debug feature for that module is named ``ORA''.
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össte ' . 'RAM-Verbraucher auf ihrem Server. Hier wird aufgelistet, welche '. 'Teile der SGA wieviel RAM benö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; }