参考文档： https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/ic_deploy.htm#GIORA473 https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/ic_deploy2.htm#GIORA487
OGG Integrated模式分为: 1.Local deployment:The source database and the mining database are the same 2.Downstream deployment:The source and mining databases are different databases. You create the logmining server at the downstream database.
此次是测试Downstream deployment 1.环境 ogg：12.2 source oracle：SID:DEVPRMY DB_UNIQUE_NAME:DEVPRMY 归档模式(必须) downstream oracle：SID:BDTEST DB_UNIQUE_NAME:BDTEST 归档模式(必须) target oracle：SID:BDTEST
环境要求：将上面的信息重新规整
2.source库配置tnsnames和archive参数，以传日志到downstream server BDTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.217.137)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BDTEST) ) SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=BDTEST ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=BDTEST' scope=both; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
3.将source库的口令文件传到downstream server scp orapwDEVPRMY oracle@172.31.217.137:/opt/app/oracle/product/11g/dbs/orapwBDTEST 4.如果要在Real-time Mode使用OGG，要在downstream库中添加standby redo log： 4.1.standby redo log size >= source log file size 4.2.The number of standby log file groups >= The number of source online log file groups+1 So if you have "n" threads at the source database, each having "m" redo log groups, you should configure n*(m+1) redo log groups at the downstream mining database.
检查source库上的日志： SQL> SELECT BYTES,BYTES/1024/1024 MB FROM GV$LOG; BYTES MB ---------- ---------- 52428800 50 52428800 50 52428800 50
SQL> SELECT COUNT(GROUP#) FROM GV$LOG; COUNT(GROUP#) ------------- 3
在downstream库上添加standby redo log： ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/app/oracle/oradata/BDTEST/standby_redo01') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/opt/app/oracle/oradata/BDTEST/standby_redo02') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/opt/app/oracle/oradata/BDTEST/standby_redo03') SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/opt/app/oracle/oradata/BDTEST/standby_redo04') SIZE 50M; 查看standby redo log：SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG; 5.downstream库中配置standby redo log自动归档： ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/opt/app/oracle/archivelog VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)' scope=both; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 6.在source库和downstream库中配置log_archive_config参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DEVPRMY,BDTEST)' scope=both; 7.创建OGG用户 7.1.source库上创建ogg用户(will be used to fetch data and metadata from DBMS1)： create user ogg identified by Ogg$1; exec dbms_goldengate_auth.grant_admin_privilege('OGG'); 7.2.downstream库中创建ogg用户(will be used to retrieve logical change records from the logmining server at the downstream mining database): create user ogg identified by Ogg$1; grant dba to ogg;(因为downstream也是OGG目标库，所以需要写入表的权限) exec dbms_goldengate_auth.grant_admin_privilege('OGG'); (CREATE RULE、CREATE RULE SET、SELECT ANY TABLE、ALTER ANY TABLE、SELECT ANY TRANSACTION、CREATE JOB、EXECUTE ANY RULE SET、CREATE EVALUATION CONTEXT、ALTER SESSION、DEQUEUE ANY QUEUE、FLASHBACK ANY TABLE、SELECT_CATALOG_ROLE等权限) 8.downstream库中向source库的表添加trandata 8.1.添加source的tns DEVPRMY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.217.131)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DEVPRMY) ) ) 8.2.>DBLOGIN USERIDALIAS DEVPRMY >ADD TRANDATA SCOTT.T1 9.downstream库添加extract、replicat 9.1.downstream库修改参数：alter system set enable_goldengate_replication=true scope=both; 9.2.downstram库的ogg上添加extract: >DBLOGIN USERIDALIAS DEVPRMY >MININGDBLOGIN USERIDALIAS BDTEST >REGISTER EXTRACT ext1 DATABASE >ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW >add exttrail ./dirdat/my,extract ext1,megabytes 100 其中，ext1为 EXTRACT ext1 USERIDALIAS DEVPRMY TRANLOGOPTIONS MININGUSERALIAS BDTEST TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y) EXTTRAIL ./dirdat/my TABLE SCOTT.T1; --目标端ogg配置 Add the Replicat process group connected to the target PDB zwc5 GGSCI (test12c.localdomain) 1> dblogin userid c##ggadmin@zwc5, password ggadmin Successfully logged into database ZWC5. GGSCI (test12c.localdomain) 3> add replicat rep1 integrated exttrail ./dirdat/rt REPLICAT (Integrated) added. GGSCI (test12c.localdomain) 5> view params rep1 REPLICAT rep1 --SETENV (ORACLE_SID='zhongwc') DBOPTIONS INTEGRATEDPARAMS(parallelism 6) USERID C##GGADMIN@zwc5, PASSWORD ggadmin ASSUMETARGETDEFS --SOURCECATALOG zwc5 MAP ZHONGWC1.GGTEST.*, TARGET ZWC5.GGTEST.*; 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文件
转载于 https://www.cnblogs.com/zhugablog/p/8488081.html