#
# Copyright (c) 2001, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#  $Id: oracle_db_files.pl /st_emdbsa_11.2/3 2009/02/19 10:46:49 yozhang Exp $ 
#
#
# NAME
#   oracle_db_files.pl
#
# DESC
#     Subroutine for getting the file metrics for a Oracle DB files along with the filesystem or raw device
#
#
# FUNCTIONS
#
#
# NOTES
#           Target Adress expected to be in the standard format
#      (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dlsun1170)(PORT=1531))(CONNECT_DATA=(SID=emd)))
#
# MODIFIED	(MM/DD/YY)
# yozhang       02/17/09 - Fix bug 8216584: remove vtempfiles for 11.2DB
# mnihalan      02/09/09 - Fix bug 7514751
# yozhang       02/05/09 - Fix bug 8216584
# ajdsouza      12/05/05 - XbranchMerge ajdsouza_stg_bug4739322_fix1 from 
#                          st_emdbgc_10.2.0.1.0 
# MODIFIED   (MM/DD/YY)
# ajdsouza      12/02/05 - fix bug 4739322, iload storage modules after db io
#                           to prevent nls issues
# xshen         01/12/05 - modify redolog query 
# ajdsouza      10/13/04 - Add hint for v$datafile
# ajdsouza      08/19/04 - Added locale
# ajdsouza      07/23/04 - Commented the column os_storage_entity
# ajdsouza      06/25/04 - storage reporting sources 
# ajdsouza      04/16/04 - Creation, Replicated the sqls from oracle_database.xml
#
#

require v5.6.1;
require 'emd_common.pl';

use strict;
use warnings;
use DBI;

my $fptr_get_os_storage_entity_path;
my %list_of_sqls;
my %order_of_fields;
my %os_file_name;

# define the other configuration variables
%list_of_sqls = (
       db_datafiles => 
       [
        "
    	   SELECT REPLACE(REPLACE(ddf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           vdf.status    \"status\",
           ddf.tablespace_name \"tablespace_name\",
           NVL(ddf.bytes,0) \"bytes\",
           ddf.autoextensible \"autoextensible\",
           ddf.increment_by \"increment_by\",
           ddf.maxbytes \"max_bytes\"
      FROM sys.dba_data_files ddf,
           v\$datafile vdf /*+ all_rows use_concat */
     WHERE (ddf.file_name = vdf.name)",      
        "
    SELECT REPLACE(REPLACE(dtf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           vtf.status     \"status\",
           dtf.tablespace_name \"tablespace_name\",
           NVL(dtf.bytes,0) \"bytes\",
           dtf.autoextensible \"autoextensible\",
           dtf.increment_by \"increment_by\",
           dtf.maxbytes \"max_bytes\"
      FROM sys.dba_temp_files dtf,
           v\$tempfile vtf
     WHERE (dtf.file_id = vtf.file#)" 
       ],
       db_datafiles_10gR2 => 
       [
        "
    	 SELECT REPLACE(REPLACE(ddf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           ddf.online_status    \"status\",
           ddf.tablespace_name \"tablespace_name\",
           NVL(ddf.bytes,0) \"bytes\",
           ddf.autoextensible \"autoextensible\",
           ddf.increment_by \"increment_by\",
           ddf.maxbytes \"max_bytes\"
      FROM sys.dba_data_files ddf",      
        "
    	SELECT REPLACE(REPLACE(dtf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           vtf.status     \"status\",
           dtf.tablespace_name \"tablespace_name\",
           NVL(dtf.bytes,0) \"bytes\",
           dtf.autoextensible \"autoextensible\",
           dtf.increment_by \"increment_by\",
           dtf.maxbytes \"max_bytes\"
      FROM sys.dba_temp_files dtf,
           v\$tempfile vtf
     WHERE (dtf.file_id = vtf.file#)" 
       ],
       db_datafiles_11gR2 => 
       [
        "
    	 SELECT REPLACE(REPLACE(ddf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           ddf.online_status    \"status\",
           ddf.tablespace_name \"tablespace_name\",
           NVL(ddf.bytes,0) \"bytes\",
           ddf.autoextensible \"autoextensible\",
           ddf.increment_by \"increment_by\",
           ddf.maxbytes \"max_bytes\"
      FROM sys.dba_data_files ddf",      
        "
    	SELECT REPLACE(REPLACE(dtf.file_name, chr(10), ''), chr(13), '') \"file_name\", 
           dtf.status     \"status\",
           dtf.tablespace_name \"tablespace_name\",
           NVL(dtf.bytes,0) \"bytes\",
           dtf.autoextensible \"autoextensible\",
           dtf.increment_by \"increment_by\",
           dtf.maxbytes \"max_bytes\"
      FROM sys.dba_temp_files dtf" 
       ],
       db_controlfiles => 
       [
        "
   SELECT   cf.name \"file_name\",
            db.controlfile_type \"status\",
            to_char(db.controlfile_created,'YYYY-MM-DD HH24:MI:SS') \"creation_date\",
            db.controlfile_sequence# \"sequence_no\",
            db.controlfile_change# \"last_change_scn\",
            to_char(db.controlfile_time,'YYYY-MM-DD HH24:MI:SS') \"last_modification_date\"
    FROM   v\$controlfile cf,
            v\$database db"
        ],
       db_redologs =>
       [
        "
       SELECT  l.group# \"group_no\",
       NLS_INITCAP(l.status) \"status\",
       l.members \"no_of_members\",
       lf.member \"file_name\",
       NLS_INITCAP(l.archived) \"archived\",
          l.bytes \"size_k\",
          l.sequence# \"sequence_no\",
          l.first_change# \"first_change_scn\",
          l.thread# as thread_num
  FROM      v\$log l,
          v\$logfile lf
  WHERE   l.group# = lf.group#"
       ]
      );

%order_of_fields = (
          db_datafiles => {
                   file_name=>1,
                   status=>2,
                   tablespace_name=>3,
                   bytes=>4,
                   autoextensible=>5,
                   increment_by=>6,
                   max_bytes=>7,
                   os_storage_entity_identifier=>8
                  },
          db_datafiles_10gR2 => {
                   file_name=>1,
                   status=>2,
                   tablespace_name=>3,
                   bytes=>4,
                   autoextensible=>5,
                   increment_by=>6,
                   max_bytes=>7,
                   os_storage_entity_identifier=>8
                  },
          db_datafiles_11gR2 => {
                   file_name=>1,
                   status=>2,
                   tablespace_name=>3,
                   bytes=>4,
                   autoextensible=>5,
                   increment_by=>6,
                   max_bytes=>7,
                   os_storage_entity_identifier=>8
                  },
          db_controlfiles => {
               file_name=>1,
               status=>2,
               creation_date=>3,
               sequence_no=>4,
               last_change_scn=>5,
               last_modification_date=>6,
               os_storage_entity_identifier=>7
                   },
          db_redologs => {
                group_no=>1,
                status=>2,
                no_of_members=>3,
                file_name=>4,
                archived=>5,
                size_k=>6,
                sequence_no=>7,
                first_change_scn=>8,
                os_storage_entity_identifier=>9,
                thread_num=>10
               }
         );

%os_file_name = (
       db_datafiles =>'file_name',      
       db_datafiles_10gR2 =>'file_name',      
       db_datafiles_11gR2 =>'file_name',      
       db_controlfiles=>'file_name',
       db_redologs=>'file_name'
      );

#-----------------------------------------------------------------------------------------
# subs declared
my %stdinArgs;
my $password;
my $username;
my $address;
my $role;
my $mode;
my @results;

# DB connection timeout and retry values in secs
my $db_timeout  = 10;   #timeout secs
my $db_retry    = 3;    #number of retries
my $db_waittime = 60;   #wait time before retry

# Get the username,password and target database base address from
# fetchlet environment
%stdinArgs = get_stdinvars();

$password = $stdinArgs{EM_TARGET_PASSWORD} if $stdinArgs{EM_TARGET_PASSWORD};
$username = $stdinArgs{EM_TARGET_USERNAME} if $stdinArgs{EM_TARGET_USERNAME};
$address = $ENV{EM_TARGET_ADDRESS} if $ENV{EM_TARGET_ADDRESS};
$role = $ENV{EM_TARGET_ROLE} if $ENV{EM_TARGET_ROLE};
$mode = 0;

$mode = 2 if $role and $role =~ /SYSDBA/i;
$mode = 4 if $role and $role =~ /SYSOPER/i;

die "ERROR: Target database credentials not available \n" unless $username and $password and $address;

EMD_PERL_DEBUG ("Connecting to $username $address \n");

#--------------------------------
# Get connection to the database
#--------------------------------
my %dbh;

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

$dbh{dbh}->{FetchHashKeyName} = 'NAME_lc';

#---------------------------------------------------------------------------------
# Execute the sql ad fetch the results as an array of hashes
#---------------------------------------------------------------------------------
die "Usage perl oracle_database.pl <metric_name>" unless $ARGV[0];

die "The metric name $ARGV[0] is invalid" unless $list_of_sqls{$ARGV[0]} and @{$list_of_sqls{$ARGV[0]}};

for  my $sql ( @{$list_of_sqls{$ARGV[0]}} )
{

    my $sth = $dbh{dbh}->prepare($sql) or $dbh{dbh}->disconnect and die "ERROR : preparing $sql\n";
    
    $sth->execute or $sth->finish and $dbh{dbh}->disconnect and die "ERROR : executing $sql\n";
    
    my $array_ref = $sth->fetchall_arrayref( $order_of_fields{$ARGV[0]} ) or $sth->finish and $dbh{dbh}->disconnect and die "ERROR: fetching $sql \n";
    
    die "ERROR : $sql fetch :: ".$sth->errstr." \n" and $sth->finish and $dbh{dbh}->disconnect if $sth->err;
    
    # this check is not required refer bug# 4764143, there can be queries that fetch no results
    # die "ERROR : $sql No rows found \n" and $sth->finish and $dbh{dbh}->disconnect unless $sth->rows and $array_ref and @{$array_ref};

    next unless  $sth->rows and $array_ref and @{$array_ref};

    # storage the results in a global
    for my $row ( @{$array_ref} )
    {
      my %rowhsh = %{$row};
      push @results,\%rowhsh;
    }

    $sth->finish or $dbh{dbh}->disconnect and die "Failed to disconnect from the database $address";   
    
}
    
$dbh{dbh}->disconnect or return;
    
#---------------------------------------------------------------------------------
# Load the storage libraries after DB I/O is completed
#  This will prevent any issues with DBI NLS
#---------------------------------------------------------------------------------
# Get a pinter to the storage::Register::get_os_storage_entity_path
# function, dont fail if module is absent

$SIG{'__WARN__'} = sub {};

eval '
use storage::Register; 
$fptr_get_os_storage_entity_path = \&storage::Register::get_os_storage_entity_path 
';

if (  $@ )
{
 # In case of failure setting the environment
  undef $fptr_get_os_storage_entity_path 
   if $fptr_get_os_storage_entity_path; 

  # Do no use locale, in the absence of use locale the namespace of this
  # script will use the default locale
  # but the storage modules will use the locale set in them

  # Set environment to C locale
  # OS binaries can see this locale
  my $clocale='C';

  for ( qw ( LC_ALL LC_COLLATE LC_CTYPE LC_TIME LC_NUMERIC LC_MESSAGES LC_MONETARY LANG LANGUAGE ) )
  {
    $ENV{$_}=$clocale;
  }
}

$SIG{'__WARN__'} = sub { warn "$_[0]"; }; 

# Shouldnt we set locale here if it was not set in Register



#---------------------------------------------------------------------------------
# Get the filesystem or the raw device used by the oracle file
#---------------------------------------------------------------------------------
if ( $os_file_name{$ARGV[0]} )
{
   
   for my $row ( @results )
   {   
       
       # Initialize
       $row->{os_storage_entity_identifier}='NA';
       
       next unless $row->{$os_file_name{$ARGV[0]}};

        next unless $fptr_get_os_storage_entity_path;

       # Do not show warn messaegs from storage reporting module
       $SIG{'__WARN__'} = sub {};

       # Get the filesystem or raw device for the oracle file
       $row->{os_storage_entity_identifier} = $fptr_get_os_storage_entity_path->( $row->{$os_file_name{$ARGV[0]}} );

       $SIG{'__WARN__'} = sub { warn "$_[0]"; }; 

       # if the field is blanked out set it to NA
       $row->{os_storage_entity_identifier}='NA' unless $row->{os_storage_entity_identifier};

       
    }

}

#---------------------------------------------------------------------------------
# Print the metrics to STDOUT
#---------------------------------------------------------------------------------
for my $row ( @results )
{
   print "em_result=";
   
   for my $key ( sort { $order_of_fields{$ARGV[0]}{$a} <=> $order_of_fields{$ARGV[0]}{$b} } keys %{$order_of_fields{$ARGV[0]}} )
      {
       
       warn " field $key is not in the list of quieried fields for ARGV[0] \n" unless exists $row->{$key};
       
       print "$row->{$key}" if $row->{$key};
       
       print "|" unless $order_of_fields{$ARGV[0]}{$key} == keys %{$order_of_fields{$ARGV[0]}};
       
      }
   
   print "\n";
}
    

exit 0;