363 lines
11 KiB
Bash
Executable File
363 lines
11 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
## Usage: dbaas_restorepoint.sh <parameters>, where:
|
|
## -d|--database <database name>
|
|
## -o|--operation <list|create|delete|flashback>
|
|
## -r|--restorepoint <restore point name>
|
|
|
|
|
|
|
|
# Static parameters
|
|
#------------------
|
|
typeset ORATAB='/etc/oratab'
|
|
typeset ROOTDIR='/dbfs_tools/TOOLS/dbaas'
|
|
typeset ENVIRONMENT='dev'
|
|
|
|
# Global variables
|
|
#-----------------
|
|
typeset THIS_SHELL
|
|
typeset DB_UNIQUE_NAME
|
|
typeset OHOME
|
|
typeset OVERSION
|
|
typeset GHOME
|
|
typeset EXEC_ID
|
|
typeset SQLPLUS
|
|
typeset DB_SRVCTL
|
|
typeset GRID_SRVCTL
|
|
typeset -i LAST_RETURN_CODE
|
|
typeset -i AUX_INTEGER
|
|
typeset AUX_STRING
|
|
typeset DB_CONNECT_STRING
|
|
typeset STEP_MESSAGE
|
|
typeset STEP
|
|
typeset OPERATION
|
|
typeset RESTOREPOINT
|
|
|
|
|
|
# Procedures
|
|
#-----------
|
|
usage() {
|
|
[ "$*" ] && echo "$0: $*"
|
|
sed -n '/^##/,/^$/s/^## \{0,1\}//p' "$0"
|
|
exit 2
|
|
} 2>/dev/null
|
|
|
|
|
|
get_oracle_home (){
|
|
# 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 ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
fi
|
|
# Get GID_HOME
|
|
GHOME=$(cat ${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 }'> ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
typeset -i DBCOUNT=$(cat ${ROOTDIR}/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 ${ROOTDIR}/log/${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 /nolog"
|
|
DB_SRVCTL=${OHOME}/bin/srvctl
|
|
echo " INFO: database name=${DB_UNIQUE_NAME}, oracle home=${OHOME}, version=${OVERSION} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
}
|
|
|
|
ckeck_database_connectivity (){
|
|
${SQLPLUS} <<EOF! > /dev/null
|
|
set pages 0
|
|
set head off
|
|
set feed off
|
|
connect ${DB_CONNECT_STRING} as sysdba
|
|
spool ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';
|
|
select sysdate from dual;
|
|
spool off
|
|
exit
|
|
EOF!
|
|
# Remove the empty lines from the file
|
|
sed -i '/^[[:space:]]*$/d' ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
}
|
|
|
|
|
|
get_exec_id (){
|
|
EXEC_ID="$(date +"%Y%m%d%H%M%S")$$"
|
|
}
|
|
|
|
count_oracle_error_in_spool (){
|
|
typeset SPOOL=${1}
|
|
typeset -i ORA_COUNT=$(cat ${SPOOL} | grep ORA- |wc -l)
|
|
typeset -i SP2_COUNT=$(cat ${SPOOL} | grep SP2- |wc -l)
|
|
typeset -i ERR_COUNT=$(echo $((${ORA_COUNT} + ${SP2_COUNT})))
|
|
return ${ERR_COUNT}
|
|
}
|
|
|
|
|
|
create_restore_point (){
|
|
${SQLPLUS} <<EOF! > /dev/null
|
|
set pages 0
|
|
set head off
|
|
set feed off
|
|
connect ${DB_CONNECT_STRING} as sysdba
|
|
spool ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
create restore point R${EXEC_ID} guarantee flashback database;
|
|
spool off
|
|
exit
|
|
EOF!
|
|
# Remove the empty lines from the file
|
|
sed -i '/^[[:space:]]*$/d' ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
STEP_MESSAGE="create restorepoint R${EXEC_ID}"
|
|
count_oracle_error_in_spool "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
}
|
|
|
|
delete_restore_point (){
|
|
${SQLPLUS} <<EOF! > /dev/null
|
|
set pages 0
|
|
set head off
|
|
set feed off
|
|
connect ${DB_CONNECT_STRING} as sysdba
|
|
spool ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
drop restore point ${RESTOREPOINT};
|
|
spool off
|
|
exit
|
|
EOF!
|
|
# Remove the empty lines from the file
|
|
sed -i '/^[[:space:]]*$/d' ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
STEP_MESSAGE="drop restorepoint R${EXEC_ID}"
|
|
count_oracle_error_in_spool "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
cat ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
}
|
|
|
|
|
|
list_restore_points (){
|
|
${SQLPLUS} <<EOF! > /dev/null
|
|
connect ${DB_CONNECT_STRING} as sysdba
|
|
|
|
set lines 180 pages 100
|
|
set verify off
|
|
|
|
DEFINE BYTES_FORMAT="9,999,999"
|
|
DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024"
|
|
|
|
spool ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
|
|
COLUMN time HEADING "Time" FORMAT a18
|
|
COLUMN name HEADING "Name" FORMAT a30
|
|
COLUMN guarantee_flashback_database HEADING "Guar|anted" FORMAT a5
|
|
COLUMN preserved HEADING "Pre|ser|ved" FORMAT a3
|
|
COLUMN restore_point_time HEADING "Restore|Point|Time" FORMAT a18
|
|
COLUMN scn HEADING "SCN" FORMAT 999999999999999
|
|
COLUMN database_incarnation# HEADING "DB|Inc#" FORMAT 9999
|
|
COLUMN storage_size HEADING "Size(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
|
|
SELECT TO_CHAR(r.time,'DD-MON-YY HH24:MI:SS') time
|
|
, r.name
|
|
, r.guarantee_flashback_database
|
|
, r.preserved
|
|
, r.database_incarnation#
|
|
, r.scn
|
|
, (r.storage_size)/&&BYTES_DIVIDER storage_size
|
|
, TO_CHAR(r.restore_point_time,'DD-MON-YY HH24:MI:SS') restore_point_time
|
|
FROM v\$restore_point r
|
|
ORDER BY r.time;
|
|
spool off
|
|
exit
|
|
EOF!
|
|
STEP_MESSAGE="create restorepoint R${EXEC_ID}"
|
|
count_oracle_error_in_spool "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
sed -i 's/no rows selected/ INFO: no restorepoint found [OK]/g' ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
fi
|
|
}
|
|
|
|
flashback_to_restore_point (){
|
|
STEP_MESSAGE="stop database"
|
|
# Get the first node of the database
|
|
typeset FIRST_DB_INSTANCE=$(${DB_SRVCTL} status database -d ${DB_UNIQUE_NAME} | head -n 1| awk -F " " '{print $2}')
|
|
/dbfs_tools/TOOLS/admin/sh/db_blackout.sh ${DB_UNIQUE_NAME} ON
|
|
${DB_SRVCTL} stop database -d ${DB_UNIQUE_NAME}
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
|
|
STEP_MESSAGE="start in mount mode ${FIRST_DB_INSTANCE} instance of ${DB_UNIQUE_NAME} database "
|
|
${DB_SRVCTL} start instance -i ${FIRST_DB_INSTANCE} -d ${DB_UNIQUE_NAME} -o "mount"
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
sleep 30
|
|
${SQLPLUS} <<EOF! > /dev/null
|
|
set pages 0
|
|
set head off
|
|
set feed off
|
|
connect ${DB_CONNECT_STRING} as sysdba
|
|
spool ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
flashback database to restore point ${RESTOREPOINT};
|
|
alter database open resetlogs;
|
|
spool off
|
|
exit
|
|
EOF!
|
|
cat ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
# Remove the empty lines from the file
|
|
sed -i '/^[[:space:]]*$/d' ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
STEP_MESSAGE="flashback to restorepoint ${RESTOREPOINT}"
|
|
count_oracle_error_in_spool "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
LAST_RETURN_CODE=$?
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
cat ${ROOTDIR}/workdir/${EXEC_ID}.tmp
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
|
|
STEP_MESSAGE="restart database"
|
|
${DB_SRVCTL} stop database -d ${DB_UNIQUE_NAME}
|
|
${DB_SRVCTL} start database -d ${DB_UNIQUE_NAME}
|
|
LAST_RETURN_CODE=$?
|
|
${DB_SRVCTL} start service -d ${DB_UNIQUE_NAME}
|
|
/dbfs_tools/TOOLS/admin/sh/db_blackout.sh ${DB_UNIQUE_NAME} OFF
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
}
|
|
|
|
|
|
# --------
|
|
# M a i n
|
|
#---------
|
|
|
|
# Get the execution id of the shell
|
|
#----------------------------------
|
|
get_exec_id
|
|
THIS_SHELL=$(basename "$0")
|
|
|
|
# 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^^}
|
|
;;
|
|
-r | --restorepoint ) shift
|
|
RESTOREPOINT=$1
|
|
;;
|
|
-o | --operation ) shift
|
|
OPERATION=$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 ! [[ "${OPERATION}" == "list" || "${OPERATION}" == "create" || "${OPERATION}" == "delete" || "${OPERATION}" == "flashback" ]] ; then
|
|
usage
|
|
exit 1
|
|
fi
|
|
|
|
if [[ "${OPERATION}" == "delete" || "${OPERATION}" == "flashback" ]] ; then
|
|
if [ -z "${RESTOREPOINT}" ]; then
|
|
usage
|
|
exit 1
|
|
fi
|
|
fi
|
|
|
|
if [[ "${ENVIRONMENT}" == "prod" ]] ; then
|
|
DB_CONNECT_STRING="sys/plusdacces@dmp01-scan/${DB_UNIQUE_NAME}"
|
|
else
|
|
DB_CONNECT_STRING="sys/plusdacces@dmt01-scan/${DB_UNIQUE_NAME}"
|
|
fi
|
|
|
|
|
|
# Get the ORACLE_HOME of the database
|
|
#------------------------------------
|
|
get_oracle_home
|
|
|
|
# Try to connect sys as sysdba and get the SYSDATE
|
|
ckeck_database_connectivity
|
|
|
|
STEP_MESSAGE="call sysdate() in ${DB_UNIQUE_NAME} to check database connectivity"
|
|
count_oracle_error_in_spool "${ROOTDIR}/workdir/${EXEC_ID}.tmp"
|
|
LAST_RETURN_CODE=$?
|
|
|
|
if [[ ( ${LAST_RETURN_CODE} != 0 ) ]] ; then
|
|
echo " ERROR: ${STEP_MESSAGE} [FAILED]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
exit 1
|
|
else
|
|
AUX_STRING=$(cat "${ROOTDIR}/workdir/${EXEC_ID}.tmp")
|
|
echo " INFO: ${STEP_MESSAGE}: ${AUX_STRING} [OK]" | tee -a ${ROOTDIR}/log/${THIS_SHELl}_${EXEC_ID}.log
|
|
fi
|
|
|
|
|
|
if [[ "${OPERATION}" == "create" ]] ; then
|
|
create_restore_point
|
|
exit $?
|
|
fi
|
|
|
|
if [[ "${OPERATION}" == "list" ]] ; then
|
|
list_restore_points
|
|
exit $?
|
|
fi
|
|
|
|
if [[ "${OPERATION}" == "flashback" ]] ; then
|
|
flashback_to_restore_point
|
|
exit $?
|
|
fi
|
|
|
|
if [[ "${OPERATION}" == "delete" ]] ; then
|
|
delete_restore_point
|
|
exit $?
|
|
fi
|