#!/bin/ksh #-----------------------------------------------------------------------------# # # # # # Invocation: # # refresh_mviews [-d disk] [-y YYYYMMDD] [-f from_step] [-t to_steps] # # # #-----------------------------------------------------------------------------# # Modification history: # # Date Programmer Description # # # # # #-----------------------------------------------------------------------------# #-----------------------------------------------------------------------------# # STEP names and descriptions # #-----------------------------------------------------------------------------# # FIRST_STEP must be equal to the step number of the first step FIRST_STEP=10 STEP_NAME[10]="CreateScripts" STEP_DESC[10]="CreateScripts" STEP_NAME[20]="CompleteRefresh" STEP_DESC[20]="CompleteRefresh" STEP_NAME[25]="RunStats" STEP_DESC[25]="RunStats" STEP_NAME[30]="ChangeSynonyms" STEP_DESC[30]="ChangeSynonyms" STEP_NAME[40]="UpdateControlTable" STEP_DESC[40]="UpdateControlTable" LAST_STEP=40 #---------------------------------------------------------------------# # step 10 - CreateScripts # # create scripts for: # 1) refresh all the mviews in mview_control table # 2) gathering stats on all the mviews created in script 1 # 3-5) change all the synonyms to point on the new mviews in all relevant accounts # # To add more mviews all is needed is to add rows to mview_control #---------------------------------------------------------------------# CreateScripts () { sqlplus -s ${TABLE_OWNER}/${TABLE_OWNER_PASS}@${DB_NAME} <> ${LOG_FILE} 2>&1 set heading off set pages 0 set feedback off set lines 150 spool /tmp/refresh_mviews.sql select 'spool /tmp/refresh_mviews.log' from dual; select 'set timing on' from dual; --select 'exec dbms_mview.refresh('''||NEXT_MVIEW_NAME||''');' select 'exec dbms_mview.refresh('''||NEXT_MVIEW_NAME||''', atomic_refresh => FALSE);' from mview_control; select 'exit' from dual; spool off spool /tmp/run_stats.sql select 'spool /tmp/run_stats.log' from dual; select 'set timing on' from dual; select 'exec dbms_stats.gather_table_stats(''${TABLE_OWNER}'','''||NEXT_MVIEW_NAME||''',estimate_percent=>10,degree=>4,cascade=>true);' from mview_control; select 'exit' from dual; spool off spool /tmp/change_synonyms.sql select 'spool /tmp/change_synonyms.log' from dual; select 'set timing on' from dual; select 'set echo on ' from dual; select 'create or replace synonym '||SYNONYM_NAME||' for ${TABLE_OWNER}.'||NEXT_MVIEW_NAME||';' from mview_control; select 'exit' from dual; spool off exit EOF CheckIfFileExist /tmp/refresh_mviews.sql >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/refresh_mviews.sql >> ${LOG_FILE} 2>&1 check_return_code CheckIfFileExist /tmp/run_stats.sql >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/run_stats.sql >> ${LOG_FILE} 2>&1 check_return_code CheckIfFileExist /tmp/change_synonyms.sql >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/change_synonyms.sql >> ${LOG_FILE} 2>&1 check_return_code } #---------------------------------------------------------------------# # step 20 - CompleteRefresh # # Execute the refresh script and checks for errors #---------------------------------------------------------------------# CompleteRefresh () { sqlplus ${TABLE_OWNER}/${TABLE_OWNER_PASS}@${DB_NAME} @/tmp/refresh_mviews.sql >> ${LOG_FILE} 2>&1 CheckIfFileExist /tmp/refresh_mviews.log >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/refresh_mviews.log >> ${LOG_FILE} 2>&1 check_return_code } #---------------------------------------------------------------------# # step 25 - RunStats # # run the stats script and checks for errors #---------------------------------------------------------------------# RunStats () { sqlplus ${TABLE_OWNER}/${TABLE_OWNER_PASS}@${DB_NAME} @/tmp/run_stats.sql >> ${LOG_FILE} 2>&1 CheckIfFileExist /tmp/run_stats.log >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/run_stats.log >> ${LOG_FILE} 2>&1 check_return_code } #---------------------------------------------------------------------# # step 30 - ChangeSynonyms # # excute the synonyms scripts #---------------------------------------------------------------------# ChangeSynonyms () { sqlplus ${TABLE_OWNER}/${TABLE_OWNER_PASS}@${DB_NAME} @/tmp/change_synonyms.sql >> ${LOG_FILE} 2>&1 CheckIfFileExist /tmp/change_synonyms.log >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/change_synonyms.log >> ${LOG_FILE} 2>&1 check_return_code } #---------------------------------------------------------------------# # step 40 - UpdateControlTable # # Switch between th tow columns in mview_control table # the snapshots that did not refresh this time will be refreshed # at the next time #---------------------------------------------------------------------# UpdateControlTable () { sqlplus -s ${TABLE_OWNER}/${TABLE_OWNER_PASS}@${DB_NAME} <> ${LOG_FILE} 2>&1 spool /tmp/update_control_table.log update mview_control set NEXT_MVIEW_NAME = CURRENT_MVIEW_NAME, CURRENT_MVIEW_NAME = NEXT_MVIEW_NAME, LAST_REFRESH = sysdate; commit; exit EOF CheckIfFileExist /tmp/update_control_table.log >> ${LOG_FILE} 2>&1 check_return_code CheckOraError /tmp/update_control_table.log >> ${LOG_FILE} 2>&1 check_return_code } ###################################################################### # LOCAL FUNCTIONS ###################################################################### ######################## Check Oracle Error ######################## CheckOraError() { grep "ORA-" $1 if [ $? = 0 ] then false fi } ######################## Check if file exist ############33 CheckIfFileExist() { if [ ! -f $1 ] then echo "LOG FILE $1 NOT EXIST" false fi } ############################################################################### unset TWO_TASK p_run=1 # the step_driver requires the following line COMMand_LINE="$0 $*" # handle parameters as required by application set -- `getopt d:e:f:lt:y: $*` if [ $? -ne 0 ] then echo "Usage: $0 [-l] [-f from_step] [-t to_step] [-d disk] [-y YYYYMMDD]" exit 2 fi while [ $# -gt 0 ] do case $1 in -d) DISK=$2; shift 2 ;; -e) TEST_ENV=$2; shift 2 ;; -f) FROM_STEP=$2; shift 2 ;; -l) STEP_LIST_ONLY=yes; shift ;; -t) TO_STEP=$2; shift 2 ;; -y) YYYYMMDD=$2; shift 2 ;; --) shift; break ;; esac done #----------------------------------------------------------------------# # Set from_STEP and TO_STEP if they exists for backwards compatibility # # with Tadiran code. New programs should not include this sections. # if [ -z "${FROM_STEP}" ] # then # FROM_STEP=$1 # fi # if [ -z "${TO_STEP}" ] # then # TO_STEP=$2 # fi # #----------------------------------------------------------------------# # set global environment variables # or use a test environment if TEST_ENV is set #if [ $? -ne 0 ] #then # echo "${SHELL_NAME}: cannot set environment" # exit 1 #fi if [ -z "${UP}" ] then #stty -echo #echo "Enter the Oracle password for user ${ORA_DAILY_OWNER}: \c" #read oracle_password #stty echo UP="/" ; export UP fi # #-----------------------------------------------------------------------------# # Optional section if addition environment variables or parameters specific # # to this program are required. # # # # Get additional parameters used for the execution of this shell from # # a file with the current file name and a .prm suffix. # # PARAM_FILE=/usr/`hostname`/bin/`basename $0`.prm # #-----------------------------------------------------------------------------# . /home/oracle/ora_env PARAM_FILE=/home/oracle/scripts/`basename $0`.prm . ${PARAM_FILE} if [ $? -ne 0 ] then echo "$0: cannot set parameters from ${PARAM_FILE}" exit 1 fi # . /OracleTools/env_${DB_NAME} # Load general routines . /home/oracle/scripts/general_routines if [ $? -ne 0 ] then echo "${SHELL_NAME}: cannot set general_routines" exit 1 fi # call the step driver to step through the above functions . /home/oracle/scripts/step_driver # step_driver should not return echo "${SHELL_NAME}: ERROR premature completion" | \ tee -a ${LOG_FILE} exit 1