Oracle uses goldengate to perform one-way replication for Oracle and mysql. Oracle uses one-way replication for Oracle and mysql respectively. Blog. csdn. netq947817003articledetails13294937 II. Add the corresponding content to the ORACLE master database and the ORACLE target database respectively.
Oracle uses goldengate to perform one-way replication for Oracle and mysql. Oracle uses one-way replication for Oracle and mysql respectively. Http://blog.csdn.net/q947817003/article/details/13294937 2. Add corresponding on ORACLE master database and ORACLE target database respectively
Oracle uses goldengate to perform one-way replication to Oracle and mysql respectively.
1. The one-way replication from Oracle to Oracle and mysql is as follows:
ORACLE-mysql is based on one-way replication. Http://blog.csdn.net/q947817003/article/details/13294937
2. Add the corresponding process ORACLE source on the ORACLE master database and ORACLE target database respectively:
1. Add and configure the extract process to perform the following operations in sequence:
Add extract extora, tranlog, begin now
Location of the target file -- here, note that rmttrail refers to the path of the target file.
Add rmttrail/u01/ogg/dirdat/ot, extract extora
Edit params extora
Write Data in the open edit window
Extract extora
Userid ogg, password ogg
Rmthost 192.168.1.213, mgrport 7809
Rmttrail/u01/ogg/dirdat/ot
Table test5.test5;
######################################## ##########
2. Add the replicat process to the target end: ----- here, the MYSQL target database and the ORACLE target database are on the same virtual machine. If the same situation occurs, note the following:
Ogg for mysql and ogg for oracle are installed in different directories, and the MGR and receiving and extraction processes involved are independent.
All related processes must be started independently.
At the same time, note that the ogg for mysql and ogg for oracle master processes must run on different ports. FOR example, my ogg for oracle runs on 7809. ogg for mysql runs on 7810.
Run the following commands one by one:
Dblogin userid ogg, password ogg
Add checkpointtable ogg. checkpoint ########## if you have already added this table, you do not need to execute this statement.
Add replicat repora, exttrail/u01/ogg/dirdat/ot, checkpointtable ogg. checkpoint
Edit params repora
In the pop-up editing window, enter the following: --- Here I only set the table test5 for synchronizing the test5 user
Try again.
Replicat repora
Handlecollisions
Assumetargetdefs
Userid ogg, password ogg
Discardfile/u01/ogg/dirdat/repora_discard.txt, append, megabytes 10
Map test5.test5, target test5.test5;
3. Create users and tables in the target ORACLE database, and start the OGG process of the master and target databases. 1. The statements used to create users and tables in the objective database of replicel are as follows:
Run create user test5 identified by test5 as the DBA user;
Grant connect, resource to test5;
Conn test5/test5
Create table test5 (aa int primary key );
2. Start the OGG process of the master and target Databases
The statements involved are roughly as follows:
Start mgr
Start extract extmysql or start extmysql
Start replicat repmysql or start repmysql
Start extract extora or start extora
Start replicat repora or start repora
3. The status after the process is started is as follows:
ORACLE master database OGG process: -- only extmysql extora is used, and the others are used for other replication tests. -- The following OGG process is the same.
GGSCI (bys001.oel.com) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Extract abended EXT1 00:00:00
Extract abended EXT3 00:00:00
Extract running extmysql 07:39:30
Extract running extora 00:00:00
Extract abended PUMP3 00:00:00 26:20:29
Replicat abended REP2 00:00:00
Ogg process of the mysql target database:
GGSCI (bys2.oel.com) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Replicat running repmysql 00:00:00 00:00:00
ORACLE target database OGG process:
GGSCI (bys2.oel.com) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Extract abended EXT2 00:00:00
Replicat abended REP1 00:00:00
Replicat abended REP3 00:00:00
Replicat running repora 00:00:00: 00:
The OGG-related processes involved are running normally.
Iv. synchronization test
Note: The SQL statement execution time is inaccurate in the following tests, for example, the following statement
00:19:21 TEST5 @ bys1> insert into test5 values (333 );
1 row created.
00:19:50 TEST5 @
If the execution time of the inserted statement is counted, the time in the next command prompt returned after the statement is executed is counted, that is, 00:19:50, instead of 00:19:21. Currently, there is no lab environment.
1. Insert in the source database: -- The source database already has two data records, which are left when one-way replication of ORACLE-mysql is performed. It is not cleared here,
The test shows that the newly added ORACLE target database does not initially contain the two data items. In my configuration, OGG will not automatically synchronize the data in the source and target database tables.
TEST5 @ bys1> select * from test5;
AA
----------
100
200
TEST5 @ bys1> set time on
00:19:21 TEST5 @ bys1> insert into test5 values (333 );
1 row created.
00:19:50 TEST5 @ bys1> commit;
Commit complete.
00:19:53 TEST5 @ bys1> --- record the submission time to view Data Synchronization information in the target database.
2. query data synchronization in the target ORACLE Database: --- because the data in the target database is not initialized, the original database has two data types: 100 and 200. The target database has not initialized the data. -- You can manually insert the two pieces of data.
[Oracle @ bys2 mysqlogg] $ sqlplus test5/test5
TEST5 @ bys2> set time on
00:19:32 TEST5 @ bys2> select * from test5;
No rows selected
00:19:54 TEST5 @ bys2> select * from test5 ;-
No rows selected
00:19:57 TEST5 @ bys2> select * from test5; --- you can see that the delay after the master database is submitted is more than 4 seconds.
AA
----------
333
3. Target MYSQL Database Synchronization:[Oracle @ bys2 mysqlogg] $ mysql-u root-p oggtest
Enter password:
Mysql> select * from test5;
+ ----- +
| Aa |
+ ----- +
| 1, 100 |
| 1, 200 |
| 1, 333 |
+ ----- +
3 rows in set (0.00 sec)
Mysql> select * from chktab; ---- query this table. The audit_ts column is updated after the OACLE source database sends a COMMIT. The last_update_ts column is displayed at 00:19:54 seconds, which is more than 4 seconds delayed than the ORACLE target database.
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| Group_name | group_key | seqno | rba | audit_ts | create_ts | last_update_ts | current_dir |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
| REPMYSQL | 2345303211 | 2 | 1218 | 00:19:50. 795556 | 14:14:31 |/u01/mysqlogg |
+ ------------ + ------- + ------ + ------------------------------ + --------------------- + ------------------- + --------------- +
1 row in set (0.00 sec)
