Oracle GoldenGate系统之----单向同步数据表

最新推荐文章于 2024-09-05 10:00:22 发布

cuiwu7783

最新推荐文章于 2024-09-05 10:00:22 发布

阅读量446

点赞数

文章标签：数据库操作系统网络

一、准备工作
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

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

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

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

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

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;

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

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;

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/429786/viewspace-1062078/

cuiwu7783

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle GoldenGate系统之----单向同步数据表

一、准备工作1、配置监听及网络服务源端 [oracle@dg ~(03:31:05)]$ cd $ORACLE_HOME/network/admin[oracle@dg admin(03:31:1...
复制链接

扫一扫