Tag Archives: OGG replication from mysql to oracle

Oracle GoldenGate Heterogeneous Replication

Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c

Contents
______________________________________________________________________________________________________________

1. Overview
2. Environment
3. Install GoldenGate 12C for MySQL
4. Install GoldenGate 12C for Oracle 12.1.0.1.0

5. Create Database and User

a) On Source MySQL
b) On Target Oracle 12c

6. OGG Configuration on Source MySQL

         a) Set Environment
         b) Login to ggsci
         c) Configure Manager
         d) Create Extract Group
         e) Create Pump Extract Group
         f) Create Source Table Definitions

7. OGG Configuration on Target Oracle 12c

       a) Set Environment
       b) Login to Database
       c) Configure Manager
       d) Set up the checkpoint table
            i) Create a GLOBALS file
           ii) Activate the GLOBALS parameters
          iii) Add a Replicat checkpoint table

8. Create the Replicat Group
9. On Source (MySQL) Start Manager, Extract and Pump
10. On Target (Oracle 12C) start the Manager and Replicat

11. Test and Verify the Results

INSERT OPERATION

      a1) Execute Insert on the Source Oracle Database MySQL
      a2) Verify Insert Changes on Target Oracle Database Oracle 12C

UPDATE OPERATION

      b1) Execute update on the Source Oracle Database MySQL
      b2) Verify Update Changes on Target Oracle Database Oracle 12c

DELETE OPERATION

      c1) Execute Delete on the Source Oracle Database MySQL
      c2) Verify Delete Changes on Target Oracle Database Oracle 12c

_____________________________________________________________________________________________________________


1. Overview

Configure Oracle GoldenGate to perform one-way replication from MySQL 5.5 database to an Oracle 12c database on same server


2. Environment

Source (MySQL):

Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.8
GoldenGate Version		: 12.1.2.0.0 for MySQL
Database Name			: CHITTI
File System			: Normal 
Golden Gate User		: OGGUSER
Golden Gate Extract		: E_MYSQL
Golden Gate Pump		: P_MYSQL
Application User		: CHITTI (Replicating this user data to target)
GoldenGate Home			: /u01/app/oracle/product/mysqlogg

Target (Oracle 12c):

		
Server Name			: RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.8
Database Version		: 12.1.0.1.0
GoldenGate Version		: 12.1.2.0.0 for Oracle 12c
Database Name			: ORCL12
File System			: Normal 
Golden Gate User		: OGGUSER
Golden Gate Replicat	        : R_MYSQL
Application User		: CHITTI
GoldenGate Home			: /u01/app/oracle/product/12cogg


3. Install GoldenGate 12C for MySQL

We will publish soon about this separatly !!!


4. Install GoldenGate 12C for Oracle 12.1.0.1.0

We will publish soon about this separatly !!!


5. Create MySQL Database and User CHITTI for testing


a) On Source (MySQL)

[root@rac1 raj]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> create database CHITTI;
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> CREATE USER 'CHITTI'@'localhost' IDENTIFIED BY 'CHITTI';
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user where user='CHITTI';
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost | CHITTI |
+-----------+--------+
1 row in set (0.15 sec)

mysql>

mysql> GRANT ALL ON *.* TO 'CHITTI'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| CHITTI             |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql>

mysql> show tables;
Empty set (0.00 sec)

mysql>


-- Create the source database tables
  
[root@rac1 oracle]# mysql -u CHITTI -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> USE CHITTI;
Database changed
mysql> 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| CHITTI     |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT USER();
+------------------+
| USER()           |
+------------------+
| CHITTI@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>
mysql> USE CHITTI;
Database changed
mysql> drop table if exists trans_type;
Query OK, 0 rows affected (0.21 sec)

mysql> create table trans_type (
    ->   trans_id smallint,
    ->   trans varchar(5),
    ->   primary key (trans_id))
    ->   ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql>

mysql> show tables;
+------------------+
| Tables_in_CHITTI |
+------------------+
| trans_type       |
+------------------+
1 rows in set (0.00 sec)

mysql>

mysql> select count(*) as trans_type from trans_type;
+------------+
| trans_type |
+------------+
|          0 |
+------------+
1 row in set (0.01 sec)

mysql>


b) On Target (Oracle 12c)

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL12    READ WRITE

SQL> create user CHITTI identified by CHITTI default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to chitti;

Grant succeeded.

SQL> alter user chitti quota unlimited on users;

User altered.

SQL> conn CHITTI/CHITTI;
Connected.

SQL> create table trans_type (
  trans_id smallint,
  trans varchar2(5),
  primary key (trans_id)
  using index
);

Table created.

SQL> 
SQL> col tname for a15
SQL> select * from tab;

TNAME           TABTYPE  CLUSTERID
--------------- ------- ----------
TRANS_TYPE      TABLE

1 rows selected.

SQL>
SQL> set echo on;
SQL> select count(*) from TRANS_TYPE;

  COUNT(*)
----------
         0  <------------------ empty table 

SQL>


6. OGG Configuration on Source MySQL


a) Set Environment

. mysql.env

#!/bin/bash
GG=/u01/app/oracle/product/mysqlogg ; export GG
JAVA_HOME=/u01/software/jdk1.7.0_45 ; export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH:$HOME/bin:/usr/local/bin ; export PATH
LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server/libjvm.so:/u01/app/oracle/product/mysqlogg ; export LD_LIBRARY_PATH
alias ggsci='cd $GG; rlwrap ./ggsci'


b) Login to ggsci

[root@rac1 ~]# cd /home/oracle
[root@rac1 oracle]# . mysql.env
[root@rac1 oracle]# ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 24 2013 15:32:47
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.rajasekhar.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (rac1.rajasekhar.com) 3> 


c) Configure Manager

GGSCI (rac1.rajasekhar.com) 4> edit param mgr

-- Add below lines

PORT 15100
DYNAMICPORTLIST 15110-15120
PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10

GGSCI (rac1.rajasekhar.com) 5> view params mgr

PORT 15100
DYNAMICPORTLIST 15110-15120
PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10

GGSCI (rac1.rajasekhar.com) 6>


d) Create Extract Group

-- CREATE EXTRACT AND LOCAL EXTRACT TRAIL OF 50MB

GGSCI (rac1.rajasekhar.com) 6> ADD EXTRACT E_MYSQL, VAM, BEGIN NOW
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 7> ADD EXTTRAIL ./dirdat/eo, EXTRACT E_MYSQL, MEGABYTES 50
EXTTRAIL added.

GGSCI (rac1.rajasekhar.com) 8>

GGSCI (rac1.rajasekhar.com) 22> edit param E_MYSQL

-- Add below lines

EXTRACT E_MYSQL

EXTTRAIL ./dirdat/eo

SOURCEDB CHITTI@localhost:3306, USERID CHITTI, PASSWORD CHITTI

TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/pts-bin.index

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.trans_type;

GGSCI (rac1.rajasekhar.com) 23> view param E_MYSQL

EXTRACT E_MYSQL

EXTTRAIL ./dirdat/eo

SOURCEDB CHITTI@localhost:3306, USERID CHITTI, PASSWORD CHITTI

TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/pts-bin.index

STATOPTIONS RESETREPORTSTATS

REPORT AT 00:01

REPORTROLLOVER AT 00:01

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.trans_type;


GGSCI (rac1.rajasekhar.com) 24>


e) Create Pump Extract Group

-- CREATE DATA PUMP FOR ORACLE 12C AND REMOTE EXTRACT TRAIL OF 50MB

GGSCI (rac1.rajasekhar.com) 25> ADD EXTRACT P_MYSQL, EXTTRAILSOURCE ./dirdat/eo
EXTRACT added.


GGSCI (rac1.rajasekhar.com) 26> ADD RMTTRAIL ./dirdat/ra, EXTRACT P_MYSQL, MEGABYTES 50
RMTTRAIL added.


GGSCI (rac1.rajasekhar.com) 27> edit param P_MYSQL

-- Add below lines

EXTRACT P_MYSQL

USERID ogguser, password oracle

RMTHOST 192.168.2.101, MGRPORT 15000

RMTTRAIL ./dirdat/ra

PASSTHRU

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.*;


GGSCI (rac1.rajasekhar.com) 28> view params P_MYSQL

EXTRACT P_MYSQL

USERID ogguser, password oracle

RMTHOST 192.168.2.101, MGRPORT 15000

RMTTRAIL ./dirdat/ra

PASSTHRU

REPORTCOUNT EVERY 60 SECONDS, RATE

TABLE CHITTI.*;


GGSCI (rac1.rajasekhar.com) 29>


f) Create Source Table Definitions

login as root
cd /home/oracle
. mysql.env

cd /u01/app/oracle/product/mysqlogg/dirprm/

vi defgen_chitti.prm

-- Add below lines
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD CHITTI
TABLE CHITTI.*;

[root@rac1 dirprm]# cat defgen_chitti.prm
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD CHITTI
TABLE CHITTI.*;
[root@rac1 dirprm]#

cd /u01/app/oracle/product/mysqlogg

[root@rac1 mysqlogg]# ./defgen paramfile /u01/app/oracle/product/mysqlogg/dirprm/defgen_chitti.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
 Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
 Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 24 2013 16:01:59

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2018-09-05 15:12:31
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Nov 7 18:13:56 PST 2012, Release 2.6.32-300.39.1.el5uek
Node: rac1.rajasekhar.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 9617

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirdef/chittidefs.sql
SOURCEDB CHITTI@localhost, USERID CHITTI, PASSWORD ******
TABLE CHITTI.*;
Expanding wildcard table specification CHITTI.*:

Retrieving definition for CHITTI.trans_type.


Definitions generated for 1 table in ./dirdef/chittidefs.sql.

[root@rac1 mysqlogg]#

[root@rac1 dirdef]# pwd
/u01/app/oracle/product/mysqlogg/dirdef
[root@rac1 dirdef]# cp chittidefs.sql /u01/app/oracle/product/12cogg/dirdef
[root@rac1 dirdef]# chmod 644 /u01/app/oracle/product/12cogg/dirdef/chittidefs.sql
[root@rac1 dirdef]#


7. OGG Configuration on Target Oracle 12C


a) Set Environment

. 12c.env

#!/bin/bash
GG=/u01/app/oracle/product/12cogg ; export GG
ORACLE_HOME=/u01/app/oracle/product/12c/db_1;  export ORACLE_HOME
ORACLE_SID=ORCL12 ; export ORACLE_SID
JAVA_HOME=/u01/software/jdk1.7.0_45 ; export JAVA_HOME
PATH=$JAVA_HOME/bin:$PATH:$HOME/bin:/usr/local/bin ; export PATH
LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$ORACLE_HOME/lib; export LD_LIBRARY_PATH
alias ggsci='cd $GG; rlwrap ./ggsci'


b) Login to Database

[oracle@rac1 ~]$ . 12c.env
[oracle@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.rajasekhar.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2> 


c) Configure Manager

GGSCI (rac1.rajasekhar.com) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (rac1.rajasekhar.com) 3> edit param mgr

-- Add below lines

PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 4> view param mgr

PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 5>


d) Set up the checkpoint table


i) Create a GLOBALS file

— Global file need to create on target where replicat process running
— Checkpoint tables are only used by the Replicat, so NO need to create on source because it’s one-way replication.


ii) Activate the GLOBALS parameters

GGSCI (rac1.rajasekhar.com) 5> EDIT PARAMS ./GLOBALS

-- Add below entries
GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 7> view params ./GLOBALS

GGSCHEMA OGGUSER
CHECKPOINTTABLE OGGUSER.CHECKPOINT


GGSCI (rac1.rajasekhar.com) 8>

For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI.

GGSCI (rac1.rajasekhar.com) 8> exit
[oracle@rac1 12cogg]$


iii) Add a Replicat checkpoint table

GGSCI (rac1.rajasekhar.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2> ADD CHECKPOINTTABLE OGGUSER.CHECKPOINT

Successfully created checkpoint table OGGUSER.CHECKPOINT.

GGSCI (rac1.rajasekhar.com) 3> 


8. Create the Replicat Group

GGSCI (rac1.rajasekhar.com) 3> ADD REPLICAT R_MYSQL, EXTTRAIL ./dirdat/ra, CHECKPOINTTABLE OGGUSER.CHECKPOINT
REPLICAT added.


GGSCI (rac1.rajasekhar.com) 4>  edit param R_MYSQL

-- Add below lines

REPLICAT R_MYSQL

SOURCEDEFS ./dirdef/chittidefs.sql

USERID ogguser, PASSWORD oracle

DISCARDFILE ./dirout/R_MYSQL.DSC, PURGE

STATOPTIONS RESETREPORTSTATS

MAP CHITTI.*, TARGET CHITTI.*;


GGSCI (rac1.rajasekhar.com) 7> view param R_MYSQL

REPLICAT R_MYSQL

SOURCEDEFS ./dirdef/chittidefs.sql

USERID ogguser, PASSWORD oracle

DISCARDFILE ./dirout/R_MYSQL.DSC, PURGE

STATOPTIONS RESETREPORTSTATS

MAP CHITTI.*, TARGET CHITTI.*;


GGSCI (rac1.rajasekhar.com) 8>


9. On Source (MySQL) Start Manager, Extract and Pump

GGSCI (rac1.rajasekhar.com) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     E_MYSQL     00:00:00      02:11:01
EXTRACT     STOPPED     P_MYSQL     00:00:00      02:10:38


GGSCI (rac1.rajasekhar.com) 2> start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 3> start E_MYSQL

Sending START request to MANAGER ...
EXTRACT E_MYSQL starting


GGSCI (rac1.rajasekhar.com) 4> start P_MYSQL

Sending START request to MANAGER ...
EXTRACT P_MYSQL starting


GGSCI (rac1.rajasekhar.com) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_MYSQL     02:12:25      00:00:05
EXTRACT     RUNNING     P_MYSQL     00:00:00      02:12:08


GGSCI (rac1.rajasekhar.com) 6>


10. On Target (Oracle 12C) start the Manager and Replicat

GGSCI (rac1.rajasekhar.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
REPLICAT    STOPPED     R_MYSQL     00:00:00      00:47:04


GGSCI (rac1.rajasekhar.com) 9> start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 10> start R_MYSQL

Sending START request to MANAGER ...
REPLICAT R_MYSQL starting


GGSCI (rac1.rajasekhar.com) 11>
GGSCI (rac1.rajasekhar.com) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     R_MYSQL     00:00:00      00:00:04


GGSCI (rac1.rajasekhar.com) 16>


11. Test and Verify the Results


INSERT OPERATION


a1) Execute Insert on the Source Oracle Database MySQL

[root@rac1 oracle]# mysql -u CHITTI -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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> USE CHITTI;
Database changed

mysql> select user();
+------------------+
| user()           |
+------------------+
| CHITTI@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>

mysql> INSERT INTO trans_type values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO trans_type values(2,'TEJU');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO trans_type values(3,'RAJ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO trans_type values(4,'SOMU');
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO trans_type values(5,'SREERAM');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>


GGSCI (rac1.rajasekhar.com) 15> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:26:03.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00 <----
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.

GGSCI (rac1.rajasekhar.com) 17>
GGSCI (rac1.rajasekhar.com) 18> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:26:37.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00 <----
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 18>

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SREER |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


a2) Verify Insert Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 17> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:31:09.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00 <---
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 18>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU
         5 SREER

SQL>


UPDATE OPERATION


b1) Execute update on the Source Oracle Database MySQL

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SREER |
+----------+-------+
5 rows in set (0.00 sec)

mysql> update trans_type set trans='SELVA' where trans_id='5';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SELVA |
+----------+-------+
5 rows in set (0.00 sec)

mysql>


GGSCI (rac1.rajasekhar.com) 19> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:46:50.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:47:11.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 21>


b2) Verify Update Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 18> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:48:53.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00
        Total updates                                      1.00 <----
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   6.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 19>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU
         5 SELVA  <------

SQL>


DELETE OPERATION


c1) Execute Delete on the Source Oracle Database MySQL

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | SELVA |
+----------+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM trans_type WHERE TRANS_ID = 5;
Query OK, 1 row affected (0.21 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trans_type;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
+----------+-------+
4 rows in set (0.00 sec)

mysql>

GGSCI (rac1.rajasekhar.com) 21> stats E_MYSQL total

Sending STATS request to EXTRACT E_MYSQL ...

Start of Statistics at 2018-09-05 17:52:42.

Output to ./dirdat/eo:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:10:24 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 22> stats P_MYSQL total

Sending STATS request to EXTRACT P_MYSQL ...

Start of Statistics at 2018-09-05 17:52:50.

Output to ./dirdat/ra:

Extracting from CHITTI.trans_type to CHITTI.trans_type:

*** Total statistics since 2018-09-05 17:23:49 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 23>


c2) Verify Delete Changes on Target Oracle Database Oracle 12c

GGSCI (rac1.rajasekhar.com) 19> stats R_MYSQL total

Sending STATS request to REPLICAT R_MYSQL ...

Start of Statistics at 2018-09-05 17:55:07.

Replicating from CHITTI.trans_type to CHITTI.TRANS_TYPE:

*** Total statistics since 2018-09-05 17:24:11 ***
        Total inserts                                      5.00
        Total updates                                      1.00
        Total deletes                                      1.00 <-----
        Total discards                                     0.00
        Total operations                                   7.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20>

SQL> select * from CHITTI.TRANS_TYPE;

  TRANS_ID TRANS
---------- -----
         1 SUGI
         2 TEJU
         3 RAJ
         4 SOMU

SQL>

GGSCI (rac1.rajasekhar.com) 20> info R_MYSQL

REPLICAT   R_MYSQL   Last Started 2018-09-05 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           13305
Log Read Checkpoint  File ./dirdat/ra000000
                     2018-09-05 17:52:02.000864  RBA 2433


GGSCI (rac1.rajasekhar.com) 21> lag R_MYSQL

Sending GETLAG request to REPLICAT R_MYSQL ...
Last record lag 7 seconds.
At EOF, no more records to process.


GGSCI (rac1.rajasekhar.com) 22>

It’s working !!! 🙂 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com