Oracle GoldenGate系統之----單向同步資料表
一、準備工作
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle GoldenGate系統之----單向同步資料表(續)OracleGo
- Oracle GoldenGate系統之----雙向同步資料表OracleGo
- 使用GoldenGate 實現Oracle for Oracle 單向資料同步（實現表的DML操作同步）GoOracle
- GoldenGate單向表DML同步Go
- GoldenGate實現oracle和sqlserver雙向資料同步GoOracleSQLServer
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- Oracle GoldenGate系統之----資料初始化OracleGo
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- Goldengate單表新增同步Go
- Oracle Dataguard + Goldengate資料同步OracleGo
- Linux下GoldenGate單機單向同步LinuxGo
- oracle資料庫配置goldengate同步Oracle資料庫Go
- Linux下GoldenGate單機雙向同步LinuxGo
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 上OracleGo
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 下OracleGo
- 使用goldengate的資料泵進行Oracle-Oracle的單向複製GoOracle
- GoldenGate雙向同步配置Go
- goldengate對oracle臨時表的同步GoOracle
- 【GoldenGate】Oracle GoldenGate(四) 壓力測試同步百萬行資料GoOracle
- Oracle GoldenGate資料同步備份軟體概述OracleGo
- goldengate同步中更改資料表結構維護Go
- 內外網資料的簡單單向同步
- Oracle GoldenGate系統之----安裝及使用OracleGo
- goldengate 單向DDLGo
- 通過goldengate從ORACLE向mysql的單向複製GoOracleMySql
- GoldenGate配置（一）之單向複製配置Go
- 使用goldengate從mysql同步資料到oracleGoMySqlOracle
- 安裝配置Oracle GoldenGate for DB2(單向)OracleGoDB2
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- 【GoldenGate】Oracle GoldenGate(三) DDL同步配置GoOracle
- Mysql資料庫單向同步（一主兩從）MySql資料庫
- 利用Oracle GoldenGate記錄源系統所有表的操作OracleGo
- goldengate不使用資料泵完成Oracle-Oracle的雙向複製GoOracle
- Oracle GoldenGate系統之----相關引數說明OracleGo
- Oracle GoldenGate 資料同步初始化最佳實戰（Data Pump）OracleGo
- Oracle 11g GoldenGate單向複製配置（DML）OracleGo
- 實戰goldengate之ora-To-ora單向複製Go
- ogg12 mysql to oracle 單向同步MySqlOracle