基于Oracle Data Guard技术下的SAP DR系统的解决方案 － 日志传输

SAP镜像系统通常与主系统放置在不同的物理位置中，中间使用专线方式相连。但由于目前网络的费用较为昂贵，采用Oracle的service直写方式肯定不现实。

较为折衷的办法如下：

在与主系统同一物理环境内，比如DEV或QAS下，分配一块足够大的空间，做为PRD系统的第二日志写入点，并此系统中安排定期运行的脚本，压缩日志并采用诸如FTP一类的方式传输至镜像系统中。

之所以不采用brarchive的方式来进行传输，是因为brarchive会导致后面安排的任何brarchive和brbackup进程无法启动（SAP进程的排他性检测）。因为如果要传输日志，brarchive必须要时刻启动着，当新安排的brarchive和brbackup要启动时，SAP会检查进程中是否有br类的进程，如果有，后面安排的将会停止，换言之，这台机器将不能再安排任何br类的操作，除非停止brarchive的传输进程，这是非常不可取的。

 

在本解决方案中，假设了几个基本参数（完全依照真实数据而来）

日志大小　280M

主系统与镜像系统的网络传输带宽　1M

OS SuSE Linux Enterprise 9.3

用户　ora<SID>

用户组 ora

 

参与的系统

PRD　－　主系统

DEV　－　第二日志写入点的位置

MIRRPRD　－　镜像系统

 

在主系统上的配置

修改/etc/fstab，添加这一行

saperpdev:/prddbarch    /oracle/PRD/oraarch2    nfs rsize=32768,wsize=32768,hard 0 0

注：请先行在第二镜像点创建目录，目录权限均为ora<sid>:dba

修改init<SID>.ora，将参数设为如下

filesystemio_options     = setall

disk_asynch_io           = true

log_archive_dest_1 = "LOCATION=/oracle/PRD/oraarch/PRDarch MANDATORY REOPEN"

log_archive_dest_2 = "LOCATION=/oracle/PRD/oraarch2/PRDarch REOPEN"

创建远程数据库连接的口令文件。

orapwd -file=/oracle/<SID>/102_64/dbs/PWD<SID>.ora password=xxxx entries=100

重启数据库后，主系统就会写往两个点写日志了。

 

在第二日志写入点上的配置

修改/etc/fstab，

mirrprd:/oracle/PRD/mirrTrans /oracle/DEV/mirrTrans/standby nfs defaults 0 0

把镜像机器上的处理目录挂到第二日志点的传输脚本目录的standby目录下。

修改/oracle/<SID>/102_64/network/admin/tnsnames.ora

PRIMARYPRD.WORLD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS =

        (COMMUNITY = SAP.WORLD)

        (PROTOCOL = TCP)

        (HOST = <Your Primary DB IP or hostname>)

        (PORT = 1527)

      )

    )

    (CONNECT_DATA =

      (SID = <Your primary DB SID>)

      (GLOBAL_NAME = PRD.WORLD)

    )

  )

重启listener后，tnsping primaryprd看一下结果，是否通了。

在/oracle/<SID>下创建传输日志的工作目录，目录结构如下

/oracle/<SID>/mirrTrans     工作目录

/oracle/<SID>/mirrTrans/logs　传输脚本产生的日志将会发在这个目录下

/oracle/<SID>/mirrTrans/primary　查询主系统的最新日志更新序列后产生的序列号文件

/oracle/<SID>/mirrTrans/standby　镜像系统日志处理目录，挂接到第二日志点的目录中

 

在此目录下生成search_seq.sql文件

connect system/<password>@PRIMARYPRD

set tab off

set pagesize 0

set linesize 50

spool primary/primary.sequence

select max(sequence#) from v$log_history

/

exit

测试一下运行的情况

sqlplus /nolog @search_seq.sql

cat primary/primary.sequence

你会看到形如1234这样的数字，这代表了当前主系统更新到的号。

创建transfer_log.sh

 

ORACLE_HOME=/oracle/DEV/102_64

export ORACLE_HOME

 

WORK_DIR=/oracle/DEV/mirrTrans

STANDBY_DIR=$WORK_DIR/standby

PRIMARY_DIR=$WORK_DIR/primary

ARCH_DIR=/prddbarch

TRANSFER_FILE_LIST=""

UNTRANSFER_FILE_LIST=""

WORK_IN_LAN=0

START_TIME=`date "+%F_%T"`

LOGFILE_NAME=$WORK_DIR/logs/transfer_log_$START_TIME.log

STATUS_LOG=$WORK_DIR/transfer_status_`date "+%Y%m"`.log

 

log_status(){

  echo $1 >> $STATUS_LOG

}

 

log(){

  echo $1 >> $LOGFILE_NAME

}

 

log_space(){

  log " "

}

 

log_separator(){

  log "========================================================================="

}

 

generate_file_list(){

  start=$1

  end=$2

  start=`expr $start + 1`

  while [ $start -le $end ]

  do

   dbf=PRDarch1_"$start"_642797612.dbf

   echo $dbf

   if test -f $STANDBY_DIR/$dbf || test -f $STANDBY_DIR/$dbf.bz2

   then

      UNTRANSFER_FILE_LIST=$UNTRANSFER_FILE_LIST" "$dbf

   else

      TRANSFER_FILE_LIST=$TRANSFER_FILE_LIST" "$dbf

   fi

   start=`expr $start + 1`

  done

  log_space

  log "[info] Files following have been transferred to standby site"

  for file_name in $UNTRANSFER_FILE_LIST

  do

    log "[info] $file_name ..."

  done

  log_space

  log "[info] Files following need to transfer to standby site"

  if [ "$TRANSFER_FILE_LIST" != "" ]

  then

    size=0

    for file_name in $TRANSFER_FILE_LIST

    do

     log "[info] $file_name ..."

     file_size=`ls -l $ARCH_DIR/$file_name|awk '{print $5}'`

     size=`expr $size + $file_size`

    done

    log "[info] Total size is: `echo $size|awk '{printf "%4.2f", $1/1024/1024}'`MB"

  else

    log "[info] All files have been transferred to the standby site"

  fi

  log_space

}

 

transfer_file(){

log_space

log "[info] Timestamp: `date '+%F %T'`"

log "[info] Transfer file $1 via FTP"

ftp -i -v -n mirrprd<<EOF >> $LOGFILE_NAME

user <ftpuser> <ftppassword>

bin

cd /oracle/PRD/mirrTrans

put $1

bye

EOF

touch $STANDBY_DIR/$1.end

log "[info] Timestamp: `date '+%F %T'`"

log "[info] Transfer file $1 done"

log "[info] Delete $1, because it has been transferred to standby site"

rm $1

log "[info] Delete $1 done"

log_space

}

 

process_file(){

  log_space

  if [ $WORK_IN_LAN = 1 ]

  then

    echo "[info] Work in LAN, transfer file directly"

    transfer_file $1

  else

    log "[info] Work not in LAN, compress it first"

    log "[info] Timestamp: `date '+%F %T'`"

    bzip2 $1

    log "[info] Timestamp: `date '+%F %T'`"

    log "[info] Compress done"

    transfer_file $1.bz2

  fi

  log_space

}

 

end_job(){

  log_status "$START_TIME|transferLog|$1"

  log "[info] Timestamp: `date '+%F %T'`"

  log "[info] Transfer logs completed $2"

  log_separator

  rm transfer.lck

}

 

cd $WORK_DIR

 

if test -f transfer.lck

then

   log "[info] A process is running, quit this one now"

   exit

fi

 

log "[info] Mirror Logs Transfer (1.0)"

log "[info] Start of processing at: $START_TIME"

log_space

 

log "[info] Parameters"

log "[info] WORK_DIR:                   $WORK_DIR"

log "[info] PRIMARY_DIR:                $PRIMARY_DIR"

log "[info] STANDBY_DIR:                $STANDBY_DIR"

log "[info] File transfer via:          FTP"

log "[info] I. To make sure there is only one process running to transfer the file"

log "[info] Register this process"

touch transfer.lck

log "[info] Register done"

log_space

 

log "[info] Get the latest archived log sequence of Primary DB"

log "[exec] sqlplus /nolog @search_seq.sql" 

$ORACLE_HOME/bin/sqlplus /nolog @search_seq.sql >>$LOGFILE_NAME

log "[info] Get sequence done"

log_space

 

read PRIMARY_SEQUENCE < $PRIMARY_DIR/primary.sequence

log "[info] PRIMARY_SEQUENCE:          $PRIMARY_SEQUENCE"

read STANDBY_SEQUENCE < $STANDBY_DIR/standby.sequence

log "[info] STANDBY_SEQUENCE:          $STANDBY_SEQUENCE"

log_space

if [ $STANDBY_SEQUENCE = $PRIMARY_SEQUENCE ]

then

   log "[info] Standby has updated to the latest sequence with Primary, don't do anything"

   end_job 1 "without doing anything"

   exit

fi

 

generate_file_list $STANDBY_SEQUENCE $PRIMARY_SEQUENCE

 

if [ "$TRANSFER_FILE_LIST" == "" ]

then

   log "[info] No new logs need to transfer, do nothing and exit"

   end_job 2 "without doning anything"

   exit

fi

 

cd $ARCH_DIR

for FILE_NAME in $TRANSFER_FILE_LIST

do

  process_file $FILE_NAME

done

cd $WORK_DIR

end_job 0 "successfully"

这个脚本有个参数WORK_IN_LAN，当把它设为1时，表示工作在同一内网中，则需要传输的日志不会被压缩，直接发送即可。

请参看以下主流程的图例。

 

之所以有一个检查tranfer.lck的动作，是因为万一上一个传输动作没有完成，则这次的传输自动停止，以免发生冲突。所以第一次运行时，需要在镜像点那边先获取镜像数据库的序列号，并

cat 1234 > standby.sequence

以下是处理文件的流程。

每次传输完一个文件后，都会在镜像系统的对应目录下生成一个以.end为结尾的文件，表示传输完成。

 

运行这个脚本，sh transfer_log.sh，会在logs目录下产生一个这样的日志transfer_log_2008-04-11_13:40:01.log

[info] Mirror Logs Transfer (1.0)

[info] Start of processing at: 2008-04-11_13:40:01

 

[info] Parameters

[info] WORK_DIR: /oracle/DEV/mirrTrans

[info] PRIMARY_DIR: /oracle/DEV/mirrTrans/primary

[info] STANDBY_DIR: /oracle/DEV/mirrTrans/standby

[info] File transfer via: FTP

[info] I. To make sure there is only one process running to transfer the file

[info] Register this process

[info] Register done

 

[info] Get the latest archived log sequence of Primary DB

[exec] sqlplus /nolog @search_seq.sql

 

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Apr 11 13:40:01 2008

 

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

 

Connected.

          9669

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

[info] Get sequence done

 

[info] PRIMARY_SEQUENCE: 9669

[info] STANDBY_SEQUENCE: 9665

 

 

[info] Files following have been transferred to standby site

[info] PRDarch1_9666_642797612.dbf ...

[info] PRDarch1_9667_642797612.dbf ...

 

[info] Files following need to transfer to standby site

[info] PRDarch1_9668_642797612.dbf ...

[info] PRDarch1_9669_642797612.dbf ...

[info] Total size is: 387.64MB

 

 

[info] Work not in LAN, compress it first

[info] Timestamp: 2008-04-11 13:40:01

[info] Timestamp: 2008-04-11 13:41:18

[info] Compress done

 

[info] Timestamp: 2008-04-11 13:41:18

[info] Transfer file PRDarch1_9668_642797612.dbf.bz2 via FTP

Connected to mirrprd.

220---------- Welcome to Pure-FTPd [TLS] ----------

220-You are user number 1 of 50 allowed.

220-Local time is now 13:41. Server port: 21.

220-IPv6 connections are also welcome on this server.

220 You will be disconnected after 15 minutes of inactivity.

331 User Nippon OK. Password required

230-User Nippon has group access to:  ftp      video    audio    dialout

230- uucp   

230 OK. Current directory is /home/ Nippon

Remote system type is UNIX.

Using binary mode to transfer files.

200 TYPE is now 8-bit binary

250 OK. Current directory is /oracle/PRD/mirrTrans

local: PRDarch1_9668_642797612.dbf.bz2 remote: PRDarch1_9668_642797612.dbf.bz2

229 Extended Passive mode OK (|||23429|)

150 Accepted data connection

226-30556.3 Mbytes free disk space

226-File successfully transferred

226 122.915 seconds (measured here), 451.40 Kbytes per second

56815382 bytes sent in 02:02 (451.47 KB/s)

221-Goodbye. You uploaded 55484 and downloaded 0 kbytes.

221 Logout.

[info] Timestamp: 2008-04-11 13:43:21

[info] Transfer file PRDarch1_9668_642797612.dbf.bz2 done

[info] Delete PRDarch1_9668_642797612.dbf.bz2, because it has been transferred to standby site

[info] Delete PRDarch1_9668_642797612.dbf.bz2 done

 

 

 

[info] Work not in LAN, compress it first

[info] Timestamp: 2008-04-11 13:43:21

[info] Timestamp: 2008-04-11 13:44:35

[info] Compress done

 

[info] Timestamp: 2008-04-11 13:44:35

[info] Transfer file PRDarch1_9669_642797612.dbf.bz2 via FTP

Connected to mirrprd.

220---------- Welcome to Pure-FTPd [TLS] ----------

220-You are user number 1 of 50 allowed.

220-Local time is now 13:44. Server port: 21.

220-IPv6 connections are also welcome on this server.

220 You will be disconnected after 15 minutes of inactivity.

331 User Nippon OK. Password required

230-User Nippon has group access to:  ftp      video    audio    dialout

230- uucp   

230 OK. Current directory is /home/ Nippon

Remote system type is UNIX.

Using binary mode to transfer files.

200 TYPE is now 8-bit binary

250 OK. Current directory is /oracle/PRD/mirrTrans

local: PRDarch1_9669_642797612.dbf.bz2 remote: PRDarch1_9669_642797612.dbf.bz2

229 Extended Passive mode OK (|||26613|)

150 Accepted data connection

226-30497.2 Mbytes free disk space

226-File successfully transferred

226 135.808 seconds (measured here), 445.02 Kbytes per second

61887882 bytes sent in 02:15 (445.09 KB/s)

221-Goodbye. You uploaded 60438 and downloaded 0 kbytes.

221 Logout.

[info] Timestamp: 2008-04-11 13:46:51

[info] Transfer file PRDarch1_9669_642797612.dbf.bz2 done

[info] Delete PRDarch1_9669_642797612.dbf.bz2, because it has been transferred to standby site

[info] Delete PRDarch1_9669_642797612.dbf.bz2 done

 

 

[info] Timestamp: 2008-04-11 13:46:51

[info] Transfer logs completed successfully

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

再到镜像系统查看，在相应的目录下，应该会有一堆.bz2文件。