Tuesday, July 8, 2025

Extract and Replicat Configuration

   No comments
  • Introduction: In the previous posts, we had covered the installation of Oracle GoldenGate (OGG) and database configuration for both Oracle and MySQL environments. Please visit those, links are given below. In this article, we will go through the essential steps to configure Extract & Replicat for real time replication.

    • Oracle GoldenGate 23ai Installation for Oracle
    Oracle GoldenGate 23ai Installation for MySQL
    Prepare Database for GoldenGate Replication

  • Prerequisites: Below are the minimum requirements.
    • Oracle GoldenGate software installed on should be installed.
    • Database configuration for GoldenGate should be done on source & target database.

  • Environment Used:
    Server Source (Oracle) Target (MySQL) OGG (Oracle) OGG (MySQL)
    Hostname orcl.oraeasy.com mysqlOGG.oraeasy.com ogg.oraeasy.com mysqlOGG.oraeasy.com
    OS OEL 9 OEL 9 OEL 9 OEL 9
    DB Name ORCL mysqldb NA NA

    Please note that we are using the same server for MySQL Database and for its GoldenGate.

    Below are steps of configuration
    1. Extract configuration.
    2. Distribution path.
    3. Initial Load using csvsql.
    4. Replicat configuration.
  • Now first we will configure the extract at source side:
1. Login into Source Oracle GoldenGate. Follow below to configure Extract. Here we will create a normal extract and will do the intial load manually.

Click on Home and then click + sign in Extraxt section.
Select the Extract type, give its name and click Next.

Provide the Database crdential details, PDB name and trail file name. Then click Next.

Keep the default options and click Next.

Now add the replication schema & table details. Here we are replicating Company table of Test schema. Last click on Create & Run.

Monitor the progress in Notifications.

Now Extract is created & running.


  • Now we need to configure Distribution Path so that trail files can be transferred from Source to Target for repliaction:
    • 1. First create a user on Target side distribution services.

    Click on User Administration and then Click + sign.

    Provide username & its password. Select User Role as Operator and click Submit.


    User has been created.


    2. Now create user with same name at Source side in Path Connection.

    Click on Path Connection and then Click on + sign.


    Provide username & its password. Click on Submit.


    User has been created.


    3. Now on Source side create the Distribution Path.

    Click on Distribution Path and click + sign.


    Provide the path name and click Next.


    Provide the Source side details and click Next.


    Provide the Target side details and click Next.


    Keep the default options and click Next.

    Keep the default options and click Next.

    Click Create & Run.


    Now Distribution Path has been creared.


    We can see the Path running in Receiver Services at target side.


    Please note that Receiver service must be running at target for Distribution Path. You can crosscheck & start if required like below.
    
[oracle@mysqlOGG ~]$ ps -ef | grep recvsrvr
oracle      3853     656  0 12:17 ?        00:00:21 /ogg/ogg23ai_ma/bin/recvsrvr --config /ogg/ogg23ai_sm/var/run/OggMySQL-recvsrvr-config.dat
oracle      6583    6278  1 13:35 pts/2    00:00:00 ./recvsrvr -d /ogg/ogg23ai_deploy
oracle      6600    6278  0 13:36 pts/2    00:00:00 grep --color=auto recvsrvr
[oracle@mysqlOGG ~]$ netstat -tulnp | grep recvsrvr
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:7809            0.0.0.0:*               LISTEN      6583/./recvsrvr
tcp6       0      0 :::7805                 :::*                    LISTEN      3853/recvsrvr
tcp6       0      0 :::7809                 :::*                    LISTEN      6583/./recvsrvr
udp        0      0 0.0.0.0:44001           0.0.0.0:*                           3853/recvsrvr
udp        0      0 0.0.0.0:7805            0.0.0.0:*                           3853/recvsrvr
udp        0      0 0.0.0.0:7809            0.0.0.0:*                           6583/./recvsrvr
[oracle@mysqlOGG ~]$

    

     To start the recevier service
    
[oracle@mysqlOGG ~]$ cd /ogg/ogg23ai_ma/bin/
[oracle@mysqlOGG bin]$ ls
adminclient  cachefiledump  chkptdump      convchk  defgen    emsclnt  keygen   oggca.sh  orapki  recvsrvr  retrace         trailscan
adminsrvr    checkprm       ConfigService  convprm  distsrvr  extract  logdump  oggerr    pmsrvr  replicat  ServiceManager  XAGTask
[oracle@mysqlOGG bin]$ ./recvsrvr -d /ogg/ogg23ai_deploy &

  • Now we need to do intial load from Oracle to MySql Database. In that process we will export the table data till now from Oracle and import the same in MySQL. We will also note the current SCN (System Change Number) of Oracle database, as it will be used in Replicat at target side.

    • 1. First capture the current SCN and table data in a spool file at source side. 
    
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    6564887


SQL> show user
USER is "TEST"
SQL>
SQL> def
DEFINE _DATE           = "05-JUN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCLPDB" (CHAR)
DEFINE _USER           = "TEST" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> SET COLSEP ','
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 1000
SQL> SET TRIMSPOOL ON
SQL> SET FEEDBACK OFF
SQL> SET HEADING ON
SQL> 
SQL> SPOOL /home/oracle/company.csv
SQL> SELECT * FROM COMPANY;

SQL> SPOOL OFF

    2. Now transfer the spool file from Oracle to MySQL Database server. 
    
[oracle@orcl ~]$ scp company.csv mysql@mysqlOGG.oraeasy.com:/var/lib/mysql
mysql@mysqlogg.oraeasy.com's password:
company.csv                           100%  872   360.6KB/s   00:00
[oracle@orcl ~]$

    3. Now we will import the table data in MySQL database. For this we will use csvsql utility from the csvkit suite. So let's install the required software for csvsql. 
    
[root@mysqlOGG ~]# dnf install python3-pip -y
Last metadata expiration check: 1:48:33 ago on Thu 05 Jun 2025 12:44:38 PM IST.
Dependencies resolved.
==============================================================================================================================================================
 Package                               Architecture                     Version                                 Repository                               Size
==============================================================================================================================================================
Installing:
 python3-pip                           noarch                           21.3.1-1.el9                            ol9_appstream                           3.0 M

Transaction Summary
==============================================================================================================================================================
Install  1 Package

Total download size: 3.0 M
Installed size: 8.8 M
Downloading Packages:
python3-pip-21.3.1-1.el9.noarch.rpm                                                                                           1.1 MB/s | 3.0 MB     00:02
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                         1.1 MB/s | 3.0 MB     00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                      1/1
  Installing       : python3-pip-21.3.1-1.el9.noarch                                                                                                      1/1
  Running scriptlet: python3-pip-21.3.1-1.el9.noarch                                                                                                      1/1
  Verifying        : python3-pip-21.3.1-1.el9.noarch                                                                                                      1/1

Installed:
  python3-pip-21.3.1-1.el9.noarch

Complete!
[root@mysqlOGG ~]#
[root@mysqlOGG ~]# pip install csvkit

Collecting csvkit
  Downloading csvkit-2.1.0-py2.py3-none-any.whl (75 kB)
     |████████████████████████████████| 75 kB 190 kB/s
Collecting agate-dbf>=0.2.3
  Downloading agate_dbf-0.2.3-py2.py3-none-any.whl (3.6 kB)
Collecting openpyxl
  Downloading openpyxl-3.1.5-py2.py3-none-any.whl (250 kB)
     |████████████████████████████████| 250 kB 4.1 MB/s
Collecting xlrd
  Downloading xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
     |████████████████████████████████| 96 kB 3.3 MB/s
Collecting agate-sql>=0.7.0
  Downloading agate_sql-0.7.2-py2.py3-none-any.whl (7.3 kB)
Collecting sqlalchemy
  Downloading sqlalchemy-2.0.41-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.2 MB)
     |████████████████████████████████| 3.2 MB 4.0 MB/s
Collecting agate-excel>=0.4.0
  Downloading agate_excel-0.4.1-py2.py3-none-any.whl (7.1 kB)
Collecting agate>=1.12.0
  Downloading agate-1.13.0-py2.py3-none-any.whl (95 kB)
     |████████████████████████████████| 95 kB 2.0 MB/s
Collecting importlib_metadata
  Downloading importlib_metadata-8.7.0-py3-none-any.whl (27 kB)
Collecting pytimeparse>=1.1.5
  Downloading pytimeparse-1.1.8-py2.py3-none-any.whl (10.0 kB)
Collecting Babel>=2.0
  Downloading babel-2.17.0-py3-none-any.whl (10.2 MB)
     |████████████████████████████████| 10.2 MB 935 kB/s
Collecting parsedatetime!=2.5,>=2.1
  Downloading parsedatetime-2.6-py3-none-any.whl (42 kB)
     |████████████████████████████████| 42 kB 706 kB/s
Collecting python-slugify>=1.2.1
  Downloading python_slugify-8.0.4-py2.py3-none-any.whl (10 kB)
Collecting leather>=0.3.2
  Downloading leather-0.4.0-py2.py3-none-any.whl (30 kB)
Collecting isodate>=0.5.4
  Downloading isodate-0.7.2-py3-none-any.whl (22 kB)
Collecting dbfread>=2.0.5
  Downloading dbfread-2.0.7-py2.py3-none-any.whl (20 kB)
Collecting olefile
  Downloading olefile-0.47-py2.py3-none-any.whl (114 kB)
     |████████████████████████████████| 114 kB 4.4 MB/s
Collecting et-xmlfile
  Downloading et_xmlfile-2.0.0-py3-none-any.whl (18 kB)
Collecting typing-extensions>=4.6.0
  Downloading typing_extensions-4.14.0-py3-none-any.whl (43 kB)
     |████████████████████████████████| 43 kB 1.5 MB/s
Collecting greenlet>=1
  Downloading greenlet-3.2.2-cp39-cp39-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl (579 kB)
     |████████████████████████████████| 579 kB 6.7 MB/s
Collecting zipp>=3.20
  Downloading zipp-3.22.0-py3-none-any.whl (9.8 kB)
Collecting text-unidecode>=1.3
  Downloading text_unidecode-1.3-py2.py3-none-any.whl (78 kB)
     |████████████████████████████████| 78 kB 2.5 MB/s
Installing collected packages: text-unidecode, typing-extensions, pytimeparse, python-slugify, parsedatetime, leather, isodate, greenlet, et-xmlfile, Babel, zipp, xlrd, sqlalchemy, openpyxl, olefile, dbfread, agate, importlib-metadata, agate-sql, agate-excel, agate-dbf, csvkit
Successfully installed Babel-2.17.0 agate-1.13.0 agate-dbf-0.2.3 agate-excel-0.4.1 agate-sql-0.7.2 csvkit-2.1.0 dbfread-2.0.7 et-xmlfile-2.0.0 greenlet-3.2.2 importlib-metadata-8.7.0 isodate-0.7.2 leather-0.4.0 olefile-0.47 openpyxl-3.1.5 parsedatetime-2.6 python-slugify-8.0.4 pytimeparse-1.1.8 sqlalchemy-2.0.41 text-unidecode-1.3 typing-extensions-4.14.0 xlrd-2.0.1 zipp-3.22.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[root@mysqlOGG ~]#

[root@mysqlOGG ~]# pip install mysql-connector-python

Collecting mysql-connector-python
  Downloading mysql_connector_python-9.3.0-cp39-cp39-manylinux_2_28_x86_64.whl (33.8 MB)
     |████████████████████████████████| 33.8 MB 4.5 MB/s
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-9.3.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[root@mysqlOGG ~]#

    4. Now format the csv file so that it will work for csvsql utility . 
    
[mysql@mysqlOGG ~]$ head company.csv
EMP_ID,NAME,COMPANY
       104,Vijay                                       ,PATH
       105,Vikas                                       ,INFOSYS
       101,Yash                                        ,WIPRO
       102,Vijay                                       ,AIRTEL
       103,Riya                                        ,TCS
SQL> spool off
[mysql@mysqlOGG ~]$ sed -i '/^SQL> spool off/d' final_company.csv
[mysql@mysqlOGG ~]$
[mysql@mysqlOGG ~]$ head final_company.csv
EMP_ID,NAME,COMPANY
       104,Vijay              ,PATH
       105,Vikas              ,INFOSYS
       101,Yash               ,WIPRO
       102,Vijay              ,AIRTEL
       103,Riya               ,TCS

    5. Now we will use csvsql utility to import the table data. Here we are using user as "test", table name as "company" and database name as "mysqldb". 
    
[mysql@mysqlOGG ~]$ csvsql --db "mysql+mysqlconnector://test:India#123@localhost:3306/mysqldb"   --tables company   --insert   --no-inference   --snifflimit 0   --delimiter ','   company.csv
[mysql@mysqlOGG ~]$
[mysql@mysqlOGG ~]$ mysql -u test -p mysqldb
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.4.5 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

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 * from company;
+--------+------------------------------+---------+
| EMP_ID | NAME                         | COMPANY |
+--------+------------------------------+---------+
|    104 | Vijay                        | PATH    |
|    105 | Vikas                        | INFOSYS |
|    101 | Yash                         | WIPRO   |
|    102 | Vijay                        | AIRTEL  |
|    103 | Riya                         | TCS     |
+--------+------------------------------+---------+
5 rows in set (0.00 sec)

  • Now we need to configure the Replicat process.

    • 1. Login into Target Oracle GoldenGate. Follow below to configure Replicat.

    Click on Home and then click + sign in Replicat section.

    Provide the Replicat type, name & its description. Click Next.

    Provide the trail name, credential details & checkpoint table. Click Next.

    Keep the default options & click Next.

    Now configure the parameter file & add replication table details. Last click on Create.

    Now Replicat has been created.
    2. Now we need to start the Replicat. We will do this with Start with Option and profiding the SCN which was captured at source.

    Click on the 3 dots and then click on Start with Options.

    Now select After CSN as Start point and provide SCN number in CSN section. Last click Submit.

    Now wait for sometimes and Replicat lag will be 0.

    Now GoldenGate configuration has been completed between Oracle to MySQL. We can also verify the real time replication by querying the table data on both sides.
    

    At Source (Oracle)
    
SQL> set lines 333 pages 333
SQL> col name for a20
SQL> col company for a30
SQL> select * from company;

    EMP_ID NAME                 COMPANY
---------- -------------------- ------------------------------
       104 Vijay                PATH
       105 Vikas                INFOSYS
       106 sachin               CISCO
       107 Ajay                 XYZ
       101 Yash                 WIPRO
       102 Vijay                AIRTEL
       103 Riya                 TCS

7 rows selected.

    


    At Target (MySQL)
    

mysql> select * from company;
+--------+------------------+---------+
| EMP_ID | NAME             | COMPANY |
+--------+------------------+---------+
|    104 | Vijay            | PATH    |
|    105 | Vikas            | INFOSYS |
|    101 | Yash             | WIPRO   |
|    102 | Vijay            | AIRTEL  |
|    103 | Riya             | TCS     |
|    106 | sachin           | CISCO   |
|    107 | Ajay             | XYZ     |
+--------+------------------+---------+
7 rows in set (0.09 sec)


    Thanks for visiting!!

    0 comments:

    Post a Comment