#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/db/workload/workload_capture.pl /st_emdbsa_11.2/5 2009/11/17 15:21:47 keiwong Exp $
#
# workload_capture.pl
#
# Copyright (c) 2006, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      workload_capture.pl
#
#    DESCRIPTION
#      This script will start a workload capture. It may involve restarting the
#      database before the capture.
#
#    NOTES
#      When restarting RAC, all nodes are first shut down and only one node is
#      started up. After capture begins, all the nodes will be started up.
#
#    MODIFIED   (MM/DD/YY)
#       keiwong  11/12/09 - support STS capture
#       keiwong  03/27/09 - include force shutdown option for RAC
#       keiwong  07/16/08 - don't use srvctl to start up db in restricted mode
#       keiwong  05/05/08 - handle non-ASCII value
#       keiwong  04/29/08 - add db_version and connect_descriptor parameters
#       keiwong  10/05/06 - Fix bug 5558757
#       keiwong  07/06/06 - Creation
# 

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

use strict;
use vars qw($DBSTATE_SUCCESS_CODE $TARGET_TYPE_ORACLE_DATABASE $TARGET_TYPE_RAC_DATABASE $RESTRICTED_MODE $STATE_OPEN $DBROLE_NORMAL @SQLCMDS $NT);
use vars qw($oracle_home $oracle_sid $db_name $username $password $role $target_type $is_rac $db_version $connect_descriptor);

# Set up global database parameters
#
# Parameter:
# $oracle_home: Oracle home
# $oracle_sid: Oracle SID
# $db_name: DB name
# $username: DB user name
# $password: DB user password
# $role: DB role
# $target_type: target type
# $is_rac: true for RAC target
# $db_version: DB version
# $connect_descriptor: connect descriptor
#
# Return value:
# None
sub set_db_parameters
{
    ($oracle_home, $oracle_sid, $db_name, $username, $password, $role, $target_type, $is_rac, $db_version, $connect_descriptor) = @_;

    EMD_PERL_DEBUG("workload_capture:set_db_parameters oracle_home=$oracle_home oracle_sid=$oracle_sid db_name=$db_name username=$username role=$role target_type=$target_type is_rac=$is_rac db_version=$db_version connect_descriptor=$connect_descriptor");

    set_db_var($oracle_home, $oracle_sid, 1);
}

# Start workload capture
#
# Parameter:
# $capture_name: name of the capture
# $capture_directory: name of directory object for capture
# $duration: length of capture in seconds
# $filter_type: "INCLUDE" or "EXCLUDE"
# $filters: name:attribute:value:type,name:attribute:value:type,...
# $restart_database: 0/1 (no/yes)
# $shutdown_options: "NORMAL" or "IMMEDIATE" or ...
# $startup_options: "NOMOUNT RESTRICT FORCE ..."
# $force_shutdown: 0/1 (no/yes)
# $create_sts: 0/1 (no/yes)
#
# Return value:
# Success: 0
# Failure: !0
sub capture_workload
{
    my $redirect_file = make_temp_file();

    # save stdout
    open OLD_STDOUT, ">& STDOUT";
    my $old_stdout = *OLD_STDOUT;

    # redirect stdout and stderr
    open STDOUT, "> $redirect_file";
    open STDERR, ">& STDOUT";

    my ($capture_name, $capture_directory, $duration, $filter_type, $filters, $restart_database, $shutdown_options, $startup_options, $force_shutdown, $create_sts) = @_;

    my $shutdown_status = 0;
    my $startup_status = 0;
    my $startup_all_status = 0;
    my $capture_status = 0;

    EMD_PERL_DEBUG("workload_capture:capture_workload restart_database=$restart_database");

    # check if capture is running
    my $validation_status = is_capture_running();

    if ($validation_status > 0)
    {
        printError("Failed to start workload capture: capture is already running.");
        return dump_file($validation_status, $redirect_file, $old_stdout, 1);
    }
    elsif ($validation_status < 0)
    {
        return dump_file($validation_status, $redirect_file, $old_stdout, 1);
    }

    # check if capture name already exists
    $validation_status = capture_exists($capture_name);

    if ($validation_status > 0)
    {
        printError("Failed to start workload capture: capture $capture_name already exists.");
        return dump_file($validation_status, $redirect_file, $old_stdout, 1);
    }
    elsif ($validation_status < 0)
    {
        return dump_file($validation_status, $redirect_file, $old_stdout, 1);
    }

    # make sure capture directory object exists
    $validation_status = dir_object_exists($capture_directory);

    if ($validation_status == 0)
    {
        printError("Failed to start workload capture: directory object $capture_directory does not exist.");
        return dump_file(!$validation_status, $redirect_file, $old_stdout, 1);
    }
    elsif ($validation_status < 0)
    {
        return dump_file($validation_status, $redirect_file, $old_stdout, 1);
    }

    if ($restart_database)
    {
        $shutdown_status = shutdown_database($shutdown_options, $force_shutdown);

        if (!$shutdown_status)
        {
            my $startup_options_restricted = $startup_options;

            if ($startup_options_restricted !~ /$RESTRICTED_MODE/i)
            {
                # must be first started up in restricted mode
                $startup_options_restricted .= " $RESTRICTED_MODE";
            }

            if ($startup_options_restricted !~ /$STATE_OPEN/i)
            {
                $startup_options_restricted .= " $STATE_OPEN";
            }

            # bring up only one instance before capture
            $startup_status =
                startup_database($startup_options_restricted, $oracle_sid);

            if ($startup_status)
            {
                printError("Failed to start up database instance $oracle_sid.");
            }
        }
        else
        {
            printError("Failed to shut down database $db_name.");
        }
    }

    if (!$shutdown_status && !$startup_status)
    {
        $capture_status = start_capture($capture_name, $capture_directory,
            $duration, $filter_type, $filters, $restart_database, $create_sts);
    }

    if ($restart_database && $is_rac =~ /true/i)
    {
        # bring up all instances for RAC
        $startup_all_status = startup_database($startup_options);

        if ($startup_all_status)
        {
            printError("Failed to start up RAC database $db_name.");
        }
    }

    return dump_file($shutdown_status + $startup_status + $startup_all_status +
        $capture_status, $redirect_file, $old_stdout, 1);
}

# Shut down database
# set_db_parameters should be called first
#
# Parameter:
# $shutdown_options: "NORMAL" or "IMMEDIATE" or ...
# $force_shutdown: 0/1 (no/yes)
# $instance_list: list of instances names
#
# Return value:
# Success: 0
# Failure: not 0
sub shutdown_database
{
    my ($shutdown_options, $force_shutdown, $instance_list) = @_;

    EMD_PERL_DEBUG("workload_capture:shutdown_database shutdown_options=$shutdown_options force_shutdown=$force_shutdown target_type=$target_type instance_list=$instance_list");

    my $tns = "";
    my $is_db_10i = 1;
    my $restore_db_state = 1;
    my $init_state = "";
    my @pre_shutdown_sql;
    my $shutdown_target_type =
        ($is_rac =~ /true/i) ? $TARGET_TYPE_RAC_DATABASE : $target_type;
    my $shutdown_options_formatted =
        ($is_rac =~ /true/i && $shutdown_options) ?
            '"'.$shutdown_options.'"' : $shutdown_options;

    if ($is_rac =~ /true/i && $force_shutdown)
    {
        $shutdown_options_formatted .= " -f";
    }

    my $return_value =
        shutdown_db($shutdown_target_type, $oracle_home, $oracle_sid, $db_name,
            $username, $password, $role, $tns, $instance_list, $is_db_10i,
            $shutdown_options_formatted, $restore_db_state, $init_state,
            @pre_shutdown_sql);

    return $return_value;
}

# Start up database
# set_db_parameters should be called first
#
# Parameter:
# $startup_options: "NOMOUNT RESTRICT FORCE ..."
# $instance_list: list of instances names
#
# Return value:
# Success: 0
# Failure: !0
sub startup_database
{
    my ($startup_options, $instance_list) = @_;

    EMD_PERL_DEBUG("workload_capture:startup_database startup_options=$startup_options target_type=$target_type instance_list=$instance_list");

    my $tns = "";
    my $is_db_10i = 1;
    my $sql_run_state = "";
    my $restore_db_state = 0;
    my $init_state = "";
    my $bounce_after_post_sql = 0;
    my @post_startup_sql;
    my $startup_target_type =
        ($startup_options =~ /$RESTRICTED_MODE/i) ?
            $TARGET_TYPE_ORACLE_DATABASE :
            ($is_rac =~ /true/i) ? $TARGET_TYPE_RAC_DATABASE : $target_type;
    my $startup_options_formatted = $startup_options;

    if ($startup_target_type =~ /$TARGET_TYPE_RAC_DATABASE/i &&
        $startup_options_formatted)
    {
        # trim leading and trailing spaces
        $startup_options_formatted =~ s/^\s+//;
        $startup_options_formatted =~ s/\s+$//;

        # if db version is at least 11.2 or unknown
        if (isVersionEqualOrHigher($db_version, "11.2"))
        {
            # use ',' as separator
            $startup_options_formatted =~ s/\s+/,/g;
        }

        $startup_options_formatted = '"'.$startup_options_formatted.'"';
    }

    my $return_value =
        startup_db($startup_target_type, $oracle_home, $oracle_sid, $db_name,
            $username, $password, $role, $tns, $instance_list, $is_db_10i,
            $startup_options_formatted, $sql_run_state, $restore_db_state,
            $init_state, $bounce_after_post_sql, @post_startup_sql);

    return $return_value;
}

# Start capture
#
# Parameter:
# $capture_name: name of the capture
# $capture_directory: name of directory object for capture
# $duration: length of capture in seconds
# $filter_type: "INCLUDE" or "EXCLUDE"
# $filters: name:attribute:value:type,name:attribute:value:type,...
# $restart_database: 0/1 (no/yes)
# $create_sts: 0/1 (no/yes)
#
# Return value:
# Success: 0
# Failure: !0
sub start_capture
{
    my ($capture_name, $capture_directory, $duration, $filter_type, $filters, $restart_database, $create_sts) = @_;

    EMD_PERL_DEBUG("workload_capture:start_capture capture_name=$capture_name capture_directory=$capture_directory duration=$duration filter_type=$filter_type filters=$filters restart_database=$restart_database create_sts=$create_sts");

    # clear any current filters
    my $clear_filters_status = clear_current_filters();

    if ($clear_filters_status)
    {
        return $clear_filters_status;
    }

    # add workload filters
    my $add_filters_status = add_filters($filters);
    my $return_value = $add_filters_status;

    if (!$add_filters_status)
    {
        my $auto_unrestrict = $restart_database ? "TRUE" : "FALSE";
        my $capture_sts = $create_sts ? "TRUE" : "FALSE";

        # @SQLCMDS is defined in dbstate.sql
        @SQLCMDS =
            "exec dbms_workload_capture.start_capture(UNISTR('$capture_name'), UNISTR('$capture_directory'), $duration, '$filter_type', $auto_unrestrict, $capture_sts)";

        my $sql_status =
            execute_sqlplus($username, $password, $role, $connect_descriptor);

        if ($sql_status != $DBSTATE_SUCCESS_CODE)
        {
            printError("Failed to start workload capture $capture_name.");
            $return_value = 1;
        }
    }

    return $return_value;
}

# Add workload filters
#
# Parameter:
# $filters: name:attribute:value:type,name:attribute:value:type,...
#
# Return value:
# Success: 0
# Failure: !0
sub add_filters
{
    EMD_PERL_DEBUG("workload_capture:add_filters filters=@_[0]");

    my @filters = split / *, */, @_[0];

    my $return_value = 0;

    while (@filters)
    {
        my ($name, $attribute, $value, $type) = split / *: */, shift @filters;

        # type can be "string" or "number"
        # @SQLCMDS is defined in dbstate.sql
        if ($type =~ /string/i)
        {
            @SQLCMDS = "exec dbms_workload_capture.add_filter(UNISTR('$name'), '$attribute', UNISTR('$value'))";
        }
        else
        {
            @SQLCMDS = "exec dbms_workload_capture.add_filter(UNISTR('$name'), '$attribute', $value)";
        }

        my $sql_status =
            execute_sqlplus($username, $password, $role, $connect_descriptor);

        if ($sql_status != $DBSTATE_SUCCESS_CODE)
        {
            printError("Failed to add workload capture filter $name.");
            $return_value = 1;
        }
    }

    return $return_value;
}

# Check if capture is running
#
# Return value:
# True: > 0
# False: 0
# Error: < 0
sub is_capture_running
{
    EMD_PERL_DEBUG("workload_capture:is_capture_running");

    my $return_value = -1;

    my $sql = "SET ECHO OFF;\n";
    $sql .= "SET SERVEROUTPUT ON;\n";
    $sql .= "VARIABLE result NUMBER;\n";
    $sql .= "BEGIN\n";
    $sql .= " SELECT COUNT(*) INTO :result FROM dba_workload_captures WHERE status = 'IN PROGRESS';\n";
    $sql .= " dbms_output.put_line('result=' || :result);\n";
    $sql .= "END;\n";
    $sql .= "/\n";

    @SQLCMDS = $sql;

    my $temp_file = make_temp_file();

    my $sql_status =
        execute_sqlplus($username, $password, $role, $connect_descriptor,
            $temp_file);

    if ($sql_status != $DBSTATE_SUCCESS_CODE)
    {
        printError("Failed to check if capture is running.");
    }

    open(SQL_OUTPUT, $temp_file);

    while (<SQL_OUTPUT>)
    {
        if ($sql_status != $DBSTATE_SUCCESS_CODE)
        {
            printError($_);
        }
        elsif (/=/)
        {
            my @output = split /=/;
            $return_value = $output[1];
            last;
        }
    }

    close(SQL_OUTPUT);

    if ($return_value > -1)
    {
        removeFile($temp_file);
    }

    EMD_PERL_DEBUG("workload_capture:is_capture_running return_value=$return_value");

    return $return_value;
}

# Check if a capture name exists
#
# Parameter:
# $capture_name: name of capture
#
# Return value:
# True: > 0
# False: 0
# Error: < 0
sub capture_exists
{
    my ($capture_name) = @_;

    EMD_PERL_DEBUG("workload_capture:capture_exists capture_name=$capture_name");

    my $return_value = -1;

    if (! $capture_name)
    {
        return $return_value;
    }

    my $sql = "SET ECHO OFF;\n";
    $sql .= "SET SERVEROUTPUT ON;\n";
    $sql .= "VARIABLE result NUMBER;\n";
    $sql .= "BEGIN\n";
    $sql .= " SELECT COUNT(*) INTO :result FROM dba_workload_captures WHERE ASCIISTR(name) = '$capture_name';\n";
    $sql .= " dbms_output.put_line('result=' || :result);\n";
    $sql .= "END;\n";
    $sql .= "/\n";

    @SQLCMDS = $sql;

    my $temp_file = make_temp_file();

    my $sql_status =
        execute_sqlplus($username, $password, $role, $connect_descriptor,
            $temp_file);

    if ($sql_status != $DBSTATE_SUCCESS_CODE)
    {
        printError("Failed to check if capture exists.");
    }

    open(SQL_OUTPUT, $temp_file);

    while (<SQL_OUTPUT>)
    {
        if ($sql_status != $DBSTATE_SUCCESS_CODE)
        {
            printError($_);
        }
        elsif (/=/)
        {
            my @output = split /=/;
            $return_value = $output[1];
            last;
        }
    }

    close(SQL_OUTPUT);

    if ($return_value > -1)
    {
        removeFile($temp_file);
    }

    EMD_PERL_DEBUG("workload_capture:capture_exists return_value=$return_value");

    return $return_value;
}

# Check if a directory object exists
#
# Parameter:
# $dir_object_name: name of directory object
#
# Return value:
# True: > 0
# False: 0
# Error: < 0
sub dir_object_exists
{
    my ($dir_object_name) = @_;

    EMD_PERL_DEBUG("workload_capture:dir_object_exists dir_object_name=$dir_object_name");

    my $return_value = -1;

    if (! $dir_object_name)
    {
        return $return_value;
    }

    my $sql = "SET ECHO OFF;\n";
    $sql .= "SET SERVEROUTPUT ON;\n";
    $sql .= "VARIABLE result NUMBER;\n";
    $sql .= "BEGIN\n";
    $sql .= " SELECT COUNT(*) INTO :result FROM all_directories WHERE ASCIISTR(directory_name) = '$dir_object_name';\n";
    $sql .= " dbms_output.put_line('result=' || :result);\n";
    $sql .= "END;\n";
    $sql .= "/\n";

    @SQLCMDS = $sql;

    my $temp_file = make_temp_file();

    my $sql_status =
        execute_sqlplus($username, $password, $role, $connect_descriptor,
            $temp_file);

    if ($sql_status != $DBSTATE_SUCCESS_CODE)
    {
        printError("Failed to check if directory object exists.");
    }

    open(SQL_OUTPUT, $temp_file);

    while (<SQL_OUTPUT>)
    {
        if ($sql_status != $DBSTATE_SUCCESS_CODE)
        {
            printError($_);
        }
        elsif (/=/)
        {
            my @output = split /=/;
            $return_value = $output[1];
            last;
        }
    }

    close(SQL_OUTPUT);

    if ($return_value > -1)
    {
        removeFile($temp_file);
    }

    EMD_PERL_DEBUG("workload_capture:dir_object_exists return_value=$return_value");

    return $return_value;
}

# Clear all current filters
#
# Return value:
# Success: 0
# Failure: !0
sub clear_current_filters
{
    EMD_PERL_DEBUG("workload_capture:clear_current_filters");

    my $return_value = 0;

    my $sql = "SET ECHO OFF;\n";
    $sql .= "BEGIN\n";
    $sql .= " FOR i in (SELECT name FROM dba_workload_filters WHERE type = 'CAPTURE' AND status = 'NEW')\n";
    $sql .= " LOOP\n";
    $sql .= "   dbms_workload_capture.delete_filter(i.name);\n";
    $sql .= " END LOOP;\n";
    $sql .= "END;\n";
    $sql .= "/\n";

    @SQLCMDS = $sql;

    my $temp_file = make_temp_file();

    my $sql_status =
        execute_sqlplus($username, $password, $role, $connect_descriptor,
            $temp_file);

    if ($sql_status != $DBSTATE_SUCCESS_CODE)
    {
        printError("Failed to clear current filters.");
        $return_value = 1;

        open(SQL_OUTPUT, $temp_file);

        while (<SQL_OUTPUT>)
        {
            printError($_);
        }

        close SQL_OUTPUT;
    }

    if ($return_value == 0)
    {
        removeFile($temp_file);
    }

    return $return_value;
}

# Make a temporary file
#
# Return value:
# Temporary file name
sub make_temp_file
{
    EMD_PERL_DEBUG("workload_capture:make_temp_file");

    my $dir = tempdir(CLEANUP => 1);
    my ($temp_handle, $temp_file);

    if (!$NT)
    {
        ($temp_handle, $temp_file) = tempfile(DIR => $dir);
    }
    else
    {
        $temp_file = "$dir\\"."wcapture.$$";
    }

    return $temp_file;
}

# Return status and optionally send content of a file to a file handle if
# status is not zero.
#
# Parameter:
# $status: return status
# $file: name of file to dump
# $output: file handle for output
# $remove: optionally remove file afterward
#
# Return value:
# Return status
sub dump_file
{
    my ($status, $file, $output, $remove) = @_;

    # send content of file to $output if status is not 0
    if ($status && $file && $output)
    {
        open FILE, $file;
        print $output <FILE>;
        close FILE;
    }

    if ($remove && $file)
    {
        removeFile($file);
    }

    return $status;
}

# Execute sqlplus
#
# Parameter:
# dbUsername: The username to log onto the database with.
# dbPassword: The password for the username.
# dbRole: The role for the username.
# tns The TNS descriptor. Optional.
# outputFile Redirect output to the named file. Optional.
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output or to the file passed in.
sub execute_sqlplus
{
    my ($dbUsername, $dbPassword, $dbRole, $tns, $outputFile) = @_;
    my $connStr;

    if (defined($tns) && $tns ne "")
    {
        if (!($tns =~ /^'/ || $tns =~ /^"/))
        {
            $tns = '"'.$tns.'"';
        }

        $connStr = "${dbUsername}/${dbPassword}\@${tns}";
    }
    else
    {
        $connStr = "${dbUsername}/${dbPassword}";
    }

    if (!($dbRole =~ /$DBROLE_NORMAL/i || $dbRole eq ''))
    {
        $connStr .= " as $dbRole";
    }

    return (&executeSQLPlus($connStr, $outputFile));
}

# Check if a db version is equal to or higher than a given version
#
# Parameter:
# $version1: version in question
# $version2: version to compare with
#
# Return value:
# True: > 0
# False: 0
# Error: < 0
sub isVersionEqualOrHigher
{
    my ($version1, $version2) = @_;

    EMD_PERL_DEBUG("workload_capture:isVersionEqualOrHigher version1=$version1 version2=$version2");

    if (!$version1 || !$version2)
    {
        return -1;
    }

    my @parts1 = split('\.', $version1);
    my @parts2 = split('\.', $version2);

    # make lengths equal with '0'
    while (@parts1 < @parts2)
    {
        push(@parts1, 0);
    }

    while (@parts2 < @parts1)
    {
        push(@parts2, 0);
    }

    for (my $i = 0; @parts1 > $i && @parts2 > $i; $i++)
    {
        if ($parts1[$i] < $parts2[$i])
        {
            return 0;
        }
        elsif ($parts1[$i] > $parts2[$i])
        {
            return 1;
        }
    }

    # equal
    return 1;
}

1;