GoldenGate简单复制环境的搭建（r10笔记第79天）

今天试了下搭建GoldenGate，搭建的过程也简单总结了一下。 目前源数据库是newtest2 目标数据库是dataguru 都是11.2.0.4.0版本 关于GoldenGate的下载可以到下面的链接下载 http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html 在11g中，存在一个和GoldenGate有关的参数，默认是false,我们需要在源库，目标库都开启

 SQL> show parameter golde
 NAME                                 TYPE                   VALUE
 ------------------------------------ ---------------------- ------------------------------
 enable_goldengate_replication        boolean                FALSE
 
 SQL> alter system set enable_goldengate_replication=true;
 alter database

然后查看supplemental log的情况，这个也是需要的设置。 SQL> select SUPPLEMENTAL_LOG_DATA_min from v$database; SUPPLEMENTAL_LOG ---------------- YES

alter database add supplemental log data; 做完之后，切换日志使得附加日志生效。

SQL> alter system archive log current; System altered.当然我们希望在源端和目标端的数据库都配置一个数据库用户。OGG是宣称不需要触发器，中间表，增量标记和时间戳字段的。

源端的配置数据库用户的脚本如下：

create user ogg_source identified by oracle default tablespace users;
 grant alter session to ogg_source;
 grant create session to ogg_source;
 grant connect to ogg_source;
 grant resource to ogg_source;
 grant select any dictionary to ogg_source;
 grant select any table to ogg_source;
 grant flashback any table to ogg_source;
 grant alter any table to ogg_source;

目标端配置数据库用户的脚本如下：

 create user ogg_target identified by oracle default tablespace users;
 grant alter session to ogg_target;
 grant create session to ogg_target;
 grant connect to ogg_target;
 grant resource to ogg_target;
 grant select any dictionary to ogg_target;
 grant select any table to ogg_target;
 grant flashback any table to ogg_target;
 grant alter any table to ogg_target;
 grant insert any table to ogg_target;
 grant update any table to ogg_target;
 grant delete any table to ogg_target;

添加一个表日志：

> EDIT PARAMS ./GLOBALS
GGSCHEMA ogg_source
CHECKPOINTTABLE ogg_source.CHKPTAB

在源端使用数据库用户登录

1> dblogin userid ogg_source,password oracle Successfully logged into database. 为所有要复制的表添加trandata,目前我需要复制的是newtest2这个数据库下的n1用户

2> add trandata n1.*; ERROR: No viable tables matched specification.

这个时候就会明显感觉到OGG的格式不大喜欢结尾符，得去掉结尾符才可以。

3> add trandata n1.* 2016-11-11 14:13:05 WARNING OGG-06439 No unique key is defined for table A. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table N1.A. TRANDATA for scheduling columns has been added on table 'N1.A'. 不过抛出来下面的错误,是权限不够，还需要附一个权限。

2016-11-11 14:13:05 WARNING OGG-00706 Failed to add supplemental log group on table N1.A due to ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_CAPTURE_ADM' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '"N1"."A"', supplemental_logging => 'none'); END; 解决方法：

SQL> grant execute on sys.DBMS_CAPTURE_ADM to ogg_source; Grant succeeded. 再次运行就没有问题了。 然后连接到数据库，开始配置抽取进程

14> dblogin userid ogg_source,password oracle Successfully logged into database.

配置参数，使用本地的方式。

15> edit params ext_1 EXTRACT EXT_1 USERID ogg_source, PASSWORD oracle EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss TABLE N1.*;添加抽取进程，从数据库日志中抓取数据。

> ADD EXTRACT EXT_1, TRANLOG, BEGIN NOW EXTRACT added.

配置本地队列,然后启动

> ADD EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT EXT_1

> start EXT_1 Sending START request to MANAGER ... EXTRACT EXT_1 starting

配置完成之后，就可以查看是否能够正常抽取了，可以看到是没有正常启动。状态现在还是STOPPED

> info EXT_1 EXTRACT EXT_1 Initialized 2016-11-11 16:16 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:01:22 ago) Log Read Checkpoint Oracle Redo Logs 2016-11-11 16:16:04 Seqno 0, RBA 0 SCN 0.0 (0)在11g中没有配置和OGG相关的数据库参数，就会抛出如下的问题了.

2016-11-11 16:17:11 ERROR OGG-02091 Oracle GoldenGate Capture for Oracle, ext_1.prm: Operation not supported because enable_goldengate_replication is not set to true. 2016-11-11 16:17:11 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext_1.prm: PROCESS ABENDING.修复之后，重新启动，这个问题就迎刃而解了,查看这个抽取进程的状态，可以看到已经有了状态数据。 > info ext_1 EXTRACT EXT_1 Last Started 2016-11-11 16:20 Status RUNNING Checkpoint Lag 00:04:49 (updated 00:00:03 ago) Process ID 53455 Log Read Checkpoint Oracle Redo Logs 2016-11-11 16:16:04 Seqno 9416, RBA 8206848 SCN 0.0 (0)然后开始在源端配置PUMP进程，目标数据库的IP是10.127.2.32，PASSTHRU代表是一个传输进程data pump

> edit params dpump_1 EXTRACT dpump_1 PASSTHRU RMTHOST 10.127.2.32, MGRPORT 1530 RMTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss TABLE n1.*;

> ADD EXTRACT dpump_1,EXTTRAILSOURCE /home/oracle/ogg/ogg_work/dirdat/ss EXTRACT added. > ADD RMTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT dpump_1 RMTTRAIL added.

配置完成之后，启动PUMP进程。 > start dpump_1 Sending START request to MANAGER ... EXTRACT DPUMP_1 starting查看DUMP进程的信息如下：

> info dpump_1 EXTRACT DPUMP_1 Last Started 2016-11-11 16:24 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Process ID 53479 Log Read Checkpoint File /home/oracle/ogg/ogg_work/dirdat/ss000000000 First Record RBA 0

对于OGG如果估计最常用的就是info all这个命令了。

> info all
 Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 MANAGER     RUNNING                                           
 EXTRACT     RUNNING     DPUMP_1     00:00:00      00:00:07    
 EXTRACT     RUNNING     EXT_1       00:00:00      00:00:00

源端配置完成，我们来配置目标端。 添加表日志：

> edit param ./GLOBALS GGSCHEMA ogg_target CHECKPOINTTABLE ogg_target.CHKPTAB

登录到数据库端：

> dblogin userid ogg_target,password oracle Successfully logged into database.

目标端配置检查点：

> add checkpointtable ogg_target.CHKPTAB

Successfully created checkpoint table ogg_target.CHKPTAB

配置投递队列参数，将数据应用到目标库，这里有个映射关系，就是源库的n1.*和目标库的n1.*是对应的。

> edit params rep_1 REPLICAT REP_1 USERID ogg_target, PASSWORD oracle ASSUMETARGETDEFS HANDLECOLLISIONS MAP n1.*,TARGET n1.*; 添加投递队列

> ADD REPLICAT REP_1, EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss,CHECKPOINTTABLE ggs_target.CHKPTAB REPLICAT added.

这里自己给自己埋了一个坑，先不说破了。看看下面的错误。

启动投递应用进程，但是发现却有报错。

> start REP_1 Sending START request to MANAGER ... REPLICAT REP_1 starting

错误提示是比较奇怪的，怎么用户名密码错误了。

Delivery for Oracle, rep_1.prm: OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied),还有SYS.DBMS_STREAMS的访问权限不足

下面这个地方错误花了些时间来分析，最后才发现是自己把用户名写错了。

先删除，再添加

>delete replicat rep_1 添加的语句如下ogg_target.CHKPTAB是主要的地方，自己给写错为ggs_target了。

> ADD REPLICAT REP_1, EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss,CHECKPOINTTABLE ogg_target.CHKPTAB REPLICAT added.

再次启动就没有问题了。

> start REP_1 Sending START request to MANAGER ... REPLICAT REP_1 starting

启动成功后的状态是这样的。 > INFO REP_1 REPLICAT REP_1 Last Started 2016-11-11 17:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Process ID 69571 Log Read Checkpoint File /home/oracle/ogg/ogg_work/dirdat/ss000000000 First Record RBA 0不过这个过程中，目标库还没有初始数据所以还无法同步，我们应该在目标库先初始基础数据，通过exp/imp,expdp/impdp都可以。 同步之后来简单来一个小的测试。

在源端删除表test_lock的数据

SQL> delete from test_lock; 99 rows deleted. SQL> commit; Commit complete.

然后在源端查看，可以看到抽取进程的SCN开始变化

2016-11-11 17:15:04 Seqno 9417, RBA 729600 SCN 0.17848618 (17848618) 稍等一会就变为 2016-11-11 17:17:25 Seqno 9417, RBA 841728 SCN 0.17848776 (17848776) 然后查看目标端,开始是有数据的

SQL> select count(*)from test_lock;
   COUNT(*)
 ----------
         99

稍作等待，就可以看到数据被删除了。

 SQL> /
   COUNT(*)
 ----------
          0