308 lines
11 KiB
Bash
Executable File
308 lines
11 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
## Usage: db_change_oh.sh <parameters>, where:
|
|
## -d|--database <database name>
|
|
## -o|--oraclehome <new oracle home>
|
|
|
|
# Global variables
|
|
#-----------------
|
|
typeset ABS_PATH_THIS_SHELL
|
|
typeset ABS_PATH_EXECUTION_LOG_FILE
|
|
typeset EXEC_ID
|
|
typeset ROOTDIR="/dbfs_tools/TOOLS/admin/sh"
|
|
typeset UPLEVEL_SCRIPT_PATH
|
|
typeset NEW_ORACLE_HOME
|
|
typeset THIS_HOST
|
|
typeset DBNODE1
|
|
typeset DBNODE2
|
|
typeset DBINST1
|
|
typeset DBINST2
|
|
typeset EXECUTION_TYPE
|
|
typeset EXECUTION_HOST
|
|
typeset EXECUTION_INSTANCE
|
|
typeset EXECUTION_HOST_BIS
|
|
typeset EXECUTION_INSTANCE_BIS
|
|
typeset THIS_SHELL
|
|
typeset DB_UNIQUE_NAME
|
|
typeset OHOME
|
|
typeset OVERSION
|
|
typeset GHOME
|
|
typeset SQLPLUS
|
|
typeset DB_SRVCTL
|
|
typeset GRID_SRVCTL
|
|
typeset -i LAST_RETURN_CODE
|
|
typeset AUX_STRING
|
|
typeset DATABASE_ROLE
|
|
|
|
# Procedures
|
|
#-----------
|
|
usage() {
|
|
[ "$*" ] && echo "$0: $*"
|
|
sed -n '/^##/,/^$/s/^## \{0,1\}//p' "$0"
|
|
exit 2
|
|
} 2>/dev/null
|
|
|
|
get_database_info (){
|
|
# Get the last 3 letters of database unique name parameter
|
|
AUX_STRING=$(echo ${DB_UNIQUE_NAME} | rev | cut -c 1-3 | rev)
|
|
if [[ "${AUX_STRING}" != "EXA" ]] ; then
|
|
echo " ERROR: Invalid database name: ${DB_UNIQUE_NAME} [FAILED]" | tee -a ${UPLEVEL_SCRIPT_PATH}/logs/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
fi
|
|
# Get GID_HOME
|
|
GHOME=$(cat /etc/oratab | grep -v '^#' | grep "+ASM" | cut -d":" -f2 | sed 's/ //g')
|
|
GRID_SRVCTL=${GHOME}/bin/srvctl
|
|
${GRID_SRVCTL} config database -v | grep ${DB_UNIQUE_NAME} | awk '{ print $1 }'> ${UPLEVEL_SCRIPT_PATH}/workdir/${EXEC_ID}.tmp
|
|
typeset -i DBCOUNT=$(cat ${UPLEVEL_SCRIPT_PATH}/workdir/${EXEC_ID}.tmp | wc -l)
|
|
if [[ ( ${DBCOUNT} > 1 ) || ( ${DBCOUNT} = 0 ) ]] ; then
|
|
echo " ERROR: ${DBCOUNT} databases founded for the database name: ${DB_UNIQUE_NAME} [FAILED]" | tee -a ${UPLEVEL_SCRIPT_PATH}/logs/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
OHOME=$(${GRID_SRVCTL} config database -v | grep ${DB_UNIQUE_NAME} | awk '{ print $2 }')
|
|
export ORACLE_HOME=${OHOME}
|
|
OVERSION=$(${GRID_SRVCTL} config database -v | grep ${DB_UNIQUE_NAME} | awk '{ print $3 }')
|
|
SQLPLUS="${OHOME}/bin/sqlplus -s / as sysdba"
|
|
DB_SRVCTL=${OHOME}/bin/srvctl
|
|
echo "DB_UNIQUE_NAME=${DB_UNIQUE_NAME}, ORACLE_HOME=${OHOME}, VERSION=${OVERSION}"
|
|
fi
|
|
|
|
# Identify database instances
|
|
THIS_HOST=$(hostname -s)
|
|
THIS_HOST_NUMBER=$(echo ${THIS_HOST} | rev | cut -c 1-1 | rev)
|
|
THIS_HOST_BASE=${THIS_HOST:0:10}
|
|
DB_UNIQUE_NAME_BASE=${DB_UNIQUE_NAME:0:${#DB_UNIQUE_NAME}-3}
|
|
|
|
STR1=$(${DB_SRVCTL} config database -db ${DB_UNIQUE_NAME} | grep "Configured nodes")
|
|
STR2=${STR1#"Configured nodes: "}
|
|
DBNODE1=$(echo ${STR2} | awk -F"," '{ print $1}')
|
|
DBNODE2=$(echo ${STR2} | awk -F"," '{ print $2}')
|
|
|
|
STR1=$(${DB_SRVCTL} config database -db ${DB_UNIQUE_NAME} | grep "Database instances")
|
|
STR2=${STR1#"Database instances: "}
|
|
DBINST1=$(echo ${STR2} | awk -F"," '{ print $1}')
|
|
DBINST2=$(echo ${STR2} | awk -F"," '{ print $2}')
|
|
|
|
echo "Database instances: ${DBINST1}@${DBNODE1}, ${DBINST2}@${DBNODE2}"
|
|
}
|
|
|
|
continue_if_ok () {
|
|
RETURN_CODE=$1
|
|
if [[ ( ${RETURN_CODE} != 0 ) ]] ; then
|
|
echo "ERROR, please check the logfile: ${ABS_PATH_EXECUTION_LOG_FILE}"
|
|
exit 1
|
|
fi
|
|
}
|
|
|
|
restart_database (){
|
|
${DB_SRVCTL} stop database -db ${DB_UNIQUE_NAME} > ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
|
|
${DB_SRVCTL} start database -db ${DB_UNIQUE_NAME} > ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
}
|
|
|
|
copy_init_and_passwordfile (){
|
|
cp -p ${OHOME}/dbs/init${EXECUTION_INSTANCE}.ora ${NEW_ORACLE_HOME}/dbs/init${EXECUTION_INSTANCE}.ora >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
cp -p ${OHOME}/dbs/orapw${EXECUTION_INSTANCE} ${NEW_ORACLE_HOME}/dbs/orapw${EXECUTION_INSTANCE} >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
scp -p ${OHOME}/dbs/init${EXECUTION_INSTANCE}.ora ${EXECUTION_HOST_BIS}:${NEW_ORACLE_HOME}/dbs/init${EXECUTION_INSTANCE_BIS}.ora >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
scp -p ${OHOME}/dbs/orapw${EXECUTION_INSTANCE} ${EXECUTION_HOST_BIS}:${NEW_ORACLE_HOME}/dbs/orapw${EXECUTION_INSTANCE_BIS} >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
}
|
|
|
|
disable_cluster (){
|
|
export ORACLE_HOME=${OHOME}
|
|
export ORACLE_SID=${EXECUTION_INSTANCE}
|
|
${ORACLE_HOME}/bin/sqlplus / as sysdba<<EOF! >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
whenever oserror exit failure
|
|
whenever sqlerror exit sql.sqlcode
|
|
alter system set cluster_database=false scope=spfile sid='*';
|
|
EOF!
|
|
continue_if_ok $?
|
|
}
|
|
|
|
stop_db (){
|
|
${DB_SRVCTL} stop database -db ${DB_UNIQUE_NAME} >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
}
|
|
|
|
update_oratab (){
|
|
cp /etc/oratab ${UPLEVEL_SCRIPT_PATH}/logs/oratab_${DB_UNIQUE_NAME}_${EXECUTION_HOST}_${EXEC_ID}.bak
|
|
scp ${EXECUTION_HOST_BIS}:/etc/oratab ${UPLEVEL_SCRIPT_PATH}/logs/oratab_${DB_UNIQUE_NAME}_${EXECUTION_HOST_BIS}_${EXEC_ID}.bak >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
|
|
sed '/'${DB_UNIQUE_NAME}':/d' /etc/oratab > ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp
|
|
echo "${DB_UNIQUE_NAME}:${NEW_ORACLE_HOME}:N" >> ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp
|
|
cp ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp /etc/oratab
|
|
|
|
sed '/'${DB_UNIQUE_NAME}':/d' ${UPLEVEL_SCRIPT_PATH}/logs/oratab_${DB_UNIQUE_NAME}_${EXECUTION_HOST_BIS}_${EXEC_ID}.bak > ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp
|
|
echo "${DB_UNIQUE_NAME}:${NEW_ORACLE_HOME}:N" >> ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp
|
|
scp ${UPLEVEL_SCRIPT_PATH}/workdir/oratab_${DB_UNIQUE_NAME}_{EXEC_ID}.tmp ${EXECUTION_HOST_BIS}:/etc/oratab >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
}
|
|
|
|
execute_datapatch (){
|
|
# Switch to new ORACLE_HOME
|
|
export ORACLE_HOME=${NEW_ORACLE_HOME}
|
|
|
|
echo " Start instance ${EXECUTION_INSTANCE}@${EXECUTION_HOST} in UPGRADE mode"
|
|
${ORACLE_HOME}/bin/sqlplus / as sysdba<<EOF! >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
whenever oserror exit failure
|
|
whenever sqlerror exit sql.sqlcode
|
|
startup upgrade;
|
|
EOF!
|
|
continue_if_ok $?
|
|
|
|
# Asked by Michael :)
|
|
${ORACLE_HOME}/bin/sqlplus / as sysdba<<EOF! >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
whenever oserror exit failure
|
|
whenever sqlerror exit sql.sqlcode
|
|
begin
|
|
FOR dummy IN (SELECT index_name from dba_indexes where owner='SYS' and index_name='[FK_RES]')
|
|
LOOP
|
|
execute immediate 'drop index SYS."[FK_RES]"';
|
|
END LOOP;
|
|
end;
|
|
/
|
|
EOF!
|
|
continue_if_ok $?
|
|
|
|
echo " Executing DATAPATCH"
|
|
${ORACLE_HOME}/OPatch/datapatch >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
}
|
|
|
|
enable_cluster (){
|
|
echo " Set cluster_database=true and shutdown ${DB_UNIQUE_NAME} database"
|
|
${ORACLE_HOME}/bin/sqlplus / as sysdba<<EOF! >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
whenever oserror exit failure
|
|
whenever sqlerror exit sql.sqlcode
|
|
alter system set cluster_database=true scope=spfile sid='*';
|
|
shutdown immediate;
|
|
EOF!
|
|
continue_if_ok $?
|
|
}
|
|
|
|
update_CRS (){
|
|
${DB_SRVCTL} modify database -db ${DB_UNIQUE_NAME} -oraclehome ${NEW_ORACLE_HOME} >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
}
|
|
|
|
start_db (){
|
|
${DB_SRVCTL} start database -db ${DB_UNIQUE_NAME} >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
continue_if_ok $?
|
|
|
|
${DB_SRVCTL} status database -db ${DB_UNIQUE_NAME} -v >> ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log 2>&1
|
|
}
|
|
|
|
get_database_role (){
|
|
export ORACLE_HOME=${OHOME}
|
|
export ORACLE_SID=${EXECUTION_INSTANCE}
|
|
DATABASE_ROLE=$(${ORACLE_HOME}/bin/sqlplus -s / as sysdba<<EOF!
|
|
whenever sqlerror exit failure;
|
|
set pages 0 head off feed off
|
|
select database_role from v\$database;
|
|
EOF!
|
|
)
|
|
continue_if_ok $?
|
|
echo " Database role: ${DATABASE_ROLE}"
|
|
}
|
|
|
|
local_execution (){
|
|
get_database_role
|
|
echo " Logfile: ${ABS_PATH_EXECUTION_LOG_FILE}"
|
|
echo " Restart ${DB_UNIQUE_NAME} database to start the procedure in clean conditions"
|
|
restart_database
|
|
echo " Copy init and passwordfile to new ORACLE_HOME"
|
|
copy_init_and_passwordfile
|
|
|
|
if [ "${DATABASE_ROLE}" == "PRIMARY" ]; then
|
|
echo " Set cluster_database=false for ${DB_UNIQUE_NAME} database"
|
|
disable_cluster
|
|
echo " Stop ${DB_UNIQUE_NAME} database"
|
|
stop_db
|
|
fi
|
|
|
|
# Update oratab sed '/YODAEXA:/d' oratab > oratab.
|
|
echo " Update ${DB_UNIQUE_NAME} in oratab with the new ORACLE_HOME"
|
|
update_oratab
|
|
|
|
if [ "${DATABASE_ROLE}" == "PRIMARY" ]; then
|
|
execute_datapatch
|
|
enable_cluster
|
|
fi
|
|
|
|
echo " Update ${DB_UNIQUE_NAME} in CRS with new ORACLE_HOME"
|
|
update_CRS
|
|
|
|
if [ "${DATABASE_ROLE}" == "PRIMARY" ]; then
|
|
echo " Start ${DB_UNIQUE_NAME} database"
|
|
start_db
|
|
else
|
|
echo " Restart ${DB_UNIQUE_NAME} database"
|
|
stop_db
|
|
start_db
|
|
fi
|
|
|
|
}
|
|
|
|
# --------
|
|
# M a i n
|
|
#---------
|
|
|
|
THIS_SHELL=$(basename "$0")
|
|
EXEC_ID="$(date +"%Y%m%d%H%M%S")$$"
|
|
|
|
# Parse the input parameters
|
|
while [ "$1" != "" ]; do
|
|
case $1 in
|
|
-d | --database ) shift
|
|
# Uppercase the database name
|
|
AUX_STRING=$1
|
|
DB_UNIQUE_NAME=${AUX_STRING^^}
|
|
;;
|
|
-o | --oraclehome ) shift
|
|
NEW_ORACLE_HOME=$1
|
|
;;
|
|
-h | --help ) usage
|
|
exit
|
|
;;
|
|
* ) usage
|
|
exit 1
|
|
esac
|
|
shift
|
|
done
|
|
|
|
# Check for parameter consistance
|
|
#--------------------------------
|
|
if [ -z "${DB_UNIQUE_NAME}" ]; then usage; exit 1; fi
|
|
if [ -z "${NEW_ORACLE_HOME}" ]; then usage; exit 1; fi
|
|
|
|
ABS_PATH_THIS_SHELL=$(readlink -f ${THIS_SHELL})
|
|
UPLEVEL_SCRIPT_PATH=$(dirname "${ROOTDIR}")
|
|
ABS_PATH_EXECUTION_LOG_FILE=$(readlink -f ${UPLEVEL_SCRIPT_PATH}/logs/${DB_UNIQUE_NAME}_${EXEC_ID}.log)
|
|
|
|
get_database_info
|
|
echo "I like to move it, move it!"
|
|
|
|
if [ "${THIS_HOST}" == "${DBNODE1}" ]; then
|
|
EXECUTION_HOST=${THIS_HOST}
|
|
EXECUTION_INSTANCE=${DBINST1}
|
|
EXECUTION_HOST_BIS=${DBNODE2}
|
|
EXECUTION_INSTANCE_BIS=${DBINST2}
|
|
echo "BEGIN ORACLE HOME MOVE for ${DB_UNIQUE_NAME} database"
|
|
local_execution
|
|
echo "END ORACLE HOME MOVE for ${DB_UNIQUE_NAME} database"
|
|
RETURN_CODE=0
|
|
elif [ "${THIS_HOST}" == "${DBNODE2}" ]; then
|
|
EXECUTION_HOST=${THIS_HOST}
|
|
EXECUTION_INSTANCE=${DBINST2}
|
|
EXECUTION_HOST_BIS=${DBNODE1}
|
|
EXECUTION_INSTANCE_BIS=${DBINST1}
|
|
echo "BEGIN DATAPATCH for ${DB_UNIQUE_NAME} database"
|
|
local_execution
|
|
echo "END DATAPATCH for ${DB_UNIQUE_NAME} database"
|
|
RETURN_CODE=0
|
|
else
|
|
echo "ERROR: please execute this shell on ${DBNODE1} or ${DBNODE2}"
|
|
RETURN_CODE=1
|
|
fi
|
|
|
|
exit ${RETURN_CODE}
|