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
[oracle@xcell oradba]$ ls -lrt *ggs*
-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 INSTALLATIONfirst unzip the zip files [oracle@xcell oradba]$ unzip -l 191003_ggs_Linux_x64_MySQL_64bit.zipArchive: 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 INSTALLATIONUnzip 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 MySQLVersion 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (xcell.home.com) 1> showParameter settings:SET DEBUG OFFCurrent directory: /gg_gate/mssqlEditor: vi2020-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 OFFCurrent directory: /gg_gate/mssqlEditor: vi Reports (.rpt) /gg_gate/mssql/dirrptParameters (.prm) /gg_gate/mssql/dirprmReplicat Checkpoints (.cpr) /gg_gate/mssql/dirchkExtract Checkpoints (.cpe) /gg_gate/mssql/dirchkProcess Status (.pcs) /gg_gate/mssql/dirpcsSQL Scripts (.sql) /gg_gate/mssql/dirsqlDatabase Definitions (.def) /gg_gate/mssql/dirdefDump files (.dmp) /gg_gate/mssql/dirdmpMasterkey wallet files (.wlt) /gg_gate/mssql/dirwltCredential 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 mgrGGSCI (xcell.home.com) 1> view param mgr PORT 7811 GGSCI (xcell.home.com) 2> GGSCI (xcell.home.com) 7> start mgrManager 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 ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. 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 mydbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> 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 mydbReading table information for completion of table and column namesYou 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 configurationEdit param file and add below lines. GGSCI (xcell.home.com) 2> view param EXTMYSQL extract extmysqlSETENV(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 3307sourcedb mydb, userid oggadm1, password WElcome@@1exttrail /gg_gate/mysql/dirdat/ltTRANLOGOPTIONS ALTLOGDEST /u01/mysql/mysql-5.7.28-el7-x86_64/data/mysql.indextable mydb.*; GGSCI (xcell.home.com) 3> view param DPMYSQL EXTRACT DPMYSQLRMTHOST 192.168.10.151,MGRPORT 7809RMTTRAIL /gg_gate/oracle/dirdat/mysql/rtPASSTHRUtable mydb.*; GGSCI (xcell.home.com) 4>GGSCI (xcell.home.com) 1> start mgrManager started.GGSCI (xcell.home.com) 4> add extract extmysql,tranlog,begin nowEXTRACT added.GGSCI (xcell.home.com) 4> add exttrail /gg/gate/mysql/dirdat/lt,extract extmysqlEXTTRAIL 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/ltEXTRACT added.GGSCI (xcell.home.com) 4>add rmttrail /gg/gate/oracle/dirdat/rt,extract dpmysqlRMTTRAIL 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 running3. Enable the parameter for GoldenGate replicationalter system set enable_goldengate_replication=true;4. Create golden gate user and grant below roles under pluggable databasecreate 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 columnsalter 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 ggadminSuccessfully 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 repmysqlSETENV(ORACLE_HOME=’/u01/oracle/database19c’)SETENV(ORACLE_SID=’pogb’)SETENV(TNS_ADMIN=’/u01/oracle/database19c/network/admin/’)useridalias oggadmin_ggsASSUMETARGETDEFSMAP 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 RUNNINGREPLICAT 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 namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> 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 ENDGGSCI (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 2020Version 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 – ProductionVersion 19.3.0.0.0
SQL> alter session set container=POGB;
Session altered.
SQL> select * from MYORA.DEPT;
DEPTNO DNAME LOC———- ————– ————- 10 CLERK BANGALORE
SQL>
