MySQL to Oracle Heterogeneous GoldenGate Unidirectional Replication
In this article we will see MySQL to Oracle GoldenGate Unidirectional Replication.
Prerequisites for Replication:
1.Create two VM’s on your machine
2.Install MySQL on Source Sever.
3.Install Oracle Software and Create database on target.
4.Make sure two Vm’s are pinging each other.
Node Details :
MySQL(SOURCE)
The MySQL database name is ggadmin, and uses the innodb engine.
The MySQL server name is mysql1.
GG2(TARGET)
192.168.0.40 gg2.orcl.com gg2
Database Name :ORCl
Instance Name : orcl
Download Goldengate Binaries
Download GoldenGate binaries from Oracle.com using below link(Oracle)
https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
Make sure that you are using the correct OS version of binaries.
MySQL SOURCE ;
Starting on the MySQL database server, mysql1, install the GoldenGate software.
Start the MySQL
|
1 2 |
[root@mysql ~]# service mysqld start Redirecting to /bin/systemctl start mysqld.service |
Check MySQL is riunning or not
|
1 2 3 |
[root@mysql ~]# ps -ef |grep mysql mysql 108336 1 4 01:21 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid root 108365 108186 0 01:21 pts/2 00:00:00 grep --color=auto mysql |
Check MySQL server status
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@mysql ~]# service mysqld status Redirecting to /bin/systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-06-06 01:21:38 EDT; 37s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 108333 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 108256 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 108336 (mysqld) CGroup: /system.slice/mysqld.service └─108336 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Jun 06 01:21:33 mysql.orcl.com systemd[1]: Starting MySQL Server... Jun 06 01:21:38 mysql.orcl.com systemd[1]: Started MySQL Server. |
Connect to MySQL and check the databases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[root@mysql ~]# mysql -u root -pWElcome@@1 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.26 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
set the Bash_profile
|
1 2 3 |
[root@mysql ~]# vi .bash_profile export MYSQL_HOME=/usr/bin export LD_LIBRARY_PATH=/opt |
Run the bash_profile
|
1 |
[root@mysql ~]# . .bash_profile |
Edit my.cnf file and add the following parameters
|
1 2 3 4 5 6 7 8 9 10 |
[root@mysql ~]# vi /etc/my.cnf server-id=1 [mysqld] log-bin=/var/lib/mysql/mgg-bin max_binlog_size=4096 binlog_format=row socket=/tmp/mysql.sock [client] socket=/tmp/mysql.sock |
Now copy GoldenGate software into /opt
|
1 2 3 4 |
[root@mysql opt]# ls -l total 68456 -rw-r--r--. 1 root root 70098487 Jun 1 21:10 191000_ggs_Linux_x64_MySQL_64bit.zip drwxr-xr-x. 2 root root 6 Oct 30 2018 rh |
Unzip the S/W file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[root@mysql opt]# unzip 191000_ggs_Linux_x64_MySQL_64bit.zip Archive: 191000_ggs_Linux_x64_MySQL_64bit.zip inflating: ggs_Linux_x64_MySQL_64bit.tar inflating: OGG-19.1.0.0-README.txt inflating: OGG_WinUnix_Rel_Notes_19.1.0.0.0.pdf [root@mysql opt]# ls -latr total 346208 drwxr-xr-x. 2 root root 6 Oct 30 2018 rh -rw-rw-r--. 1 root root 284221440 May 8 23:54 ggs_Linux_x64_MySQL_64bit.tar -rwxrwxrwx. 1 root root 189027 May 10 16:55 OGG_WinUnix_Rel_Notes_19.1.0.0.0.pdf -rwxrwxrwx. 1 root root 1412 May 10 17:49 OGG-19.1.0.0-README.txt -rw-r--r--. 1 root root 70098487 Jun 1 21:10 191000_ggs_Linux_x64_MySQL_64bit.zip dr-xr-xr-x. 17 root root 224 Jun 6 00:11 .. drwxr-xr-x. 3 root root 172 Jun 6 03:30 . |
untar the file
|
1 |
[root@mysql opt]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar |
Run the ./ggsci command and create sub directories
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[root@mysql opt]# ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447 Linux, x64, 64bit (optimized), MySQL Enterprise on May 8 2019 20:42:57 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (mysql.orcl.com) 1> create subdirs Creating subdirectories under current directory /opt Parameter file /opt/dirprm: created. Report file /opt/dirrpt: created. Checkpoint file /opt/dirchk: created. Process status files /opt/dirpcs: created. SQL script files /opt/dirsql: created. Database definitions files /opt/dirdef: created. Extract data files /opt/dirdat: created. Temporary files /opt/dirtmp: created. Credential store files /opt/dircrd: created. Masterkey wallet files /opt/dirwlt: created. Dump files /opt/dirdmp: created. |
Check manager sunning or not , If not start the Mgr after specifying the port
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
GGSCI (mysql.orcl.com) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (mysql.orcl.com) 3> edit param mgr PORT 7811 GGSCI (mysql.orcl.com) 4> start mgr GGSCI (mysql.orcl.com) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING |
Connect as Root user and create users
|
1 2 3 4 5 6 7 |
mysql>CREATE USER 'ogguser1'@'localhost' IDENTIFIED BY 'WElcome@@1'; mysql>CREATE USER 'oggadm1'@'localhost' IDENTIFIED BY 'WElcome@@1'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'ogguser1'@'localhost'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'oggadm1'@'localhost'; |
Create database
|
1 |
CREATE DATABASE oggdb1; |
Connect to oggdb1 and create objects
|
1 2 3 4 5 6 7 |
mysql> USE oggdb1; mysql> CREATE TABLE oggdb1.DEPT ( DEPTNO TINYINT, DNAME VARCHAR(14), LOC VARCHAR(13) ); |
Create unique index on dept table
|
1 |
mysql> CREATE UNIQUE INDEX PK_DEPT ON oggdb1.DEPT(DEPTNO); |
Verify that the MySQL ODBC connector is installed. As the root Linux user, run the following package manager query command:
login as root on OS:
|
1 |
rpm -qa odbc |
If it doesn’t exist, as root, create the /usr/local/etc/odbc.ini file. Add (or verify) the following in the file:
vi /usr/local/etc/odbc.ini
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
gedit /usr/local/etc/odbc.ini [ODBC Data Sources] OGGDB1 = MyODBC 3.51 Driver DSN OGGDB1 Driver = /usr/lib/libmyodbc3.so Description = Connector/ODBC 3.51 Driver DSN Server = localhost Port = 3306 User = oggadm1 Password = WElcome@@1 Database = oggdb1 Option = 3 Socket = /tmp/mysql.sock |
Connect to GGSCI on the Oracle server and create the initial load extract. Since these are small tables, we will use GoldenGate to do the initial load. If these were large tables, we would need to use a different, faster loading method such as load data infile. Also, note that the parameters point to the target (MySQL) database.
Login as oggadm1
|
1 2 3 |
/opt]# ./ggsci GGSCI>dblogin SourceDB oggdb1, UserID oggadm1, Password WElcome@@1 |
Note : In case of Issues with login with mysql please follow below steps
Why I get the “Can’t connect to local MySQL Server through socket…” error?Details: The error is shown as follows:
Answer: The reason is the file doesn’t exist in the /tmp folder. Oracle Golden Gate for MySQL by default requires the access of the MySQL socket file. By default, it is /tmp/mysql.sock If the file is not in the folder you can create a symbolic link as follows:
If you have multiple MySQL instances on the same server (For example, in my environment, I have MySQL 5.5, 5.6 and 5.7 setup in one machine). you can set the MYSQL_UNIX_PORT environment variable as follows:
When using Oracle GoldenGate you can use the SETENV set as follows:
|
Edit extmysql file
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 6>edit param extmysql extract extmysql SETENV(MYSQL_HOME='/usr/bin') SETENV(MYSQL_UNIX_PORT='/var/lib/mysql/mysql.sock') dboptions host localhost, connectionport 3306 sourcedb oggdb1, userid oggadm1, password WElcome@@1 exttrail /opt/dirdat/lt TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/mgg-bin.index table oggdb1.DEPT; |
Add extract process
|
1 2 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 7> add extract extmysql,tranlog,begin now EXTRACT added. |
Add extrail file to extract process
|
1 2 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 8> add exttrail /opt/dirdat/lt,extract extmysql EXTTRAIL added. |
Start Extract process
|
1 2 3 4 5 6 7 8 9 10 11 12 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 9> start extmysql Sending START request to MANAGER ... EXTRACT EXTMYSQL starting GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTMYSQL 00:00:17 00:00:07 |
Add extract process for datapump(dpmysql)
|
1 2 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 11> add extract dpmysql,exttrailsource /opt/dirdat/lt EXTRACT added. |
Add remote trail file
|
1 2 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 12> add rmttrail /oraeng/app/oracle/product/ogg_src/dirdat/my,extract dpmysql RMTTRAIL added. |
Edit Datapump param file
|
1 2 3 4 5 6 7 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 13> edit param dpmysql EXTRACT DPMYSQL RMTHOST 192.168.0.40,MGRPORT 7809 RMTTRAIL /oraeng/app/oracle/product/ogg_src/dirdat/my PASSTHRU table oggdb1.DEPT; |
Start dpmysql
|
1 2 3 4 5 6 7 8 9 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 14> start dpmysql Sending START request to MANAGER ... EXTRACT DPMYSQL starting GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPMYSQL 00:00:00 00:07:21 EXTRACT RUNNING EXTMYSQL 00:00:00 00:00:05 |
Connect to mysql prompt and insert record into dept table
|
1 2 3 4 5 6 7 8 9 10 |
mysql> insert into oggdb1.DEPT values (10,'SALES','CAN'); Query OK, 1 row affected (0.01 sec) mysql> select * from oggdb1.DEPT; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | SALES | CAN | +--------+-------+------+ 1 row in set (0.00 sec) |
Connect ggsci of mysql and cheek stats
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 16> stats dpmysql Sending STATS request to EXTRACT DPMYSQL ... Start of Statistics at 2019-06-06 05:33:21. Output to /oraeng/app/oracle/product/ogg_src/dirdat/my: Extracting from oggdb1.DEPT to oggdb1.DEPT: *** Total statistics since 2019-06-06 05:33:00 *** 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 2019-06-06 05:33:00 *** 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 2019-06-06 05:33:00 *** 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 2019-06-06 05:33:00 *** 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. |
ORACLE TARGET :
In Target side keep ready with Oracle Software installation and Oracle database Creation.
Steps to Install Oracle GoldenGate
Download GoldenGate Binaries
Download GoldenGate binaries from Oracle.com using below link
https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
Make sure that you are using the correct OS version of binaries.
Setting bash_profile
Export the path in the bash profile
vi .bash_profile
|
1 2 3 4 5 6 |
export ORACLE_SID=primary export ORACLE_HOME=/u01/app/oracle/product/12.1.0 export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_NET=$ORACLE_HOME/network/admin. .bash_p export GGS_HOME=/oraeng/app/oracle/product/ogg_trg export LD_LIBRARY_PATH=$ORACLE_HOME/lib |
Run the bash_profile file
|
1 |
[oracle@gg1 ~]$ . .bash_profile |
Check instance info :
|
1 2 3 4 5 |
SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PRIMARY - primary@gg1.orcl.com - primary - 12.1.0.2.0 - READ WRITE - 05-JUN-2019 18:00:05 - PRIMARY |
Create directory for GoldenGate home :
Create directory for gghome
|
1 2 3 |
[root@gg1 ~]# mkdir -p /oraeng/app/oracle/product/ogg_src [root@gg1 ~]# chown -R oracle:oinstall /oraeng/app/oracle/product/ogg_src [root@gg1 ~]# chmod -R 755 /oraeng/app/oracle/product/ogg_src |
Copy the downloaded file to /opt location Using Winscp
Unzip GoldenGate Software :
Check file in /opt dir :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@gg1 opt]$ ls -ltr total 532148 drwxr-xr-x. 3 oracle dba 4096 May 9 21:30 fbo_ggs_Linux_x64_shiphome -rwxrwxrwx. 1 oracle dba 189027 May 11 02:25 OGG_WinUnix_Rel_Notes_19.1.0.0.0.pdf -rwxrwxrwx. 1 oracle dba 1412 May 11 03:19 OGG-19.1.0.0-README.txt -rw-r--r--. 1 root root 544713582 May 31 08:46 191000_fbo_ggs_Linux_x64_shiphome.zip GOTO gbo_ directory [oracle@gg1 opt]$ cd fbo_ggs_Linux_x64_shiphome/ [oracle@gg1 fbo_ggs_Linux_x64_shiphome]$ ls -ltr total 4 drwxr-xr-x. 5 oracle dba 4096 May 9 21:30 Disk1 GOTO Disk1 directory [oracle@gg1 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/ [oracle@gg1 Disk1]$ ls -ltr total 6 drwxr-xr-x.4 oracle dba 4096 May 9 21:29 install drwxr-xr-x.12 oracle dba 4096 May 9 21:30 stage -rwxr-xr-x.1 oracle dba 918 May 9 21:30 runInstaller drwxrwxr-x.2 oracle dba 4096 May 9 21:30 response |
Now do runinstaller as Oracle user :
|
1 2 3 4 5 6 7 8 |
[oracle@gg1 Disk1]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 3551 MB Passed Checking swap space: must be greater than 150 MB. Actual 7999 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-06-04_06-23-03PM. Please wait ... |
On step 1 choose version of your oracle software
Click on Next
Browse location for GG_HOME
Start Manager with any port number(Default 7809)
Click on Next
Check summary
Click on Install
Installation process and extracts files into target directory
Finally GG will successfully installed on GG1
Click on Close
Goto GGS_HOME and connect to ./ggsci
Give info all to check manager process is running or not
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@ogg ogg_src]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.0 OGGCORE_19.1.0.0.0_PLATFORMS_190508.1447_FBO Linux, x64, 64bit (optimized), Oracle 12c on May 9 2019 06:21:59 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (ogg.orcl.com) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING |
Open New terminal for SQL prompt On GG1 :
Connect to database and create user and tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@gg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 4 18:58:11 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing option SYS>>grant dba to Ktexperts identified by ktexperts; Grant succeeded. SYS>>conn ktexperts/ktexperts Connected. KTEXPERTS>> KTEXPERTS>>create table dept (deptno number,dname varchar2(10),loc varchar2(15)); Table created. Elapsed: 00:00:00.20 KTEXPERTS>>alter table dept add primary key(deptno); Table altered. Elapsed: 00:00:00.56 KTEXPERTS>> KTEXPERTS>>SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME=' DEPT’; CONSTRAINT_NAME CONSTRAINT_TYPE --------------------------- ----------------------------- SYS_C005224 |
Enable the parameter for GoldenGate replication
|
1 2 |
SYS>>alter system set enable_goldengate_replication=true; System altered. |
Create admin for GoldeGate
|
1 2 3 4 5 6 |
SYS>>create user ggadmin identified by ggadmin; User created. Elapsed: 00:00:00.09 SYS>>grant dba to ggadmin; Grant succeeded. Elapsed: 00:00:00.01 |
Now execute below Package for additional privs
|
1 2 3 |
SYS>>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggadmin',privilege_type=>'CAPTURE',grant_optional_privileges=>'*'); PL/SQL procedure successfully completed. Elapsed: 00:00:06.34 |
Add supplemental logdata on columns
|
1 2 |
SYS>>alter database add supplemental log data(all) columns; Database altered. |
GGSCI Prompt :
Login as ggamdin user
|
1 2 |
GGSCI (ogg.orcl.com) 2> dblogin userid ggadmin@orcl,password ggadmin Successfully logged into database |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
create wallet and add credential store to create alias name GGSCI (ogg.orcl.com) 3> create wallet Created wallet. Opened wallet. GGSCI (ogg.orcl.com) 4> add credentialstore Credential store created. GGSCI (ogg.orcl.com) 5> alter credentialstore add user ggadmin@orcl,password ggadmin,alias oggadmin_trg Credential store altered. |
Add Replicat process
|
1 2 |
GGSCI (ogg.orcl.com as ggadmin@orcl) 6> add replicat repmysql,integrated exttrail /oraeng/app/oracle/product/ogg_src/dirdat/my REPLICAT (Integrated) added. |
Edit repmysql param file
|
1 2 3 4 5 6 7 8 9 |
GGSCI (ogg.orcl.com as ggadmin@orcl) 7> edit param repmysql REPLICAT repmysql SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') SETENV(ORACLE_SID='orcl') SETENV(TNS_ADMIN='/home/oracle') useridalias oggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP oggdb1.DEPT,target ktexperts.dept; |
Start the replicate process
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
GGSCI (ogg.orcl.com as ggadmin@orcl) 8> start repmysql Sending START request to MANAGER ... REPLICAT REPMYSQL starting GGSCI (ogg.orcl.com as ggadmin@orcl) 9> ! start repmysql REPLICAT REPMYSQL is already running. GGSCI (ogg.orcl.com as ggadmin@orcl) 10> info all Program Stats Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPMYSQL 00:00:00 00:00:05 |
check the stats repmysql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
GGSCI (ogg.orcl.com as ggadmin@orcl) 11> stats repmysql Sending STATS request to REPLICAT REPMYSQL ... Start of Statistics at 2019-06-06 20:16:38. 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 oggdb1.DEPT to KTEXPERTS.DEPT: *** Total statistics since 2019-06-06 20:11:02 *** 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 2019-06-06 20:11:02 *** 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 2019-06-06 20:11:02 *** 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 2019-06-06 20:11:02 *** 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. |
INSERT STATEMENT :
MySQL Source :
|
1 2 |
mysql>insert into DEPT values(20,'MARK','IND'); Query OK, 1 row affected (0.00 sec) |
|
1 2 3 4 5 6 7 8 |
mysql>select * from oggdb1.DEPT; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | SALES | CAN | | 20 | MARK | IND | +--------+-------+------+ 2 rows in set (0.00 sec) |
Oracle Target :
|
1 2 3 4 5 |
KTEXPERTS>>select * from dept; DEPTNO DNAME LOC ---------- ---------- --------------- 10 SALES CAN 20 MARK IND |
UPDATE STATEMENT :
MySQL Source :
|
1 2 3 |
mysql>update DEPT set loc='ÚK' where deptno=10; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
1 2 3 4 5 6 7 8 |
mysql>select * from oggdb1.DEPT; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | SALES | UK | | 20 | MARK | IND | +--------+-------+------+ 2 rows in set (0.00 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
GGSCI (mysql.orcl.com DBLOGIN as oggadm1) 17> stats dpmysql Sending STATS request to EXTRACT DPMYSQL ... Start of Statistics at 2019-06-06 05:33:21. Output to /oraeng/app/oracle/product/ogg_src/dirdat/my: Extracting from oggdb1.DEPT to oggdb1.DEPT: *** Total statistics since 2019-06-06 05:33:00 *** Total inserts 2.00 Total updates 1.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 3.00 |
Oracle Target :
|
1 2 3 4 5 |
KTEXPERTS>>select * from dept; DEPTNO DNAME LOC ---------- ---------- --------------- 10 SALES UK 20 MARK IND |
DELETE STATEMENT :
MySQL Source
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql>select * from DEPT +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | SALES | UK | | 20 | MARK | IND | +--------+-------+------+ 2 rows in set (0.00 sec) mysql>DELETE FROM DEPT WHERE DEPTNO=20; Query OK, 1 row affected (0.21 sec) mysql>select * from DEPT +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | SALES | UK | +--------+-------+------+ 1 row in set (0.00 sec) |
Oracle Target :
|
1 2 3 4 |
KTEXPERTS>>select * from dept; DEPTNO DNAME LOC ------ ---------- --------------- 10 SALES UK |
Make changes and verify that they are being replicated, use the same method we used for Oracle.
Debugging notes: The file ggserr.log is in the GoldenGate home directory. It can be helpful to tail the file during the entire process on both the Oracle and MySQL server to identify any errors.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
Ajay Kumar
Hi All
If anyone needs GG Training and also needs to configure GG for heterogeneous Databases then Please contact Ashish..
Details :Ashish Agarwal
Email id :ashishagarwalag@gmail.com
YouTube Channel:
https://www.youtube.com/c/ashishagarwal_gg
Linkedin:
https://www.linkedin.com/in/ashish-agarwal-a1399663/