合 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 |
3.2 Creating the MySQL demo tables
Connect to the MySQL database and execute demo_mysql_create.sql script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -bash-3.2$ mysql gateway -u <your MySQl user> -p<your MySQL user's password' <demo_mysql_create.sql which will create the TCUSTMER table: mysql> describe gateway.TCUSTMER; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | CUST_CODE | varchar(4) | NO | PRI | NULL | | | NAME | varchar(30) | YES | | NULL | | | CITY | varchar(20) | YES | | NULL | | | STATE | char(2) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) |
4. Oracle database pre-requisites
4.1 Verify Oracle DB connection
In my Oracle database I have a user mysql with password
1 2 3 4 5 6 7 8 9 10 | GGGSCI 1> dblogin userid mysql, password <passwd> Successfully logged into database. and check the table list: GGSCI 4> list tables * MYSQL.TCUSTMER MYSQL.TCUSTORD |
Found 2 tables matching list criteria.
4.2 Add the TRANDATA
to be able to capture the changes for the table we need to add trandata:
1 2 3 4 | GGSCI 7> add trandata MYSQL.TCUSTMER Logging of supplemental redo data enabled for table MYSQL.TCUSTMER. |
5. GoldenGate initial load (no data pump process configured to keep it simple)
The Oracle table already contains some data, so let's define an initial load process to first replicate the data to the target:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI 8> edit param emini EXTRACT emini SETENV (ORACLE_SID=db1124) SETENV (ORACLE_HOME=/home/oracle/product/11.2.0.4/db_gtw) USERID mysql, PASSWORD <passwd> RMTHOST remote_server, MGRPORT 7806 RMTTASK REPLICAT, GROUP RMINI TABLE mysql.TCUSTMER; GGSCI 9> ADD EXTRACT emini, SOURCEISTABLE EXTRACT added. |
6. The SOURCEDEFS file
when replicating data between an Oracle and a foreign database we also need to create the SOURCEDEFS file which requires a definition generator parameter file. It is created in ggsci containing the username and password as well as the table we want to map.
6.1 DEFGEN parameter file
1 2 3 4 5 | GGSCI 10> edit param defgen DEFSFILE ./dirsql/GGMySQLTEST.sql USERID mysql, password <passwd> TABLE MYSQL.TCUSTMER; |
