OGG整合模式抽取(downstream模式)

 

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