DBA Hub

📋Steps in this guide1/21

Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c

Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c Contents

oracle Oracle 12cclusteringintermediate
by OracleDba
14 views
1

Overview

Contents 1. Overview
2

Section 2

1. Overview 2. Environment Source (MySQL):

Code/Command (click line numbers to comment):

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

Section 3

Target (Oracle 12c): 3. Install GoldenGate 12C for MySQL

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

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
4

Section 4

4. Install GoldenGate 12C for Oracle 12.1.0.1.0 5. Create MySQL Database and User CHITTI for testing

Code/Command (click line numbers to comment):

1
2
3
We will publish soon about this separatly !!!

We will publish soon about this separatly !!!
5

Section 5

a) On Source (MySQL) -- Create the source database tables b) On Target (Oracle 12c)

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
[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>
6

Section 6

6. OGG Configuration on Source MySQL a) Set Environment

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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>

. 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'
7

Section 7

b) Login to ggsci c) Configure Manager

Code/Command (click line numbers to comment):

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
[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>

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>
8

Section 8

-- Add below lines d) Create Extract Group e) Create Pump Extract Group

Code/Command (click line numbers to comment):

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
49
50
51
-- 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>
9

Section 9

-- CREATE DATA PUMP FOR ORACLE 12C AND REMOTE EXTRACT TRAIL OF 50MB f) Create Source Table Definitions vi defgen_chitti.prm

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- 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>

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]#
10

Section 10

7. OGG Configuration on Target Oracle 12C a) Set Environment b) Login to Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
. 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'
11

Section 11

b) Login to Database DBLOGIN USERID ogguser, PASSWORD oracle c) Configure Manager

Code/Command (click line numbers to comment):

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
[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>

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>
12

Section 12

d) Set up the checkpoint table i) Create a GLOBALS file NO

Code/Command (click line numbers to comment):

1
2
3
4
— 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.
13

Section 13

ii) Activate the GLOBALS parameters iii) Add a Replicat checkpoint table

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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]$

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>
14

Section 14

8. Create the Replicat Group 9. On Source (MySQL) Start Manager, Extract and Pump

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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>

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>
15

Section 15

10. On Target (Oracle 12C) start the Manager and Replicat 11. Test and Verify the Results INSERT OPERATION

Code/Command (click line numbers to comment):

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
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>
16

Section 16

INSERT OPERATION a1) Execute Insert on the Source Oracle Database MySQL a2) Verify Insert Changes on Target Oracle Database Oracle 12c

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
[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>
17

Section 17

select * from CHITTI.TRANS_TYPE; UPDATE OPERATION b1) Execute update on the Source Oracle Database MySQL

Code/Command (click line numbers to comment):

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
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>
18

Section 18

b2) Verify Update Changes on Target Oracle Database Oracle 12c 5 SELVA <------ DELETE OPERATION

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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>

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>
19

Section 19

DELETE OPERATION c1) Execute Delete on the Source Oracle Database MySQL Total deletes 1.00 <----

Code/Command (click line numbers to comment):

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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>
20

Section 20

c2) Verify Delete Changes on Target Oracle Database Oracle 12c Total deletes 1.00 <----- select * from CHITTI.TRANS_TYPE; lag R_MYSQL

Code/Command (click line numbers to comment):

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
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>
21

Section 21

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. 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!