合 How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
- GOAL
- SOLUTION
- 1. Pre-requisites
- 2. GoldenGate Manager
- 3. GoldenGate demo tables
- 3.1 The demo tables for the Oracle database
- 3.2 Creating the MySQL demo tables
- 4. Oracle database pre-requisites
- 4.1 Verify Oracle DB connection
- 4.2 Add the TRANDATA
- 5. GoldenGate initial load (no data pump process configured to keep it simple)
- 6. The SOURCEDEFS file
- 6.1 DEFGEN parameter file
- 6.2 Defgen command
- 6.3 COPY DEFGEN file
- 7. MySQL initial load replicat process
- 8. Start Initial Load Extract process
- 9. GoldenGate Extract for the Oracle database
- 9.1 GoldenGate extract parameter file
- 9.2 Add GoldenGate extract
- 9.3 start GoldenGate extract
- 9.4 Status extract
- 10 MySQL replicat
- 10.1 MySQl replicat parameter file
- 10.2 add and start MySQL REPLICAT process
- 10.3 REPLICAT status
- 11. Verify the replication
- 11.1 Update Oracle table content
- 11.2 check replicat statistics
How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
GOAL
This note describes a set up of a GoldenGate replication between Oracle and MySQL
SOLUTION
Please make sure you have installed GoldenGate on the Oracle and the MySQL server. First let's start with the configuration of GoldenGate on the MySQL machine.
1. Pre-requisites
So let's start ggsci: ./ggsci
and in ggsci let's create the subdirectories:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI 1> create subdirs Creating subdirectories under current directory /var/lib/mysql/ggs Parameter files /var/lib/mysql/ggs/dirprm: already exists Report files /var/lib/mysql/ggs/dirrpt: created Checkpoint files /var/lib/mysql/ggs/dirchk: created Process status files /var/lib/mysql/ggs/dirpcs: created SQL script files /var/lib/mysql/ggs/dirsql: created Database definitions files /var/lib/mysql/ggs/dirdef: created Extract data files /var/lib/mysql/ggs/dirdat: created Temporary files /var/lib/mysql/ggs/dirtmp: created Stdout files /var/lib/mysql/ggs/dirout: created |
2. GoldenGate Manager
The next step is to configure a basic GoldenGate manager process:
1 2 3 4 5 6 7 | GSCI (server) 2> edit param mgr PORT 7806 and start it: GGSCI 3> start mgr |
(SIDE NOTE - I have to use 7806 port here as my replicat and extract will be running on the same machine...)
We do the same steps now for the GG side that will connect to the Oracle database and use here now the port 7809.
So at the end we have now 2 manager processes running - one for the Oracle GG extract and the other one for the MySQL replicat.
3. GoldenGate demo tables
For a basic replication we create the demo tables in Oracle and MySQL using the scripts shipped with the installation.
3.1 The demo tables for the Oracle database
connect to the Oracle database and execute the demo_ora_create and demo_ora_insert script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> @demo_ora_create SQL> @demo_ora_insert.sql 1 row created. 1 row created. 1 row created. 1 row created. Commit complete. SQL> select count(*) from tcustmer; COUNT(*) \---------- 2 |