Category Archives: Common

TAR Common

 


tar_help() 
##############################################################
# This will display the help for the tar command
##############################################################
{ 
tar --help
} 


tar_create()
##############################################################
# This will create a tar file
##############################################################
{
tarnm=$1
tardir=$2

tar -cvf $tarnm $tardir

}

tar_view()
##############################################################
# This will allow you to view a tar 
############################################################## 
{
flnm=$1
tar -tvf $flnm
}

tar_all()
{
echo " "
}

tar_mv()

{

cd /oracle/admin/scripts

ls -lt asm*tar

}

 

gg_common.ksh 04172019


#!/bin/ksh
############################################################################
# Script Name..: gg_common.ksh
# Description..: Common GG Hub functions for inclusion to all driver scripts
# Author.......: Michael Culp
# Date.........: 04/23/2010
# Version......: .65
# Modified By..: Michael Culp
# Date Modified: 06/07/2017
# Comments.....: Change this library to make mods to all functions
# Schema owner.: N/A
# Dependent on.: N/A
# Script type..: function library
# Function list:
############################################################################

############################################################################
#
############################################################################

gg_init()
##########################################################
# These variables should be set prior to setup
# Call this function anytime the variables need to be
# set for values that will be utilized in other functioons
# gg_init should be used to determine which environment
# needs to be setup
#########################################################
{

# Take a parameter for the instance
gginst=$1


# Do not change the following 2 variables
#########################################################
ggcomver=".92"
ggcomdt="10/18/2018"

######################################################### 
# set -xv
# This will set the ggini file based on the hostname
#########################################################
initflnm=`hostname -a`".ggini"

echo "Display the init file name"
echo $initflnm

# Test to see if the is an init file first
# if so read it, otherwise set these variables

#   future enhancement check for various scripts standard names
echo
echo
echo "Checking for the existance of the ggini initialization file.."
echo


# if [ -f /oracle/admin/scripts/ggcommon/`hostname -a`.ggini ]; then
if [ -f /oracle/admin/scripts/ggcommon/`hostname -a`.gginix ]; then
   echo "....file exists...."
   echo
   echo " The ggini file was read ....Y"
   echo
   echo "....reading variables from the .ggini file....."
   echo
#   . ./`hostname -a`.ggini
   . /oracle/admin/scripts/ggcommon/`hostname -a`.ggini
else
   echo "....file does not exist"
   echo
   echo " The ggini file was not read ...."
   echo
   echo "....reading variables from the gg_init function....."
   echo
   #########################################################
   # Set the Oracle inventory location
   #########################################################
   orainv="/oracle/oraInventory"

   echo
   echo "Running gg_init function()......."
   echo

   #######################################################
   # GoldenGate operational directory
   # This needs to be set during build of additional
   # GoldenGate instances
   #######################################################
   # gg_dir="/oracle/product/gg123"
   # gg_dir="/oracle/product/gg123ms"
   # gg_dir="/oracle/product/gg122"

   # gg_dir="/oracle/product/ggvad001"
   # gg_dir="/oracle/product/ggvad001"
   # gg_dir="/oracle/product/ggvad001"
   # gg_dir="/oracle/product/ggtxd003"
   # gg_dir="/oracle/product/ggtxd002"
   # gg_dir="/oracle/product/ggtxd001"
   # gg_dir="/oracle/product/ggtxd006"
   # gg_dir="/oracle/product/ggtxd007"
   gg_dir="/oracle/product/ggvat004"

   # gg_dir="/oracle/product/gg121"

   export GGS_HOME=$gg_dir

   export OGG_HOME=$GGS_HOME

   #######################################################
   # GoldenGate version
   # We support GG 12.1, 12.2, 12.3 currently
   #######################################################
   ggver="12.3"
   # ggver="12.2"
   # ggver="12.1"

   # export DBFS_MNT=/dbfs

   #######################################################
   # GoldenGate source version
   #######################################################
   ggsrcver="12.1"

   #######################################################
   # ggflnm - is the zip file name of the GG distro
   # This is the name of the goldengate zip files
   #######################################################
   # File below is 12.3.0.1.2
   ggflnm="123014_fbo_ggs_Linux_x64_shiphome.zip"

   # File below is 12.3.0.1.2
   # ggflnm="123012_fbo_ggs_Linux_x64_shiphome.zip"

   # File below is 12.2
   # ggflnm="V100692-01.zip"

   # File below is 12.3 Micro Service Architecture
   # ggflnm="123010_fbo_ggs_Linux_x64_shiphome.zip"
   #######################################################


   #######################################################
   # ggdir_dist - directory of the distro zip file
   # This is the name of the directory where the distribution GoldenGate software is stored
   # Choose one and uncomment it
   #######################################################
   ##### GoldenGate ver. 12.3
   # ggdir_dist="/ora01/Oracle_SW/gg123/"
   ##### GoldenGate ver. 12.3.0.1.4
   ggdir_dist="/ora01/Oracle_SW/gg123/123014/"
   ##### GoldenGate ver. 12.3 MicroService Architecture
   # ggdir_dist="/ora01/Oracle_SW/gg123ms/"
   # ggdir_dist="/ora01/Oracle_SW/gg122/"
   # ggdir_dist="/ora01/Oracle_SW/gg121/"

   #######################################################
   # gginstdir - Location of the installer
   # GoldenGate installer directory
   #######################################################
   gginstdir=$ggdir_dist"fbo_ggs_Linux_x64_shiphome/Disk1"

   #######################################################
   # ggrspfldir - Response file directory
   # Response file directory
   #######################################################
   ggrspfldir=$ggdir_dist"fbo_ggs_Linux_x64_shiphome/Disk1/response"

   #######################################################
   # ggrspfl - response file name
   # Response file name for install of the GG binaries
   #######################################################
   ggrspfl="oggcore_mrc.rsp"

   #######################################################
   # XAG version
   #######################################################
   # xagver="7.1b"
   # xagver="8.1b"
   xagver="9.1"

   #######################################################
   # This is the name of the directory where the XAG software is stored
   #######################################################
   # xagdir_dist="/ora01/Oracle_SW/xag71/"
   # xagdir_dist="/ora01/Oracle_SW/xag81b/"
   xagdir_dist="/ora01/Oracle_SW/xag91/"

   #######################################################
   # XAG Ops directory
   # This is the operational directory for XAG
   #######################################################
   # xagdir="/oracle/product/xag_71"
   # xagdir="/oracle/product/xag_81b"
   xagdir="/oracle/product/xag_91"


   #######################################################
   # XAG home directory
   #######################################################
   XAG_HOME=/oracle/product/xag_91
   XAG_LOG=/oracle/product/xag_91/log/nodename


   #######################################################
   # This is the name of the xag zip file
   #######################################################
   # xagflnm="xagpack81b.zip"
   xagflnm="xagpack91.zip"

   # Read the GG environment files
   # . ./ggoraenv.env

fi

XAG_SD=/oracle/admin/scripts/ggcommon

CRS_SD=/oracle/admin/scripts/crsdbfs
DBFS_SD=/oracle/admin/scripts/clle_dbfscommon
GG_SD=/oracle/admin/scripts/ggcommon
ASM_SD=/oracle/admin/scripts/asmcommon
DG_SD=/oracle/admin/scripts/dgcommon


############################################################################
# Show the variable status dashboard,  comment this line to not display it
############################################################################
gg_init_dbrd

}

gg_init_dbrd()
########################################################
# This is a display dashboard that shows the values of
# Environmental variables
# used in various functions
########################################################
{

echo
echo "Change variables in gg_common.ksh gg_init...."
echo "Display with gg_init_dbrd function .........."
echo

echo
echo "..gg_common.ksh ver. $ggcomver Date $ggcomdt"
echo "....Oracle Inventory location................: " $orainv
echo "....GoldenGate Version.......................: " $ggver
echo "....GoldenGate Source Version supported......: " $ggsrcver
echo
echo "================= Installation Parameters ======================="
echo "....GoldenGate Filename......................: " $ggflnm
echo "....GoldenGate software distribution dir.....: " $ggdir_dist
echo "....GoldenGate Response File Dir.............: " $ggrspfldir
echo "....GoldenGate Response File ................: " $ggrspfl
echo "....GoldenGate Installer directory...........: " $gginstdir
echo "....GoldenGate Ops  Directory................: " $gg_dir
echo "....GoldenGate Home..GGS_HOME................: " $GGS_HOME
echo "....GoldenGate Home..OGG_HOME................: " $OGG_HOME
echo
echo "....XAG Version..............................: " $xagver
echo "....XAG Filename.............................: " $xagflnm
echo "....XAG Dist directory.......................: " $xagdir_dist
echo "....XAG Ops directory........................: " $xagdir
echo "....XAG Home directory.......................: " $XAG_HOME
echo ".......XAG scripts directory.................: " $XAG_SD
echo "....XAG Log Directory........................: " $XAG_LOG
echo
echo "================= DBFS Parameters =============================="

echo "================= Scripts Directoriess ========================="
echo ".......CRS.................: " $CRS_SD
echo ".......DBFS................: " $DBFS_SD
echo ".......GoldenGate..........: " $GG_SD
echo ".......XAG.................: " $XAG_SD
echo ".......DataGuard...........: " $DG_SD
echo "================================================================"

echo
echo "....LD_LIBRARY_PATH..........................: " $LD_LIBRARY_PATH
echo
echo "....PATH.....................................: " $PATH
echo

echo "Installation directories......................."
echo
ls -lt /oracle/product/


# echo "Show ldd for libraries........................."
# ldd -d extract
# echo "....end transmission..........................."

}


gg_cr_inst_init()
{

echo
echo "Create Install INIT file....."
echo
cat << EOS > gg_inst_init_$gginst.env

export GGS_HOME=/oracle/product/gg122

EOS

}

gg_unzip()
##############################################
# Unzip the GG distribution file
# See gg_init function for the variables:
#     ggdir_dist
#     ggflnm
##############################################
{

ggdir_dist=$1
ggflnm=$2

echo
echo "Unzip function...."
echo

cd $ggdir_dist
unzip $ggflnm

}



crs_common.ksh 04162019


 1="lrdne67mp" 2="[DBFSXD011"]-> cat crs_common.ksh

##############################################################################
# CRS Common functions
#
##############################################################################

crs_inst_order()
{

echo
echo "Function name.: crs_inst_order................."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Shows the installation order for the CRS services..."
echo

echo "Create a service first for GoldenGate"
echo " function - srvctl_add_svc"
echo " add a resource in CRS for DBFS (crs_add_res_dbfs)......"
echo "   naming is dbfs_ gg instance name.."
echo " start the DBFS resource............."
echo " stop the DBFS resource.............."
echo " status of the resource.............."
echo " relocate the resource..............."
echo " "

}

crs_res_add_dbfs()
##############################################################################
# Add resource to CRS for DBFS
##############################################################################
{

# GoldenGate XAG Instance
gg_inst=$1

# Database for dependencies
dbnm=$2

# Project Directory
projdir=$3

echo $gg_inst
echo $dbnm
echo $projscr
echo $projdir/mount_dbfs_$gg_inst.bsh

echo
echo "Function name.: crs_res_add_dbfs..............."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Creating a CRS resource for DBFS..............."
echo

######################################################################
# Check to see if the .bsh script has been created ...."
######################################################################
if [ ! -f "$projscr/mount_dbfs_$gg_inst.bsh" ]; then
   echo
   echo "The mount script does not exist please create and re-run";
   echo
   exit
else
   echo
   echo "The mount script exists and proceeding with CRS resource create"
   echo
fi

######################################################################
# Check to see if the .conf file has been created ...."
######################################################################
if [ ! -f "$projscr/mount-dbfs_$gg_inst.conf" ]; then
   echo
   echo "The mount conf file does not exist please create and re-run";
   echo
   exit
else
   echo "The mount conf file exists and proceeding with CRS resource create"
fi

echo
echo "Adding CRS resource for DBFS dbfs_"$gg_inst
echo "MAKE SURE THE mount_dbfs script is in place before executing this!!!!"
echo

$CRS_HOME/bin/crsctl add resource dbfs_${gg_inst} -type cluster_resource -attr "ACTION_S                                  CRIPT=$projscr/mount_dbfs_${gg_inst}.bsh, CHECK_INTERVAL=30, RESTART_ATTEMPTS=10, START_                                  DEPENDENCIES='hard(ora.$dbnm.db)pullup(ora.$dbnm.db)',STOP_DEPENDENCIES='hard(ora.$dbnm.                                  db)',SCRIPT_TIMEOUT=300"

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl add resource dbfs_${gg_inst} -type clust                                  er_resource -attr "ACTION_SCRIPT=/oracle/admin/scripts/crsdbfs/wrap_mount_dbfs_${gg_inst                                  }.bsh, CHECK_INTERVAL=30, RESTART_ATTEMPTS=10, START_DEPENDENCIES='hard(ora.$dbnm.db)pul                                  lup(ora.$dbnm.db)',STOP_DEPENDENCIES='hard(ora.$dbnm.db)',SCRIPT_TIMEOUT=300"

}



crs_res_start()
##################################################################
# for the $1 parm only the name after dbfs_ needs to be specified
# Start the dbfs resource
##################################################################
{

dbfsmnt=$1
nodename=$2

clear
echo
echo "Function name.: crs_res_start.................."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Starting CRS resource for DBFS mount "$dbfsmnt
echo
echo "Start DBFS resource...."
echo

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl start resource dbfs_$dbfsmnt -n server67mp
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl start resource dbfs_$dbfsmnt -n $nodename

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl status resource dbfs_ggtxd001

echo
echo "Status of DBFS resource...."
echo

crs_res_stat $dbfsmnt

echo
echo "df -hs or /dbfs_ mountpoints ...."
echo

df -ha |grep dbfs_

}


crs_res_stop()
{

dbfsmnt=$1

clear
echo
echo "Function name.: crs_add_stop..................."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Stopping CRS resource for DBFS................."
echo
echo "Stop DBFS resource...."
echo
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stop resource dbfs_$dbfsmnt

echo
echo "Status of DBFS resource...."
echo
crs_res_stat $dbfsmnt

echo
echo "df -hs or /dbfs_ mountpoints ...."
echo
df -ha |grep dbfs_

}


crs_res_stat()
#########################################
# Resource status
#########################################
{

gg_inst=$1

### RVK Change Jan 14th 2019 >>>
### Replaced CRS_HOME hardcode path with $CRS_HOME (Note: CRS_HOME in Dev VA is differen                                  t
### RVK End <<<

$CRS_HOME/bin/crsctl status resource dbfs_$gg_inst

}

crs_res_relo()
#########################################
# Resource relocate
#########################################
{

gg_inst=$1
to_node=$2

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl relocate resource dbfs_$gg_inst -node $to_                                  node

}


crs_res_del()
#########################################
# Delete DBFS resource
#########################################
{

gg_inst=$1

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl delete resource dbfs_$gg_inst

}


crs_stat()
{

########################################################################################                                  ###
# The difference on the crs_stat is the directory mismatch on the TX vs VA cluster pair
# Directory needed for TX cluster is 12.1.0.2
########################################################################################                                  ###

########################################################################################                                  ###
# This should be in the ggini file in order to drive the correct directory for all stand                                  ard
# scripts
########################################################################################                                  ###

bindir="12.1.0.2"

echo
echo "crsctl stat res -t...."
echo
/oracle_crs/product/$bindir/crs_1/bin/crsctl status resource -t

echo
echo "Show services from the resource list...."
echo
/oracle_crs/product/$bindir/crs_1/bin/crsctl status resource -t|grep -a1 .svc

echo
echo "If nothing shows here, there are no dbfs mounts ......"
echo
df -ha|grep dbfs

}


crs_stat_xag()
{

gg_inst=$1
dbnm=$2

echo
echo "If XAG resource is not yet setup then this will not work....."
echo
echo "XAG Instance name is $gg_inst / crsctl stat res xag.$gg_inst.goldengate -p all par                                  ameters ...."
echo

# /oracle_crs/product/$bindir/crs_1/bin/crsctl stat res xag.ggtxd001.goldengate -p
/oracle_crs/product/$bindir/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p

echo
echo "Cluster resource cluster resource  (dbfs) action script values...."
echo

/oracle_crs/product/$bindir/crs_1/bin/crsctl stat res -w "TYPE = cluster_resource" -p |                                   grep ACTION_SCRIPT

echo
echo "Cluster resource (dbfs) ..."
echo
# /oracle_crs/product/$bindir/crs_1/bin/crsctl stat res ora.dbfsxd01.dbfs_ggtxd001.svc -                                  p
/oracle_crs/product/$bindir/crs_1/bin/crsctl stat res ora.$dbnm.dbfs_$gg_inst.svc -p

}


crs_stat_xag_det()
{

########################################################################################                                  
# Were setting bindir because there is a difference between location of the binaries
# on TX cluster vs VA cluster
########################################################################################                                  

bindir="12.1.0.2"

gg_inst=$1

echo
echo "XAG Instance name is $gg_inst / crsctl stat res xag.$gg_inst.goldengate -p specifi                                  c parameters ...."
echo

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   DATAGUARD_AUTOSTART
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   DB_SERVICES
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   START_DEP
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   STOP_DEP
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   HOSTING
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   FILESYST
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   GG_HOME
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   PLACEMENT
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   USE_LOCAL_SERVICES
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep                                   RELOCATE

}


srvctl_add_svc()
#######################################################
# This is critical for the overall operation
#######################################################
{

gg_inst=$1
dbnm=$2
pfd=$3
avl=$4

echo
echo "Create a service for GoldenGate in the format of svc_$gg_inst.....................                                  ..."
echo "This service is then used for setup through XAG for keying when GoldenGate fails o                                  ver"
echo "SRVCTL add service for svc_$gg_inst when role is primary..........................                                  ..."
echo "This adds a GoldenGate resource thru SRVCTL ......................................                                  ..."
echo "-preferred takes an instance name as parameter as does -available.................                                  ..."
echo
echo "Showing before services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

echo
echo "Attempting to add service svc_"$gg_inst
echo

# Example on next line
# srvctl add service -db $dbnm -service svc_$gg_inst -role PRIMARY -preferred DBFSXD011                                   -available DBFSXD012
srvctl add service -db $dbnm -service svc_$gg_inst -role PRIMARY -preferred $pfd -availa                                  ble $avl

echo
echo "Showing after services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

}


srvctl_svc_mod()
{

dbnm=$1
svcnm=$2
pfd=$3
avl=$4

echo
echo "This modifies the GG service created for XAG triggering "
echo "the service name automatically has svc_ prepended to avoid misconfig for different                                   service"
echo

srvctl modify service -db $dbnm -service svc_$svcnm -modifyconfig -preferred $pfd -avail                                  able $avl -force

}


srvctl_sho_svc()
{

dbnm=$1

clear
echo
echo "Showing services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

}


crs_cr_mount_scrpt()
{

vsn="1.00"
gginst=ggmrct001
flnm="mount_dbfs_mculp_test.bsh"


echo
echo "Create a mount script...."
echo " Version "$vsn
echo

# cat here for create of file
echo "....creating $flnm file......."

cat << EOS > $flnm

### This script is from Note 1054431.1, ensure you have the latest version
### Note 1054431.1 provides information about the setup required to use this script
### MCulp modified 08/01/2018
### The start section has been modified to work with BOA environment

###############################################
# mount-dbfs.bsh start / stop / check / status
###############################################

###############################################################
### All configuration parameters are now in an external file
###############################################################

###########################################
### Ensure that when multiple mounts are used, there are separate copies
###   of mount-dbfs.sh that reference separate CONFIG file pathnames
# CONFIG=/etc/oracle/mount-dbfs.conf

# MCulp change location #1
# This should have a conf file unique for each mount
# The location is /oracle/admin/scripts for both the script
# and the config file
##############################################################

# CONFIG=/oracle/admin/scripts/crsdbfs/mount-dbfs_ggtxp002.conf
CONFIG=/oracle/admin/scripts/crsdbfs/mount-dbfs_ggtxp003.conf


###########################################
### No editing is required below this point
###########################################
### date-based versioning YYYYMMDD
VERSION=20160215

### source configuration file
if [ -r $CONFIG ]; then
   . $CONFIG
else
   echo "$0 ERROR: cannot read config file $CONFIG, aborting"
   exit 1
fi

### the DBNAME should be set to the CDB name (from config file)
CDB=$DBNAME

### determine platform
UNAME_S=`uname -s`
if   [ $UNAME_S = 'Linux' ]; then LINUX=1; SOLARIS=0;
elif [ $UNAME_S = 'SunOS' ]; then LINUX=0; SOLARIS=1;
fi

GREP=/bin/grep
AWK=/bin/awk
SED=/bin/sed
ECHO=/bin/echo
LOGGER="/bin/logger -t DBFS_${MOUNT_POINT}"
RMF='/bin/rm -f'
TOUCH=/bin/touch
CHMOD=/bin/chmod
PS=/bin/ps
SLEEP=/bin/sleep
KILL=/bin/kill
BASENAME=/bin/basename
STAT=/usr/bin/stat
ID=/usr/bin/id
WC=/usr/bin/wc
SRVCTL=$ORACLE_HOME/bin/srvctl
DBFS_CLIENT=$ORACLE_HOME/bin/dbfs_client
HN=/bin/hostname
PERL=/usr/bin/perl
MOUNT=/bin/mount
### ensure messages are displayed in English for pattern matching
LANG=en_US.UTF-8
NLS_LANG=American_America.AL32UTF8
NUMACTL=/usr/bin/numactl
RPMCTL=/bin/rpm


if [ -z "$STATUS_TIMEOUT" ]; then STATUS_TIMEOUT=0; fi

if [ $LINUX -eq 1 ]; then
  MOUNT=/bin/mount
  XARGS='/usr/bin/xargs -r'
  FUSERMOUNT=/bin/fusermount
  LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64
elif [ $SOLARIS -eq 1 ]; then
  MOUNT=/sbin/mount
  XARGS=/usr/bin/xargs
  UMOUNT=/usr/sbin/umount
  LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/usr/lib:/lib
fi

DBFS_PWDFILE=$DBFS_PWDFILE_BASE.$$

export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
export STAT MOUNT_POINT PERL_ALARM_TIMEOUT SOLARIS LINUX
export PATH=$ORACLE_HOME/bin:$PATH
export STATUS_TIMEOUT
export LANG NLS_LANG

logit () {
  ### type: info, error, debug
  type=$1
  msg=$2
  if [ "$type" = "info" ]; then
    $ECHO $msg
    $LOGGER -p ${LOGGER_FACILITY}.info "$msg"
  elif [ "$type" = "error" ]; then
    $ECHO $msg
    $LOGGER -p ${LOGGER_FACILITY}.error "$msg"
  elif [ "$type" = "debug" ]; then
    $ECHO $msg
    $LOGGER -p ${LOGGER_FACILITY}.debug "$msg"
  fi
}

### must not be root
if [ `$ID -u` -eq 0 ]; then
  logit error "Run this as the Oracle software owner, not root"
  exit 1
fi

### determine how we were called, derive location
SCRIPTPATH=$0
SCRIPTNAME=`$BASENAME $SCRIPTPATH`

$ECHO $SCRIPTPATH | $GREP ^/ > /dev/null 2>&1
if [ $? -ne 0 ]; then
  MYDIR=`pwd`
  SCRIPTPATH=${MYDIR}/${SCRIPTPATH}
fi

### must cd to a directory where the oracle owner can get CWD
cd /tmp

case "$1" in

##########################################
# Start begins here
##########################################
'start')
  echo "Start...."
  echo
  logit info "$SCRIPTNAME mounting DBFS at $MOUNT_POINT from database $DBNAME"

  ### check to see if it is already mounted
  $SCRIPTPATH status > /dev/null 2>&1
  if [ $? -eq 0 ]; then
    logit error "$MOUNT_POINT already mounted, use $SCRIPTNAME stop before attempting to                                   start"
    $SCRIPTPATH status
    exit 1
  fi

  ### set the ORACLE_SID dynamically based on OCR info, if it is running
  # if RAC, set SID with following command
  export ORACLE_SID=$($SRVCTL status instance -d $DBNAME -n `$HN` | \
         $GREP 'is running' | $AWK '{print $2}' )

  # if single instance, set ORACLE_SID based on below command instead
  if [ -z "$ORACLE_SID" ]; then
    export ORACLE_SID=$($SRVCTL config db -d $DBNAME | $GREP 'instance' | $AWK '{print $                                  3}')
  fi

  logit info "ORACLE_SID is $ORACLE_SID"

  ### further checks required if we are using a PDB
  ### if the local instance isn't running (because ORACLE_SID is null) don't bother
  if [ -n "$ORACLE_SID" -a "$IS_PDB" = 'true' ]; then
    logit info "IS_PDB = true"
    HOSTNAME=`$HN -s`
    ### by checking to see if the service is running on the local instance,
    ###   we can deterine whether we should continue or not
    PDB_SERVICE_STATE=$($SRVCTL status service -d $CDB -s $PDB_SERVICE | \
        $GREP "$ORACLE_SID" )
    ### if the output from the last command is > 0, service is running locally
    if [ -n "$PDB_SERVICE_STATE" ]; then
      logit info "PDB service is online"
    else
      logit error "PDB service is offline.  Exiting"
      exit 2
    fi
  fi

  ### if there's no SID defined locally or it isn't running, stop
  if [ -z "$ORACLE_SID" -a "$WALLET" = 'false' ]; then
    logit error "No running ORACLE_SID available on this host, exiting"
    exit 2
  fi

  ### version comparison function, used in numa section
  # Compare string versions
  # Returns in stdout and in the err code
  # 0        - a equal b
  # 1        - a greater than b
  # 255(-1)  - a less than b
  # version should be in format 1.2.3.4.5
  # It might be a short version like 1.2.3 or 1.2.3.

  version_cmp ()
  {
    local -a al=(`$ECHO $1 | $SED -e 's/\./ /g'`)
    local -a bl=(`$ECHO $2 | $SED -e 's/\./ /g'`)

    local -i i=0
    for ((i=0; i < ${#al[@]}; i++)); do
      # ap is always non-empty. Gap is not possible in both arrays
      local ap=${al[$i]}
      local bp=${bl[$i]}
      # Only $ap defined. $a longer (bigger) then $b
      if [ -z "$bp" ] || [ $ap -gt $bp ]; then
        $ECHO "1"
        return 1
      elif [ $ap -lt $bp ]; then
        $ECHO "-1"
        return -1
      fi
    done
    # Check for the next part from @bl. It means $a shorter (smaller) then $b
    if [ -n "${bl[$i]}" ]; then
      $ECHO "-1"
      return -1
    fi
    # Both arrays ended at the same time. They are equal
    $ECHO "0"
    return 0
  }

# MCulp commenting out the whole section as we do not need this currently
########################################################################################                                  ###
#  ### if numa system on Linux, update mount_options for bug 10004611
#  if [ $LINUX -eq 1 ]; then
#    RPMEXA=`$RPMCTL -q --queryformat '%{VERSION}' exadata-base`
#    NUMASYS=`$NUMACTL --hardware | $GREP available: | cut -c1-18`
#
#    ###Check if an X8 system and image greater than or equal to 11.2.3.3.0 to support n                                  uma mount option
#    if [ "$NUMASYS" = 'available: 8 nodes' ]; then
#      if [ `version_cmp $RPMEXA 11.2.3.3.0` -eq 0 ] || [ `version_cmp $RPMEXA 11.2.3.3.                                  0` -eq 1 ]; then
#        # MOUNT_OPTIONS=$MOUNT_OPTIONS,numa
#        MOUNT_OPTIONS=$MOUNT_OPTIONS
#      else
#        MOUNT_OPTIONS=$MOUNT_OPTIONS
#      fi
#
#    ###Check if an X5-2 system to support numa mount option
#    elif [ "$NUMASYS" = 'available: 2 nodes' ]; then
#      # MOUNT_OPTIONS=$MOUNT_OPTIONS,numa
#      MOUNT_OPTIONS=$MOUNT_OPTIONS
#    ###Check for all other X2 systems, do not use numa option
#    elif [ "$NUMASYS" = 'available: 1 nodes' ]; then
#      MOUNT_OPTIONS=$MOUNT_OPTIONS
#    ###Exit for errors or unexpected values, post in messages file
#    else
#      logit error "Unexpected numa value.  Exiting."
#      logit error "Numa hardware value is: $NUMASYS"
#      logit error "RPM version of Exadata base is: $RPMEXA"
#      exit 1
#    fi
#  else
#    logit info "skipped numa check for Solaris...not applicable"
#  fi
########################################################################################                                  ###

  ### if using password-based startup, use this
  if [ "$WALLET" = 'false' -a -n "$DBFS_PASSWD" ]; then
    $RMF $DBFS_PWDFILE
    if [ -f $DBFS_PWDFILE ]; then
      logit error "please remove $DBFS_PWDFILE and try again"
      exit 1
    fi

    $TOUCH $DBFS_PWDFILE
    $CHMOD 600 $DBFS_PWDFILE
    $ECHO $DBFS_PASSWD > $DBFS_PWDFILE

    logit info "spawning dbfs_client command using SID $ORACLE_SID"
    ### if PDB, mount with this command
    if [ "$IS_PDB" = 'true' ]; then
      (nohup $DBFS_CLIENT ${DBFS_USER}@${PDB} -o $MOUNT_OPTIONS \
          $MOUNT_POINT < $DBFS_PWDFILE | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &
    ### if not PDB, mount with this command instead
    else
      echo "Correct command...."

      ### MCulp changed

#     nohup $ORACLE_HOME/bin/dbfs_client ggtxp003/not4good@dbfs_ggtxp003 -o rw,direct_io                                   /dbfs_ggtxp003 < /oracle/admin/scripts/clle_dbfscommon/.gg_password &>/oracle/admin/scr                                  ipts/crsdbfs/nohup.out

#     nohup $ORACLE_HOME/bin/dbfs_client ggtxp003/not4good@dbfs_ggtxp003 -o rw,direct_io                                   /dbfs_ggtxp003 < /oracle/admin/scripts/clle_dbfscommon/.gg_password &
      nohup $ORACLE_HOME/bin/dbfs_client ggtxp002/not4good@dbfs_ggtxp003 -o $MOUNT_OPTIO                                  NS /dbfs_ggtxp003 < /oracle/admin/scripts/clle_dbfscommon/.gg_password &

#     MOUNT_OPTIONS=rw,direct_io

# This is the original mount command unmodified
# MAKE SURE TO MODIFY THE entry after the @ symbol

# The next line is the original line
#        (nohup $DBFS_CLIENT ${DBFS_USER}@ -o $MOUNT_OPTIONS \
#          $MOUNT_POINT < $DBFS_PWDFILE | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &


# MOUNT_TNS as added to the conf file that is read in
########################################################################################
echo
echo $DBFS_USER
echo
echo $MOUNT_TNS
echo
echo $MOUNT_OPTIONS
echo
echo


#       (nohup $DBFS_CLIENT ${DBFS_USER}@$MOUNT_TNS -o $MOUNT_OPTIONS \
#          $MOUNT_POINT < $DBFS_PWDFILE | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &

    fi
  ### if wallet is true, mount with this command
  elif [ "$WALLET" = true ]; then
    logit info "doing mount $MOUNT_POINT using SID $ORACLE_SID with wallet now"
    (nohup $DBFS_CLIENT /@${DBFS_LOCAL_TNSALIAS} -o $MOUNT_OPTIONS,wallet \
          $MOUNT_POINT | $LOGGER -p ${LOGGER_FACILITY}.info 2>&1 & ) &
  fi

  ### allow time for the mount table update before checking it
  $SLEEP 1
  $RMF $DBFS_PWDFILE
  ### set return code based on success of mounting
  $SCRIPTPATH status > /dev/null 2>&1
  if [ $? -eq 0 ]; then
    logit info "Start -- ONLINE"
    exit 0
  else
    logit info "Start -- OFFLINE"
    exit 1
  fi

  ;;


############################################################
# MCulp Changed
# Added z to the fusermount options as the u by itself
# was not working correctly
############################################################

'stop')
  $SCRIPTPATH status > /dev/null
  if [ $? -eq 0 ]; then
     logit info "unmounting DBFS from $MOUNT_POINT"
     if [ $LINUX -eq 1 ]; then
        logit info "umounting the filesystem using '$FUSERMOUNT -uz $MOUNT_POINT'"
#       $FUSERMOUNT -u $MOUNT_POINT
        $FUSERMOUNT -uz $MOUNT_POINT

     elif [ $SOLARIS -eq 1 ]; then
        logit info "umounting the filesystem using '$UMOUNT $MOUNT_POINT'"
        $UMOUNT $MOUNT_POINT > /dev/null 2>&1
     fi
     $SCRIPTPATH status > /dev/null
     if [ $? -eq 0 ]; then
        logit error "Stop - stopped, but still mounted, error"
        exit 1
     else
        logit info "Stop - stopped, now not mounted"
        exit 0
     fi
  else
    logit error "filesystem $MOUNT_POINT not currently mounted, no need to stop"
  fi
  ;;


'check'|'status')
### check to see if it is mounted
### fire off a short process in perl to do the check (need the alarm builtin)
logit debug "Checking status now"
$PERL <<'TOT'
   $timeout = $ENV{'PERL_ALARM_TIMEOUT'};
   $SIG{ALRM} = sub {
      ### we have a problem and need to cleanup
      exit 3;
      die "timeout" ;
   };
   alarm $timeout;
   eval {
      $STATUSOUT=`$ENV{'STAT'} -f -c "%T" $ENV{'MOUNT_POINT'} 2>&1 `;
      chomp($STATUSOUT);
      ### added fuseblk check for Linux 6 output
      if ( ( $ENV{'SOLARIS'} == 1 && $STATUSOUT eq 'uvfs' ) ||
           ( $ENV{'LINUX'} == 1   && $STATUSOUT eq 'UNKNOWN (0x65735546)' ) ||
           ( $ENV{'LINUX'} == 1   && $STATUSOUT eq 'fuseblk' ) ) {
         ### status is okay
         exit 0;
      } elsif ( $STATUSOUT =~ /Transport endpoint is not connected/ ) {
        ### we have a problem, need to clean up
        exit 2;
      } else {
        ### filesystem is offline
        exit 1;
      }
    };
TOT

RC=$?
### process return codes from the perl block
if [ $RC -eq 3 ]; then
   STATUS_TIMEOUT=$(( $STATUS_TIMEOUT + 1 ))
   logit error "Found timeout while checking status, cleaning mount automatically"
   $SCRIPTPATH clean
   logit debug "Check -- OFFLINE"
   exit 1
elif [ $RC -eq 2 ]; then
   STATUS_TIMEOUT=$(( $STATUS_TIMEOUT + 1 ))
   logit error "Found error while checking status, cleaning mount automatically"
   $SCRIPTPATH clean
   logit debug "Check -- OFFLINE"
   exit 1
elif [ $RC -eq 1 ]; then
    logit debug "Check -- OFFLINE"
    exit 1
elif [ $RC -eq 0 ]; then
    logit debug "Check -- ONLINE"
    exit 0
fi
;;

'restart')
  logit info "restarting DBFS"
  $SCRIPTPATH stop
  $SLEEP 2
  $SCRIPTPATH start
  ;;

##########################################################################
# clean abort
# MCulp 08/01/2018
# Added z to the fusermount option below
##########################################################################

'clean'|'abort')
   logit info "cleaning up DBFS nicely using (fusermount -u|umount)"

   if [ $LINUX -eq 1 ]; then
      $FUSERMOUNT -u $MOUNT_POINT
   elif [ $SOLARIS -eq 1 ]; then
      $UMOUNT $MOUNT_POINT > /dev/null 2>&1
   fi

   $SLEEP 1
   FORCE_CLEANUP=0

   if [ $STATUS_TIMEOUT -gt 1 ]; then
      FORCE_CLEANUP=1
   else
      $SCRIPTPATH status > /dev/null
      if [ $? -eq 0 ]; then FORCE_CLEANUP=1; fi
   fi


   if [ $FORCE_CLEANUP -eq 1 ]; then
      logit error "tried (fusermount -u|umount), still mounted, now cleaning with (fuser                                  mount -uz -z|umount -f) and kill"

      if [ $LINUX -eq 1 ]; then
         $FUSERMOUNT -u -z $MOUNT_POINT
      elif [ $SOLARIS -eq 1 ]; then
         $ECHO "running umount -f now"
         $UMOUNT -f $MOUNT_POINT > /dev/null 2>&1
      fi

      if [ $LINUX -eq 1 ]; then
         PIDS=`$PS -ef | $GREP -w "$MOUNT_POINT" | $GREP dbfs_client| $GREP -v grep | \
         $AWK '{print $2}'`
         if [ -n "$PIDS" ]; then $KILL -9 $PIDS; fi
         PIDS=`$PS -ef | $GREP -w "$MOUNT_POINT" | $GREP mount.dbfs | $GREP -v grep | \
         $AWK '{print $2}'`
         if [ -n "$PIDS" ]; then $KILL -9 $PIDS; fi
      elif [ $SOLARIS -eq 1 ]; then
         PIDS=`$PS -ef | $GREP dbfs_client| $GREP -v grep | $AWK '{print $2}'`
         REALPIDS=' '
         for pid in $PIDS
         do
            ARGS=`pargs $pid`
            $ECHO $ARGS | $GREP  "$MOUNT_POINT$"  > /dev/null
            RET=$?

            if [ $RET -eq 0 ]; then REALPIDS="$REALPIDS $pid"; fi
         done
        if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi
        ### do it a 2nd time to clean up others
        if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi
        PIDS=`$PS -ef | $GREP dbfs_client| $GREP -v grep | $AWK '{print $2}'`
        REALPIDS=' '
        for pid in $PIDS
        do
           ARGS=`pargs $pid`
           $ECHO $ARGS | $GREP  "$MOUNT_POINT$"  > /dev/null
           RET=$?
           if [ $RET -eq 0 ]; then REALPIDS="$REALPIDS $pid"; fi
        done
        if [ -n "$REALPIDS" ]; then $KILL -9 $REALPIDS; fi

      fi
      exit 1
   fi
  ;;

'version')
  ### simply show the version
  echo "$VERSION"
  ;;

*)
  $ECHO "Usage: $SCRIPTNAME { start | stop | check | status | restart | clean | abort |                                   version }"
  ;;

esac
EOS

echo "$flnm has been created please review file and modify file....."

###### end of the file






crs_common.ksh

This is a common library for CRS related items
I created a directory called crsdbfs for it, but crscommon would be better


##############################################################################
# CRS Common functions
# MCulp Sept 2018
# Setting up resources against CRS
##############################################################################
crs_init()
{

echo "set common variables...."

}

crs_inst_order()
{

echo
echo "Function name.: crs_inst_order................."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Shows the installation order for the CRS services..."
echo

echo "Create a service first for GoldenGate"
echo " function - srvctl_add_svc"
echo " add a resource in CRS for DBFS (crs_add_res_dbfs)......"
echo "   naming is dbfs_ gg instance name.."
echo " start the DBFS resource............."
echo " stop the DBFS resource.............."
echo " status of the resource.............."
echo " relocate the resource..............."
echo " "

}



crs_res_add()
{

crs_add_res_dbfs

}
crs_add_res_dbfs()
##############################################################################
# Add CRS resource to for DBFS
##############################################################################
{

# GoldenGate XAG Instance
# gg_inst=$1

# Database for dependencies
# dbnm=$2

# echo $gg_inst
# echo $dbnm

# exit

# echo
# echo "Adding CRS resource for DBFS dbfs_"$gg_inst
# echo "MAKE SURE THE mount_dbfs script is in place before executing this!!!!"
# echo

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl add resource dbfs_${gg_inst} -type cluster_resource 
# -attr "ACTION_SCRIPT=/oracle/admin/scripts/crsdbfs/mount_dbfs_${gg_inst}.bsh, CHECK_INTERVAL=30, RESTART_ATTEMPTS=10, 
# START_DEPENDENCIES='hard(ora.$dbnm.db)pullup(ora.$dbnm.db)',STOP_DEPENDENCIES='hard(ora.$dbnm.db)',SCRIPT_TIMEOUT=300"


# GoldenGate XAG Instance
gg_inst=$1

# Database for dependencies
dbnm=$2

# Project Directory
projdir=$3

echo $gg_inst
echo $dbnm
echo $projscr
echo $projdir/mount_dbfs_$gg_inst.bsh

echo
echo "Function name.: crs_res_add_dbfs..............."
echo "Library file..: crs_common.ksh................."
echo "Directory ....: /oracle/admin/scripts/crsdbfs.."
echo
echo "Creating a CRS resource for DBFS..............."
echo

######################################################################
# Check to see if the .bsh script has been created ...."
######################################################################
if [ ! -f "$projscr/mount_dbfs_$gg_inst.bsh" ]; then
 echo
  echo "The mount script does not exist please create and re-run";
  echo
  exit
else
  echo
  echo "The mount script exists and proceeding with CRS resource create"
  echo
fi

######################################################################
# Check to see if the .conf file has been created ...."
######################################################################
if [ ! -f "$projscr/mount-dbfs_$gg_inst.conf" ]; then
  echo
  echo "The mount conf file does not exist please create and re-run";
  echo
  exit
else
  echo "The mount conf file exists and proceeding with CRS resource create"
fi

echo
echo "Adding CRS resource for DBFS dbfs_"$gg_inst
echo "MAKE SURE THE mount_dbfs script is in place before executing this!!!!"
echo

$CRS_HOME/bin/crsctl add resource dbfs_${gg_inst} -type cluster_resource -attr "ACTION_SCRIPT=$projscr/mount_dbfs_${gg_inst}.bsh, CHECK_INTERVAL=30, RESTART_ATTEMPTS=10, START_DEPENDENCIES='hard(ora.$dbnm.db)pullup(ora.$dbnm.db)',STOP_DEPENDENCIES='hard(ora.$dbnm.db)',SCRIPT_TIMEOUT=300"

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl add resource dbfs_${gg_inst} -type cluster_resource -attr "ACTION_SCRIPT=/oracle/admin/scripts/crsdbfs/wrap_mount_dbfs_${gg_inst}.bsh, CHECK_INTERVAL=30, RESTART_ATTEMPTS=10, START_DEPENDENCIES='hard(ora.$dbnm.db)pullup(ora.$dbnm.db)',STOP_DEPENDENCIES='hard(ora.$dbnm.db)',SCRIPT_TIMEOUT=300"

}




crs_res_start()
##################################################################
# for the $1 parm only the name after dbfs_ needs to be specified
# Start the dbfs resource
##################################################################
{

dbfsmnt=$1
nodename=$2

clear
echo
echo "Start DBFS resource...."
echo

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl start resource dbfs_$dbfsmnt -n lrdne67mp
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl start resource dbfs_$dbfsmnt -n $nodename

# /oracle_crs/product/12.1.0.2/crs_1/bin/crsctl status resource dbfs_ggtxd001

echo
echo "Status of DBFS resource...."
echo

crs_stat_res $dbfsmnt

echo
echo "df -hs or /dbfs_ mountpoints ...."
echo

df -ha |grep dbfs_

}

crs_res_stop()
{

dbfsmnt=$1

clear
echo
echo "Stop DBFS resource...."
echo
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stop resource dbfs_$dbfsmnt

echo
echo "Status of DBFS resource...."
echo

crs_stat_res $dbfsmnt

echo
echo "df -hs or /dbfs_ mountpoints ...."
echo

df -ha |grep dbfs_

}

crs_stat_res()
#########################################
# Resource status
#########################################
{

gg_inst=$1

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl status resource dbfs_$gg_inst

}

crs_res_relo()
#########################################
# Resource relocate
#########################################
{

gg_inst=$1
to_node=$2

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl relocate resource dbfs_$gg_inst -node $to_node

}

crs_res_del()
#########################################
# Delete DBFS resource
#########################################
{

gg_inst=$1

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl delete resource dbfs_$gg_inst

}

crs_stat()
{

###########################################################################################
# The difference on the crs_stat is the directory mismatch on the TX vs VA cluster pair
# Directory needed for TX cluster is 12.1.0.2
###########################################################################################

###########################################################################################
# This should be in the ggini file in order to drive the correct directory for all standard
# scripts
###########################################################################################

bindir="12.1.0.2"

echo
echo "crsctl stat res -t...."
echo
/oracle_crs/product/$bindir/crs_1/bin/crsctl status resource -t

echo
echo "Show services from the resource list...."
echo
/oracle_crs/product/$bindir/crs_1/bin/crsctl status resource -t|grep -a1 .svc

echo
echo "If nothing shows here, there are no dbfs mounts ......"
echo
df -ha|grep dbfs

}

crs_stat_xag()
{

gg_inst=$1

echo
echo "XAG Instance name is $gg_inst / crsctl stat res xag.$gg_inst.goldengate -p all parameters ...."
echo

# /oracle_crs/product/$bindir/crs_1/bin/crsctl stat res xag.ggtxd001.goldengate -p
/oracle_crs/product/$bindir/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p

}

crs_stat_xag_det()
{

bindir="12.1.0.2"

gg_inst=$1

echo
echo "XAG Instance name is $gg_inst / crsctl stat res xag.$gg_inst.goldengate -p specific parameters ...."
echo

/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep DATAGUARD_AUTOSTART
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep DB_SERVICES
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep START_DEP
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep STOP_DEP
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep HOSTING
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep FILESYST
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep GG_HOME
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep PLACEMENT
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep USE_LOCAL_SERVICES
/oracle_crs/product/12.1.0.2/crs_1/bin/crsctl stat res xag.$gg_inst.goldengate -p |grep RELOCATE

}

srvctl_add_svc()
{

gg_inst=$1
dbnm=$2
pfd=$3
avl=$4

echo
echo "SRVCTL add service for svc_$gg_inst when role is primary..."
echo "This adds a GoldenGate resource thru SRVCTL ..............."
echo "-preferred take an instance name as parameter as does -available"
echo

echo
echo "Showing services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

echo
echo "Adding service svc_"$gg_inst
echo
# srvctl add service -db $dbnm -service svc_$gg_inst -role PRIMARY -preferred DBFSXD011 -available DBFSXD012
srvctl add service -db $dbnm -service svc_$gg_inst -role PRIMARY -preferred $pfd -available $avl

# srvctl add service -db $dbnm -service svc_$gg_inst -preferred $pfd -available $avl

echo
echo "Showing services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

}

srvctl_sho_svc()
{

dbnm=$1

clear
echo
echo "Showing services database "$dbnm
echo
srvctl config service -db $dbnm|grep "Service "

}

db_common.ksh


#!/bin/ksh
############################################################################
# Script Name..: db_common.ksh
# Description..: common db functions was derived from ddl_common.ksh
# Author.......: Michael Culp
# Date.........: 11/30/2008
# Version......: .09
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Functions....:
#              : lst_objs_all
#              :
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################


db_comm_init()
{

echo "Init parameters for db_common....."
echo
echo

}

db_comm_dbrd()
{

echo "DB Common Dashboard......."

}


db_comm_sho_cmds()
{

echo
echo "Show commands from the db_common.ksh library......"
echo

}

chg_profile()
#########################################################
# Change a users oracle profile 
# 2 parameters username 
# profile
#########################################################
{

usr=$1
prfl=$2

echo
echo "Changing the profile....."
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
-- spool <some file name>

alter user ${usr} profile ${prfl};

EOF
}

qry_template_function()
{
echo
echo "    "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
-- spool <some file name>

-- select username from dba_users;

EOF
}

lst_users()
{

usrs=$1

echo
echo
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
-- spool <some file name>

PROMPT
PROMPT
PROMPT Schemas / Users like ${usrs}

select username from dba_users
 where username like '${usrs}%'
 order by username;

EOF
}

drp_app_users()
{
#########################################################################
# App users that need to be dropped and should not be in the database
#########################################################################

schm=$1

echo
echo "Drop the application user "${schm}
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
-- spool <some file name>
-- drop user PIPER cascade;

drop user ${schm} cascade;

EOF
}

db_cr_svcs_script()
{

####################################################
# Create the services script
####################################################

db_nm=$1
dir=/oracle/admin/scripts/rspfiles
# nl=lcdre5wbpdbd1,lcdre5wcpdbd1
instlst=PIPERD011,PIPERD012

echo "....${db_nm} database services creation script...."

############################################################################
############################################################################

echo "....Creating database services creation script......."
# echo > ${db_nm}.ksh
cat << EOS > ${dir}/cr_${db_nm}_svcs.ksh
#!/bin/ksh

###########################################################################
# This will create services needed for a database
###########################################################################

srvctl add service -d ${db_nm} -s ${db_nm}_SVC_01 -r ${instlst}
srvctl start service -d ${db_nm} -s ${db_nm}_SVC_01
srvctl status service -d ${db_nm}

EOS

chmod +x ${dir}/cr_${db_nm}_svcs.ksh

}

srvctl_cfg()
# Shows the config of all databases on the cluster
{

srvctl config

}

srvctl_stat_db()
{

srvctl status database -d DBFSXT01

}

gen_srv_lst ()
#######################################################
# Create a server list to dblst file
# Filename is dblst
#######################################################
{
srvctl config > dblst
clear
echo
echo "Databases listed abbreviated in clusterware"
echo
cat dblst
}


gen_srv_lst_verbose ()
{
#######################################################
# Create a verbose server list to dblst file
#######################################################
srvctl config database -v > dblst
clear
echo
echo "Databases listed verbose in clusterware"
echo
# cat the file
cat dblst
}


lst_type_hm ()
{
#######################################################
# call gen_srv_lst first
#######################################################

file="dblst"
while read line
do
   echo
   db=`echo $line | cut -d ' ' -f1`
   echo "####################################################################"
   echo " Database Name...: "$db
   dr=`echo $line | cut -d ' ' -f2`
   echo
   echo " Directory   "$dr
   echo
   ${dr}/bin/srvctl status database -d $db
   ${dr}/bin/srvctl config database -d $db |grep Type
   ${dr}/bin/srvctl config database -d $db |grep home
   ${dr}/bin/srvctl status service -d $db
   sho_env
   echo
   echo "####################################################################"
done <"$file"
}

lst_type_dir ()
{
################################################################
# you must generate dblst first
# Cycle through the dblst file and show the directory sizes
################################################################
file="dblst"
while read line
do
   echo
   echo "Database Name...: "$line
   echo
#   srvctl config database -d $line |grep "Oracle home"|cut -d' ' -f3
   db_dir=`srvctl config database -d $line |grep "Oracle home"|cut -d' ' -f3`
#   echo $db_dir
   db_dir_sz=`df $db_dir|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f1`
   db_dir_sz_rem=`df $db_dir|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f5`

   db_gi_sz=`df /oracle_crs|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f1`
   db_gi_sz_rem=`df /oracle_crs|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f5`

#    db_ora01_sz=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|cut -d' ' -f3`
   db_ora01_sz=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|awk '{print $2}'`
   db_ora01_sz_rem=`df /ora01|tail -n 1|sed -e 's/^[ \t]*//'|awk '{print $4}'`

   echo "########### Filesystem Space ###########"
   echo
   echo "GI Allocated........: "$db_gi_sz
   echo "GI Remaining........: "$db_gi_sz_rem
   echo
   echo "Database Allocated..: "$db_dir_sz
   echo "Database Remaining..: "$db_dir_sz_rem
   echo
   echo "ORA01 Allocated.....: "$db_ora01_sz
   echo "ORA01 Remaining.....: "$db_ora01_sz_rem
   echo
   find_db
done <"$file"
}

db_rec_parm()
{
###############################################################################
# Show the parameters for recovery
###############################################################################
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF
--   select name from v\$database;

   show parameter recov

EOF
}

db_psu ()
{
###############################################################################
# Show the DB PSU patches for all on the current server
# designed to replace the stand alone script db_psu.ksh
###############################################################################
# use gen_srv_lst to refresh the file on the next line
###############################################################################

gen_srv_lst

file="dblst"
hostnm=`hostname`
log_file=db_psu.log

echo "Database PSU Logfile...: ">$log_file

while read line
do
   echo "#########################################################################"
   echo "$line"
   srvctl status database -d $line |grep $hostnm|grep "is running"
   ###############################################################################
   # Check to see if instance is running
   ###############################################################################
   db_run=`srvctl status database -d $line |grep $hostnm|grep "is running"|wc -l`
   sho_env
   ############################################################
   # If instance not running loop back up with continue command
   ############################################################
   if [ $db_run -eq 0 ]; then
      echo
      echo "No DB instance found running looping to the list"
      echo
      continue
   fi
   db_inst=`srvctl status database -d $line |grep $hostnm|grep "is running"|cut -d' ' -f2`
   echo "This is the running instance...: "$db_inst
   export NEW_ORACLE_SID=$db_inst; . ~/.profile > /dev/null
   echo
   echo "After environment change..."
   sho_env

   ${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF >>$log_file
   select name from v\$database;
EOF

   ${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF >>$log_file
   set lines 150
   column action        format a8
   column action_time   format a30
   column namespace     format a10
   column version       format a10
   column id            format 999
   column bundle_series format a10
   column comments      format a50
   select action_time, action, namespace, version, id, bundle_series, comments
     from dba_registry_history
    order by action_time desc;
EOF
   opatch lsinventory >>$log_file
   echo
done <"$file"
}

sho_env()
{

###############################################################
# Show the current environment
# Prereqs..:
###############################################################

echo "Show Environment"
echo
echo "Oracle Base....: "$ORACLE_BASE
echo "Oracle Home....: "$ORACLE_HOME
echo "Library Path...: "$ORACLE_HOME/lib
echo "Oracle SID.....: "$ORACLE_SID
echo "Current environment is set to...: "$ORACLE_SID
echo "Currently logged in as.: "`whoami`
echo
}

crs_srv_lst_cfg()
{
##############################################################
# Loops through the database list checks the Oracle home size
# pre-req
#       gen_srv_lst
##############################################################

# Create the file to use for databases
gen_srv_lst

file="dblst"
while read line
do
   echo "===================================================================="
   echo "Database Name.......: "$line
   srvctl config database -d $line
   echo "....Oracle home is..."
   srvctl config database -d $line |grep "Oracle home"|awk '{print $3}'

#   find_db

done <"$file"
}

crs_sho_srv_cfg_home()
{
#######################################################################
# This use the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################
gen_srv_lst

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir
   cd $db_hm_dir
   echo "SRVCTl config database listing>>>>>>>>"
   srvctl config database -d $db_nm
   echo $line|awk '{print $3}'
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   find_db

done <"$file"
}



crs_sho_srvctl_cfg()
#######################################################################
# This uses the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################
{
# Generate a fresh list
gen_srv_lst_verbose

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   # echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   # Get the home directory for this database
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir

   # change to the home directory
   cd $db_hm_dir
   # Next line works because the directory its executing from is different
   # ./srvctl config database -d $db_nm
   echo $line|awk '{print $3}'
   sho_hm=`./srvctl config database -d $db_nm |grep "Oracle home"|cut -d' ' -f3`
   echo $sho_hm
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   echo "$0"
#   find_db

done <"$file"
}

crs_sho_srvctl_stat()
{

#######################################################################
# This use the file generated by gen_srv_lst_verbose
# And print the correct config information from the correct srvctl
# from the correct directory
#######################################################################

#######################################################################
# Generate a fresh list
#######################################################################
gen_srv_lst_verbose

file="dblst"
while read line
do
   echo
   echo
   # echo "Database...: "$line
   echo $line
   # echo "Database name..: "$line|awk '{print $1}'
   db_nm=`echo $line|awk '{print $1}'`
   echo "Home directory.: "$line|awk '{print $2}'
   db_hm_dir=`echo $line|awk '{print $2}'`
   db_hm_dir=$db_hm_dir/bin
   echo $db_hm_dir
   cd $db_hm_dir

   # Next line works because the directory its executing from is different
   ./srvctl status database -d $db_nm

   echo $line|awk '{print $3}'
   sho_hm=`./srvctl config database -d $db_nm |grep "Oracle home"|cut -d' ' -f3`
   echo $sho_hm
   echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
#   find_db

done <"$file"
}



QA Common Library

#!/bin/ksh
############################################################################
# Script Name..: qa_common.ksh
# Description..:
# Author.......: Michael Culp
# Date.........: 04/20/2013
# Version......: .08
# Modified By..: Michael Culp
# Date Modified: 11/02/2015
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
# :
# Functions : next_menu
# : get_db_details - gets information from the v$database table
# : creates variables to use in this script
# Dev Notes : function to update the cluster and add clusters from here
# : will be forthcoming, should be in the QA menu
#---------------------------------------------------------------------------
# Features 2 add :
# This script need to check to see if the TNS entries are there for the APEX database
# Add TNS names entries for the BRAIN1D database if not found
# Also create a .env file dbadmin_apex.conf
############################################################################
# This is the menuing system for the DBA general stuff
# automate a lot of the DBA tasks
# Michael Culp 2010
# Make sure the EOF end is aligned on the 1st column
# If you want the sys password used, create the .syspasswd
############################################################################
# Next line uncomment for debugging purposes only
# set -xv
# Look for SQL Plus
############################################################################

qa_init()
{

echo
echo "QA Init function......"
echo

}

qa_dbrd()
{

echo
echo "QA Dashboard function......"
echo

}

qa_sho_cmd()
{

echo
echo "QA Show commands and aliases....."
echo

}

asm_common.ksh


#!/bin/ksh
############################################################################
# Script Name..: asm_com.ksh
# Description..: Common library for ASM items
# Author.......: Michael Culp
# Date.........: 10/17/2008
# Version......: .08
# Modified By..:
# Date Modified:
# Comments.....: imported from asm_diskmnt.ksh
# Schema owner.:
#    alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################

# set -vx

initialize()
{
hostnm=`hostname -s`
log_file=asm_db_diskspc.log
# This won't be the same on all clusters
crs_dir=/oracle_crs/product/11.2.0/crs_1/bin/
}

get_asm_info_txt()
############################################################################
# Start ASM procedure put a copy in asm_sum.txt
# All this does is create the text file
############################################################################
{

${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<EOF > asm_sum.txt

SET LINESIZE  175
SET PAGESIZE  9999
SET HEADING OFF

-- set verify off
-- COLUMN group_name             FORMAT a32           HEAD 'Disk Group Name'
-- COLUMN state                  FORMAT a11           HEAD 'State'
-- COLUMN type                   FORMAT a6            HEAD 'Type'
-- COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
-- COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (MB)'
-- COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
-- COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

select name, state, type, total_mb, free_mb, (total_mb - free_mb) used_mb, ROUND((1-(free_mb / total_mb))*100, 2) pct_used FROM v\$asm_diskgroup ORDER BY pct_used;


-- COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
-- COLUMN lun_size               FORMAT 9999          HEAD 'LUN Size|(GB)'
-- COLUMN total_luns             FORMAT 9999          HEAD 'Total|LUNs'

-- compute sum label "Grand Total: " of total_luns on report

-- SELECT vadg.name group_name,
--       ceil(round(vad.total_mb)/1024) lun_size,
--       count(*) total_luns
-- FROM v\\$asm_diskgroup vadg,
--     v\\$asm_disk vad
-- WHERE vad.group_number=vadg.group_number
-- GROUP BY vadg.name, vad.total_mb
-- ORDER BY vadg.name, vad.total_mb;

EOF

# cat asm_sum.txt
# testdisp=`cat asm_sum.txt`
# echo "this is the echo"
# echo "$testdisp"
# testdisp=$(<asm_sum.txt)
# echo "$testdisp"
#### End procedure
}

asm_file_type()
##################################################################
# Function name: asm_stat
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{

echo "ASM File types stored in this instance"
echo -----------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set lines 120
   set pagesize 66
   column name format a25
   column type format a20
   select distinct type
     from v\$asm_file;

EOF

}

asm_files()
##################################################################
# Function name: asm_files
# Description..: show file types in ASM
# Author.......: Michael Culp
##################################################################
{

echo
echo
echo "ASM files stored in ASM instance (better)"
echo -----------------------------------------------------
echo
echo

sqlplus -s "/ as sysdba" <<EOF
SET LINESIZE  150
SET PAGESIZE  9999
SET VERIFY    off

COLUMN full_alias_path        FORMAT a80                  HEAD 'File Name'
COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'
COLUMN bytes                  FORMAT 9,999,999,999,999    HEAD 'Bytes'
COLUMN blocks                 FORMAT 9,999,999,999,999    HEAD 'Blocks'
COLUMN space                  FORMAT 9,999,999,999,999    HEAD 'Space'
COLUMN type                   FORMAT a18                  HEAD 'File Type'
COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'
COLUMN striped                FORMAT a8                   HEAD 'Striped'
COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'
COLUMN disk_group_name        noprint

BREAK ON report ON disk_group_name SKIP 1

compute sum label ""              of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

SELECT
    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
  , bytes
  , space
  , NVL(LPAD(type, 18), '<DIRECTORY>')  type
  , creation_date
  , disk_group_name
  , LPAD(system_created, 4) system_created
FROM
    ( SELECT g.name            disk_group_name
        , a.parent_index       pindex
        , a.name               alias_name
        , a.reference_index    rindex
        , a.system_created     system_created
        , f.bytes              bytes
        , f.blocks             blocks
        , f.space              space
        , f.type               type
        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date
      FROM
          v\$asm_file f RIGHT OUTER JOIN v\$asm_alias     a USING (group_number, file_number)
                                    JOIN v\$asm_diskgroup g USING (group_number)
    )
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
    CONNECT BY PRIOR rindex = pindex;

EOF
}

asm_spc_free()
{

echo "Amount of space left on individual ASM disk devices"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set pagesize 66
   set lines 100
   column path format a25
   select path,
          state,
          total_mb,
          free_mb
     from v\$asm_disk;
EOF

}

asm_io_stat()
{

echo "IO Stats by ASM disk devices"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
   set pagesize 66
   set lines 120
   column path format a25
   column read_time heading "Read Time" format 999,999.99
   column write_time                    format 999,999.99
   column writes                        format 999,999,999,999
   column reads                         format 999,999,999,999
   column bytes_written                 format 999,999,999,999
   column bytes_read                    format 999,999,999,999
   select path,
          reads,
          read_time,
          bytes_read,
          writes,
          write_time,
          bytes_written
     from v\$asm_disk;

EOF

}


asm_unbal()
{

echo "ASM Unbalanced report"
echo -----------------------------------------------------------
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
-- spool <some file name>

column "Diskgroup" format A30
column "Imbalance" format 99.9 Heading "Percent|Imbalance"
column "Variance"  format 99.9 Heading "Percent|Disk Size|Variance"
column "MinFree"   format 99.9 Heading "Minimum|Percent|Free"
column "DiskCnt"   format 9999 Heading "Disk|Count"
column "Type"      format A10  Heading "Diskgroup|Redundancy"

SELECT g.name "Diskgroup",
       100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance",
       100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance",
       100*(min(d.free_mb/d.total_mb)) "MinFree",
       count(*) "DiskCnt",
       g.type "Type"
  FROM v\$asm_disk d,
       v\$asm_diskgroup g
 WHERE d.group_number = g.group_number and
       d.group_number <> 0 and
       d.state = 'NORMAL' and
       d.mount_status = 'CACHED'
 GROUP BY g.name, g.type;

-- spool off
EOF


}

asm_stub()
{
sqlplus -s "/ as sysdba" <<EOF
-- spool <some file name>

-- spool off
EOF
}

asm_dg_size()
################################################################
# Script Name..: asm_disk_size.ksh
# Description..: ASM Disk Group sizing
# Author.......: Michael Culp
################################################################
{

echo -----------------------------------------------------------
echo "ASM Diskgroups"
echo -----------------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF
spool asm_diskgroups.txt

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v\$asm_diskgroup
ORDER BY
    name;


COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN lun_size               FORMAT 9999          HEAD 'LUN Size|(GB)'
COLUMN total_luns             FORMAT 9999          HEAD 'Total|LUNs'

compute sum label "Grand Total: " of total_luns on report

SELECT
   vadg.name                                   group_name
  ,ceil(round(vad.total_mb)/1024)              lun_size
  ,count(*)                                    total_luns
FROM
   v\$asm_diskgroup vadg,
   v\$asm_disk vad
WHERE
   vad.group_number=vadg.group_number
GROUP BY
   vadg.name
  ,vad.total_mb
ORDER BY
   vadg.name
  ,vad.total_mb;

spool off

EOF

}

asm_disk_2_dg()
######################################################################
# Disk device to diskgroup mapping
######################################################################
{

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
column name format a20
column value format a20
-- spool <some file name>

SELECT SUBSTR(d.name,1,16) AS asmdisk,
       d.mount_status,
       d.state,
       dg.name AS diskgroup
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_DISK d
 WHERE dg.group_number = d.group_number
  order by dg.name;

SELECT dg.name AS diskgroup,
       SUBSTR(a.name,1,18) AS name,
       SUBSTR(a.value,1,24) AS value,
       read_only
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_DATA_DG01' AND
       dg.group_number = a.group_number;

SELECT dg.name AS diskgroup,
       SUBSTR(a.name,1,18) AS name,
       SUBSTR(a.value,1,24) AS value,
       read_only
  FROM V\$ASM_DISKGROUP dg,
       V\$ASM_ATTRIBUTE a WHERE dg.name = 'SHARED_FRA_DG01' AND
       dg.group_number = a.group_number;
-- spool off
EOF


}

asm_dsk_dev_lst()
########################################################
# Disk device listing
########################################################
{

echo
echo "ASM Disk devices....."
echo
ls -l /dev/mapper/asm*p1

echo
echo "ASM Disk devices (data)....."
echo
ls -l /dev/mapper/asm_d*p1

echo
echo "ASM Disk devices (fra)....."
echo
ls -l /dev/mapper/asm_f*p1

echo
echo "ASM Disk devices (system)....."
echo
ls -l /dev/mapper/asm_s*p1


# ls -l /dev/mapper/fra*p1
echo
echo "All devices from /dev/mapper directory ....."
echo
ls -l /dev/mapper
ls -l /dev/mapper/oraclevg*

}


asm_dsk_sze()
####################################################
# Shows the sizes of the diskgroups
# No prereq for ASM env
####################################################
{

clear

sqlplus -s "/ as sysdba" <<EOF
set wrap off
set lines 155 pages 9999
col "Group"      for 999
col "Group Name" for a25    Head "Group|Name"
col "Disk Name"  for a10
col "State"      for a10
col "Type"       for a10   Head "Diskgroup|Redundancy"
col "Total GB"   for 9,999,990 Head "Total|GB"
col "Free GB"    for 9,999,990 Head "Free|GB"
col "Imbalance"  for 99.9  Head "Percent|Imbalance"
col "Variance"   for 99.9  Head "Percent|Disk Size|Variance"
col "MinFree"    for 99.9  Head "Minimum|Percent|Free"
col "MaxFree"    for 99.9  Head "Maximum|Percent|Free"
col "DiskCnt"    for 9999  Head "Disk|Count"

prompt
prompt ASM Disk Groups
prompt ===============

SELECT g.group_number  "Group"
,      g.name          "Group Name"
,      g.state         "State"
,      g.type          "Type"
,      g.total_mb/1024 "Total GB"
,      g.free_mb/1024  "Free GB"
,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
,      100*(min(d.free_mb/d.total_mb)) "MinFree"
,      100*(max(d.free_mb/d.total_mb)) "MaxFree"
,      count(*)        "DiskCnt"
FROM v\$asm_disk d, v\$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
EOF

}

asm_0002()
{
sqlplus -s "/ as sysdba" <<EOF

set wrap off
set lines 175 pages 9999

prompt ASM Disks In Use
prompt ================

col "Group"          for 999
col "Disk"           for 999
col "Header"         for a9
col "Mode"           for a8
col "State"          for a8
col "Created"        for a10          Head "Added To|Diskgroup"
--col "Redundancy"     for a10
--col "Failure Group"  for a10  Head "Failure|Group"
col "Path"           for a19
--col "ReadTime"       for 999999990    Head "Read Time|seconds"
--col "WriteTime"      for 999999990    Head "Write Time|seconds"
--col "BytesRead"      for 999990.00    Head "GigaBytes|Read"
--col "BytesWrite"     for 999990.00    Head "GigaBytes|Written"
col "SecsPerRead"    for 9.000        Head "Seconds|PerRead"
col "SecsPerWrite"   for 9.000        Head "Seconds|PerWrite"

select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
,      mode_status   "Mode"
,      state         "State"
,      create_date   "Created"
--,      redundancy    "Redundancy"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
,      name          "Disk Name"
--,      failgroup     "Failure Group"
,      path          "Path"
--,      read_time     "ReadTime"
--,      write_time    "WriteTime"
--,      bytes_read/1073741824    "BytesRead"
--,      bytes_written/1073741824 "BytesWrite"
,      read_time/reads "SecsPerRead"
,      write_time/writes "SecsPerWrite"
from   v\$asm_disk_stat
where header_status not in ('FORMER','CANDIDATE')
order by group_number
,        disk_number;

EOF

}


dbfs_common.ksh


############################################################
# DBFS Functions
# Use the following functions to install and maintain dbfs
############################################################

############################################################
# dbfs_init           - Initialize variables or other functions
# dbfs_pre_inst       - Check for OS params
# dbfs_inst_fuse      - Install FUSE libraries
# dbfs_cfg_fuse       - Config FUSE libraries
# dbfs_chk_db_usr     -
# dbfs_cr_tblspc      - Create DBFS tablespace
# dbfs_drp_tblspc     - Drop DBFS tablespace
# dbfs_cr_usr         - Create DBFS user
# dbfs_drp_usr        - Drop DBFS user
# dbfs_cr_objs        - Create DBFS objects
# dbfs_cr_mnt_pnt     - Create the DBFS mountpoint
# dbfs_test_access    - Test access to the DBFS directories
# dbfs_inst_qa        - QA the install
# dbfs_sho            - Stub only needs code
# dbfs_status         - Stub only needs code
############################################################

sho_dbfs_cmds()
{

echo "DBFS Installation commands"
echo "=========================="
echo "dbfs_init_fn.ksh           - "

echo "dbfs_cr_tblspc_cicm_fn.ksh - Create tablespace for CICM "
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""
echo ""

}
dbfs_init()
{

echo "dbfs_init........"
echo "This is the var init process....."
echo "Put any variables here needed for the process..."

}

dbfs_dbrd()
{

echo "DBFS Dashboard..............:"
echo "============================="
echo
echo "Oracle Home.............: "$ORACLE_HOME
echo "Oracle Client Home......: "$ORA_CLI_HOME
echo "ASM Diskgroup for DBFS..: "
echo "Tablespace for DBFS.....: "
echo "Mountpoint for DBFS.....: "$mntpnt
echo "Date for logfile........: "$usedate


echo
echo "DBFS Mounts identified......."
echo "============================="
echo

df -ha|grep dbfs

echo
echo

dbfs_svc_stat

}


dbfs_pre_inst()
{

echo
echo "dbfs_pre_inst........"
echo "Checking for uname -a for dbfs pre check....."
echo
echo
uname -a
echo
echo
echo "Checking for cat release....."
echo
echo
cat /etc/*-release

}

dbfs_inst_fuse()
############################################################
# Install the FUSE libraries
############################################################
{

############################################################
# not sure how this will get done via bank environment
############################################################
yum install fuse fuse-libs kernel-devel

}

dbfs_cfg_fuse()
############################################################
# this is done as root
# Config the FUSE sub-system
############################################################
{

ls -IL /usr/bin/fusermount

/sbin/modprobe fuse

chmod 666 /dev/fuse

echo "/sbin/modprobe fuse ">> /etc/rc.modules

grep fuse /etc/group

usermod -a -G fuse oracle

grep fuse /etc/group

id oracle

}


dbfs_chk_db_usr()
############################################################
# this is done as oracle
############################################################
{

show user

select name, created, from v\$database;

}


dbfs_cr_tblspc()
{


create bigfile tablespace dbfs_ts
datafile 'XXXXXXX'
size 1024M
autoextend on next 100M maxsize 3G nologging extent management
local autoallocate segment space management auto;

}


dbfs_drp_tblspc()
{

echo "TBD..."

}



dbfs_cr_usr()
{

create user dbfs_user identified by dbfs_user
default tablespace dbfs_ts
quota unlimited on dbfs_ts;

grant create_session, create table, creaet view, create procedure, dbfs_role to dbfs_user;

grant resource to dbfs_user;

}


dbfs_drp_usr()
{

echo "TBD..."

}



dbfs_cr_objs()
{

show user

rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1


create store
begin dbms dbfs sfs.createFilesystem(store name => 'FS1', tbl name => 'FS1',
tbl tbs =>'dbfs ts', lob tbs => 'dbfs ts', do partition => false

register store


mount store



chmod store



}


dbfs_cr_mnt_pnt()
######################################
# create mount point
# run as root
######################################
{

flsystm=/u06dbfs

su -

echo
echo "create dirs"
echo

mkdir $flsystm

chown oracle:dba $flsystm

ls -ld $flsystm

}


dbfs_test_access()
{

df -k

sqlplus dbfs_user/dbfs_user@DBFSDB << EOF

show user

exit


EOF

id
dbfs_client dbfs_user/dbfs_user@DBFSDB /u06dbfs


}











menu_common.ksh



next_menu()
############################################################################
# Press any key to continue
############################################################################
{
echo
echo "# ----------------------------------------------------------------------- #"
echo "[ ...  Press any key to continue ... ]"
read next
}

menu_sel_text()
{
echo
echo "#   Enter Menu Selection: "
}


main_menu()

{
MNUTXT=" Main Menu "
dashboard

# echo "#     Add ASM disks in bulk                                     Future    #"
# echo "#     Add HealthCheck Info for this database                    Future    #"
# echo "#     Add DBA to APEX                                           Future    #"
# echo "#     Add cron job for APEX disk size update for this database  Future    #"
# echo "#     Add databases to APEX                                               #"
# echo "#     Run and email report for Disk Sizes                       Future    #"
# echo "#     Run and email report for DBA to Projects (future)                   #"
# echo "#     Run and email report for unassigned projects to DBAs                #"
# echo "#     Server Details                                                      #"
# echo "#     Update DB Details in APEX                                           #"
# echo "#     Update disk usage for database in APEX                              #"
# echo "#     Show Instance Details                                               #"
# echo "#     Show Database Details                                               #"
# echo "#                                                                         #"
# echo "#     EPG Status                                                          #"
# echo "#     Perform APEX HealthCheck                                  Future    #"
# echo "#     Add / Update scripts for all menu items                   Future    #"

echo "# ------------------------------------------------------------------------- #"
echo "#   0.  Add "$DB_NAME" database to DBAdmin ""            14. "$DB_NAME" Info"
echo "#   1.                                                                      #"
echo "#   2.  Display Instances (as /etc/oratab)                                  #"
echo "#   3.  Check this DB against oratab                                        #"
echo "#   4.  Change Instance Environment                                         #"
echo "#   5.                                                                      #"
echo "#   6.  Check all active DBs against oratab   (future)                      #"
echo "#   7.                                                                      #"
echo "#   8.                                                                      #"
echo "#   9.                                                                      #"
echo "#  10.                                                                      #"
echo "#  11.                                                                      #"
echo "#  12.                                                                      #"
echo "#  13.  Performance Tuning Menu                                             #"
echo "# ------------------------------------------------------------------------- #"
echo "#   x.  Exit APEX Menu    21. Build & QA Menu    23. Display Menu           #"
echo "# ------------------------------------------------------------------------- #"
menu_sel_text

read x
ans=`echo $x | tr '[a-z]' '[A-Z]'`
#
case "$ans"
in
       0 )
       ###################################
       # Add database to APEX if not there
       ###################################

       # chk_existing
       # inst_InCentralRepository
       # chk_db_running
       # mch_InCentralRepository
       # db_InCentralRepository
       # get_db_details
       # insertDB_InCentralRepository

       clear
       echo
       echo
       if [ $database_id = "" ]; then
          insertDB_APEX
       else
          echo "Database found in DBAdmin  (record # "$database_id")"
       fi
       next_menu;
       ;;


       1 )
       next_menu;
       ;;

       2 )
       disp_inst
       rest_save
       next_menu;
       ;;

       3 )
       chk_db_nm2dblst
       next_menu;
       ;;

       4 )
       clear
       disp_inst
       echo
       echo " Enter Instance Name: "
       echo
       typeset -u instnm
       read instnm
       echo $instnm
       chg_inst
       next_menu;
       ;;

       5 )
       next_menu;
       ;;

       6 )
       next_menu;
       ;;

       7 )
       next_menu;
       ;;

       8 )
       next_menu;
       ;;

       9 )
       next_menu;
       ;;

       10 )
       next_menu;
       ;;

       11 )
       # find_db
       next_menu;
       ;;

       12 )
       next_menu;
       ;;

       13 )
       MNU04=000
       echo $MNU04
       echo "Test Begin"
       while true
       do
         if [ "$MNU04" -eq 999 ]; then
            break;
            echo "This breaks"
         else
            echo "This continues"
         fi
         menu_perftune
       done
       echo "Test end"
       next_menu;
       ;;

       14 )
       get_db_details
       next_menu;
       ;;

       15 )
       # db_qa_rev
       next_menu;
       ;;

       16 )
       # db_qa
       next_menu;
       ;;

       17 )
       # update_db_details
       # find_db
       next_menu;
       ;;

       18 )
       # db_ind_size
       next_menu;
       ;;

       19 )
       # get_inst_details
       next_menu;
       ;;

       20 )
       # sho_db_details
       # get_db_details
       next_menu;
       ;;

       21 )
       MNU02=000
       echo $MNU02
       echo "Test Begin"
       while true
       do
         if [ "$MNU02" -eq 999 ]; then
            break;
            echo "This breaks"
         else
            echo "This continues"
         fi
         menu_02
       done
       echo "Test end"
       next_menu;
       ;;

       22)
       ./epg_stat.ksh
       next_menu;
       ;;

       23)
       MNU03=000
       echo $MNU03
       echo "Test Begin"
       while true
       do
         if [ "$MNU03" -eq 999 ]; then
            break;
            echo "This breaks"
         else
            echo "This continues"
         fi
         menu_03
       done
       echo "Test end"
       next_menu;
       ;;

       24)
       next_menu;
       ;;

       q|X|x )
        exit;
       ;;
       * )
        main_menu;
       ;;
   esac
}