接上篇，
运行测试事务
现在我们准备在Amazon RDS MySQL数据库（ Master Source ）上运行一些事务，并将其由MySQL Native复制复制到Amazon EC2 MySQL（ 中间源 ）从属数据库，然后从EC2 MySQL从属数据库复制到Oracle DBaaS（ 目标 ）。
在这个例子中，我们从源和目标的空表开始。
通过Amazon EC2实例上的mysql检查源表（Amazon RDS MySQL数据库）
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1303Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;
+———-+| count(*) |+———-+| 0 |+———-+1 row in set (0.00 sec)+———-+| count(*) |+———-+| 0 |+———-+1 row in set (0.00 sec)
通过Amazon EC2实例上的mysql检查中间源表（Amazon EC2 MySQL数据库）
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h localhost -u root -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 16Server version: 5.6.35-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;
+———-+| count(*) |+———-+| 0 |+———-+1 row in set (0.00 sec)+———-+| count(*) |+———-+| 0 |+———-+1 row in set (0.00 sec)
从GGCS实例检查目标表
[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ sqlplus mp_tcustdb@target/mp_tcustdb <<EOFselect count(*) from TCUSTMER;select count(*) from TCUSTORD;EOF
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 01:54:25 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Last Successful login time: Sat Apr 29 2017 01:37:20 -04:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL>COUNT(*)———-0SQL>COUNT(*)———-0SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
注意 ：当GGCS实例被配置时，在GGCS实例的tnsnames.ora中创建一个默认的TNS网络服务名称，即“ 目标 ”网络服务。 这是连接网络服务名称，其中包含与GGCS实例配置时相关联的数据库的连接信息。 tnsnames.ora的位置可以在/ u01 app oracle oci network admin目录下找到。
以下是在GGCS实例配置后生成的tnsnames.ora文件的示例：
#GGCS generated file
target =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ateam-dbcs-aa)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GGPDBAA.a228251.oraclecloud.internal)
)
)
通过Amazon EC2上的mysql在源表（Amazon RDS MySQL）上运行测试事务
我们从表中插入事务开始 - 在每个表上插入5个记录，共有10个操作，因为我们有2个表。
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1303Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TCUSTMER VALUES (‘WILL’,’BG SOFTWARE CO.’,’SEATTLE’,’WA’);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO TCUSTMER VALUES (‘JANE’,’ROCKY FLYER INC.’,’DENVER’,’CO’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TCUSTORD VALUES (‘WILL’,’1994-09-30 15:33:00′,’CAR’,144,17520,3,100);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO TCUSTORD VALUES (‘JANE’,’1995-11-11 13:52:00′,’PLANE’,256,133300,1,100);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TCUSTMER VALUES (‘DAVE’,’DAVE”S PLANES INC.’,’TALLAHASSEE’,’FL’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO TCUSTMER VALUES (‘BILL’,’BILL”S USED CARS’,’DENVER’,’CO’);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TCUSTMER VALUES (‘ANN’,’ANN”S BOATS’,’SEATTLE’,’WA’);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TCUSTORD VALUES (‘BILL’,’1995-12-31 15:00:00′,’CAR’,765,15000,3,100);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TCUSTORD values (‘BILL’,’1996-01-01 00:00:00′,’TRUCK’,333,26000,15,100);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TCUSTORD values (‘DAVE’,’1993-11-03 07:51:35′,’PLANE’,600,135000,2,200);
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 1 row affected (0.01 sec)
现在，将更新事务处理到表中 - 在TCUSTORD表上更新3条记录，在TCUSTMER表上更新1条记录，共4个操作。
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1303Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 14000.00 WHERE CUST_CODE = ‘BILL’ AND ORDER_DATE = ‘1995-12-31 15:00:00’ AND PRODUCT_CODE = ‘CAR’ AND ORDER_ID = 765;
Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 25000.00 WHERE CUST_CODE = ‘BILL’ AND ORDER_DATE = ‘1996-01-01 00:00:00’ AND PRODUCT_CODE = ‘TRUCK’ AND ORDER_ID = 333;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TCUSTORD SET PRODUCT_PRICE = 16520.00 WHERE CUST_CODE = ‘WILL’ AND ORDER_DATE = ‘1994-09-30 15:33:00’ AND PRODUCT_CODE = ‘CAR’ AND ORDER_ID = 144;
Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE TCUSTMER SET CITY = ‘NEW YORK’, STATE = ‘NY’ WHERE CUST_CODE = ‘ANN’;
Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
现在，将从表中删除事务 - 从TCUSTORD表中删除2条记录，共有2条删除操作。
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1305Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM TCUSTORD WHERE CUST_CODE = ‘DAVE’ AND ORDER_DATE = ‘1993-11-03 07:51:35’ AND PRODUCT_CODE = ‘PLANE’ AND ORDER_ID = 600;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE from TCUSTORD WHERE CUST_CODE = ‘JANE’ AND ORDER_DATE = ‘1995-11-11 13:52:00’ AND PRODUCT_CODE = ‘PLANE’ AND ORDER_ID = 256;
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
现在，让我们通过mysql来简单的计算一下我们的源数据库（Amazon RDS MySQL）中的最终记录总数。
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h ateam-mysql5634-aa.cvm92k6xdpun.us-west-1.rds.amazonaws.com -u ggmaster -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1326Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;
+———-+| count(*) |+———-+| 5 |+———-+1 row in set (0.00 sec)+———-+| count(*) |+———-+| 3 |+———-+1 row in set (0.00 sec)
现在，我们来看看在中间源数据库（EC2 MySQL）中是否有相同数量的记录，通过mysql进行简单的计数。
[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ mysql -h localhost -u root -p
Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 17Server version: 5.6.35-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql>
mysql> use tcustdb;
Database changed
mysql> select count(*) from TCUSTMER; select count(*) from TCUSTORD;
+———-+| count(*) |+———-+| 5 |+———-+1 row in set (0.00 sec)+———-+| count(*) |+———-+| 3 |+———-+1 row in set (0.00 sec)
在这一点上，我们通过MySQL本机复制在数据库中使用RDS MySQL（Master）和EC2 MySQL（Slave OGG Source）的数据库同步，我们执行了以下操作：
| Table Name | Insert | Update | Delete | Total Operations | Final # of Rows/Records |
|---|---|---|---|---|---|
| TCUSTDB.TCUSTMER | 5 | 1 | 0 | 6 | 5 |
| TCUSTDB.TCUSTORD | 5 | 3 | 2 | 10 | 3 |
共有10个插入，4个更新和2个删除。
检查在线更改数据捕获提取过程ETCUSTDB统计信息（Amazon EC2上的OGG）
现在，我们通过GGCSI“ STATS ”命令检查我们的Extract进程ETCUSTDB的统计信息 ，这应该捕获并反映我们刚刚在源表上执行的操作。
mpapio@borg:~$ ssh -i mp_opc_ssh_key ec2-user@aws-ateam-gg-hub-mysql-aa
Last login: Sat Apr 29 03:13:30 2017__| __|_ )_| ( / Amazon Linux AMI___|\___|___|https://aws.amazon.com/amazon-linux-ami/2017.03-release-notes/
[ec2-user@ip-10-0-1-12 ~]$ cd ogg12c_mysql56[ec2-user@ip-10-0-1-12 ogg12c_mysql56]$ ./ggsci
Oracle GoldenGate Command Interpreter for MySQLVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (ip-10-0-1-12) 1> stats extract etcustdb, total, table *.*
Sending STATS request to EXTRACT ETCUSTDB …Start of Statistics at 2017-04-29 17:51:38.Output to ./dirdat/ea:
Extracting from tcustdb.TCUSTMER to tcustdb.TCUSTMER:*** Total statistics since 2017-04-29 06:07:53 ***Total inserts 5.00Total updates 1.00Total deletes 0.00Total discards 0.00Total operations 6.00
Extracting from tcustdb.TCUSTORD to tcustdb.TCUSTORD:*** Total statistics since 2017-04-29 06:07:53 ***Total inserts 5.00Total updates 3.00Total deletes 2.00Total discards 0.00Total operations 10.00
检查提取Datapump进程PTCUSTDB统计信息（Amazon EC2上的OGG）
现在，我们通过相同的GGCSI“ STATS ”命令检查我们的Extract Datapump进程PTCUSTDB的统计信息 ，这也反映了我们刚刚在源表上执行的操作的相同数量。
GGSCI (ip-10-0-1-12) 2> stats extract ptcustdb, total, table *.*
Sending STATS request to EXTRACT PTCUSTDB …Start of Statistics at 2017-04-29 17:57:48.Output to ./dirdat/pa:
Extracting from tcustdb.TCUSTMER to tcustdb.TCUSTMER:*** Total statistics since 2017-04-29 06:16:08 ***Total inserts 5.00Total updates 1.00Total deletes 0.00Total discards 0.00Total operations 6.00
Extracting from tcustdb.TCUSTORD to tcustdb.TCUSTORD:*** Total statistics since 2017-04-29 06:16:08 ***Total inserts 5.00Total updates 3.00Total deletes 2.00Total discards 0.00Total operations 10.00
检查在线更改传送复制过程RTCUSTDB统计信息（OPC上的GGCS实例）
现在，我们通过我们对Extract进程所做的相同的GGCSI“ STATS ”命令来查看我们的在线交付复制过程RTCUSTDB的统计信息 。这也反映了我们刚刚在源表上执行并由Extract（ETCUSTDB）进程捕获的操作的相同数量，并由Extract Datapump（PTCUSTDB）进程发送。
mpapio@borg:~$ ssh -i ateam_opc_ssh_key opc@ateam-ggcs-aa
[opc@ateam-ggcs-aa-ggcs-1 ~]$ sudo su – oracle[oracle@ateam-ggcs-aa-ggcs-1 ~]$ cd $GGHOME[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ ggsci
GGSCI (ateam-ggcs-aa-ggcs-1) 1> dblogin useridalias ggcsuser_alias
Successfully logged into database GGPDBAA.
GGSCI (ateam-ggcs-aa-ggcs-1 as c##ggadmin@GGDBAA/GGPDBAA) 2> stats replicat rtcustdb, total, table *.*
Sending STATS request to REPLICAT RTCUSTDB …Start of Statistics at 2017-04-29 14:05:14.Integrated Replicat Statistics:Total transactions 13.00Redirected 0.00DDL operations 0.00Stored procedures 0.00Datatype functionality 0.00Event actions 0.00Direct transactions ratio 0.00%
Replicating from tcustdb.TCUSTMER to GGPDBAA.MP_TCUSTDB.TCUSTMER:*** Total statistics since 2017-04-29 02:17:22 ***Total inserts 5.00Total updates 1.00Total deletes 0.00Total discards 0.00Total operations 6.00
Replicating from tcustdb.TCUSTORD to GGPDBAA.MP_TCUSTDB.TCUSTORD:*** Total statistics since 2017-04-29 02:17:22 ***Total inserts 5.00Total updates 3.00Total deletes 2.00Total discards 0.00Total operations 10.00
现在，最后一步，我们通过sqlplus来简单地计算我们在Oracle DBCS上的目标数据库中的最终记录总数，并确保结果与Amazon RDS源数据库中的最终记录总数相匹配。
[oracle@ateam-ggcs-aa-ggcs-1 gghome]$ sqlplus mp_tcustdb@target/mp_tcustdb <<EOF> select count(*) from TCUSTMER;> select count(*) from TCUSTORD;> EOF
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 14:12:34 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Last Successful login time: Sat Apr 29 2017 03:22:41 -04:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL>COUNT(*)———-5SQL>
COUNT(*)
———-
3
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
总结
本文介绍了如何将Oracle GoldenGate（OGG）复制从Amazon Relational Database Service（RDS）MySQL数据库配置到Oracle公共云（OPC）上的Oracle数据库云服务（OPC）与MySQL Native复制相结合的步骤。
