#  $Header: emdb/sysman/admin/scripts/db/dbarchfull.pl /st_emdbsa_11.2/2 2009/03/30 12:23:46 pbantis Exp $
#
# Copyright (c) 2001, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      dbarchfull.pl - Archive Area metric
#
#    DESCRIPTION
#      If the database is not in ARCHIVELOG mode, then return NOARCHIVELOG as 
#      the destination (key value).
#      If the database is in ARCHIVELOG mode, the script will get all archive 
#      destinations, and check the disk usage, then print the result.
#      Where the result is: Archive Area Destination, Total Archive Area (KB),
#      Archive Area Used (KB), and Archive Area Used (%).
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#      pbantis   03/26/09 - Remove calls to oraFilterError.
#      pbantis   08/27/08 - Backport pbantis_bug-7270027 from main
#      rimmidi   07/30/08 - Code slap from 10205 to 11.2DBControl
#      rimmidi   05/14/08 - Code slap from 11GC to 10.2.0.5.
#      kganapat  09/20/07 - Call filterOraError subroutine before die
#      ngade     04/29/07 - fix bug 5904791
#      rrawat    04/08/07 - Bug-5895873
#      mappusam  10/20/05 - bug-3776508
#      hying     08/23/05 - 4551530, ASM Windows fix 
#      pbantis   06/09/05 - ASM support.
#      pbantis   03/15/05 - bug 3303757 return orig arch dest to avoid dups. 
#      pbantis   03/14/05 - Bug 3764553 die on errors. 
#      pbantis   11/04/03 - Change sprintf to print statement 
#      pbantis   10/30/03 - Bug 322156 - only get distinct destinations 
#      pbantis   07/17/03 - Oraperl to Perl DBI
#      hying     07/08/03 - Fix bug 2988520
#      aaitghez  06/10/03 - pass credentials to stdin
#      hying     11/15/02 - Check recovery area and quota
#      ychan     09/18/02 - ychan_move_dbscript
#      xxu       06/24/02 - remove /usr/local/bin/perl
#      prabuck   02/21/02 - removed SQL logic...caller must provide info
#      qsong     09/13/01 - fix bug 1986171, add '/' to arch_dest if it's a dir
#      aaitghez  08/05/01 - adding dbms_registration call.
#      xxu       07/31/01 - add trace support
#      qsong     07/24/01 -
#      njagathe  07/11/01 - Remove setting of ORACLE_HOME
#      qsong     07/02/01 - fix typo
#      aaitghez  06/22/01 - adding required lib.
#      aaitghez  06/22/01 - set oracle_home at start of script.
#      xxu       05/29/01 - rm 73 support
#      xxu       05/21/01 - cut over from tcl
#      xxu       05/21/01 - Creation
#

# use/require appropriate libraries

use strict;
use DBI;

require "emd_common.pl";
require "semd_common.pl";
require "$ENV{EMDROOT}/sysman/admin/scripts/db/db_common.pl";

use vars qw($S);

my %stdinArgs = get_stdinvars();
my $username = $stdinArgs{"EM_TARGET_USERNAME"};
my $password = $stdinArgs{"EM_TARGET_PASSWORD"};
my $address = $ENV{EM_TARGET_ADDRESS};
my $role = $ENV{EM_TARGET_ROLE};
my $mode = 0;
if($role =~ /SYSDBA/i)
{
    $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
    $mode = 4;
}

# get $ORACLE_HOME from environment
my $oracle_home = $ENV{EM_TARGET_ORACLE_HOME};
my $arch_dest_dir;
my @diskusage;
my $orig_arch_dest;
my $arch_dest;
my $quota_size;
my $quota_used;
my $dest_count;
my @all_arch_dests;
my @all_quota_size;
my @all_quota_used;

my $lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", 
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0, AutoCommit => 0})
    or die "em_error=Could not connect to $username/$address: $DBI::errstr\n";

# get archive_log_mode
my $sql = "select log_mode from v\$database";
my $cur = $lda->prepare($sql)
    or die "prepare($sql): $DBI::errstr\n";
$cur->execute()
    or die "cur->execute(): $DBI::errstr\n";

my @fetch_row = $cur->fetchrow_array();
my $log_mode = $fetch_row[0];
# if database is not in ARCHIVELOG mode
if ( $log_mode eq "NOARCHIVELOG" ) 
{
    # the case when the archive log mode is off
    # For the bug3776508 to stop alert for noarchivelog for "free archive area metric"
    # instead of returing zero return the highest value 999999999.
 
    print "em_result=NOARCHIVELOG|999999999|0|0\n";
    EMD_PERL_DEBUG("em_result=NOARCHIVELOG|999999999|0|0\n");
    $lda->disconnect
        or warn "disconnect $DBI::errstr\n";
    exit 0;
}

$sql = "select LPAD(version, 10, '0') from v\$instance";
$cur = $lda->prepare($sql)
    or die "prepare($sql): $DBI::errstr\n";
$cur->execute()
    or die "cur->execute(): $DBI::errstr\n";

@fetch_row = $cur->fetchrow_array();
my $db_version = $fetch_row[0];
my $min_8i_db_version = '08.1.5.0.0';
my $min_9i_db_version = '09.0.0.0.0';
my $min_10gR1_db_version = '10.1.0.0.0';
my $min_10gR2_db_version = '10.2.0.0.0';
my $i;

if ($db_version < $min_8i_db_version)
{
    $sql = "select destination from v\$archive_dest where status = 'NORMAL'";
    $cur = $lda->prepare($sql)
        or die "prepare($sql): $DBI::errstr\n";
    $cur->execute()
        or die "cur->execute(): $DBI::errstr\n";
    @fetch_row = $cur->fetchrow_array();
    $all_arch_dests[0] = $fetch_row[0];
    $all_quota_size[0] = 0;
    $all_quota_used[0] = 0;
    $dest_count = 1;
}
elsif ($db_version < $min_9i_db_version)
{
    $sql = "select destination from v\$archive_dest where (status = 'VALID' and target != 'STANDBY')";
    $cur = $lda->prepare($sql)
        or die "prepare($sql): $DBI::errstr\n";
    $cur->execute()
        or die "cur->execute(): $DBI::errstr\n";
    $i = 0;
    while ( @fetch_row = $cur->fetchrow_array() )
    {
        $all_arch_dests[$i] = $fetch_row[0];
        if ($all_arch_dests[$i])
        {
            $all_quota_size[$i] = 0;
            $all_quota_used[$i] = 0;
            $i++;
        }
    }
    $dest_count = $i;
}
else
{
    $sql = "select distinct(destination), decode(destination, NULL, 0, quota_size), decode(destination, NULL, 0, quota_used) from v\$archive_dest where status = 'VALID' and target != 'STANDBY' and target != 'REMOTE'";
    $cur = $lda->prepare($sql)
        or die "prepare($sql): $DBI::errstr\n";
    $cur->execute()
        or die "cur->execute(): $DBI::errstr\n";
    $i = 0;
    while ( @fetch_row = $cur->fetchrow_array() )
    {
        $all_arch_dests[$i] = $fetch_row[0];
        if ($all_arch_dests[$i])
        {
            $all_quota_size[$i] = $fetch_row[1];
            $all_quota_used[$i] = $fetch_row[2];
            $i++;
        }
    }
    $dest_count = $i;
}


$sql = "select name, value from v\$parameter where name = 'db_block_size' or name = 'log_archive_format'";
$cur = $lda->prepare($sql)
    or die "prepare($sql): $DBI::errstr\n";
$cur->execute()
    or die "cur->execute(): $DBI::errstr\n";
my $name;
my $arch_format;
my $block_size_kb;
for ($i = 0; $i < 2; $i++)
{
    @fetch_row = $cur->fetchrow_array();
    $name = $fetch_row[0];
    if ($name eq 'db_block_size')
    {
        $block_size_kb = $fetch_row[1] / 1024;
    }
    else
    {
        $arch_format = $fetch_row[1];
    }
}

if($dest_count == 0)
{
    print "em_error=No primary archive destinations.  All valid archive destinations are standby databases.\n";
    $lda->disconnect
        or warn "disconnect $DBI::errstr\n";
    exit 0;
}

for ($i = 0; $i < $dest_count; $i++)
{
    $orig_arch_dest = $arch_dest = $all_arch_dests[$i];
    $quota_size = $all_quota_size[$i];
    $quota_used = $all_quota_used[$i];

    if ($arch_dest eq "USE_DB_RECOVERY_FILE_DEST")
    {
      EMD_PERL_DEBUG("Skipping USE_DB_RECOVERY_FILE_DEST\n");
      next;
    }

    # resolve ? to $ORACLE_HOME to qualify destination relative to $ORACLE_HOME

    $arch_dest =~ s/^\?/$oracle_home/g;

    # check if the $arch_dest is a directory or not, if it's
    # a directory, append a '/' at the end  

    if ( -d $arch_dest)
    {
        $arch_dest .= '/';
        $arch_dest_dir = $arch_dest;
    } 
    else 
    {
        $arch_dest .= $arch_format;
        $arch_dest_dir = dirname ($arch_dest);
    }

    # evaluate disk usage

    EMD_PERL_DEBUG ("archive dest=$orig_arch_dest.");
    EMD_PERL_DEBUG ("archive dest dir=$arch_dest_dir.");
    if ($quota_size > 0)
    {
        my $total = $quota_size * $block_size_kb;
        my $used = $quota_used * $block_size_kb;
        my $usage = ($quota_used/$quota_size)*100;
        EMD_PERL_DEBUG("$orig_arch_dest: total = $total(KB), used = $used(KB), usage = $usage");
        print "em_result=$orig_arch_dest|$total|$used|$usage\n";
    }
    else
    {
        # ASM
        my $first_character = substr($orig_arch_dest,0,1);
        if ($first_character eq "+")
        {
            if (($db_version >= $min_10gR2_db_version) || ($db_version >= $min_10gR1_db_version))
            {
                # Extract the disk group name from the archive destination.
                # Example archive destinations: +DATAFILE, +DATAFILE/arch, +DATAFILE\\arch
                my $localS = $S;
                if ($localS eq '\\')
                {
                    $localS = '\\\\';
                }
                my @diskGroups = split(/$localS/, $orig_arch_dest);
                # Skip over the plus-sign.
                my $diskGroup = substr($diskGroups[0], 1, length($diskGroups[0]) - 1);            
                EMD_PERL_DEBUG("diskGroup=$diskGroup");
                
                # For 10gR2
                if ($db_version >= $min_10gR2_db_version)
                {
                    $sql = "select to_char(NVL(dg.total_mb,0)), to_char(NVL(dg.usable_file_mb, 0)) from V\$ASM_DISKGROUP_STAT dg where state in ('CONNECTED', 'MOUNTED') and name='$diskGroup'";
                }    
                # For 10gR1
                elsif ($db_version >= $min_10gR1_db_version)
                {
                    $sql = "select to_char(NVL(dg.total_mb,0)), to_char(NVL(dg.free_mb, 0)) from V\$ASM_DISKGROUP dg where state='CONNECTED' and name='$diskGroup'";
                }                        
                $cur = $lda->prepare($sql)
                    or die "prepare($sql): $DBI::errstr\n";
                $cur->execute()
                    or die "cur->execute(): $DBI::errstr\n";
                if ( @fetch_row = $cur->fetchrow_array() )
                {
                    # Total (KB)
                    $diskusage[0] = $fetch_row[0] * 1024;

                    if ($db_version >= $min_10gR2_db_version)
                    {
                        # Used (KB) = Total - Free
                        $diskusage[1] = $diskusage[0] - ($fetch_row[1] * 1024);

                        # Used (%)
                        if ($diskusage[0] > 0)
                        {
                            # Calculate usage % and round to nearest interger.
                            $diskusage[2] = sprintf("%.0f", ($diskusage[1]/$diskusage[0]) * 100);
                        }
                        else
                        {
                            $diskusage[2] = -1;
                        }
                    }    
                    elsif ($db_version >= $min_10gR1_db_version)
                    {
                        # For 10gR1, free_mb is always 0, so don't want to trigger unnecessary alerts.
                        # Used (KB)
                        $diskusage[1] = 0;

                        # Used (%)
                        $diskusage[2] = -1;
                    }                        
                    EMD_PERL_DEBUG("$orig_arch_dest: total = $diskusage[0](KB), used = $diskusage[1](KB), usage = $diskusage[2]");
                    print "em_result=$orig_arch_dest|$diskusage[0]|$diskusage[1]|$diskusage[2]\n";
                }
                else
                {
                    EMD_PERL_ERROR("Couldn't get disk usage for disk group $diskGroup for disk $orig_arch_dest.");
                    print "em_error=Couldn't get disk usage for disk group $diskGroup for disk $orig_arch_dest\n";
                    print "em_result=$orig_arch_dest|0|0|-1\n";
                } 
            }
            else
            {
                EMD_PERL_ERROR ("Couldn't get disk usage for disk $orig_arch_dest.");
                print "em_error=Couldn't get disk usage for disk $orig_arch_dest\n";
                print "em_result=$orig_arch_dest|0|0|-1\n";
            } 
        }
        # Non-ASM
        else
        {
            @diskusage = get_disk_usage($arch_dest_dir);
            if ( $diskusage[0] == -1 ) 
            {
                EMD_PERL_ERROR ("Couldn't get disk usage for disk $orig_arch_dest.");
                print "em_error=Couldn't get disk usage for disk $orig_arch_dest\n";
                print "em_result=$orig_arch_dest|0|0|-1\n";
            } 
            else 
            {
                EMD_PERL_DEBUG ("$orig_arch_dest: total = $diskusage[0](KB), used = $diskusage[1](KB), usage = $diskusage[3]");
                print "em_result=$orig_arch_dest|$diskusage[0]|$diskusage[1]|$diskusage[3]\n";
            }
        }
    }
}

$lda->disconnect
    or warn "final disconnect $DBI::errstr\n";

exit 0;