OGG Integrated Mode(downstream方式)环境搭建

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文件