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

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

具體使用者許可權，可參考文件：

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檔案

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

相關文章