Ask Me

Golden Gate cross platform replication from MySQL to Oracle

 


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 INSTALLATION
  1. first unzip the zip files 
[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

SQL>


Popular Posts