Oracle DG+sqlserver alwayson主节点切换时OGG源端目标端容灾实践
Demonson 于 2020-08-28 19:16:52 发布 431 收藏 2
分类专栏： OGG 文章标签： ogg oracle DG SQLserver
版权
OGG
专栏收录该内容
5 篇文章3 订阅
订阅专栏
背景：
源端数据库是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
————————————————
版权声明：本文为CSDN博主「Demonson」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/demonson/article/details/108284429