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