问题描述:OGG搭建完成后,源端insert数据导致目标端复制进程报错OGG-00768、OGG-00769,如下所示:
1、异常重现
--告警日志如下
WARNING OGG-02761 Source definitions file, /opt/ogg/target_endpoint/dirdef/ogg_test.info_tab, is ignored because trail file /opt/ogg/target_end
point/dirdat/rt000000000 contains table definitions.
2024-07-17 10:13:12 WARNING OGG-00769 Failed to validate table name (info_tab) in Database (ogg). SQL error (0).
2024-07-17 10:13:12 ERROR OGG-00768 No rows found while fetching the metadata for table ogg.info_tab. SQL error (0).
2024-07-17 10:13:12 ERROR OGG-01668 PROCESS ABENDING.
2024-07-17 10:13:12 INFO OGG-25701 The file caching thread was shutdown. Thread ID: 140121457022720.
2、问题分析
--复制进程文件内容如下
GGSCI (leo-mysql-ogg) 20> view param r_tab1
replicat r_tab1
targetdb ogg@192.168.133.35:3306,userid ogg,password ogg
sourcedefs /opt/ogg/target_endpoint/dirdef/ogg_test.info_tab
HANDLECOLLISIONS
MAP ORCLPDB.test_ogg.*,target ogg.info_tab;
--查目标端是否存在表ogg.info_tab
mysql> select database();
+------------+
| database() |
+------------+
| ogg |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_ogg |
+--------------------+
| ggs_checkpoint |
| ggs_checkpoint_lox |
| test_ogg |
+--------------------+
3 rows in set (0.00 sec)
说明:经确认目标端不存在ogg.info_tab表.
--后来发现在搭建OGG源端建表结构时,表名建错导致.
mysql> use ogg
Database changed
mysql> create table test_ogg(id int,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.00 sec)
3、解决方案
--目标端删除test_ogg表
mysql> drop table test_ogg;
Query OK, 0 rows affected (0.01 sec)
mysql> create table info_tab(id int,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.00 sec)
--目标端重启复制进程
GGSCI (leo-mysql-ogg) 23> start r_tab1
Sending START request to Manager ...
Replicat group R_TAB1 starting.
GGSCI (leo-mysql-ogg) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_TAB1 00:00:00 00:00:00
4、数据验证
--目标端查询
mysql> select * from info_tab;
+----+-------+
| id | name |
+----+-------+
| 1 | alina |
+----+-------+
1 row in set (0.00 sec)
GGSCI (leo-mysql-ogg) 26> stats r_tab1
Sending STATS request to Replicat group R_TAB1 ...
Start of statistics at 2024-07-17 14:07:19.
Replicating from ORCLPDB.TEST_OGG.INFO_TAB to ogg.info_tab:
*** Total statistics since 2024-07-17 11:32:23 ***
Total inserts 5.00
Total updates 0.00
Total deletes 4.00
Total upserts 0.00
Total discards 0.00
Total operations 9.00
*** Daily statistics since 2024-07-17 11:32:23 ***
Total inserts 5.00
Total updates 0.00
Total deletes 4.00
Total upserts 0.00
Total discards 0.00
Total operations 9.00
*** Hourly statistics since 2024-07-17 11:32:23 ***
Total inserts 5.00
Total updates 0.00
Total deletes 4.00
Total upserts 0.00
Total discards 0.00
Total operations 9.00
*** Latest statistics since 2024-07-17 11:32:23 ***
Total inserts 5.00
Total updates 0.00
Total deletes 4.00
Total upserts 0.00
Total discards 0.00
Total operations 9.00
End of statistics.
--源端insert数据
test_ogg@ORCLPDB> select * from info_tab;
ID NAME
---------- --------------------------------------------------------------------------------
1 alina
test_ogg@ORCLPDB> insert into info_tab values (2,'miss');
1 row created.
test_ogg@ORCLPDB> commit;
Commit complete.
test_ogg@ORCLPDB> select * from info_tab;
ID NAME
---------- --------------------------------------------------------------------------------
1 alina
2 miss
--目标端查询数据
mysql> select * from info_tab;
+----+-------+
| id | name |
+----+-------+
| 1 | alina |
| 2 | miss |
+----+-------+
2 rows in set (0.00 sec)
说明:如上所示,源端oracle与目标端mysql数据保持一致.
- 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.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.