Golden Gate Cross Platform Replication ( MySQL to Oracle)
Here is details of installed product and binary location. Here I have configured both databases on same server .
Databases | Oracle | MySQL |
Version | 19.3.0.0.0 | 5.7.28 |
Golden Gate | 19.1.0.0.3 | 19.1.0.0.4 |
OS | Oracle Linux Server release 7.3 | Oracle Linux Server release 7.3 |
Base Location | /u01/oracle/database19c | /u01/mysql |
Golden gate base | /gg_gate/oracle | /gg_gate_mysql |
Installation Link | - | https://deepakoracledba.blogspot.com/search/label/MySql |
Step 1: Post configuration of both database server using the linked provided , we can proceed to to download respective certified versions of golden gate binaries from oracle e-delivery site.
Here i have downloaded and transferred to my local Vm based server
-rw-r--r--. 1 root root 71654538 Apr 21 19:20 191003_ggs_Linux_x64_MySQL_64bit.zip
-rw-r--r--. 1 root root 556240981 Apr 21 19:21 191004_fbo_ggs_Linux_x64_shiphome.zip
Step 2: Installation of golden gate quite simple , we just need to unzip the binaries to respective golden gate base locations.
In 19c Oracle binary , we have GUI based installation option is available , that can be used .
MYSQL_GOLDENGATE INSTALLATION
[oracle@xcell oradba]$ unzip -l 191003_ggs_Linux_x64_MySQL_64bit.zip Archive: 191003_ggs_Linux_x64_MySQL_64bit.zip Length Date Time Name --------- ---------- ----- ---- 289024000 09-07-2019 21:22 ggs_Linux_x64_MySQL_64bit.tar 1413 05-30-2019 05:19 OGG-19.1.0.0-README.txt 326332 09-17-2019 23:38 OGG_WinUnix_Rel_Notes_19.1.0.0.3.pdf --------- ------- 289351745 3 files 2. tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C /gg_gate/mssql/ |
ORACLE _GOLDENGATE INSTALLATION Unzip 191004_fbo_ggs_Linux_x64_shiphome.zip -d /gg_gate/oracle |
Step 3: Configuring golden gate for MySQL and Oracle
MYSQL_GOLDENGATE Configuration Ø Golden gate config Go to /gg_gate/mysql Location [root@xcell mysql]# ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (xcell.home.com) 1> show Parameter settings: SET DEBUG OFF Current directory: /gg_gate/mssql Editor: vi 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirrpt does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirprm does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirchk does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirchk does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirpcs does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirsql does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirdef does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirdmp does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dirwlt does not exist yet. 2020-04-21 22:20:49 WARNING OGG-15194 Directory /gg_gate/mssql/dircrd does not exist yet. GGSCI (xcell.home.com) 2> create subdirs Creating subdirectories under current directory /gg_gate/mssql Parameter file /gg_gate/mssql/dirprm: created. Report file /gg_gate/mssql/dirrpt: created. Checkpoint file /gg_gate/mssql/dirchk: created. Process status files /gg_gate/mssql/dirpcs: created. SQL script files /gg_gate/mssql/dirsql: created. Database definitions files /gg_gate/mssql/dirdef: created. Extract data files /gg_gate/mssql/dirdat: created. Temporary files /gg_gate/mssql/dirtmp: created. Credential store files /gg_gate/mssql/dircrd: created. Masterkey wallet files /gg_gate/mssql/dirwlt: created. Dump files /gg_gate/mssql/dirdmp: created. GGSCI (xcell.home.com) 3> show Parameter settings: SET DEBUG OFF Current directory: /gg_gate/mssql Editor: vi Reports (.rpt) /gg_gate/mssql/dirrpt Parameters (.prm) /gg_gate/mssql/dirprm Replicat Checkpoints (.cpr) /gg_gate/mssql/dirchk Extract Checkpoints (.cpe) /gg_gate/mssql/dirchk Process Status (.pcs) /gg_gate/mssql/dirpcs SQL Scripts (.sql) /gg_gate/mssql/dirsql Database Definitions (.def) /gg_gate/mssql/dirdef Dump files (.dmp) /gg_gate/mssql/dirdmp Masterkey wallet files (.wlt) /gg_gate/mssql/dirwlt Credential store files (.crd) /gg_gate/mssql/dircrd GGSCI (xcell.home.com) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (xcell.home.com) 6> edit param mgr GGSCI (xcell.home.com) 1> view param mgr PORT 7811 GGSCI (xcell.home.com) 2> GGSCI (xcell.home.com) 7> start mgr Manager started. GGSCI (xcell.home.com) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING Ø MYSQL database base config - Create a new datbase - Create necessary golden gate users. - Create few tables [root@xcell ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> select @@VERSION; +------------+ | @@VERSION | +------------+ | 5.7.28-log | +------------+ 1 row in set (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> create database mydb; -- create a new database mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.59 sec) mysql> use mydb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE USER 'ogguser1'@'localhost' IDENTIFIED BY 'WElcome@@1'; Query OK, 0 rows affected (0.61 sec) mysql> CREATE USER 'oggadm1'@'localhost' IDENTIFIED BY 'WElcome@@1'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'ogguser1'@'localhost'; Query OK, 0 rows affected (0.05 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'oggadm1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> use mydb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE `DEPT` ( `DEPTNO` tinyint(4) DEFAULT NULL, `DNAME` varchar(14) DEFAULT NULL, `LOC` varchar(13) DEFAULT NULL, UNIQUE KEY `PK_DEPT` (`DEPTNO`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; mysql> show tables; +-----------------------+ | Tables_in_mydb (DEPT) | +-----------------------+ | DEPT | +-----------------------+ 1 row in set (0.00 sec) Ø Golden gate extract and Pump process configuration Edit param file and add below lines. GGSCI (xcell.home.com) 2> view param EXTMYSQL extract extmysql SETENV(MYSQL_HOME='/u01/mysql/mysql-5.7.28-el7-x86_64/bin') SETENV(MYSQL_UNIX_PORT='/u01/mysql/mysql-5.7.28-el7-x86_64/mysql.sock') dboptions host localhost, connectionport 3307 sourcedb mydb, userid oggadm1, password WElcome@@1 exttrail /gg_gate/mysql/dirdat/lt TRANLOGOPTIONS ALTLOGDEST /u01/mysql/mysql-5.7.28-el7-x86_64/data/mysql.index table mydb.*; GGSCI (xcell.home.com) 3> view param DPMYSQL EXTRACT DPMYSQL RMTHOST 192.168.10.151,MGRPORT 7809 RMTTRAIL /gg_gate/oracle/dirdat/mysql/rt PASSTHRU table mydb.*; GGSCI (xcell.home.com) 4> GGSCI (xcell.home.com) 1> start mgr Manager started. GGSCI (xcell.home.com) 4> add extract extmysql,tranlog,begin now EXTRACT added. GGSCI (xcell.home.com) 4> add exttrail /gg/gate/mysql/dirdat/lt,extract extmysql EXTTRAIL added. GGSCI (xcell.home.com) 3> start extmysql Sending START request to MANAGER ... EXTRACT EXTMYSQL starting GGSCI (xcell.home.com) 4> add extract dpmysql,exttrailsource /gg/gate/mysql/dirdat/lt EXTRACT added. GGSCI (xcell.home.com) 4>add rmttrail /gg/gate/oracle/dirdat/rt,extract dpmysql RMTTRAIL added. GGSCI (xcell.home.com) 4>start dpmysql Before starting pump need to start manager process on remote site i.e oracle end . |
Oracle GG_configuration 1. Here is have created a database called PCGB(container)/POGB(pluggable database) 2. Make sure both database and listener up and running 3. Enable the parameter for GoldenGate replication alter system set enable_goldengate_replication=true; 4. Create golden gate user and grant below roles under pluggable database create user ggadmin identified by ggadmin; exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin',privilege_type=>'CAPTURE',grant_optional_privileges=>'*'); 5. Add supplemental logdata on columns alter database add supplemental log data(all) columns; 6. Create separate schema called myora where dept table will be replicated. Create user myora identified by myodadb; create table dept (deptno number,dname varchar2(10),loc varchar2(15)); 7. Set Oracle _home and then Go to Golden gate binary location , GGSCI (xcell.home.com) 2> dblogin userid ggadmin@POGB, password ggadmin Successfully logged into database POGB. GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> create wallet Created wallet. GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> add credentialstore Credential store created. GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 3> alter credentialstore add user ggadmin@pogb,password ggadmin,alias oggadmin_ggs Credential store altered. GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4> view param REPMYSQL REPLICAT repmysql SETENV(ORACLE_HOME='/u01/oracle/database19c') SETENV(ORACLE_SID='pogb') SETENV(TNS_ADMIN='/u01/oracle/database19c/network/admin/') useridalias oggadmin_ggs ASSUMETARGETDEFS MAP mydb.*,target MYORA.*; GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4> GGSCI (xcell.home.com as ggadmin@PCGB/POGB) 4> add replicat repmysql,integrated exttrail /gg_gate/oracle/dirdat/rt GGSCI (xcell.home.com) 2> start REPMYSQL Sending START request to MANAGER ... REPLICAT REPMYSQL starting GGSCI (xcell.home.com) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPMYSQL 00:00:00 46:37:47 GGSCI (xcell.home.com) 4> |
Step 4: Add some rows on dept table in mysql and check the table .
mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| CORONA |
| DEPT |
| state_wise |
| travel_history |
+----------------+
4 rows in set (0.00 sec)
mysql> desc DEPT;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | tinyint(4) | YES | UNI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.24 sec)
mysql> insert into DEPT values (10,'CLERK','BANGALORE');
Query OK, 1 row affected (0.25 sec)
mysql> select * from DEPT;
+--------+-------+-----------+
| DEPTNO | DNAME | LOC |
+--------+-------+-----------+
| 10 | CLERK | BANGALORE |
+--------+-------+-----------+
1 row in set (0.00 sec)
mysql>
mysql>
GGSCI (xcell.home.com) 3> stats EXTMYSQL
Sending STATS request to EXTRACT EXTMYSQL ...
Start of Statistics at 2020-04-28 21:33:55.
Output to /gg_gate/mysql/dirdat/lt:
Extracting from mydb.DEPT to mydb.DEPT:
*** Total statistics since 2020-04-28 21:33:08 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2020-04-28 21:33:08 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2020-04-28 21:33:08 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2020-04-28 21:33:08 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (xcell.home.com) 4>GGSCI (xcell.home.com) 5> stats DPMYSQL
Sending STATS request to EXTRACT DPMYSQL ...
Start of Statistics at 2020-04-28 21:34:15.
Output to /gg_gate/oracle/dirdat/mysql/rt:
Extracting from mydb.DEPT to mydb.DEPT:
*** Total statistics since 2020-04-28 21:33:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2020-04-28 21:33:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2020-04-28 21:33:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2020-04-28 21:33:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (xcell.home.com) 6>
Ø CHECK IN REMOTE END
GGSCI (xcell.home.com) 4> stats REPMYSQL
Sending STATS request to REPLICAT REPMYSQL ...
Start of Statistics at 2020-04-28 21:35:01.
Integrated Replicat Statistics:
Total transactions 1.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Operation type functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from mydb.DEPT to POGB.MYORA.DEPT:
*** Total statistics since 2020-04-28 21:34:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2020-04-28 21:34:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2020-04-28 21:34:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2020-04-28 21:34:22 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (xcell.home.com) 5>
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 28 21:35:52 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=POGB;
Session altered.
SQL> select * from MYORA.DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 CLERK BANGALORE