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

柔情只为你懂 2022-12-02 00:54 185阅读 0赞

# 背景： #

> 源端数据库是Oracle DG架构，其中Oracle的多张表需要OGG实时同步到SQL server数据库上，SQL server为AlwaysOn架构，
> 
> 需求为不管源端Oracle还是目标端SQL server发生主机切换时，ogg需要随时可以容灾切换，保证ogg同步正常工作

## Oracle DG ##

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>Oracle角色</th> 
   <th>主机名</th> 
   <th>IP</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主库</td> 
   <td>host-1</td> 
   <td>192.168.1.11</td> 
  </tr> 
  <tr> 
   <td>从库</td> 
   <td>&nbsp;host-2</td> 
   <td>192.168.1.12</td> 
  </tr> 
 </tbody> 
</table>

## SQL server AlwaysOn ##

<table style="width:500px;"> 
 <thead> 
  <tr> 
   <th>SQL server角色</th> 
   <th style="width:94px;">主机名</th> 
   <th style="width:190px;">IP</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主库</td> 
   <td style="width:94px;">win-1</td> 
   <td style="width:190px;">192.168.1.21</td> 
  </tr> 
  <tr> 
   <td>备库</td> 
   <td style="width:94px;">win-2</td> 
   <td style="width:190px;">192.168.1.22</td> 
  </tr> 
  <tr> 
   <td>备库</td> 
   <td style="width:94px;">win-3</td> 
   <td style="width:190px;">192.168.1.23</td> 
  </tr> 
 </tbody> 
</table>

## 拓扑图 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70][]

> 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"/>  
>       
>       
> </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"/>  
>     <timeout start="false" time="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"/>  
>       
>       
> </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"/>  
>     <timeout start="false" time="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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlbW9uc29u_size_16_color_FFFFFF_t_70]: /images/20221123/58ac98bba5b343c185a365a95ed52f6a.png