1.不使用数据泵完成Oracle-Oracle的双向复制。
数据库配置:
ogg1和ogg2都启用归档和supplemental log:
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL>
ogg1上建立ogg和sender用户, sender下建立t1表:
SQL> create user ogg identified by oracle;
User created.
SQL> create user sender identified by oracle;
User created.
SQL> grant dba to ogg,sender;
Grant succeeded.
SQL> conn sender/oracle
Connected.
SQL> create table t1(id int);
Table created.
SQL>
ogg2上建立ogg和receiver用户, receiver下建立t1表:
SQL> create user ogg identified by oracle;User created.
SQL> create user receiver identified by oracle;
User created.
SQL> grant dba to ogg,receiver;
Grant succeeded.
SQL> conn receiver/oracle
Connected.
SQL> create table t1(id int);
Table created.
SQL>
OGG配置(oggdb1 -> oggdb2):
ogg1配置
GGSCI (ogg1) 1> add extract ext1,tranlog,begin now
EXTRACT added.
GGSCI (ogg1) 3> add exttrail /opt/ogg/dirdat/e1,extract ext1
EXTTRAIL added.
GGSCI (ogg1) 15> view params ext1
extract ext1
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb1,password oracle
rmthost ogg2,mgrport 7809
rmttrail /opt/ogg/dirdat/e1
table sender.t1;
GGSCI (ogg1) 2> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (ogg1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:06:31
GGSCI (ogg1) 5>
ogg2配置:
GGSCI (ogg2) 22> dblogin userid ogg@oggdb2 password oracle
Successfully logged into database.
GGSCI (ogg2) 23> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (ogg2) 1> add replicat rep1,exttrail /opt/ogg/dirdat/e1,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (ogg2) 24> view params rep1
replicat rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb2,password oracle
map sender.t1,target receiver.t1;
GGSCI (ogg2) 25> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (ogg2) 26> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
GGSCI (ogg2) 27>
OGG配置(oggdb2 -> oggdb1):
ogg2:
GGSCI (ogg2) 6> ADD EXTRACT ext2, TRANLOG, BEGIN now
EXTRACT added.
GGSCI (ogg2) 7> ADD RMTTRAIL /opt/ogg/dirdat/e2, EXTRACT ext2
RMTTRAIL added.
GGSCI (ogg2) 8> edit params ext2
GGSCI (ogg2) 9> view params ext2
extract ext2
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb2,password oracle
rmthost ogg1,mgrport 7809
rmttrail /opt/ogg/dirdat/e2
table receiver.t1;
GGSCI (ogg2) 10> start extract ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
GGSCI (ogg2) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT2 00:00:00 00:00:44
REPLICAT RUNNING REP1 00:00:00 00:00:10
GGSCI (ogg2) 12>
ogg1:
GGSCI (ogg1) 6> dblogin userid ogg@oggdb1 password oracle
Successfully logged into database.
GGSCI (ogg1) 7> ADD CHECKPOINTTABLE ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (ogg1) 8> ADD REPLICAT rep2, EXTTRAIL /opt/ogg/dirdat/e2, BEGIN now
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (ogg1) 9> ADD REPLICAT rep2,EXTTRAIL /opt/ogg/dirdat/e2,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (ogg1) 15> edit params rep2
GGSCI (ogg1) 16> view params rep2
replicat rep2
ASSUMETARGETDEFS
HANDLECOLLISIONS
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb1,password oracle
map receiver.t1,target sender.t1;
GGSCI (ogg1) 17> start replicat rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (ogg1) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
REPLICAT RUNNING REP2 00:00:00 00:00:01
GGSCI (ogg1) 19>
验证:
ogg1上插入一条数据:
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
SQL>
ogg2上插入另一条数据, 查询发现ogg1上传过来的数据和新插入的数据:
SQL> insert into t1 values(2);
1 row created.
Commit complete.
SQL> select * from t1;
ID
----------
1
2
SQL>
ogg1再次查询,发现ogg2的数据传到了ogg1上:
SQL> select * from t1;
ID
----------
1
2
SQL>
--EOF--
2.使用数据泵进行Oracle-Oracle的单向复制。
为简化流程,这里继续沿用第一题的DB环境, 仅贴出ogg配置流程,复制方向为ogg1->ogg2.
ogg1:
为避免干扰先停掉第一题的extract和replicat进程:
GGSCI (ogg1) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:05
REPLICAT STOPPED REP2 00:00:00 00:00:02
GGSCI (ogg1) 20> ADD EXTRACT ext3, TRANLOG, BEGIN now
EXTRACT added.
GGSCI (ogg1) 21> ADD EXTTRAIL /opt/ogg/dirdat/e3, EXTRACT ext3
EXTTRAIL added.
GGSCI (ogg1) 22> edit params ext3
GGSCI (ogg1) 23> view params ext3
extract ext3
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb1,password oracle
exttrail /opt/ogg/dirdat/e3
table sender.t1;
GGSCI (ogg1) 24> ADD EXTRACT pump3, EXTTRAILSOURCE /opt/ogg/dirdat/e3, BEGIN now
EXTRACT added.
GGSCI (ogg1) 25> ADD RMTTRAIL /opt/ogg/dirdat/p3, EXTRACT pump3
RMTTRAIL added.
GGSCI (ogg1) 26> edit params pump3
GGSCI (ogg1) 27> view params pump3
extract pump3
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb1,password oracle
rmthost ogg2,mgrport 7809
rmttrail /opt/ogg/dirdat/p3
table sender.t1;
GGSCI (ogg1) 28> start extract ext3
Sending START request to MANAGER ...
EXTRACT EXT3 starting
GGSCI (ogg1) 29> start extract pump3
Sending START request to MANAGER ...
EXTRACT PUMP3 starting
GGSCI (ogg1) 30> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:10:54
EXTRACT RUNNING EXT3 00:01:33 00:00:04
EXTRACT RUNNING PUMP3 00:00:00 00:01:00
REPLICAT STOPPED REP2 00:00:00 00:10:50
GGSCI (ogg1) 31>
ogg2:
dblogin userid ogg@oggdb2 password oracle
ADD CHECKPOINTTABLE ogg.checkpoint
GGSCI (ogg2) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT2 00:00:00 00:00:25
REPLICAT STOPPED REP1 00:00:00 00:00:20
GGSCI (ogg2) 21> ADD REPLICAT rep3,EXTTRAIL /opt/ogg/dirdat/p3,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (ogg2) 22> edit params rep3
GGSCI (ogg2) 23> view params rep3
replicat rep3
ASSUMETARGETDEFS
HANDLECOLLISIONS
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb2,password oracle
map sender.t1,target receiver.t1;
GGSCI (ogg2) 24> start replicat rep3
Sending START request to MANAGER ...
REPLICAT REP3 starting
GGSCI (ogg2) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT2 00:00:00 00:12:50
REPLICAT STOPPED REP1 00:00:00 00:12:45
REPLICAT RUNNING REP3 00:00:00 00:00:01
GGSCI (ogg2) 26>
验证:
ogg1插入一条数据并提交:
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL>
ogg2在几秒之后即可查到ogg1传过来的数据
SQL> select * from t1;
ID
----------
3
SQL>
--EOF--
3.完成Oracle-MySQL的单向复制。
为减少流程,继续沿用第一题的环境,同时在ogg2主机上启用mysqld服务,并创建同步所需要的表和db:
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> exit
Bye
[oracle@ogg2 ogg]$
复制方向: ogg1(oracle) -> ogg2(mysql)
ogg1:
GGSCI (ogg1) 32> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:13:48
EXTRACT RUNNING EXT3 00:00:04 00:00:03
EXTRACT RUNNING PUMP3 00:00:00 00:00:02
REPLICAT STOPPED REP2 00:00:00 00:13:45
GGSCI (ogg1) 33> ADD EXTRACT ext2my, TRANLOG, BEGIN now
EXTRACT added.
GGSCI (ogg1) 34> ADD RMTTRAIL /opt/oggmy/dirdat/em, EXTRACT ext2my
RMTTRAIL added.
GGSCI (ogg1) 35> edit params ext2my
GGSCI (ogg1) 36> view params ext2my
extract ext2my
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.UTF8")
userid ogg@oggdb1,password oracle
rmthost ogg2,mgrport 7809
rmttrail /opt/oggmy/dirdat/em
table sender.t1;
GGSCI (ogg1) 38> edit params defgen
GGSCI (ogg1) 39> view params defgen
DEFSFILE /opt/ogg/dirdat/ext2my.def
userid ogg@oggdb1,password oracle
table sender.t1;
GGSCI (ogg1) 40> start extract ext2my
Sending START request to MANAGER ...
EXTRACT EXT2MY starting
GGSCI (ogg1) 41> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 01:03:41
EXTRACT RUNNING EXT2MY 00:00:00 00:03:44
EXTRACT RUNNING EXT3 00:00:00 00:00:09
EXTRACT RUNNING PUMP3 00:00:00 00:00:05
REPLICAT STOPPED REP2 00:00:00 01:03:37
GGSCI (ogg1) 42> exit
[oracle@ogg1 ogg]$ defgen paramfile /opt/ogg/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 05:09:39
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-08-31 18:57:14
***********************************************************************
Operating System Version:
Linux
Version #1 Mon Apr 20 10:22:29 EDT 2009, Release 2.6.9-89.EL
Node: ogg1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 6592
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /opt/ogg/dirdat/ext2my.def
userid ogg@oggdb1,password ******
table sender.t1;
Retrieving definition for SENDER.T1
2013-08-31 18:57:16 WARNING OGG-00869 No unique key is defined for table 'T1'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Definitions generated for 1 table in /opt/ogg/dirdat/ext2my.def
[oracle@ogg1 ogg]$ scp /opt/ogg/dirdat/ext2my.def ogg2:/opt/oggmy/dirdat/ext2my.def
ext2my.def 100% 910 0.9KB/s 00:00
[oracle@ogg1 ogg]$
ogg2:
下载并解压ogg for mysql至/opt/oggmy目录.
[oracle@ogg2 oggmy]$ ./ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700
Linux, x64, 64bit (optimized), MySQL Enterprise on Jul 29 2011 22:29:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (ogg2) 20> view params mgr
PORT 7810
GGSCI (ogg2) 11> view param rep2my
replicat rep2my
sourcedefs /opt/oggmy/dirdat/ext2my.def
setenv(NLS_LANG="ENGLISH_UNITED KINDOM.UTF8")
APPLYNOOPUPDATES
HANDLECOLLISIONS
SOURCEDB mydb, USERID root, PASSWORD 1234
discardfile /opt/oggmy/dirdat/rep2my_discard.txt,append,megabytes 10
map sender.t1,target mydb.t1;
GGSCI (ogg2) 9> start manager
Manager started.
GGSCI (ogg2) 12> start replicat rep2my
Sending START request to MANAGER ...
REPLICAT REP2MY starting
GGSCI (ogg2) 19> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP2MY 00:00:00 00:37:24
GGSCI (ogg2) 20>
REPLICAT - REP2MY一直起不来,试过不同的linux和mysql版本,始终报如下相同的错误,不知何故?
[oracle@ogg2 oggmy]$ tail ggserr.log
2013-08-31 19:56:16 INFO OGG-00975 Oracle GoldenGate Manager for MySQL, mgr.prm: REPLICAT REP2MY starting.
2013-08-31 19:56:16 INFO OGG-00995 Oracle GoldenGate Delivery for MySQL, rep2my.prm: REPLICAT REP2MY starting.
2013-08-31 19:56:16 ERROR OGG-00303 Oracle GoldenGate Delivery for MySQL, rep2my.prm: Problem at line 27. Expecting file, table, or record definition.
2013-08-31 19:56:16 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL, rep2my.prm: PROCESS ABENDING.
2013-08-31 20:04:27 INFO OGG-00987 Oracle GoldenGate Command Interpreter for MySQL: GGSCI command (oracle): start replicat rep2my.
2013-08-31 20:04:27 INFO OGG-00963 Oracle GoldenGate Manager for MySQL, mgr.prm: Command received from GGSCI on host 192.168.1.122 (START REPLICAT REP2MY ).
2013-08-31 20:04:27 INFO OGG-00975 Oracle GoldenGate Manager for MySQL, mgr.prm: REPLICAT REP2MY starting.
2013-08-31 20:04:27 INFO OGG-00995 Oracle GoldenGate Delivery for MySQL, rep2my.prm: REPLICAT REP2MY starting.
2013-08-31 20:04:27 ERROR OGG-00303 Oracle GoldenGate Delivery for MySQL, rep2my.prm: Problem at line 27. Expecting file, table, or record definition.
2013-08-31 20:04:27 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL, rep2my.prm: PROCESS ABENDING.
[oracle@ogg2 oggmy]$
--EOF--
4.完成Oracle分别向Oracle和mysql双路的单向复制。
可在源端配置两个extract, 一个为Oracle->Oracle,一个为Oracle->mysql,目标端分别配置Oracle和mysql的replicat,详细步骤即为第2和第3题,因此不再重复.
4083
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
