#!/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"
}