Goldengate 简单部署测试

操作流程：

Following are descriptions of the steps in the figure:
1.  Start the Extract. Configure and start the Extract to begin capturing database
changes. You need to start the Extract beforethe initial load, so that any SQL
data manipulation language (DML) changes made during the load process can
be captured. The Extract captures the database changes from the source
database-transaction log and writes them to the source trail file. 

开启extract进程.配置并且启动extract进程去捕获数据库的更改。

需要在initial load之前开启extract，这样dml语句在执行加载期间才能被捕获到。

extract从transaction log中捕获更改并且写到source trail 文件中。

2.  Start the data pump. Configure and start the data pump to send extracted
changes captured by the local Extract into the source trail across the network
to the target server. When on the targetserver, the changes are written to the
remote trail file. 

开启pump进程。配置并且开启data pump进程去传输抽取的更改到目标服务器中。

目标服务器上会使用collector进程去写到remote trail文件中。

3.  Perform the initial load. Load the datausing either GoldenGate or vendor
DBMS utilities. Doing so creates a synchronized copy of each database, except
for changes that were captured during the load process by the Extract. These
changes will be applied in the next step when the Replicat is started. You only
need to run an initial data load the first time you setting up replication. After
this, GoldenGate will keep the data synchronized on an ongoing basis using
the Extract and Replicat processes. 

开始initial load初始化加载。使用goldengate或者数据库的工具去加载数据。

这样来创建一个和数据库同步的copy，除了加载处理的时候捕获的那些数据库更改。

这些更改会在下一步在复制进程启动的时候应用到target 数据库上面。

所以只需要运行初始化数据加载一次即可。

这些结束后，goldengate会保证数据持续的一致性。

4.  Start the Replicat. Configure and start the Replicat to begin applying changes
from the remote trail file. Changes thatwere captured while the initial-load
process was executing have already been written to the remote trail file. It may
take a Replicat some time to catch up with the changes, depending on the data
volume and the length of time changes were captured during the load process.
Also, some of these changes may cause data conflicts, because activity was
occurring in the database at the same time the data load was running. Later,
this chapter covers some GoldenGate parameters, such as HANDLECOLLISIONS,
to automatically handle these data conflicts. When the Replicat has caught up
and applied all the changes, the databases are synchronized. 

开启replicat进程。配置兵器开启复制进程去开始应用remote trail中的更改到数据库上。

这些更改会在执行initial load的时候被捕获，并且写到remote trail 文件中。可能会消耗复制进程一些时间，

依赖于数据量和初始化加载中这些数据更改的时间长短。

同样的类似于这样的更改可能会导致数据冲突，因为数据库在初始化加载执行的时间内一直是活动的。

蓝色字体为输出内容

红色字体为注意内容

绿色字体为参数内容

灰色字体为注释内容

每个版本要下载对应的goldengate 软件

10g的需要下载10g的goldengate

11g的需要下载11g的goldengate

a simple example：

10.2.0.5

fbo_ggs_Linux_x64_ora10g_64bit.zip

11.2.0.3

fbo_ggs_Linux_x64_112100_ora11g_64bit.zip

解压文件

都在/u01/gg/目录下

配置环境变量

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

否则在使用ggsci命令的时候会报错，找不到库文件

	ip	节点名称	goldengate master port	goldengate 安装目录
源端	192.168.0.3	dbserver	7890	/u01/gg
目标端	192.168.0.97	gg1	7890	/u01/gg

hosts文件保证都有对方的ip映射条目

添加附加日志

/*****参考内容

primary 数据库开启 最小附加日志

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

支持行链接和其他的存储结构（比如cluster table）

primary 数据库开启表级附加日志

通常类似于oracle、sqlserver、db2不会记录足够的事务日志。所以需要添加额外的表级附加日志，来支持goldengate的复制操作。

GSCI (dbserver) 3> dblogin userid dexter password xiaojun

Successfully logged into database.

GGSCI (dbserver) 4> add trandata dexter.double_balls ;

ERROR: Invalid TRANDATA attribute ;: expected NOKEY or COLS.

GGSCI (dbserver) 5> add trandata dexter.double_balls

Logging of supplemental redo data enabled for table DEXTER.DOUBLE_BALLS.

其在oracle中发出的sql语句如下（会自动查找主键信息）：

ALTER TABLE "DEXTER"."RED" ADD SUPPLEMENTAL LOG GROUP "GGS_RED_59438" ("RNO")

  ALWAYS  /* GOLDENGATE_DDL_REPLICATION */

ALTER TABLE dexter.double_balls

ADD SUPPLEMENTAL LOG GROUP double_balls (id) ALWAYS; 

• If you specify ALWAYS, then during an update, the database includes in the redo all columns in the log group. This is called an unconditional log group(sometimes called an "always log group"), because Oracle Database supplementally logs all the columns in the log group when the associated row is modified. If you omit ALWAYS, then the database supplementally logs all the columns in the log group only if any column in the log group is modified. This is called a conditional log group.

如果指定了always关键字，那么在update的时候，数据库会将所有列的信息包含在日志组中。这叫做无条件日志组。因为oracle数据库会在rows进行更改的时候，

附加所有列的日志到日志组中。如果没有指定always，那么数据库只是附加更改的列的信息到日志组中。这叫做条件日志组 。

生成add trandata脚本

set echo off

set verify off

set pagesize 2000

set linesize 250

set trim on

set heading off

set feedback off

spool &&SCHEMA..add_trandata.obey

select 'add trandata &SCHEMA..'||table_name

from dba_tables where owner = '&SCHEMA' ;

spool off 

ggsci中执行add trandata脚本

GGSCI (sourceserver) 1> dblogin userid gger password userpw

Successfully logged into database.

GGSCI (sourceserver) 2> obey diroby/HR.add_trandata.obey 

查看是否增加表级附加日志成功

select owner, log_group_name, table_name from dba_log_groups where owner = 'DEXTER'; 

使用impdp+dblink导入导出数据，dmp文件不落地

[oracle@srtcreen OGG]$ impdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 13:03:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\"

*/

操作：

需要拥有primary key 主键才可以对表进行复制操作

sys@ORCL>  SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

GSCI (dbserver) 5>  add trandata dexter.double_balls

GSCI (dbserver) 5> add trandata dexter.blue

GSCI (dbserver) 5> add trandata dexter.red

sys@ORCL>  select owner, log_group_name, table_name from dba_log_groups where owner = 'DEXTER';

OWNER                          LOG_GROUP_NAME                 TABLE_NAME
------------------------------ ------------------------------ ------------------------------
DEXTER                         GGS_DOUBLE_BALLS_59441         DOUBLE_BALLS
DEXTER                         GGS_BLUE_59439                 BLUE
DEXTER                         GGS_RED_59438                  RED

禁用触发器以及级联删除约束

/*************** 禁用触发器以及 级联删除约束

The reason is to prevent duplicate changes, because GoldenGate is already replicating the
resultsof the trigger and cascade-delete operations fromthe source database. If you didn’t disable the
constraints and triggers, GoldenGate would replicate those changes; then the triggers and cascadedelete constraints would also fire, causing duplicate changes on the target database.

禁用的原因是方式重复的更改，因为goldegate会复制这些因为trigger或者级联删除所带来的更改。

如果没有disable 这些约束和触发器，goldengate会复制这些更改，那么触发器和级联删除约束同样出发，导致重复的更改到目标数据库上面。

 

禁用trigger

set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off 

进行cascade delete约束