Golden Gate Cross Platform Replication ( MySQL to Oracle)

Share this post


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 .

DatabasesOracleMySQL
Version19.3.0.0.05.7.28
Golden Gate19.1.0.0.319.1.0.0.4
OSOracle Linux Server  release 7.3Oracle 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>


Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *