Ceci a été testé sur des serveurs Solaris et avec des bd's 11g et 12c
#!/usr/bin/bash
#################################################################################
###
### Script : ValidStatusStBy.sh
### Description : Verification de l etat de la BD StandBy
### Author : Victor Tijero
### Parametres :
### 1.- ORACLE_SID
### 2.- UNIQUE_NAME DE LA STANDBY
### 3.- (Y/N) MODE FLASHBACK ACTIVE ATTENDU DANS LA BD STBY
### 4.- UNIQUE_NAME DE LA STANDBY
### 5.- (Y/N) iENVOYER UN EMAIL AVEC LE RESULTAT
###
###
#################################################################################
. ${HOME}/.profile
NLS_LANG="CANADIAN FRENCH_AMERICA.WE8ISO8859P1"
export NLS_LANG
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
export NLS_DATE_FORMAT
####################################
# Initialisation variables
####################################
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
ORACLE_SID=${1}
DB_UNIQUE_NAME_STB=${2}
MOD_FLSH_ACT=${3:-Y}
NBR_JRS_TOLR_SNAP=${4:-1}
ENV_EMAIL=${5:-Y}
#-------------------------------------------------------------------------------
export ORACLE_SID
export DB_UNIQUE_NAME_STB
export MOD_FLSH_ACT
export NBR_JRS_TOLR_SNAP
export ENV_EMAIL
export V_DATE=`date +%Y%m%d_%H%M`
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
if [ ! "$1" ]; then
echo -e "ERROR - Valeur de la variable ORACLE_SID n a pas ete indique"
exit
fi
if [ ! "$2" ]; then
echo -e "ERROR - Valeur de la variable DB_UNIQUE_NAME_STB n a pas ete indique"
exit
fi
if [ ! "$3" ]; then
echo -e "ERROR - Valeur de la variable MOD_FLSH_ACT n a pas ete indique. Valeur Y assigne par defaut"
else
if [ "$MOD_FLSH_ACT" != "Y" -a "$MOD_FLSH_ACT" != "y" -a "$MOD_FLSH_ACT" != "N" -a "$MOD_FLSH_ACT" != "n" ]; then
echo -e "ERROR - Valeur de la variable MOD_FLSH_ACT doit etre Y ou N"
exit
fi
fi
if [ ! "$4" ]; then
echo -e "WARNING - Valeur de la variable NBR_JRS_TOLR ne peut pas etre NULL. Valeur 1 assigne par defaut"
else
if [ $NBR_JRS_TOLR_SNAP -le 0 ]; then
echo -e "WARNING - Valeur de la variable NBR_JRS_TOLR ne peut pas etre 0. Valeur 1 assigne par defaut"
fi
fi
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
if [ ! -d /Scripts/DataGuard/${DB_UNIQUE_NAME_STB} ]; then
mkdir -p /Scripts/DataGuard/${DB_UNIQUE_NAME_STB}
fi
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
FTEMP=/Scripts/DataGuard/${DB_UNIQUE_NAME_STB}/VerifStatusStdBy_${DB_UNIQUE_NAME_STB}.tmp
LOGFL=/Scripts/DataGuard/${DB_UNIQUE_NAME_STB}/VerifStatusStdBy_${DB_UNIQUE_NAME_STB}.log
export FTEMP
export LOGFL
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
echo -e "ORACLE_SID..........:" $ORACLE_SID
echo -e "DB_UNIQUE_NAME_STB..:" $DB_UNIQUE_NAME_STB
echo -e "MOD_FLSH_ACT........:" $MOD_FLSH_ACT
echo -e "NBR_JRS_TOLR_SNAP...:" $NBR_JRS_TOLR_SNAP
echo -e "ENV_EMAIL...........:" $ENV_EMAIL
#-------------------------------------------------------------------------------
if [ -f ${LOGFL} ]; then
echo -e "Delete Log..........:" $LOGFL
rm -f $LOGFL
fi
if [ -f $FTEMP ]; then
echo -e "Delete du Temp..... :" $FTEMP
rm -f $FTEMP
fi
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
sqlplus -s / as sysdba <<EOF >$FTEMP
set feed off pages 0 trimspool on linesize 500
SELECT CHECK_MODE_BD||','||CHECK_FLASHBK||','||CHECK_RESTPNT||','||CHECK_LOGSAPP||','||CHECK_LOGSREC||','||CHECK_APPLLOG RESULT
FROM (
SELECT '1CHECK_MODE_BD' ACHECK, DATABASE_ROLE AS VALEUR
FROM V\$DATABASE
UNION
SELECT '2CHECK_FLASHBK' ACHECK, FLASHBACK_ON AS VALEUR
FROM V\$DATABASE
UNION
SELECT '3CHECK_RESTPNT' ACHECK, NAME AS VALEUR
FROM V\$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES'
UNION
SELECT '4CHECK_LOGSAPP' ACHECK, TO_CHAR(NEXT_TIME,'YYYYMMDD_HH24MI') AS VALEUR
FROM ( SELECT DISTINCT INC_B.INCARNATION#,
FIRST_VALUE(ARC.NEXT_TIME) OVER (ORDER BY ARC.SEQUENCE# DESC) NEXT_TIME,
FIRST_VALUE(ARC.SEQUENCE#) OVER (ORDER BY ARC.SEQUENCE# DESC) SEQUENCE#,
FIRST_VALUE(ARC.NAME ) OVER (ORDER BY ARC.SEQUENCE# DESC) NAME
FROM V\$DATABASE_INCARNATION INC_B, V\$ARCHIVED_LOG ARC
WHERE ARC.APPLIED = 'YES' AND
ARC.RESETLOGS_CHANGE# = INC_B.RESETLOGS_CHANGE#
AND ARC.RESETLOGS_TIME = INC_B.RESETLOGS_TIME
AND INC_B.INCARNATION# = ( SELECT (CASE DBS.DATABASE_ROLE WHEN 'SNAPSHOT STANDBY' THEN INC_A.PRIOR_INCARNATION# ELSE INC_A.INCARNATION# END ) INCARNATION
FROM V\$DATABASE DBS, V\$DATABASE_INCARNATION INC_A
WHERE INC_A.STATUS ='CURRENT' ) )
UNION
SELECT '5CHECK_LOGSREC' ACHECK, TO_CHAR(NEXT_TIME,'YYYYMMDD_HH24MI') AS VALEUR
FROM ( SELECT DISTINCT INC_B.INCARNATION#,
FIRST_VALUE(ARC.NEXT_TIME) OVER (ORDER BY ARC.SEQUENCE# DESC) NEXT_TIME,
FIRST_VALUE(ARC.SEQUENCE#) OVER (ORDER BY ARC.SEQUENCE# DESC) SEQUENCE#,
FIRST_VALUE(ARC.NAME ) OVER (ORDER BY ARC.SEQUENCE# DESC) NAME
FROM V\$DATABASE_INCARNATION INC_B, V\$ARCHIVED_LOG ARC
WHERE ARC.RESETLOGS_CHANGE# = INC_B.RESETLOGS_CHANGE#
AND ARC.RESETLOGS_TIME = INC_B.RESETLOGS_TIME
AND INC_B.INCARNATION# = ( SELECT (CASE DBS.DATABASE_ROLE WHEN 'SNAPSHOT STANDBY' THEN INC_A.PRIOR_INCARNATION# ELSE INC_A.INCARNATION# END ) INCARNATION
FROM V\$DATABASE DBS, V\$DATABASE_INCARNATION INC_A
WHERE INC_A.STATUS ='CURRENT' ) )
UNION
SELECT DISTINCT '6CHECK_APPLLOG' ACHECK, STATUS VALEUR
FROM V\$MANAGED_STANDBY
WHERE STATUS = 'APPLYING_LOG'
ORDER BY ACHECK )
PIVOT ( MAX(VALEUR) FOR ACHECK IN ( '1CHECK_MODE_BD' AS CHECK_MODE_BD,
'2CHECK_FLASHBK' AS CHECK_FLASHBK,
'3CHECK_RESTPNT' AS CHECK_RESTPNT,
'4CHECK_LOGSAPP' AS CHECK_LOGSAPP,
'5CHECK_LOGSREC' AS CHECK_LOGSREC,
'6CHECK_APPLLOG' AS CHECK_APPLLOG
) );
EOF
#################################################################################
#################################################################################
# now process the temp file
V_FOOT="0"
IFS=","
while read CHECK_MODE_BD CHECK_FLASHBK CHECK_RESTPNT CHECK_LOGSAPP CHECK_LOGSREC CHECK_APPLLOG
do
###############################################################
###############################################################
## Ne pas creer le fichier sauf erreur
## Si un fichier de Log existe c est parce qu il y a une erreur
echo " "
echo "ORACLE_SID..........:" $ORACLE_SID
echo "DB_UNIQUE_NAME_STB..:" $DB_UNIQUE_NAME_STB
echo "NBR_JRS_TOLR_SNAP...:" $NBR_JRS_TOLR_SNAP
echo "CHECK_MODE_BD.......:" $CHECK_MODE_BD
echo "CHECK_FLASHBK.......:" $CHECK_FLASHBK
echo "CHECK_RESTPNT.......:" $CHECK_RESTPNT
echo "V_DATE..............:" $V_DATE
echo "CHECK_LOGSREC.......:" $CHECK_LOGSREC
echo "CHECK_LOGSAPP.......:" $CHECK_LOGSAPP
echo " "
###############################################################
###############################################################
if [ ! -z $FTEMP ]; then
if [ `grep "ORA-" $FTEMP | wc -l` -ge 1 ]; then
echo -e " \n" >> $LOGFL
echo -e "ERROR - Possible probleme de connexion a la Bd $ORACLE_SID\n" >> $LOGFL
echo -e "Parametres........:\n" >> $LOGFL
echo -e " ORACLE_SID..........:" $ORACLE_SID >> $LOGFL
echo -e " DB_UNIQUE_NAME_STB..:" $DB_UNIQUE_NAME_STB >> $LOGFL
echo -e " NBR_JRS_TOLR_SNAP...:" $NBR_JRS_TOLR_SNAP >> $LOGFL
echo -e " ORACLE_HOME.........:" $ORACLE_HOME >> $LOGFL
echo -e " \n" >> $LOGFL
echo -e "Erreur detecte....:\n" >> $LOGFL
cat $FTEMP >> $LOGFL
echo -e " \n" >> $LOGFL
#exit
fi
fi
########################################################
# Validation en fonction du FlashBack de la BD
########################################################
#------------------------------------------------------------#
if [ "$CHECK_FLASHBK" == "NO" ]; then
if [ "$MOD_FLSH_ACT" == "Y" -o "$MOD_FLSH_ACT" == "y" ]; then
echo -e " \n" >> $LOGFL
echo -e "WARNING - La Bd Standby ${DB_UNIQUE_NAME_STB} semble ne pas etre en mode Flashback \n" >> $LOGFL
echo -e "WARNING - Le Flashback est normalement active en PROD sur toutes les BDs avec une configuration DataGuard\n" >> $LOGFL
echo -e " \n" >> $LOGFL
fi
fi
#------------------------------------------------------------#
########################################################
# Validation en fonction de l APPLY des logs
# Une tolerance en fonction du parametre est appliquee
########################################################
#---------------------------------------------------#
# Structure YYYMMDD - HEURES - APPLY
#---------------------------------------------------#
VAH_REC_H=`echo $CHECK_LOGSREC | cut -d_ -f2 | cut -c1,2`
VAH_REC_M=`echo $CHECK_LOGSREC | cut -d_ -f2 | cut -c3,4`
VAH_APP_H=`echo $CHECK_LOGSAPP | cut -d_ -f2 | cut -c1,2`
VAH_APP_M=`echo $CHECK_LOGSAPP | cut -d_ -f2 | cut -c3,4`
V_DIF_APPLY_LOG_H=`expr $VAH_REC_H - $VAH_APP_H`
#---------------------------------------------------#
# Structure YYYMMDD - JOURS - APPLY
#---------------------------------------------------#
VAJ_REC=`echo $CHECK_LOGSREC | cut -d_ -f1`
VAJ_REC=$(/opt/csw/bin/gdate -d "$VAJ_REC $VAH_REC_H:$VAH_REC_M:00" '+%s' )
VAJ_APP=`echo $CHECK_LOGSAPP | cut -d_ -f1`
VAJ_APP=$(/opt/csw/bin/gdate -d "$VAJ_APP $VAH_APP_H:$VAH_APP_M:00" '+%s' )
V_DIF_APPLY_LOG_J=$(( ( $VAJ_REC - $VAJ_APP ) / 86400 ))
#---------------------------------------------------#
if [ "$CHECK_MODE_BD" == "SNAPSHOT STANDBY" ]; then
if [ $V_DIF_APPLY_LOG_J -gt $NBR_JRS_TOLR_SNAP ]; then #### Difference en jours. Au moins 1 jour
echo -e "INFORMATION - La Bd StandBy ${DB_UNIQUE_NAME_STB} est en Mode Snapshot \n" >> $LOGFL
echo -e "INFORMATION - Le seuil de tolerance en mode snapshot a ete etabli a ${NBR_JRS_TOLR_SNAP} jours \n" >> $LOGFL
echo -e "INFORMATION - Il se peut que des travaux soient en cours sur cette BD mais il faudrait valider\n" >> $LOGFL
echo -e "ERROR - La BD semble ne pas appliquer de logs il y a ${V_DIF_APPLY_LOG_J} jours \n" >> $LOGFL
V_FOOT="1"
fi
else
if [ ! "$CHECK_APPLLOG" ]; then #### L Apply est arrete
echo -e "INFORMATION - La Bd StandBy ${DB_UNIQUE_NAME_STB} est en Mode Physical \n" >> $LOGFL
echo -e "ERROR - La Bd semble ne pas avoir l APPLY des logs active dans sa configuration\n" >> $LOGFL
fi
if [ $V_DIF_APPLY_LOG_J -gt 0 ]; then #### Difference en jours. Au moins 1 jour
if [ $V_DIF_APPLY_LOG_J -gt 1 ]; then
echo -e "ERROR(0A) - La BD semble ne pas appliquer de logs il y a ${V_DIF_APPLY_LOG_J} jours \n" >> $LOGFL
V_FOOT="1"
else
V_DIF_ABS=`expr 24 + ${V_DIF_APPLY_LOG_H}`
if [ $V_DIF_ABS -gt 2 ]; then #### Difference en heures. Plus de 2 heures
echo -e "ERROR(1A) - La BD semble ne pas aplliquer de logs il y a plus ou moins ${V_DIF_ABS} heures \n" >> $LOGFL
V_FOOT="1"
fi
fi
else
if [ $V_DIF_APPLY_LOG_H -gt 2 ]; then #### Difference en heures. Plus de 2 heures
echo -e "ERROR(2A) - La BD semble ne pas appliquer de logs il y a plus ou moins ${V_DIF_APPLY_LOG_H} heures \n" >> $LOGFL
V_FOOT="1"
fi
fi
fi
###########################################################
########################################################
# Validation en fonction de la reception des logs
# Une tolerance de 2 heures est appliquee
########################################################
#---------------------------------------------------#
# Structure YYYMMDD - HEURES - RECEPTION
#---------------------------------------------------#
VRH_DTE_H=`echo $V_DATE | cut -d_ -f2 | cut -c1,2`
VRH_DTE_M=`echo $V_DATE | cut -d_ -f2 | cut -c3,4`
VRH_REC_H=`echo $CHECK_LOGSREC | cut -d_ -f2 | cut -c1,2`
VRH_REC_M=`echo $CHECK_LOGSREC | cut -d_ -f2 | cut -c3,4`
V_DIF_RECEP_LOG_H=`expr $VRH_DTE_H - $VRH_REC_H `
#---------------------------------------------------#
# Structure YYYMMDD - JOURS - RECEPTION
#---------------------------------------------------#
VRJ_DTE=`echo $V_DATE | cut -d_ -f1`
VRJ_DTE=$(/opt/csw/bin/gdate -d "$VRJ_DTE $VRH_DTE_H:$VRH_DTE_M:00" '+%s' )
VRJ_REC=`echo $CHECK_LOGSREC | cut -d_ -f1`
VRJ_REC=$(/opt/csw/bin/gdate -d "$VRJ_REC $VRH_REC_H:$VRH_REC_M:00" '+%s' )
V_DIF_RECEP_LOG_J=$(( ( $VRJ_DTE - $VRJ_REC ) / 86400 ))
#---------------------------------------------------#
if [ $V_DIF_RECEP_LOG_J -gt 0 ]; then #### Difference en jours. Au moins 1 jour
if [ $V_DIF_RECEP_LOG_J -gt 1 ]; then #### Difference en jours.
echo -e "ERROR(0R) - La BD semble ne pas recevoir de logs il y a ${V_DIF_RECEP_LOG_J} jours \n" >> $LOGFL
V_FOOT="1"
else
V_DIF_ABS=`expr 24 + ${V_DIF_RECEP_LOG_H}`
if [ $V_DIF_ABS -gt 2 ]; then #### Difference en heures. Plus de 2 heures
echo -e "ERROR(1R) - La BD semble ne pas recevoir de logs il y a plus ou moins ${V_DIF_ABS} heures \n" >> $LOGFL
V_FOOT="1"
fi
fi
else
if [ $V_DIF_RECEP_LOG_H -gt 2 ]; then #### Difference en heures. Plus de 2 heures
echo -e "ERROR(2R) - La BD semble ne pas recevoir de logs il y a plus ou moins ${V_DIF_RECEP_LOG_H} heures \n" >> $LOGFL
V_FOOT="1"
fi
fi
#---------------------------------------------------#
if [ $V_DIF_RECEP_LOG_J -gt 0 ]; then
if [ $V_FOOT == "1" ]; then
echo -e "INFORMATION - Dernier Log recu........ "${CHECK_LOGSREC}" \n" >> $LOGFL
echo -e "INFORMATION - Dernier Log applique....."${CHECK_LOGSAPP}" \n" >> $LOGFL
fi
fi
#------------------------------------------------------------#
EMAIL=YOUR.EMAIL@YOUR.DOMAIN
export EMAIL
#------------------------------------------------------------#
if [ -f ${LOGFL} ]; then
if [ "${ENV_EMAIL}" == "Y" ]; then
cat $LOGFL | mailx -s "Bd StandBy ${DB_UNIQUE_NAME_STB} a surveiller " $EMAIL
fi
cat $LOGFL
else
echo " "
echo "Tout semble correct"
echo " "
fi
exit
done <$FTEMP
exit
À la fin, si jamais il y a un problème, vous pouvez recevoir un message comme celui-ci :
