db_common.ksh – IT-Remote

[code language=”sql”]

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

[/code]

IT-REMOTE Office

Company

Case Studies

Support

Subcribe Our Newsletter