OGG整合模式分为两种部署方式：

     local deployment ：源数据库与挖掘数据库是在同一个库中，即OGG进程与源数据库运行在同一台服务器上

     downstream deployment ：源数据库与挖掘数据库是不同数据库，分为source database和downstream database。downstream database接收source database的redo log(只接收，不应用)，OGG进程运行在downstream database所在的服务器上，通过downstream database挖掘源库的redo log。

    downstream模式的部署方式可减轻源数据库的压力，尤其是IO资源紧张时，可将很大部分的压力转移到downstream服务器上。本文讲述downstream方式部署。有以下须注意的点：

         1）downstream 库可以同时接收archived log和online redo logs。

        2）多个source库可同时传redo log到一台downstream库，但一台downstream库只能接收一个source库的online redo logs

        3）如果要在Real-time Mode使用OGG，要在downstream库中添加standby redo log

        4）source库和downstream库不能跨平台使用，即如果source库运行在Linux 64-bit 平台上，那么downstream库也要运行在Linux 64-bit平台上。

环境信息 ：

 ogg：12.2

 source oracle：SID:BDDEV1 DB_UNIQUE_NAME:BDDEV1 ARCHIVE LOG MODE

 downstream oracle：SID:BDTEST DB_UNIQUE_NAME:BDTEST ARCHIVE LOG MODE

 target oracle：SID:BDDEV2

1.上传ogg安装包、解压、安装到/opt/app/OGG12_2目录

2.source库修改force_logging，并打开最小补充日志

     SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

     SQL> ALTER DATABASE FORCE LOGGING;

    SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

3.修改source库与downstream库，允许OGG复制

     SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true;

4.将source库的口令文件传到downstream server,并重命名为orapwBDTSET

     scp orapwBDDEV1 oracle@172.21.74.222:/opt/app/oracle/product/11g/dbs/orapwBDTEST

5.如果要在Real-time Mode使用OGG，要在downstream库中添加standby redo log：

     检查source库上的日志：

     SQL> SELECT BYTES,BYTES/1024/1024 MB FROM GV$LOG;

        BYTES         MB

     ---------- ----------

     1073741824       1024

     1073741824       1024

     1073741824       1024

     在downstream库上添加standby redo log：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/app/oracle/oradata/BDTEST/standby_redo04.log') SIZE 1024M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/opt/app/oracle/oradata/BDTEST/standby_redo05.log') SIZE 1024M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/opt/app/oracle/oradata/BDTEST/standby_redo06.log') SIZE 1024M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/opt/app/oracle/oradata/BDTEST/standby_redo07.log') SIZE 1024M;

     查看standby redo log：

        SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;

6.source库配置tnsnames和archive参数，以传日志到downstream server

     BDTEST =

       (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.222)(PORT = 1521))

            (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = BDTEST)

        )

    )

     SQL> alter system set log_archive_config = 'dg_config=(BDDEV1,BDTEST)';

     SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=BDTEST ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=BDTEST' scope=both;

7.downstream库中配置standby redo log自动归档,配置source库与target库的tns

     SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/opt/app/oracle/standby_archivelog VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)' scope=both;

     SQL> alter system set log_archive_config = 'dg_config=(BDDEV1,BDTEST)';

    BDDEV2 =

    (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.223)(PORT = 1521))

        (CONNECT_DATA =

           (SERVER = DEDICATED)

           (SERVICE_NAME = BDDEV2)

        )

   )

 BDDEV1 =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.21.74.223)(PORT = 1521))

     (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = BDDEV1)

     )

   )

8.创建OGG用户

     1)source库上创建ogg用户(will be used to fetch data and metadata from DBMS1)：

         SQL> create user ogg identified by Ogg$1;

         SQL> grant connect,resource,alter system,select any dictionary to ogg;

         SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGG');

     2)downstream库中创建ogg用户( Extract uses the credentials of this user to do metadata queries and to fetch column values as needed from the source database):

         SQL> create user ogg identified by Ogg$1;

         SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGG');

     3)target库上创建ogg用户

         SQL> create user ogg identified by Ogg$1;

         SQL> grant connect,resource,CREATE TABLE,LOCK ANY TABLE to ogg;

         SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGG');

     具体用户权限，可参考文档：https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/user_assignment.htm#GIORA553

9.源端和目标端创建测试表

     SQL> create table scott.tb_test(id int primary key,age int,name varchar2(20));

    Table created.

10.downstream服务器上配置OGG

     1）创建ogg目录：

    ./ggsci

     >create subdirs

     2）创建用户钱包：

     >add credentialstore

    >alter credentialstore add user ogg@BDDEV1 alias BDDEV1 --source库

     >alter credentialstore add user ogg@BDTEST alias BDTEST --downstream库

     >alter credentialstore add user ogg@BDDEV2 alias BDDEV2 --target库

     3）配置manager进程：

         >edit param mgr

         PORT 3321

dynamicportlist 9901-9930

autorestart er *,retries 4,waitminutes 4

startupvalidationdelay 5

purgeoldextracts /opt/app/OGG12_2/dirdat/*,usecheckpoints,minkeephours 96

>start mgr

     4)添加表级补充日志：

>dblogin useridalias BDDEV1

>add trandata scott.tb_test

>info trandata scott.*

    5)添加抽取进程

>DBLOGIN USERIDALIAS BDDEV1

>MININGDBLOGIN USERIDALIAS BDTEST

>REGISTER EXTRACT ext1 DATABASE

>ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW

>add exttrail ./dirdat/me,extract ext1,megabytes 100

其中，ext1为

EXTRACT ext1

USERIDALIAS BDDEV1

TRANLOGOPTIONS MININGUSERALIAS BDTEST

TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)

EXTTRAIL ./dirdat/me

TABLE SCOTT.TB_TEST;

>start ext1

     6)添加复制进程

>DBLOGIN USERIDALIAS BDDEV2

>ADD CHECKPOINTTABLE ogg.chkpoint_table

>ADD REPLICAT rep1, EXTTRAIL ./dirdat/me,CHECKPOINTTABLE ogg.chkpoint_table

REPLICAT REP1

USERIDALIAS BDDEV2

DBOPTIONS REPARSELOBSQL

        HANDLECOLLISIONS

        DISCARDFILE /opt/app/OGG12_2/dirrpt/rep1.dsc,append,megabytes 100

        MAP SCOTT.TB_TEST, TARGET SCOTT.TB_TEST;

        >start rep1

    7)源端改变scott.tb_test，查看进程情况

        SQL> insert into scott.tb_test values(1,10,'a');

         1 row created.

         SQL> commit;

         > stats ext1,daily

         Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2018-10-31 10:12:33.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***

        Operations                                         0.00

Output to ./dirdat/me:

Extracting from SCOTT.TB_TEST to SCOTT.TB_TEST:

*** Daily statistics since 2018-10-31 10:00:10 ***

        Total inserts                                      1.00

        Total updates                                      0.00

        Total deletes                                      0.00

        Total discards                                     0.00

        Total operations                                   1.00

End of Statistics.

> stats rep1,daily

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2018-10-31 10:12:44.

Replicating from SCOTT.TB_TEST to SCOTT.TB_TEST:

*** Daily statistics since 2018-10-31 10:10:45 ***

        Total inserts                                      1.00

        Total updates                                      0.00

        Total deletes                                      0.00

        Total discards                                     0.00

        Total operations                                   1.00

End of Statistics.

可看到，抽取与复制进程运行正常

注意：OGG版本12.3.0.1时遇到了ERROR   OGG-00662  OCI Error OCI-22053: overflow error错误；换为12.2.0.1.1版本后，提示需要打patch， ERROR OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later

此时有两种选择：

 1.打patch

 2.在downstream库上执行OGG_HOME下的 prvtlmpg.plb文件