Oracle DG+sqlserver alwayson主节点切换时OGG源端目标端容灾实践

背景：

源端数据库是Oracle DG架构，其中Oracle的多张表需要OGG实时同步到SQL server数据库上，SQL server为AlwaysOn架构，

需求为不管源端Oracle还是目标端SQL server发生主机切换时，ogg需要随时可以容灾切换，保证ogg同步正常工作

Oracle DG

Oracle角色	主机名	IP
主库	host-1	192.168.1.11
从库	host-2	192.168.1.12

SQL server AlwaysOn

SQL server角色	主机名	IP
主库	win-1	192.168.1.21
备库	win-2	192.168.1.22
备库	win-3	192.168.1.23

拓扑图

Oracle端为sersync+rsync实时同步ogg目录文件到备库，windows端为均挂载共享目录nas，将windows端ogg目录安装到nas上，实现ogg跟随SQL server主节点快速切换；

Oracle端ogg容灾部署

主库 host-1 192.168.1.11
从库 host-2 192.168.1.12

一、主从库安装ogg软件

1.创建目录 /u01/app/ogg

上传 fbo_ggs_Linux_x64_shiphome.zip
解压 unzip fbo_ggs_Linux_x64_shiphome.zip
cd  /u01/app/fbo_ggs_Linux_x64_shiphome/Disk1/response
vim oggcore.rsp
--主要修改内容：
INSTALL_OPTION=ORA11g --安装选项，如果是oracle11g选择ORA11g
SOFTWARE_LOCATION=/u01/app/ogg --OGG的安装目录，一定要是一个空目录
START_MANAGER=false --是否自动启动mgr管理进程

--其他选择默认即可

2、启动静默安装

cd /u01/app/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller -silent -responseFile /u01/app/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
等待安装完成即可。

二、Oracle主从库安装sersync+rsync

1.部署rsync服务(rsync-server服务器上配置)

yum install rsync -y --在主从库上root用户安装rsync，如果嫌yum版本过低也可以源码安装

2.修改rsync配置文件

vim /etc/rsyncd.conf --默认rsync没有配置文件，创建一个，文件中汉字仅为注释，使用中请将所有注释清除

--主库host-1

#Rsync server
uid = root
gid = root
use chroot = no                         -- 安全相关
max connections = 2000                  -- 并发连接数
timeout = 600                           -- 超时时间（秒）
pid file =/var/run/rsyncd.pid           -- 指定rsync的pid目录
lock file =/var/run/rsync.lock          -- 指定rsync的锁文件【重要】
log file = /var/log/rsyncd.log          -- 指定rsync的日志目录
ignore errors                           --忽略一些I/O错误
read only = false                       --设置rsync服务端文件为读写权限
list = false                            --不显示rsync服务端资源列表
hosts allow = 192.168.0.0/16              --允许进行数据同步的客户端IP地址，可以设置多个，用英文状态下逗号隔开
hosts deny = 0.0.0.0/32                 --禁止数据同步的客户端IP地址，可以设置多个，用英文状态下逗号隔开
auth users = rsync_backup               --执行数据同步的用户名，可以设置多个，用英文状态下逗号隔开
secrets file =/etc/rsync.12.pass        --用户认证配置文件，里面保存用户名称和密码
#################################################
[ogg]                                   -- 模块 
comment = ogg
path = /u01/app/ogg

--从库host-2
#Rsync server
uid = root
gid = root
use chroot = no
max connections = 2000
timeout = 600
pid file =/var/run/rsyncd.pid
lock file =/var/run/rsync.lock
log file = /var/log/rsyncd.log
ignore errors
read only = false
list = false
hosts allow = 192.168.0.0/16
hosts deny = 0.0.0.0/32
auth users = rsync_backup
secrets file =/etc/rsync.24.pass   --密码文件和主库区别
#################################
[ogg] 
comment = ogg
path = /u01/app/ogg

3.创建用户认证文件，设置文件权限

--主库host-1
echo  "rsync_backup:123456">/etc/rsync.12.pass  --配置文件，添加以下内容
chmod 600 /etc/rsync.12.pass
--从库host-2
echo  "rsync_backup:123456">/etc/rsync.24.pass  --配置文件，添加以下内容
chmod 600 /etc/rsync.24.pass

4.主从库启动守护进程，并写入开机自启动

--启动服务
rsync --daemon  #可以使用--config= 指定非标准路径下的配置文件

vim /etc/rc.local
# rsync server progress
/usr/bin/rsync --daemon

5.主从rsync-client密码配置

--主库host-1
echo "123456">/etc/rsync.24.pass
chmod 600 /etc/rsync.24.pass
 
--从库host-2
echo "123456">/etc/rsync.12.pass
chmod 600 /etc/rsync.12.pass

6.主从库创建日志

--主库host-1
touch /u01/app/ogg/ogg.log
--从库host-2
touch /u01/app/ogg/ogg.log

7.测试从库拉取ogg文件

--从库host-2
rsync  -avzP rsync_backup@192.168.1.11::ogg/ /u01/app/ogg/ --password-file=/etc/rsync.12.pass
...
sent 37903 bytes  received 532161555 bytes  4730661.85 bytes/sec
total size is 4541399947  speedup is 8.53
--测试成功

8.主从上传部署sersync

rz sersync2.5.4_64bit_binary_stable_final.tar.gz

tar -zxvf sersync2.5.4_64bit_binary_stable_final.tar.gz -C /usr/local/
cd /usr/local/
mv GNU-Linux-x86 sersync

9.配置sersync

cp sersync/confxml.xml  sersync/confxml.xml-bak
vim  sersync/confxml.xml

--主库host-1

<inotify>
<delete start="true"/>
<createFolder start="true"/>
<createFile start="false"/>  --改为true
<closeWrite start="true"/>
<moveFrom start="true"/>
<moveTo start="true"/>
<attrib start="false"/> --改为true
<modify start="false"/> --改为true
</inotify>

<sersync>
<localpath watch="/opt/tongbu">  改为/u01/app/ogg
    <remote ip="127.0.0.1" name="tongbu1"/> 改为<remote ip="192.168.1.12" name="ogg"/>
    <!--<remote ip="192.168.8.39" name="tongbu"/>-->
    <!--<remote ip="192.168.8.40" name="tongbu"/>-->
</localpath>
<rsync>
    <commonParams params="-artuz"/>
    <auth start="false" users="root" passwordfile="/etc/rsync.pas"/> 改为<auth start="true" users="rsync_backup" passwordfile="/etc/rsync.24.pass"/> 
    <userDefinedPort start="false" port="874"/><!-- port=874 -->
    <timeout start="false" time="100"/><!-- timeout=100 -->
    <ssh start="false"/>
</rsync>

--从库host-2

<inotify>
<delete start="true"/>
<createFolder start="true"/>
<createFile start="false"/>  --改为true
<closeWrite start="true"/>
<moveFrom start="true"/>
<moveTo start="true"/>
<attrib start="false"/> --改为true
<modify start="false"/> --改为true
</inotify>

<sersync>
<localpath watch="/opt/tongbu">  改为/u01/app/ogg
    <remote ip="127.0.0.1" name="tongbu1"/> 改为<remote ip="192.168.1.11" name="ogg"/>
    <!--<remote ip="192.168.8.39" name="tongbu"/>-->
    <!--<remote ip="192.168.8.40" name="tongbu"/>-->
</localpath>
<rsync>
    <commonParams params="-artuz"/>
    <auth start="false" users="root" passwordfile="/etc/rsync.pas"/> 改为<auth start="true" users="rsync_backup" passwordfile="/etc/rsync.12.pass"/> 
    <userDefinedPort start="false" port="874"/><!-- port=874 -->
    <timeout start="false" time="100"/><!-- timeout=100 -->
    <ssh start="false"/>
</rsync>

10.设置主从环境变量

echo "export PATH=$PATH:/usr/local/sersync/">>/etc/profile
source /etc/profile

11.现阶段是主库ogg目录同步向从库，所以需要开启主库sersync服务，监听主库ogg目录变化，同步到从库

--检查从库host-2 rsync服务启动情况
[root@host-2 ~]# netstat -nulpt| grep rsync
--主库启动sersync
[root@host-1 ~]# /usr/local/sersync/sersync2  -d -r -o /usr/local/sersync/confxml.xml
set the system param
execute：echo 50000000 > /proc/sys/fs/inotify/max_user_watches
execute：echo 327679 > /proc/sys/fs/inotify/max_queued_events
parse the command param
option: -d     run as a daemon
option: -r     rsync all the local files to the remote servers before the sersync work
option: -o     config xml name：  /usr/local/sersync/confxml.xml
daemon thread num: 10
parse xml config file
host ip : localhost    host port: 8008
will ignore the inotify createFile event 
daemon start，sersync run behind the console 
use rsync password-file :
user is    rsync_backup
passwordfile is     /etc/rsync.24.pass
config xml parse success
please set /etc/rsyncd.conf max connections=0 Manually
sersync working thread 12  = 1(primary thread) + 1(fail retry thread) + 10(daemon sub threads) 
Max threads numbers is: 22 = 12(Thread pool nums) + 10(Sub threads)
please according your cpu ，use -n param to adjust the cpu rate
------------------------------------------
rsync the directory recursivly to the remote servers once
working please wait...
execute command: cd /u01/app/ogg && rsync -artuz -R --delete ./ rsync_backup@192.168.1.12::ogg --password-file=/etc/rsync.24.pass >/dev/null 2>&1 
run the sersync: 
watch path is: /u01/app/ogg

--检查运行情况
[root@host-1 ~]# ps -ef |grep sersync
root     24387     1  0 16:50 ?        00:00:00 /usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/confxml.xml

--检查从库ogg目录是否跟随主库ogg变化
ll /u01/app/ogg/dirchk/  --文件时间变化，测试成功

三、OGG容灾演练

--计划切换DG时，可以先停止ogg同步，再停止sersync，之后再进行切换ogg，较为安全
--非计划切换DG，主库不可用，大概可以套用以下步骤

1.切换Oracle DG

host-1：主库------>备库
host-2：备库------>主库
--主备库角色状态查询
SQL>select switchover_status,database_role,open_mode from v$database;

host-1切换到备库
SQL> alter database commit to switchover to physical standby with session shutdown;
#如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话

host-2切换到主库
SQL> alter database commit to switchover to primary;
SQL> alter database open;

2.关闭host-1ogg，启动host-2ogg

--在oracle用户下的.bash_profile 添加alias ggsci='rlwrap /u01/app/ogg/ggsci'

GGSCI (host-1) 1>stop ex  --当主库切换时ex抽取进程自动中断
GGSCI (host-1) 1>stop pup
GGSCI (host-1) 1>stop mgr

3.关闭host-1上的sersync监听服务

[root@host-1 ~]# ps -ef |grep sersync
root     24387     1  0 16:50 ?        00:00:00 /usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/confxml.xml

kill -9 24387

4.启动host-2上的ogg服务

GGSCI (host-2) 1>start mgr
GGSCI (host-2) 1>start ex
GGSCI (host-2) 1>start pup
GGSCI (host-2) 1>info all

--遇到的问题
ERROR   OGG-02803  Encountered a Data Guard role transition. Alter Extract to SCN 1,458,101 and restart Extract, or r
ecreate Extract with the correct number of threads at SCN 1,458,101.
--解决办法
GGSCI (host-2) 1>alter extract ex ,scn 1458101
GGSCI (host-2) 1>start ex

--mgr或者pup无法启动，查看进程，有进程正在跑

oracle   43037     1  0 Aug27 ?        00:01:46 /home/app/ogg/extract PARAMFILE /home/app/ogg/dirprm/pup.prm REPORTFILE /home/app/ogg/dirrpt/PUP.rpt PROCESSID PUP USESUBDIRS
oracle   54741 54329  0 11:42 pts/2    00:00:00 grep --color=auto pup
--解决办法
kill -9 43037

5.修复host-1的Oracle，开启只读模式

SQL> shutdown immediate
SQL> startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
SQL>alter database recover managed standby database using current logfile disconnect from session;

6.开启host-2sersync监听服务，同步host-2上的ogg到host-1上

--检查host-1上rsync服务启动情况
[root@host-1 ~]# netstat -nulpt| grep rsync
--host-2启动sersync
[root@host-2 ~]# /usr/local/sersync/sersync2  -d -r -o /usr/local/sersync/confxml.xml

set the system param
execute：echo 50000000 > /proc/sys/fs/inotify/max_user_watches
execute：echo 327679 > /proc/sys/fs/inotify/max_queued_events
parse the command param
option: -d     run as a daemon
option: -r     rsync all the local files to the remote servers before the sersync work
option: -o     config xml name：  /usr/local/sersync/confxml.xml
daemon thread num: 10
parse xml config file
host ip : localhost    host port: 8008
will ignore the inotify createFile event 
daemon start，sersync run behind the console 
use rsync password-file :
user is    rsync_backup
passwordfile is     /etc/rsync.12.pass
config xml parse success
please set /etc/rsyncd.conf max connections=0 Manually
sersync working thread 12  = 1(primary thread) + 1(fail retry thread) + 10(daemon sub threads) 
Max threads numbers is: 22 = 12(Thread pool nums) + 10(Sub threads)
please according your cpu ，use -n param to adjust the cpu rate
------------------------------------------
rsync the directory recursivly to the remote servers once
working please wait...
execute command: cd /u01/app/ogg && rsync -artuz -R --delete ./ rsync_backup@192.168.1.11::ogg --password-file=/etc/rsync.12.pass >/dev/null 2>&1 
run the sersync: 
watch path is: /u01/app/ogg

--检查sersync运行情况
[root@host-2 ~]# ps -ef |grep sersync
root     24387     1  0 16:50 ?        00:00:00 /usr/local/sersync/sersync2 -d -r -o /usr/local/sersync/confxml.xml

--检查host-1 ogg目录是否跟随主库ogg变化
[root@host-1 ~]#ll /u01/app/ogg/dirchk/  --文件时间变化，测试成功

 

windows 端OGG切换测试

1.停止windows端win-1上的ogg进程

GGSCI (win-1) 1>stop rer
GGSCI (win-1) 1>stop mgr

2.启动windows端win-2上的ogg进程mgr

GGSCI (win-2) 1>start mgr
GGSCI (win-2) 1>info  rer

3.停止oracle端ogg的pup传输进程

GGSCI (host-1) 1>stop pup

4.修改源端pup参数rmthost为win-2

GGSCI (host-1) 1>edit param pup

extract pup
USERID ggs, PASSWORD ggs
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_SID = orcl)
rmthost 192.168.1.22, mgrport 7809
rmttrail n:\ogg\dirdat\er
PASSTHRU
WILDCARDRESOLVE DYNAMIC
Dynamicresolution
table scott.test001;

5.启动Oracle端pup

GGSCI (host-1) 1>start pup

6.启动windows端rer应用进程

GGSCI (win-2) 1>info  rer
#要是出现问题需要修改rer指针，info rer找到之前的记录
#alter replicat rer, extseqno 159, extrba 136621541

7.测试oracle端和windows端表记录，ogg同步容灾成功

额外内容：

修改windows端ogg部署的路径方法

--拷贝ogg目录到其他路径
--修改源端pup参数路径
--修改源端pup进程路径
GGSCI (host-1) 1>info pup
GGSCI (host-1) 1>delete extract pup
GGSCI (host-1) 1>add extract pup,exttrailsource ./dirdat/ex
GGSCI (host-1) 1>add rmttrail n:\ogg\dirdat\er,ext pup,megabytes 200

GGSCI (host-1) 1>alter pup extseqno 144,extrba 16599070
--修改目标端mgr参数和rer参数路径
--修改rer进程路径
GGSCI (win-1) 1>info rer

GGSCI (win-1) 1>dblogin sourcedb ggs userid ggs password ggs
GGSCI (win-1) 1>delete replicat rer
GGSCI (win-1) 1>add replicat rer,exttrail n:\ogg\dirdat\er,begin now,checkpointtable base.ckp
GGSCI (win-1) 1>alter replicat rer, extseqno 159, extrba 136621541