Data Guard 的3种数据保护模式

你的名字 2022-03-15 06:08 200阅读 0赞

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

[Data]: http://space.itpub.net/26277071/viewspace-707394