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

猪猪小飞侠

 

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

参考文档：

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：

1.standby redo log size >= source log file size

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用户

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');

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

1.添加source的tns

DEVPRMY =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = DEVPRMY)

)

)

2.>DBLOGIN USERIDALIAS DEVPRMY

>ADD TRANDATA SCOTT.T1

 

9.downstream库添加extract、replicat

1.downstream库修改参数：alter system set enable_goldengate_replication=true scope=both;

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