#  ALTRAN_PROLOG_BEGIN_TAG                                                    
#  This is an automatically generated prolog.                                  
#                                                                              
#  Copyright (C) Altran ACT S.A.S. 2017,2018,2019,2020,2021.  All rights reserved.  
#                                                                              
#  ALTRAN_PROLOG_END_TAG                                                      
#                                                                              
# IBM_PROLOG_BEGIN_TAG 
# This is an automatically generated prolog. 
#  
# 61haes_r714 src/43haes/usr/sbin/cluster/sa/oracle/sbin/DBUtilities.sh 1.15.1.1 
#  
# Licensed Materials - Property of IBM 
#  
# Restricted Materials of IBM 
#  
# COPYRIGHT International Business Machines Corp. 2006,2013 
# All Rights Reserved 
#  
# US Government Users Restricted Rights - Use, duplication or 
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp. 
#  
# IBM_PROLOG_END_TAG 
# @(#)  7d4c34b 43haes/usr/sbin/cluster/sa/oracle/sbin/DBUtilities.sh, 726, 2147A_aha726, Feb 05 2021 09:50 PM

#---------------------------------------------------------------------------
# Global Definitions
#---------------------------------------------------------------------------

. /usr/es/lib/ksh93/func_include
. /usr/es/sbin/cluster/sa/oracle/sbin/IO
. /usr/es/sbin/cluster/sa/oracle/sbin/DBGlobals

# Log directory for temp files. Choose same as that of oraclesa.log
typeset LOGDIR=$(clodmget -q 'name=oraclesa.log' -n -f value HACMPlogs 2>/dev/null)
[[ -z $LOGDIR ]] && LOGDIR="/var/hacmp/log"

# OSA_SQLPLUS_CMD

#---------------------------------------------------------------------------
# Functions:
#   osaSetDBEnvs
#   osaPfileToDBName
#   osaDBGetVGs
#   osaSQLPlusStartup
#   osaSQLPlusRun
#   osaSQLGetRow
#   osaFindListeners

#---------------------------------------------------------------------------
# Function:
#   osaSetDBEnvs
#
# Purpose:
#   Sets variables relating to an instance that all other can functions use.
#
# Arguments:
#   (1) name: Oracle DB instance name
#
# Returns:
#   0 on success
#   1 on failure
#
# Side Effects:
#   Modifies global variables within this process relating to DB instance.
# ...EXAMPLE...
#    export ORACLE_SID=asdb
#    export TNS_ADMIN=/orasoft/as10g/network/admin
#    export ORACLE_HOME=/orasoft/as10g
#
function osaSetDBEnvs {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    if [[ ! -z $1 ]]; then
        ORACLE_SID=$1
    fi
    TNS_ADMIN=${ORACLE_HOME}/network/admin
    OSA_SQLPLUS_CMD=${ORACLE_HOME}"/bin/sqlplus -S"
    export TNS_ADMIN ORACLE_HOME 
    export PATH=$ORACLE_HOME/bin:$PATH
    export ORACLE_SID
}


#----------------------------------------------------------------------------
# Function:
#   osaPfileToDBName
#
# Purpose:
#   Get DB instance name from a list of (pfile/spfile)s.  We return
#   a unique list of DB instance names.
#
# Arguments:
#   list of spfile / pfiles
#
# Output:
#   list of DB instance names
#
# Returns:
#   n/a
#
function osaPfileToDBName {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset -AE allnames
    typeset name
    for f in $*; do
          name=${f##"init"}
          name=${name##"spfile"}
          name=${name%%".ora"}
          [[ -n $name ]] && allnames[$name]=1
    done
    echo ${!allnames[*]}
}

#----------------------------------------------------------------------------
# Function: 
#   osaFindDBInstances
#
# Purpose:
#   Find Oracle DB instances by looking at $ORACLE_HOME/dbs
#
# Arguments:
#   (1) instances : A nameref for an array where instance names are
#   pushed into.
#
# Returns:
#   Number of Oracle Instances
#
function osaFindDBInstances {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset -n instances=$1
    typeset ret=0
    typeset files list
    unset IFS
    if [ -z $ORACLE_HOME ]; then
          log_msg "ORACLE_HOME not defined."
          ret=-1
    else
          if [ ! -d "${ORACLE_HOME}/dbs" ]; then
                log_msg "Oracle DB installation does not seem to be OFA.  Cannot continue..."
                ret=-1
          else
                typeset pwd=$PWD
                cd ${ORACLE_HOME}/dbs
                files=$(ls *.ora 2>/dev/null)
                list=$(osaPfileToDBName $files)
                typeset f i
                i=0
                for f in $list; do
                    instances[$i]=$f
                    (( ret=$ret+1 ))
                    (( i=$i+1 ))
                done
                cd $pwd # go back to previous directory
          fi
    fi
    return $ret
}

#----------------------------------------------------------------------------
# Function: 
#   osaFindListeners
#
# Purpose:
#   Find Oracle DB listeners by looking at $TNS_ADMIN
#
# Arguments:
#   (1) : SID  (or no arguments => use ORACLE_SID environment variable)
#
# Returns:
#   0 success
#
# Side Effects:
#   Creates/updates global array "allDBListeners" with all listeners defined
#   in listener.ora in the following format:
#   SID Listener-name Listener-hostname/IP
#
function osaFindListeners {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset sid
    typeset -i retval=0
    if [[ ! -z $1 ]]
          then
          sid=$1
    else
          sid=$ORACLE_SID
    fi
    if [[ -f ${TNS_ADMIN}/listener.ora ]]; then
        /usr/bin/perl /usr/es/sbin/cluster/sa/oracle/sbin/cl_oraListenerParse ${TNS_ADMIN}/listener.ora $sid | {
            typeset -i i=0
            typeset lname lhost 
            typeset -i status
            read status
	    if (( $status < 0 )); then
                 osaError "Unable to parse Listener file."
                retval=1
            elif (( $status == 0 )); then
                osaError "Unable to discover listeners. Please check your listener.ora and see that it is compliant with
                          the guidelines specified in Smart Assist for Oracle User's guide."
                retval=1
            else
                while [[ $i -lt $status ]]; do
                    read lname lhost
                    allDBListeners[$i]=$sid" "$lname" "$lhost
                    (( i=$i+1 ))
                done
            fi
        }
    else
        osaError "Unable to find Oracle DB Listeners at "${TNS_ADMIN}/listener.ora
        retval=1
    fi
    return $retval
}

#----------------------------------------------------------------------------
# Function:
#   osaSQLGetRow
#
# Purpose:
#   Runs an SQL-Plus compatible statement and returns it output.  Note
#   the query should be written such that a single row is returned, such
#   as a count, status string etc.  This is used for DB monitoring.
#
# Arguments:  
#   (1) stmt:  an SQL-Plus compatible statement (superset of ANSI SQL)
#
# Output:
#   Returns single-row output from SQL-Plus after executing `stmt'.
#
# Returns:
#   n/a
#
function osaSQLGetRow {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset output
    osaSQLPlusRun "$1" | {
          while read line
             do
             if [[ ! -z $line ]]
                  then
                  output=$line   
             fi
          done
    }
    # last non-NULL line is the result/row we are looking for
    echo $output
}

#----------------------------------------------------------------------------
# Function:
#   osaSQLGetRow
#
# Purpose:
#   Following function runs a generic SQL-Plus command and returns the output.
#
# Arguments:  
#   (1) stmt:  an SQL-Plus compatible statement (superset of ANSI SQL)
#
# Output:
#   Rows of output
#
# Returns:
#   0 success
#   !=0 failure
#
function osaSQLPlusRun {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset stmt=$1
    typeset loadfile=$LOGDIR/sqlplus.load.$$
    typeset rc_file=$LOGDIR/sqlplus.result.$$
    typeset sqlplus_cmd

    osaSQLPlusSetup $loadfile $rc_file sqlplus_cmd

    echo "set heading off" > $loadfile
    echo "set pagesize 1000" >> $loadfile
    echo "$stmt" >> $loadfile
    [[ -s $loadfile && -n $ORACLE_USER ]] &&
    chown $ORACLE_USER $loadfile

    osaSQLPlusExecute sqlplus_cmd $rc_file
    rc=$?
    osaSQLPlusCleanup $loadfile $rc_file
    return $rc
}

#----------------------------------------------------------------------------
# Function:
#    osaSQLPlusExecute
#
# Arguments:
#   (1) sqlplus_cmd - an SQL-Plus compatible statement (superset of ANSI SQL)
#   (2) results file - file where sqlplus output will reside
#
# Returns:
#   0 success
#   !=0 failure
#
function osaSQLPlusExecute {
    typeset -n sqlplus_cmd=$1
    typeset rc_file=$2

    log_msg "Running SQL command: "$sqlplus_cmd" as user $ORACLE_USER"
    /usr/bin/su - $ORACLE_USER -c "$sqlplus_cmd"

    [[ -z $rc_file ]] &&
        return 0

    # Append the results captured in rc_file to oraclesa.log file
    # rc_file holds, sqlplus command along with loadfile details and its return value
    log_msg "Actual sqlplus command and its results are:\n$(cat $rc_file 2>/dev/null)"

    typeset -i rc=$(tail -1 $rc_file 2>/dev/null | cut -d':' -f2)
    return $rc
}

#----------------------------------------------------------------------------
# Function:
#   osaSQLPlusSetup
#
# Purpose:
#   Setup environment file for processing by sqlplus command (executed as
#   user oracle
#
# Arguments:
#   (1) load file - file containing sqlplus statements
#   (2) results file - file where sqlplus output will reside
#
# Returns:
#   n/a
#
function osaSQLPlusSetup {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset loadfile=$1
    typeset rc_file=$2
    typeset -n sqlplus_cmd=$3
    typeset envfile=$LOGDIR/sqlplus.env.$$

    [[ -z $loadfile ]] &&
        loadfile=$LOGDIR/sqlplus.load.$$
    [[ -z $rc_file ]] &&
        rc_file=$LOGDIR/sqlplus.result.$$

    /bin/rm -f $envfile $rc_file $loadfile >/dev/null 2>&1

    # Create the unique ENV file here, will be cleaned up in osaSQLPlusCleanup
    [[ -n $ORACLE_HOME ]] &&
        echo "ORACLE_HOME=$ORACLE_HOME" > $envfile
    [[ -n $ORACLE_SID ]] && 
        echo "ORACLE_SID=$ORACLE_SID" >> $envfile
    [[ -n $TNS_ADMIN ]] &&
        echo "TNS_ADMIN=$TNS_ADMIN" >> $envfile
    [[ -s $envfile && -n $ORACLE_USER ]] &&
    chown $ORACLE_USER $envfile

    [[ -n $ORACLE_USER ]] && {
        touch $rc_file
        chown $ORACLE_USER $rc_file
    }

    # Result from this command is stored in $rc_file.
    # This will be used in osaSQLPlusExecute.
    sqlplus_cmd="/usr/es/sbin/cluster/sa/oracle/sbin/cl_oraSqlPlus -e $envfile -s $loadfile -p $ORACLE_HOME/bin/ -r $rc_file -o $ORACLE_HOME"
}

function osaSQLPlusCleanup {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset loadfile=$1
    typeset rc_file=$2
    typeset envfile=$LOGDIR/sqlplus.env.$$
    
    [[ -z $loadfile ]] &&
        loadfile=$LOGDIR/sqlplus.load.$$
    [[ -z $rc_file ]] &&
        rc_file=$LOGDIR/sqlplus.result.$$
    
    /bin/rm -f $loadfile $envfile $rc_file >/dev/null 2>&1
}
    
#----------------------------------------------------------------------------
# Function:
#   osaSQLPlusStartup
#
# Purpose:
#   Start an Oracle instance
#
# Arguments:
#   n/a
#
# Permissions:
#   This command must be invoked by the
#   oracle user (user: oracle)
#
# Returns:
#   return code from sqlplus command
#   0 success
#   <0 failure
#
function osaSQLPlusStartup {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset loadfile=$LOGDIR/sqlplus.load.$$
    typeset rc_file=$LOGDIR/sqlplus.result.$$

    osaSQLPlusSetup $loadfile $rc_file sqlplus_cmd
    sqlplus_cmd=$sqlplus_cmd" -l '/nolog'"

    echo 'connect / as sysdba;' > $loadfile
    echo 'startup mount;' >> $loadfile
    [[ -s $loadfile && -n $ORACLE_USER ]] &&
    chown $ORACLE_USER $loadfile

    osaSQLPlusExecute sqlplus_cmd $rc_file
    rc=$?
    osaSQLPlusCleanup $loadfile $rc_file
    [[ $rc == 0 ]] && {
        out=$(osaSQLPlusRun "select count(*) from v\$backup where status='ACTIVE';")
        [[ -z $out ]] && rc=1
        out=$(echo $out) #Remove white spaces
        [[ $out != 0 ]] && {
            osaSQLPlusSetup $loadfile $rc_file sqlplus_cmd
            sqlplus_cmd=$sqlplus_cmd" -l '/nolog'"
            echo 'connect / as sysdba;' > $loadfile
            echo 'alter database end backup;' >> $loadfile
            [[ -s $loadfile && -n $ORACLE_USER ]] && chown $ORACLE_USER $loadfile
            osaSQLPlusExecute sqlplus_cmd $rc_file
            rc=$?
            osaSQLPlusCleanup $loadfile $rc_file
        }
            osaSQLPlusSetup $loadfile $rc_file sqlplus_cmd
            sqlplus_cmd=$sqlplus_cmd" -l '/nolog'"
            echo 'connect / as sysdba;' > $loadfile
            echo 'alter database open;' >> $loadfile
            [[ -s $loadfile && -n $ORACLE_USER ]] && chown $ORACLE_USER $loadfile
            osaSQLPlusExecute sqlplus_cmd $rc_file
            rc=$?
            osaSQLPlusCleanup $loadfile $rc_file
    }

    return $rc
}

#----------------------------------------------------------------------------
# Function:
#   osaDBGetVersion
#
# Purpose:
#   Returns the version number of the Oracle DB
#
# Arguments:
#   n/a
# 
# Output:
#   version number in Oracle form
#
# Returns:
#   0 on success
#   1 on failure
#
function osaDBGetVersion {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset stub version rest
    
    # We will get the oracle version information from select * v$version command
    # for the releases till 11c and the output of this command is in
    # CORE 11.2.0.2.0 Production format.
    osaSQLPlusRun 'select * from v$version;' | grep -iw CORE | read stub version rest
    
    # For version 18c we will use sqlplus command and the output from this command is
    # sqlplus -V
    # SQL*Plus: Release 18.0.0.0.0 - Production
    # Version 18.4.0.0.0
    if [[ -z $version ]]
    then
        sqlpath="$ORACLE_HOME/bin/sqlplus"
        version=$($sqlpath -V | grep -w Version | cut -d" " -f2)
    fi
    
    # For version 12c also we will use sqlplus command and the output from this command is
    # sqlplus -V
    # SQL*Plus: Release 12.0.0.0.0 - Production
    if [[ -z $version ]]
    then
        sqlpath="$ORACLE_HOME/bin/sqlplus"
        version=$($sqlpath -V | grep Release | cut -d" " -f3)
    fi

    [[ -z $version ]] && return 1
    echo $version
    return 0
}

#----------------------------------------------------------------------------
# Function:
#   osaDBGetVGs
#
# Purpose:
#   Returns a list of Volume Groups that contain the current ORACLE_SID
#   datafiles, controlfiles and redo-log files.
#
# Arguments:  
#   n/a
#
# Returns:
#   Returns a [unique] list of VGs
#
function osaDBGetVGs {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset out=""
    typeset stat=$(osaSQLPlusRun 'select status from V$INSTANCE;')
    typeset var=${stat//"ORACLE not available"/}
    if [[ $var != $stat ]]
          then
          log_msg "Could not connect to DB via BEQ or DB is not mounted."
          return 1
    fi

    typeset stat=$(osaSQLGetRow 'select status from V$INSTANCE;')
    if [[ $stat == "STARTED" ]]
    then
          log_msg "Could not connect to DB via BEQ or DB is not mounted."
          return 1
    else
        out=$(osaSQLPlusRun 'select name from v$datafile;')           # datafile names
        out=$out" "$(osaSQLPlusRun 'select name from v$controlfile;') # controlfile names
        out=$out" "$(osaSQLPlusRun 'select member from v$logfile;')   # redo logfile names
        typeset f vg
        typeset -A vgs
        for f in $out; do
            vg=$(KLIB_AIX_get_vg_by_path $f)
            log_msg $f"\t===>\t"$vg
            vgs[$vg]=1
        done
        echo ${!vgs[*]}
        return 0
    fi
}

#-----------------------------------------------------------------------------
# Function:
#   osaRemoveHAComponents
#
# Purpose:
#   Remove the application monitors, application servers, and resource groups
#   from the HACMP configuration.  In addition the HACMP metadata stored for
#   this instance is removed from the cluster configuration.
#
# Arguments:
#   (1) application name
#  
# Returns:
#   0 on success
#   1 on failure
#
function osaRemoveHAComponents {
    [[ "$VERBOSE_LOGGING" == "high" ]] && set -x
    typeset appid=$1
    typeset rgs=""

    # Get ResourceGroups and check if any rgdependency
    # If rgdependency exists then log error and return
    rgs=$(osaASGetResourceGroups $appid)
    for rg in $rgs; do
        dependency=$(clodmget HACMPrgdependency | grep -w $rg)
        if [[ -n $dependency ]]
        then
            user_msg 90 10 $rg
            InternalErrorAbort
            return 1
        fi
    done
    
    for app in $(osaASGetResource $appid APPLICATIONS); do
        MONITORS=$(clvt query application $app | grep ASSOCIATEDMONITORS | awk -F= '{ print $2 }' | sed -e "s/\"//g")
        for monitor in $MONITORS; do
            clvt delete application_monitor $monitor
            (( $? != 0 )) && {
                user_msg 30 21 $monitor
                InternalErrorAbort
                return 1
            }
        done

        clvt delete application $app > /dev/null 2>&1
        (( $? != 0 )) && {
            user_msg 30 22 $app
            InternalErrorAbort
            return 1
        }
    done

    for rg in $rgs; do
        #Deleting the serviceIP of the application as modified service-ip will be added to application while recreating the instance.
        typeset serviceIP=$(clmgr -cS -a SERVICE_LABEL query resource_group $rg )
        for sip in $serviceIP; do
            if [[ -n $sip ]]; then
                clmgr delete service_ip $sip  >/dev/null 2>&1
                (( $? != 0 )) && {
                user_msg 30 12 $sip
                InternalErrorAbort
                return 1
            }
            fi  
        done
        clvt delete resource_group $rg >/dev/null 2>&1
        (( $? != 0 )) && {
            user_msg 30 23 $rg
            InternalErrorAbort
            return 1
        }
    done

    # Remove the application (SA) references for this instance
    clrmsaapp -a $appid
    (( $? != 0 )) && {
        user_msg 30 24 $appid
        InternalErrorAbort
        return 1
    }

    return 0
}


#export ORACLE_HOME="/orasoft/10g/oracle/product/10.2.0/db_2"
#osaSetDBEnvs
#osaDBGetVGs
#osaSQLGetRow 'select status from V$INSTANCE'
# #osaSQLGetRow 'desc V$INSTANCE'
#osaFindListeners oradb1 
#i=0
#num=${#allDBListeners[*]}
#if [[ $num -gt 0 ]] 
#   then
#   while [[ $i < $num ]]
#      do
#      echo ${allDBListeners[$i]}
#       (( i=$i+1 ))
#    done
# else
#     echo "No Listeners defined."
# fi