Data Guard 的3种数据保护模式

1. 最大保护模式
原理： 当事务提交时，日志必须同时写到Primary数据库和至少一个Standby数据库，以保证Primary和Standby的数据的实时同步。
问题： 1. 影响Primary数据库的性能； 2. 当日志无法同步到至少一个Standby时，Primary会被强制Shutdown。
适用范围： 除非对于数据安全极其敏感的系统，否则很少使用该模式。
2. 最大性能模式（默认）
原理：当Primary的日志无法传递到Standby时，Primary不受影响，且会不断尝试向Standby 传递日志，直至成功。
问题： 无法保证数据的绝对安全，会造成部分数据丢失。
3. 最大可用性模式（折中方案）
原理： 正常情况下，和最大保护模式一样，但当日志无法同步到Standby时，并不会导致Primary的Shutdown，此时，自动切换至最大性能模式；当所有的日志全部传递至Standby时，又切换回最大保护模式。
问题： 1. 比最大性能模式安全，当仍无法保证数据的绝对安全; 2. 对Primary数据库性能也有影响

=============================================

在道富的DataGuard架构中，大都采用最大性能模式，为了把可能的数据丢失降到最小，部署一Autosys job，该job每隔半小时查询Primary和Standby间的Gap，若Gap超过指定限度，便向oncall DBA发beep。

附录： Standby check job 代码

#!/bin/ksh

#**********************************************************************
# SCCS Information
# ----------------
# Module        : dba_oracle_check_standby
# Version       : 1.4
# Date Changed  : 10/13/09 13:42:33
# Date Retrieved: 10/19/09
# Type          :
#**********************************************************************
# Revision History:
#
# Date         Name            Description
# --------     --------------  ----------------------------------------
# 11/06/07     Alan Stanke     Original Development
# 09/16/09     Fei Wu          Added "set tab off" in check_dataguard_progress
#
#**********************************************************************
# File Description:
# -----------------
# This script. will monitor the log ship/apply progress on a
# Data Guard Standby instance
#
# This script. must be run locally on the standby machine.
# It will:
#   - connect to the standby instance and record the numbers of:
#      - the last archived log on the standby
#      - the last applied archived log on the standby
#   - connect remotely to the primary instance and record the number of:
#      - the current log on the primary
#   - compare the number of the current log on the primary with the
#        the log latest archived log on the primary
#      - generate an error if they are too far out of sync (based on
#           the log threshold parameter
#   - compare the number of the current log on the primary with the
#        the log latest applied archived log on the primary
#      - generate an error if they are too far out of sync (based on
#           the applied log threshold parameter
#
# The parameters are as follows:
#    [-s standby instance ]      (required)
#    [-p primary instance ]      (default: same name as standby instance)
#    [-t instance thread ]       (default: 1)
#    [-T log threshold]          (default: 1)
#    [-A applied log threshold]  (default: 2)
#
# Note: For OracleRAC, you will need to run this for each RAC node.
#       (i.e. one for each "thread/primary RAC instance")
#
#************************************************************************


#=================================================
sqlplus_function ( ) {
#-------------------------------------------------

sqlplus -s /nolog << EOF
$SQLIN
EOF

RETURN_CODE=$?

}


#=================================================
get_dbmaint_password  ( ) {
#-------------------------------------------------

if [ "$env_ind" = "dev" ]
then

   #======================
   # DEV environment
   #----------------------
   LD_LIBRARY_PATH=/ssb/cm/security/password/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
   PW_MATRIX=/auto/dba/monitor/.PASSWORD_MATRIX; export PW_MATRIX
   # If this is a development Linux machine,
   #    use /ssb/projects/dba/monitor/.PASSWORD_MATRIX_LINUX matrix file
   #    (LINUX has a different characters set than Solaris/AIX).
   THE_OS=`uname`
   if [ "$THE_OS" = "Linux" ]
   then
      echo "Note: using the LINUX development version of the password matrix "
      PW_MATRIX=/auto/dba/monitor/.PASSWORD_MATRIX_LINUX; export PW_MATRIX
   fi
   PW_DIR=/auto/cm/security/password/exe; export PW_DIR
   NEWARG=DB_SERVER_NEW_DEV
else
   #======================
   # PROD/UAT environment
   #----------------------
   LD_LIBRARY_PATH=/usr/local/ccms/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
   PW_MATRIX=/usr/local/ccms/security/dat/.DATABASE_MATRIX; export PW_MATRIX
   PW_DIR=/usr/local/ccms/security/exe; export PW_DIR
   NEWARG=DB_SERVER_NEW
fi

export LD_LIBRARY_PATH
export PW_MATRIX
export PW_DIR

#
# get dbmaint password
#
#NEWPSWD=`${PW_DIR}/pwEcho.exe DB_SERVER_NEW_DEV dbmaint`
#NEWPSWD=`${PW_DIR}/pwEcho.exe DB_SERVER_NEW dbmaint`
NEWPSWD=`${PW_DIR}/pwEcho.exe $NEWARG dbmaint`
rc=$?

if [ "$NEWPSWD" = "NA" ]
then
   ERROR_FLAG=y
   pswd="NA"
   echo " "
   echo "***ERROR***ERROR***ERROR*** "
   echo "Unable to get the dbmaint password from the password matrix"
   echo "exit 99"
   echo " "
   exit 99
else
   pswd=$NEWPSWD
fi

}


#=================================================
check_dataguard_progress( ) {
#-------------------------------------------------

SQLIN=`cat <<EOF

connect / as sysdba;

set feedback off
set heading off
set tab off


select 'ABLE TO CONNECT TO STANDBY' from dual;

select 'STANDBY_LAST_LOG', max(sequence#)
from   v\\$archived_log
where  thread# = $THREAD ;


select 'STANDBY_LAST_APPLIED_LOG', max(sequence#)
from   v\\$archived_log
where  applied='YES'
and    thread# = $THREAD
group by thread# ;


connect dbmaint/${pswd}@${PRIMARY_INSTANCE} ;
set feedback off
set lin 150
set heading off
set tab off

select 'ABLE TO CONNECT TO PRIMARY' from dual;

select 'PRIMARY_CURRENT_LOG', sequence#
from   v\\$log
where  status='CURRENT'
and    thread# = $THREAD ;

-- commented out following... it may be useful in the future
--select 'PRIMARY_ARCHIVE_DEST_2_STATUS', status, error from v\\$archive_dest where dest_id = 2;

EOF
`

SQLOUT=`sqlplus_function`

# debug
#echo " "
#echo "SQLOUT follows: "
#echo "$SQLOUT"
#echo " "


GREPCOUNT=`echo "$SQLOUT" | grep -c "ORA-"`
if [ "$GREPCOUNT" != "0" ]
then
   echo "***ERROR***ERROR***ERROR***"
   echo "An ORA-xxx message was encountered in the SQL output. "
   echo "SQL output follows: "
   echo "$SQLOUT"
   echo "exit 99"
   exit 99
fi

GREPCOUNT=`echo "$SQLOUT" | grep -c "ABLE TO CONNECT TO STANDBY"`
if [ "$GREPCOUNT" != "1" ]
then
   echo "***ERROR***ERROR***ERROR***"
   echo "Unable to connect to the standby instance, exit 3"
   echo "SQL output follows: "
   echo "$SQLOUT"
   exit 3
fi

GREPCOUNT=`echo "$SQLOUT" | grep -c "ABLE TO CONNECT TO PRIMARY"`
if [ "$GREPCOUNT" != "1" ]
then
   echo "***ERROR***ERROR***ERROR***"
   echo "Unable to connect to the primary instance, exit 3"
   echo "SQL output follows: "
   echo "$SQLOUT"
   exit 3
fi


STANDBY_LAST_LOG=`
echo "$SQLOUT" | \
grep  "STANDBY_LAST_LOG" | \
sed "s/  */ /g" | \
cut -d" " -f2
`

if [ "$STANDBY_LAST_LOG" = "NULL" ]
then
   echo "***ERROR***ERROR***ERROR***"
   echo "The value for the last log number on the standby is NULL"
   echo "(Make sure the thread number is valid.) "
   echo "SQL output follows: "
   echo "$SQLOUT"
   echo "exit 98"
   exit 98
fi


STANDBY_LAST_APPLIED_LOG=`
echo "$SQLOUT" | \
grep  "STANDBY_LAST_APPLIED_LOG" | \
sed "s/  */ /g" | \
cut -d" " -f2
`

PRIMARY_CURRENT_LOG=`
echo "$SQLOUT" | \
grep  "PRIMARY_CURRENT_LOG" | \
sed "s/  */ /g" | \
cut -d" " -f2
`

echo " "
echo "PRIMARY_CURRENT_LOG      = $PRIMARY_CURRENT_LOG"
echo "STANDBY_LAST_LOG         = $STANDBY_LAST_LOG "
echo "STANDBY_LAST_APPLIED_LOG = $STANDBY_LAST_APPLIED_LOG "
echo " "


echo "Comparing last log on standby with the current log on the primary... "

if [ `expr $STANDBY_LAST_LOG + $LOG_THRESHOLD` -ge $PRIMARY_CURRENT_LOG ]
then
   echo "   PASS: The last log on the standby is "
   echo "         within $LOG_THRESHOLD of the current log on the primary.  "
else
   echo "   FAIL: The last log on the standby is "
   echo "         NOT within $LOG_THRESHOLD of the current log on the primary.  "
   ERROR_FLAG="y"
fi

echo " "
echo "Comparing last applied log on standby with the current log on the primary... "

if [ `expr $STANDBY_LAST_APPLIED_LOG + $APPLIED_LOG_THRESHOLD` -ge $PRIMARY_CURRENT_LOG ]
then
   echo "   PASS: The last applied log on the standby is "
   echo "         within $APPLIED_LOG_THRESHOLD of the current log on the primary.  "
else
   echo "   FAIL: The last applied log on the standby is "
   echo "         NOT within $APPLIED_LOG_THRESHOLD of the current log on the primary.  "
   ERROR_FLAG="y"
fi

echo " "

if [ "$ERROR_FLAG" = "y" ]
then
   echo "***ERROR***ERROR***ERROR***"
   echo "See error message(s) above."
   echo "exit 97"
   echo " "
   exit 97
fi


}



#=================================================
display_usage ( ) {
#-------------------------------------------------

cat <<EOF
display_usage: dba_oracle_check_standby

All parameters not correctly passed:

  [-s standby instance ]      (required)
  [-p primary instance ]      (default: same name as standby instance)
  [-t instance thread ]       (default: 1)
   [-T log threshold]          (default: 1)
  [-A applied log threshold]  (default: 2)

EOF

exit 1

}



#=====================================================================
#=====================================================================
#=====================================================================
# MAIN LOGIC
#---------------------------------------------------------------------
#---------------------------------------------------------------------

ERROR_FLAG="n"

echo " "
echo "===================================================================="
echo "===================================================================="
echo "===================================================================="
date
echo "dba_oracle_check_standby "
echo " "


#==============
# get options
#--------------
set -- `getopt s:p:T:A:t: $*`

getopt_rc=$?
if [ "$getopt_rc" -ne "0" ]
then
   display_usage
fi

while [ $# -gt 0 ]
do
   case $1 in
           -s )
                shift
                STANDBY_INSTANCE=$1
                shift
                ;;
           -p )
                shift
                PRIMARY_INSTANCE=$1
                shift
                ;;
           -t )
                shift
                THREAD=$1
                shift
                ;;
           -T )
                shift
                LOG_THRESHOLD=$1
                shift
                ;;
           -A )
                shift
                APPLIED_LOG_THRESHOLD=$1
                shift
                ;;
           -- )
                shift
                break
   esac
done


if   [ ! "$STANDBY_INSTANCE" ]
then
   display_usage
else
   ORACLE_SID="$STANDBY_INSTANCE"
   export ORACLE_SID
fi

if   [ ! "$PRIMARY_INSTANCE" ]
then
   PRIMARY_INSTANCE="$ORACLE_SID"
fi

if   [ ! "$THREAD" ]
then
   THREAD="1"
fi

if   [ ! "$LOG_THRESHOLD" ]
then
   LOG_THRESHOLD="1"
fi

if   [ ! "$APPLIED_LOG_THRESHOLD" ]
then
   APPLIED_LOG_THRESHOLD="2"
fi

export THREAD
export LOG_THRESHOLD
export APPLIED_LOG_THRESHOLD


echo "Check Data Guard log ship/apply progress ... "
echo " "
echo "STANDBY_INSTANCE      = $STANDBY_INSTANCE "
echo "PRIMARY_INSTANCE      = $PRIMARY_INSTANCE"
echo "THREAD                = $THREAD "
echo "LOG_THRESHOLD         = $LOG_THRESHOLD"
echo "APPLIED_LOG_THRESHOLD = $APPLIED_LOG_THRESHOLD"
echo "--------------------------------------------------------------------"
echo " "


#=================
# set environment
#-----------------
if [ -r /usr/local/ccms/dba_scripts ]
then
   PATH=.:$PATH:/usr/local/ccms/dba_scripts; export PATH
else
   if [ -r /usr/local/ccms/dba/scripts ]
   then
      PATH=.:$PATH:/usr/local/ccms/dba/scripts; export PATH
   else
      PATH=.:$PATH:/ssb/projects/dba/ndi; export PATH
   fi
fi


echo "- sourcing dba_get_env_variables "

. dba_get_env_variables


get_dbmaint_password


echo "- calling check_dataguard_progress"
echo " "

check_dataguard_progress


echo " "
date
echo "completed dba_oracle_check_standby "
echo "===================================================================="
echo " "
echo " "

exit 0