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"
}