首页 > 代码库 > [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
View Code

 

[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
View Code

 

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.

 

至此就完成了。