Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c
Contents
______________________________________________________________________________________________________________
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
a1) Execute Insert on the Source Oracle Database MySQL
a2) Verify Insert Changes on Target Oracle Database Oracle 12C
b1) Execute update on the Source Oracle Database MySQL
b2) Verify Update Changes on Target Oracle Database Oracle 12c
c1) Execute Delete on the Source Oracle Database MySQL
c2) Verify Delete Changes on Target Oracle Database Oracle 12c
Configure Oracle GoldenGate to perform one-way replication from MySQL 5.5 database to an Oracle 12c database on same server
Source (MySQL):
Server Name : RAC1.DBA Learning Hub.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.DBA Learning Hub.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
[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>
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
. 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'
[root@rac1 ~]# cd /home/oracle [root@rac1 oracle]# . mysql.env [root@rac1 oracle]# ggsci Oracle GoldenGate Command Interpreter for MySQL Version 12.1.2. OGGCORE_12.1.2.0.0_PLATFORMS_ 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.DBA Learning Hub.com) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (rac1.DBA Learning Hub.com) 3>
GGSCI (rac1.DBA Learning Hub.com) 4> edit param mgr -- Add below lines PORT 15100 DYNAMICPORTLIST 0 PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10 GGSCI (rac1.DBA Learning Hub.com) 5> view params mgr PORT 15100 DYNAMICPORTLIST 0 PURGEOLDEXTRACTS ./dirdat/eo, USECHECKPOINTS, MINKEEPFILES 10 GGSCI (rac1.DBA Learning Hub.com) 6>
-- CREATE EXTRACT AND LOCAL EXTRACT TRAIL OF 50MB GGSCI (rac1.DBA Learning Hub.com) 6> ADD EXTRACT E_MYSQL, VAM, BEGIN NOW EXTRACT added. GGSCI (rac1.DBA Learning Hub.com) 7> ADD EXTTRAIL ./dirdat/eo, EXTRACT E_MYSQL, MEGABYTES 50 EXTTRAIL added. GGSCI (rac1.DBA Learning Hub.com) 8> GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.com) 24>
-- CREATE DATA PUMP FOR ORACLE 12C AND REMOTE EXTRACT TRAIL OF 50MB GGSCI (rac1.DBA Learning Hub.com) 25> ADD EXTRACT P_MYSQL, EXTTRAILSOURCE ./dirdat/eo EXTRACT added. GGSCI (rac1.DBA Learning Hub.com) 26> ADD RMTTRAIL ./dirdat/ra, EXTRACT P_MYSQL, MEGABYTES 50 RMTTRAIL added. GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.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. OGGCORE_12.1.2.0.0_PLATFORMS_ 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.DBA Learning Hub.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
. 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'
[oracle@rac1 ~]$ . 12c.env [oracle@rac1 ~]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2. OGGCORE_12.1.2.0.0_PLATFORMS__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.DBA Learning Hub.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle Successfully logged into database. GGSCI (rac1.DBA Learning Hub.com) 2>
GGSCI (rac1.DBA Learning Hub.com) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (rac1.DBA Learning Hub.com) 3> edit param mgr -- Add below lines PORT 15000 DYNAMICPORTLIST 0 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS GGSCI (rac1.DBA Learning Hub.com) 4> view param mgr PORT 15000 DYNAMICPORTLIST 0 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS GGSCI (rac1.DBA Learning Hub.com) 5>
d) Set up the checkpoint table
— 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.DBA Learning Hub.com) 5> EDIT PARAMS ./GLOBALS -- Add below entries GGSCHEMA OGGUSER CHECKPOINTTABLE OGGUSER.CHECKPOINT GGSCI (rac1.DBA Learning Hub.com) 7> view params ./GLOBALS GGSCHEMA OGGUSER CHECKPOINTTABLE OGGUSER.CHECKPOINT GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 8> exit [oracle@rac1 12cogg]$
iii) Add a Replicat checkpoint table
GGSCI (rac1.DBA Learning Hub.com) 1> DBLOGIN USERID ogguser, PASSWORD oracle Successfully logged into database. GGSCI (rac1.DBA Learning Hub.com) 2> ADD CHECKPOINTTABLE OGGUSER.CHECKPOINT Successfully created checkpoint table OGGUSER.CHECKPOINT. GGSCI (rac1.DBA Learning Hub.com) 3>
GGSCI (rac1.DBA Learning Hub.com) 3> ADD REPLICAT R_MYSQL, EXTTRAIL ./dirdat/ra, CHECKPOINTTABLE OGGUSER.CHECKPOINT REPLICAT added. GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.com) 8>
9. On Source (MySQL) Start Manager, Extract and Pump
GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 2> start mgr Manager started. GGSCI (rac1.DBA Learning Hub.com) 3> start E_MYSQL Sending START request to MANAGER ... EXTRACT E_MYSQL starting GGSCI (rac1.DBA Learning Hub.com) 4> start P_MYSQL Sending START request to MANAGER ... EXTRACT P_MYSQL starting GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 6>
10. On Target (Oracle 12C) start the Manager and Replicat
GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 9> start mgr Manager started. GGSCI (rac1.DBA Learning Hub.com) 10> start R_MYSQL Sending START request to MANAGER ... REPLICAT R_MYSQL starting GGSCI (rac1.DBA Learning Hub.com) 11> GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 16>
11. Test and Verify the Results
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.DBA Learning Hub.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.DBA Learning Hub.com) 17> GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.com) 18> SQL> select * from CHITTI.TRANS_TYPE; TRANS_ID TRANS ---------- ----- 1 SUGI 2 TEJU 3 RAJ 4 SOMU 5 SREER SQL>
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.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.com) 21>
b2) Verify Update Changes on Target Oracle Database Oracle 12c
GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 19> SQL> select * from CHITTI.TRANS_TYPE; TRANS_ID TRANS ---------- ----- 1 SUGI 2 TEJU 3 RAJ 4 SOMU 5 SELVA <------ SQL>
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.DBA Learning Hub.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.DBA Learning Hub.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.DBA Learning Hub.com) 23>
c2) Verify Delete Changes on Target Oracle Database Oracle 12c
GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.com) 20> SQL> select * from CHITTI.TRANS_TYPE; TRANS_ID TRANS ---------- ----- 1 SUGI 2 TEJU 3 RAJ 4 SOMU SQL> GGSCI (rac1.DBA Learning Hub.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: RBA 2433 GGSCI (rac1.DBA Learning Hub.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.DBA Learning Hub.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,
Email: