首页 > 代码库 > [terry笔记]ogg_迁移同步实验_零停机
[terry笔记]ogg_迁移同步实验_零停机
oracle golden gate我最近正在琢磨，这个软件我觉得约等于dataguard的逻辑模式，我认为其最大的优势是更可控制，比如可以细化到某个schema、某个table的同步。
如下实验的主要步骤：
ogg根据scn同步数据，源库零停机时间
1. 配置好ogg源端的mgr、抓取和传送进程，并启动。
2. 配置好ogg目标端的mgr、复制进程，仅启动mgr。
3. 源端可自由进行交易，此时观察源与目标的trail文件是否都正常。
4. 查询源端此时的scn，并按照参数flashbask_scn进行expdp。
5. 目标端impdp导入。
6. 目标端start replicat xxx,aftercsn xxxxx
GoldenGate重要进程介绍：
1、Manager管理进程在两端开启，监控和重启其他进程；分配数据存储和报告错误及事件；
2、Extract进程从日志中抓取并传输到target端事务数据；
3、Server Collector进程在target(接受)端接受数据并写入trail文件；
4、Replicat进程读取trail文件，并应用到traget数据库；
5、trail文件时gg自己抓捕信息的文件，是一个OS文件，存放在./dirdat/下，以X00000命名，N顺序1，2,3…此文件用完可配置参数自动删除。
一、环境准备并安装GoldenGate
1. 数据库准备情况（我是在一个主机上面两个实例）
1) 源服务器
IP地址：192.168.82.178
数据库：11.2.0.4.0 64 bit
SID: terry
操作系统版本：Oracle 6.5 64 bit
ogg版本：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
2) 目标服务器
IP地址：192.168.82.178
数据库：10.2.0.5 64 bit
SID: ora10g
操作系统版本：Oracle 6.4 64 bit
ogg版本：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
说明：前提条件是数据库已经准备方式安装完毕。
2. 下载OGG软件并解压安装
1) 源服务器
系统用户ggsmb
/home/ggsmb/ggsmb_home
2) 目标服务器都要配置
系统用户ggs
/home/ggs/ggs_home
3. 准备OGG环境变量
#源服务器、与目标服务器都要配置
$ vi /home/ggs/.bash_profile
$ vi /home/ggsmb/.bash_profile
ggsmb增加如下行：
export PATHexport ORACLE_BASE=/u01export ORACLE_HOME=$ORACLE_BASE/product/11.2.0export ORACLE_SID=terryexport PATH=$PATH:$HOME/bin:$ORACLE_HOME/binexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/home/ggsmb/ggsmb_home
ggs增加如下行：
export PATHexport ORACLE_BASE=/u02export ORACLE_HOME=$ORACLE_BASE/product/10.2.0export ORACLE_SID=ora10gexport PATH=$PATH:$HOME/bin:$ORACLE_HOME/binexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/home/ggs/ggs_home:/usr/local/lib:/usr/X11R6/libexport LD_LIBRARY_PATH_64=$ORACLE_HOME/lib
4. 配置日志模式
#目标服务器不用配置(非双向)
1) 查看规档与日志模式
sqlplus / as sysdbaselect log_mode,supplemental_log_data_min,force_logging from v$database;
2) 配置为规档模式
shutdown immediate;startup mount;alter database archivelog;alter database open;alter system set log_archive_dest_1=‘location=/u01/archive‘ scope=both;
3) 配置日志模式(打开强制规档与补充日志模式)
alter database add supplemental log data; alter database force logging;
4）查看配置结果：
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;LOG_MODE SUPPLEME FOR------------ -------- ---ARCHIVELOG YES YES
5. 创建GoldenGate用户帐号
1) 源服务器
su - oracle
sqlplus /nolog
conn / as sysdba;
create tablespace ogg datafile ‘/u01/oradata/terry/ogg.dbf‘ size 10m autoextend on next 10m;
create user ggs identified by ggs default tablespace ogg temporary tablespace TEMP quota unlimited on ogg;
grant connect,resource to ggs;
grant create session,alter session to ggs;grant select any dictionary,select any table to ggs;grant alter any table to ggs;grant flashback any table to ggs;grant execute on dbms_flashback to ggs;--或直接grant dba to ggs;
2) 目标服务器
su - oracle
sqlplus /nolog
conn / as sysdba;
create tablespace ogg datafile ‘/u02/oradata/ora10g/ogg.dbf‘ size 10m autoextend on next 10m;create user ggs identified by ggs default tablespace ogg temporary tablespace TEMP quota unlimited on ogg;grant connect,resource to ggs;grant create session,alter session to ggs;grant select any dictionary,select any table to ggs;grant alter any table to ggs;grant flashback any table to ggs;grant execute on dbms_flashback to ggs;grant insert any table to ggs;grant delete any table to ggs;grant update any table to ggs;--或直接grant dba to ggs;
6. 安装GoldenGate软件
(1) 源服务器、与目标服务器都要配置
su - ggsmb
cd /home/ggsmb/ggsmb_home
./ggsci
GGSCI (terry) 1> create subdirs
Creating subdirectories under current directory /home/ggsmb/ggsmb_home
Parameter files /home/ggsmb/ggsmb_home/dirprm: created
Report files /home/ggsmb/ggsmb_home/dirrpt: created
Checkpoint files /home/ggsmb/ggsmb_home/dirchk: created
Process status files /home/ggsmb/ggsmb_home/dirpcs: created
SQL script files /home/ggsmb/ggsmb_home/dirsql: created
Database definitions files /home/ggsmb/ggsmb_home/dirdef: created
Extract data files /home/ggsmb/ggsmb_home/dirdat: created
Temporary files /home/ggsmb/ggsmb_home/dirtmp: created
Veridata files /home/ggsmb/ggsmb_home/dirver: created
GGSCI (terry) 2> quit
(2) 目标服务器都要配置
su - ggs
cd /home/ggs/ggs_home
./ggsci
GGSCI (terry) 1> create subdirs
Creating subdirectories under current directory /home/ggs/ggs_home
Parameter files /home/ggs/ggs_home/dirprm: created
Report files /home/ggs/ggs_home/dirrpt: created
Checkpoint files /home/ggs/ggs_home/dirchk: created
Process status files /home/ggs/ggs_home/dirpcs: created
SQL script files /home/ggs/ggs_home/dirsql: created
Database definitions files /home/ggs/ggs_home/dirdef: created
Extract data files /home/ggs/ggs_home/dirdat: created
Temporary files /home/ggs/ggs_home/dirtmp: created
Veridata files /home/ggs/ggs_home/dirver: created
GGSCI (terry) 2> quit
二、 GoldenGate DML同步源端配置
cd /u01/app/oracle/ogg
./ggsci
1. 源MGR进程
1) 编辑mgr程组
edit params mgr
port 7809dynamicportlist 7800-7899autorestart extract *,retries 5,waitminutes 2purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 7lagreporthours 1laginfominutes 30lagcriticalminutes 45
说明:
port 指定mgr进程通信端口
dynamicportlist 表示mgr进程可以为源与目的端动态通信指定端口
autorestart extract 表示自动重启extract进程组，每2分钟尝试重启所有进程，重试5次。
配置参数后，重启mgr进程生效
purgeoldextracts xxx，usercheckpoints,minkeepdays x表示trail文件会保留7天，ogg会根据checkpoint删除使用过的trail文件。
lagreporthours 1 每隔1小时检查一次extract和replicat的lag。
laginfominutes 30 如果lag超过规定的值（lagcritical），会报错critical。如果没问题，会每30分钟报告信息。
lagcriticalminutes 45 会每隔45分钟报告critical信息。
2) 启动主管理进程
GGSCI (terry) 3> start mgr
Manager started.
GGSCI (terry) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
2. 配置Extract进程组
GGSCI (terry) 5> edit params test_ext
GGSCI (terry) 9> view param test_ext
extract test_extdynamicresolutionsetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)userid ggs,password ggsexttrail /home/ggsmb/ggsmb_home/dirdat/tetable test.*;
2) 添加抽取进程
GGSCI (terry) 6> add extract test_ext,tranlog,begin now
EXTRACT added.
3) 添加本地trail文件,源extract进程负责写这部分文件，pump负责把这部分文件传到目标服务器端。
GGSCI (terry) 7> add exttrail /home/ggsmb/ggsmb_home/dirdat/te,extract test_ext
EXTTRAIL added.
GGSCI (terry) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED TEST_EXT 00:00:00 00:01:13
说明:
extract eora定义extract进程名字
dynamicresolution
setenv设置环境变量
userid 登录数据库
exttrail指定本地trail文件地址
table 定义同步的表
4) 启动服务
GGSCI (terry) 19> start extract test_ext
Sending START request to MANAGER ...
EXTRACT TEST_EXT starting
GGSCI (terry) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING TEST_EXT 00:04:39 00:00:00
此时观察dirdat目录，可以发现抽取的文件：
[ggsmb@terry dirdat]$ pwd
/home/ggsmb/ggsmb_home/dirdat
[ggsmb@terry dirdat]$ ll
总用量 4
-rw-rw-rw- 1 ggsmb oinstall 1053 6月 17 11:27 te0000
3. 配置Pump进程组
1) 编辑配置文件
edit params test_p
extract test_pdynamicresolutionpassthrurmthost 192.168.82.178,mgrport 7909,compressrmttrail /home/ggs/ggs_home/dirdat/tptable test.*;
2) 添加pump进程
add extract test_p,exttrailsource /hoem/ggsmb/ggsmb_home/dirdat/te
3) 添加远程trail文件
GGSCI (terry) 27> add rmttrail /home/ggs/ggs_home/dirdat/tp,extract test_p
RMTTRAIL added.
4) 启动pump进程
GGSCI (terry) 29> start extract test_p
Sending START request to MANAGER ...
EXTRACT TEST_P starting
GGSCI (terry) 30> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING TEST_EXT 00:00:00 00:00:03
EXTRACT RUNNING TEST_P 00:00:00 00:03:04
三、 GoldenGate DML同步目标端配置
1. 目标端MGR进程
1) 编辑配置文件
GGSCI (terry) 1> view param mgr
port 7909dynamicportlist 7900-7999purgeoldextracts /home/ggs/ggs_home/dirdat/*, usecheckpoints, minkeepdays 3
2) 启动
GGSCI (terry) 2> start mgr
Manager started.
GGSCI (terry) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPL 00:00:00 20:42:08
GGSCI (terry) 4> exit
此时在目标端的dirdat中可以看到由源端传送过来的trail文件
[ggs@terry ggs_home]$ cd dirdat
[ggs@terry dirdat]$ ll
总用量 0
-rw-rw-rw- 1 ggs oinstall 0 6月 17 13:18 tp000000
2. 添加检查表
说明: 当我们在GLOBALS 文件里指定了默认的checkpoint 之后，新的Replicat groups 在创建时会自动使用这个参数，不需要其他指令
1) 编辑全局配置文件
edit params ./GLOBALS
CHECKPOINTTABLE ggs.checkpoint
2）exit # 这里需要退出ggsci终端
3) 添加checkpoint表
./ggsci
GGSCI (slave) 1> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (slave) 2> add checkpointtable ggs.checkpoint
Successfully created checkpoint table GGS.CHECKPOINT.
3. 配置目标端Peplicat进程组
1) 编辑配置文件
edit params test_r
replicat test_ruserid ggs,password ggsassumetargetdefsreperror default,discarddiscardfile /home/ggs/ggs_home/dirrpt/test_r.descdynamicresolutionmap test.*;target test.*;
2) 添加复制进程，此时不要启动进程
GGSCI (terry) 6> add replicat test_r,exttrail /home/ggs/ggs_home/dirdat/tp,checkpointtable ggs.checkpoint
REPLICAT added.
GGSCI (terry) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED TEST_R 00:00:00 00:00:03
四、查询源scn，将源数据导入目标库
1.源库按照scn导入到目标库
SQL> select * from t1;
ID
----------
1
2
3
4
5
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1136851
[oracle@terry dump]$ expdp system/oracle directory=dump dumpfile=test.dmp schemas=test flashback_scn=1136851 version=10.2
Export: Release 11.2.0.4.0 - Production on Tue Jun 17 13:30:09 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump dumpfile=test.dmp schemas=test flashback_scn=1136851 version=10.2 Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS. . exported "TEST"."T1" 4.968 KB 5 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /dump/test.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jun 17 13:30:20 2014 elapsed 0 00:00:10
[oracle@terry dump]$ exit
logout
[root@terry 桌面]# su - ora10g
[ora10g@terry ~]$ cd /dump
[ora10g@terry dump]$ impdp system/oracle directory=dump dumpfile=test.dmp
Import: Release 10.2.0.5.0 - 64bit Production on Tuesday, 17 June, 2014 13:30:57Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dump dumpfile=test.dmp Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "TEST"."T1" 4.968 KB 5 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSJob "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 13:31:00
2.验证目标库数据
SQL> conn test/test
Connected.
SQL> select * from t1;
ID
----------
1
2
3
4
5
3.源库做出交易
SQL> insert into t1 values(6);
1 row created.
SQL> insert into t1 values(7);
1 row created.
SQL> commit;
Commit complete.
此时原库的trail文件te和目标库的trail文件tp应当都出现了赠长：
[ggsmb@terry dirdat]$ ll
总用量 4
-rw-rw-rw- 1 ggsmb oinstall 1383 6月 17 13:35 te000000
[ggs@terry dirdat]$ ll
总用量 4
-rw-rw-rw- 1 ggs oinstall 1303 6月 17 13:31 tp000000
3.启动replicat（aftercsn）
GGSCI (terry) 2> start replicat test_r,aftercsn 1136851
Sending START request to MANAGER ...
REPLICAT TEST_R starting
GGSCI (terry) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TEST_R 00:00:00 00:00:01
5.验证目标库数据
SQL> /
ID
----------
1
2
3
4
5
6
7
7 rows selected.
至此就完成了。