Oracle GoldenGate系統之----單向同步資料表

wailon發表於2013-12-03

一、準備工作
1、配置監聽及網路服務
源端

[oracle@dg ~(03:31:05)]$ cd $ORACLE_HOME/network/admin
[oracle@dg admin(03:31:16)]$ more listener.ora 
 
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME = wailon)
          (SID_NAME = wailon)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
         )
   )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@dg admin(03:31:27)]$ more tnsnames.ora
LISTENER1=(ADDRESS=(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522))
wailon=
(DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
   (CONNECT_DATA=(SID=wailon)(GLOBAL_NAME=wailon))
)


 [oracle@dg admin(03:31:38)]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-SEP-2013 03:32:08

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                28-SEP-2013 02:17:08
Uptime                    2 days 1 hr. 15 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary... 
Service "wailon" has 2 instance(s).
  Instance "wailon", status UNKNOWN, has 1 handler(s) for this service...
  Instance "wailon", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@dg admin(03:32:24)]$ tnsping wailon

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 30-SEP-2013 03:32:32

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) (CONNECT_DATA=(SID=wailon)(GLOBAL_NAME=wailon)))
OK (30 msec)

 
目標端  

[oracle@dgrac ~(21:58:15)]$ cd $ORACLE_HOME/network/admin
[oracle@dgrac admin(21:59:01)]$ more listener.ora 
 
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME = ogg)
          (SID_NAME = ogg)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
         )
    )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dgrac)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@dgrac admin(21:59:05)]$ more tnsnames.ora 
  ogg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dgrac)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ogg)
    )
  )
 

[oracle@dgrac admin(22:00:01)]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-NOV-2013 22:00:03

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgrac)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                23-NOV-2013 22:00:01
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dgrac/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgrac)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
  Service "ogg" has 1 instance(s).
  Instance "ogg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@dgrac admin(22:00:03)]$ tnsping ogg

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-NOV-2013 22:00:07

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dgrac)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ogg)))
OK (10 msec)

 2、需要同步的物件
 -- 確保此時源端與目標端需要同步的表,資料一致

04:12:24 SYS@wailon> create user lrj identified by lrj;

User created.

04:12:41 SYS@wailon> alter user lrj quota 10m on users;

User altered.

04:13:20 SYS@wailon> grant dba to lrj;

Grant succeeded.

04:14:51 LRJ@wailon> create table s1 as select * from scott.emp;

Table created.

04:18:55 LRJ@wailon> select * from s1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           900        100         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1700        400         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1350        600         30
      7566 JONES      MANAGER         7839 02-APR-81          3075        100         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1350       1500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2950        100         30
      7782 CLARK      MANAGER         7839 09-JUN-81          2550        100         10
      7788 SCOTT      ANALYST         7566 19-APR-87          3100        100         20
      7839 KING       PRESIDENT            17-NOV-81          5100        100         10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1600        100         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1200        100         20
      7900 JAMES      CLERK           7698 03-DEC-81          1050        100         30
      7902 FORD       ANALYST         7566 03-DEC-81          3100        100         20
      7934 MILLER     CLERK           7782 23-JAN-82          1400        100         10

14 rows selected.

 3、開啟最小附加日誌模式(源端)
  

04:19:51 SYS@wailon> select supplemental_log_data_min from v$database;

SUPPLEME
--------
 NO

04:19:57 SYS@wailon> alter database add supplemental log data;

Database altered.

04:20:12 SYS@wailon> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

 4、開啟表級的補全日誌(源端)
 方法一:

04:20:13 SYS@wailon> alter table lrj.s1 add supplemental log data (primary key) columns;

Table altered.

 方法二:

[oracle@dg ogg(04:24:05)]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (dg) 1> dblogin userid lrj,password lrj
Successfully logged into database.

GGSCI (dg) 8> add trandata lrj.s1

2013-09-30 04:26:16  WARNING OGG-00869  No unique key is defined for table 'S1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo log data is already enabled for table LRJ.S1.


 二、源端配置
 1、配置管理程式 

GGSCI (dg) 11> view param mgr

PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7
USERID lrj@wailon, password lrj

2、配置EXTRACT程式  

GGSCI (dg) 17> view params exts1

extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid lrj@wailon,password lrj
rmthost 192.168.56.131,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
table lrj.s1;

-- 新增基於日誌方式的提取程式exts1 ,立即生效

GGSCI (dg) 18> add extract exts1,tranlog,begin now
EXTRACT added.

-- 建立遠端傳送佇列,將提取程式exts1的日誌傳送到遠端目標伺服器
GGSCI (dg) 19> add rmttrail /u01/app/ogg/dirdat/s1 extract exts1
RMTTRAIL added.

3、啟動相關程式  

GGSCI (dg) 26> start exts1

Sending START request to MANAGER ...
EXTRACT EXTS1 starting


GGSCI (dg) 27> info exts1

EXTRACT    EXTS1     Last Started 2013-09-30 04:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:58 ago)
Log Read Checkpoint  Oracle Redo Logs
                      2013-09-30 04:39:16  Seqno 1, RBA 29157392
                      SCN 0.0 (0) 

三、目標端配置
1、配置管理程式 

GGSCI (dgrac) 3> view param mgr

PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7

 2、配置REPLICAT程式 

GGSCI (dgrac) 8> view params reps1

replicat reps1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid lrj@ogg, password lrj
assumetargetdefs
reperror default,discard
discardfile /u01/app/ogg/dirout/reps1.dsc,append,megabytes 100
map lrj.s1,target lrj.s1; 

-- 增加應用程式reps1,基於檔案檢查點,立即生效

GGSCI (dgrac) 12> add replicat reps1 exttrail /u01/app/ogg/dirdat/s1,begin now,nodbcheckpoint
REPLICAT added. 

 3、啟動相關程式 

GGSCI (dgrac) 14> start reps1

Sending START request to MANAGER ...
REPLICAT REPS1 starting


GGSCI (dgrac) 15> info reps1

REPLICAT   REPS1     Last Started 2013-11-23 23:10   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File /u01/app/ogg/dirdat/s1000000
                     2013-11-23 23:10:47.000000  RBA 1026 

四、資料測試
1、 源端插入資料

[oracle@dg ogg(04:44:16)]$ sqlplus lrj/lrj

04:44:24 LRJ@wailon> insert into s1(empno,ename) values(1000,'wailon');

1 row created.

04:44:44 LRJ@wailon> commit; 
 
 -- 目標端已接收到日誌,產生日誌檔案
[oracle@dgrac ogg(23:11:12)]$ ll dirdat/s1*
-rw-rw-rw- 1 oracle oinstall 1026 Nov 23 23:10 dirdat/s1000000

-- 源端插入的資料已同步到目標端
23:12:43 LRJ@ogg>select * from s1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           900        100         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1700        400         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1350        600         30
      7566 JONES      MANAGER         7839 02-APR-81          3075        100         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1350       1500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2950        100         30
      7782 CLARK      MANAGER         7839 09-JUN-81          2550        100         10
      7788 SCOTT      ANALYST         7566 19-APR-87          3100        100         20
      7839 KING       PRESIDENT            17-NOV-81          5100        100         10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1600        100         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1200        100         20
      7900 JAMES      CLERK           7698 03-DEC-81          1050        100         30
      7902 FORD       ANALYST         7566 03-DEC-81          3100        100         20
      7934 MILLER     CLERK           7782 23-JAN-82          1400        100         10
      1000 wailon

15 rows selected.

五、使用DATAPUMP
主要防止網路中斷,而影響提取程式日誌的傳遞,先把提取的日誌儲存在本地,再通過DATAPUMP傳送到遠端,支援斷點續傳

[oracle@dg ogg(04:46:04)]$ ggsci

GGSCI (dg) 1> stop exts1

Sending STOP request to EXTRACT EXTS1 ...
Request processed.


 -- 修改提取程式exts1如下,取消RMT,生成本地exttrail日誌
GGSCI (dg) 3> view param exts1

extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid lrj@wailon,password lrj
--rmthost 192.168.56.131,mgrport 7801
--rmttrail /u01/app/ogg/dirdat/s1
exttrail /u01/app/ogg/dirdat/l1
table lrj.s1;


-- 建立DATAPUMP程式,使用提取程式產生的日誌
GGSCI (dg) 4> add extract pumps1 ,exttrailsource /u01/app/ogg/dirdat/l1,begin now
EXTRACT added.


GGSCI (dg) 6> view param pumps1

extract pumps1
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid lrj@wailon,password lrj
rmthost 192.168.56.131,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
PASSTHRU
table lrj.s1;

-- 修改原來的提取程式配置遠端佇列位置
GGSCI (dg) 7> delete rmttrail /u01/app/ogg/dirdat/s1 extract exts1
Deleting extract trail /u01/app/ogg/dirdat/s1 for extract EXTS1


GGSCI (dg) 8> add rmttrail /u01/app/ogg/dirdat/s1 extract pumps1
RMTTRAIL added.

-- 此步驟經常會忽略,而導致新的提取程式exts1無法啟動
 GGSCI (dg) 11> add exttrail /u01/app/ogg/dirdat/l1,extract exts1
EXTTRAIL added.


GGSCI (dg) 12> start exts1

Sending START request to MANAGER ...
EXTRACT EXTS1 starting


GGSCI (dg) 13> start pumps1

Sending START request to MANAGER ...
EXTRACT PUMPS1 starting


GGSCI (dg) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXTS1       00:00:00      00:00:08   
EXTRACT     RUNNING     PUMPS1      00:00:00      00:00:00    
 
-- 資料測試
[oracle@dg ogg(04:54:24)]$ sqlplus lrj/lrj

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 30 04:54:39 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

04:54:39 LRJ@wailon> select * from s1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           900        100         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1700        400         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1350        600         30
      7566 JONES      MANAGER         7839 02-APR-81          3075        100         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1350       1500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2950        100         30
      7782 CLARK      MANAGER         7839 09-JUN-81          2550        100         10
      7788 SCOTT      ANALYST         7566 19-APR-87          3100        100         20
      7839 KING       PRESIDENT            17-NOV-81          5100        100         10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1600        100         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1200        100         20
      7900 JAMES      CLERK           7698 03-DEC-81          1050        100         30
      7902 FORD       ANALYST         7566 03-DEC-81          3100        100         20
      7934 MILLER     CLERK           7782 23-JAN-82          1400        100         10
      1000 wailon

15 rows selected.

04:54:43 LRJ@wailon> delete from s1 where empno=1000;

1 row deleted.

04:54:54 LRJ@wailon> update s1 set sal=sal+100;

14 rows updated.

04:55:11 LRJ@wailon> commit;

Commit complete.


目標端
-- 重新啟動reps1應用程式

[oracle@dgrac ogg(23:12:58)]$ ggsci

GGSCI (dgrac) 1> stop reps1

Sending STOP request to REPLICAT REPS1 ...
Request processed.


GGSCI (dgrac) 2> info reps1

REPLICAT   REPS1     Last Started 2013-11-23 23:10   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  File /u01/app/ogg/dirdat/s1000000
                     2013-11-23 23:12:22.583765  RBA 1251


GGSCI (dgrac) 3> start reps1

Sending START request to MANAGER ...
REPLICAT REPS1 starting


GGSCI (dgrac) 4> info reps1

REPLICAT   REPS1     Last Started 2013-11-23 23:21   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  File /u01/app/ogg/dirdat/s1000001
                     2013-11-23 23:22:26.546365  RBA 6310

-- 檢查資料是否同步成功
[oracle@dgrac ogg(23:22:40)]$ sqlplus lrj/lrj

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 23 23:22:46 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

23:22:46 LRJ@ogg>select * from s1;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          1000        100         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1800        400         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1450        600         30
      7566 JONES      MANAGER         7839 02-APR-81          3175        100         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1450       1500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          3050        100         30
      7782 CLARK      MANAGER         7839 09-JUN-81          2650        100         10
      7788 SCOTT      ANALYST         7566 19-APR-87          3200        100         20
      7839 KING       PRESIDENT            17-NOV-81          5200        100         10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1700        100         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1300        100         20
      7900 JAMES      CLERK           7698 03-DEC-81          1150        100         30
      7902 FORD       ANALYST         7566 03-DEC-81          3200        100         20
      7934 MILLER     CLERK           7782 23-JAN-82          1500        100         10

14 rows selected.

至此,一個完整的單向同步資料表實驗完成。可以根據需要修改相關引數,同步不同使用者的不同表。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/429786/viewspace-1062078/,如需轉載,請註明出處,否則將追究法律責任。

相關文章