# 1. 创建全表校验脚本
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# full_verify.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

TABLE_NAME=”FGEDU_ORDERS”
BATCH_SIZE=10000
LOG_FILE=”/oracle/goldengate/verify/full_verify_${TABLE_NAME}_$(date +%Y%m%d).log”

echo “开始全表校验: $TABLE_NAME” >> $LOG_FILE
echo “开始时间: $(date)” >> $LOG_FILE

# 获取源表总记录数
SOURCE_TOTAL=$(sqlplus -s fgedu/password@fgedudb_source < EXIT;
EOF
)

# 获取目标表总记录数
TARGET_TOTAL=$(sqlplus -s fgedu/password@fgedudb_target < EXIT;
EOF
)

echo “源表记录数: $SOURCE_TOTAL” >> $LOG_FILE
echo “目标表记录数: $TARGET_TOTAL” >> $LOG_FILE

# 如果记录数一致，进行详细对比
if [ $SOURCE_TOTAL -eq $TARGET_TOTAL ]; then
echo “记录数一致，进行详细对比…” >> $LOG_FILE

# 使用MINUS进行全表对比
DIFF_COUNT=$(sqlplus -s fgedu/password@fgedudb_source < EXIT;
EOF
)

echo “差异记录数: $DIFF_COUNT” >> $LOG_FILE

if [ $DIFF_COUNT -eq 0 ]; then
echo “校验结果: 完全一致” >> $LOG_FILE
else
echo “校验结果: 存在差异” >> $LOG_FILE
fi
else
echo “记录数不一致，跳过详细对比” >> $LOG_FILE
fi

echo “结束时间: $(date)” >> $LOG_FILE

# 2. 执行校验
$ chmod +x full_verify.sh
$ ./full_verify.sh

# 3. 查看结果
$ cat /oracle/goldengate/verify/full_verify_FGEDU_ORDERS_20260403.log

开始全表校验: FGEDU_ORDERS
开始时间: Thu Apr 3 17:00:00 CST 2026
源表记录数: 10000000
目标表记录数: 10000000
记录数一致，进行详细对比…
差异记录数: 0
校验结果: 完全一致
结束时间: Thu Apr 3 17:30:00 CST 2026

# 1. 创建增量校验脚本
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# incremental_verify.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

TABLE_NAME=”FGEDU_ORDERS”
VERIFY_DATE=$(date +%Y%m%d)
LOG_FILE=”/oracle/goldengate/verify/incremental_verify_${TABLE_NAME}_${VERIFY_DATE}.log”

echo “开始增量校验: $TABLE_NAME” >> $LOG_FILE
echo “校验日期: $VERIFY_DATE” >> $LOG_FILE
echo “开始时间: $(date)” >> $LOG_FILE

# 获取当天新增记录数
SOURCE_NEW=$(sqlplus -s fgedu/password@fgedudb_source <= TRUNC(SYSDATE) AND created_at < TRUNC(SYSDATE) + 1;
EXIT;
EOF
)

TARGET_NEW=$(sqlplus -s fgedu/password@fgedudb_target <= TRUNC(SYSDATE) AND created_at < TRUNC(SYSDATE) + 1;
EXIT;
EOF
)

echo “源端当天新增记录数: $SOURCE_NEW” >> $LOG_FILE
echo “目标端当天新增记录数: $TARGET_NEW” >> $LOG_FILE

# 对比当天新增记录
if [ $SOURCE_NEW -eq $TARGET_NEW ]; then
echo “当天新增记录数一致” >> $LOG_FILE

# 校验新增记录的校验和
SOURCE_HASH=$(sqlplus -s fgedu/password@fgedudb_source <= TRUNC(SYSDATE) AND created_at < TRUNC(SYSDATE) + 1;
EXIT;
EOF
)

TARGET_HASH=$(sqlplus -s fgedu/password@fgedudb_target <= TRUNC(SYSDATE) AND created_at < TRUNC(SYSDATE) + 1;
EXIT;
EOF
)

echo “源端新增记录校验和: $SOURCE_HASH” >> $LOG_FILE
echo “目标端新增记录校验和: $TARGET_HASH” >> $LOG_FILE

if [ “$SOURCE_HASH” = “$TARGET_HASH” ]; then
echo “增量校验结果: 一致” >> $LOG_FILE
else
echo “增量校验结果: 不一致” >> $LOG_FILE
fi
else
echo “当天新增记录数不一致” >> $LOG_FILE
fi

echo “结束时间: $(date)” >> $LOG_FILE

# 2. 添加到crontab定时执行
echo “0 2 * * * /oracle/goldengate/verify/incremental_verify.sh” | crontab

# 3. 查看结果
$ cat /oracle/goldengate/verify/incremental_verify_FGEDU_ORDERS_20260403.log

开始增量校验: FGEDU_ORDERS
校验日期: 20260403
开始时间: Thu Apr 3 02:00:00 CST 2026
源端当天新增记录数: 15000
目标端当天新增记录数: 15000
当天新增记录数一致
源端新增记录校验和: 987654321
目标端新增记录校验和: 987654321
增量校验结果: 一致
结束时间: Thu Apr 3 02:05:00 CST 2026

# 1. 记录数不一致
SQL> — 源端
SQL> SELECT COUNT(*) FROM fgedu.fgedu_orders;

COUNT(*)
———-
10000000

SQL> — 目标端
SQL> SELECT COUNT(*) FROM fgedu.fgedu_orders;

COUNT(*)
———-
9999995

# 差异：目标端少5条记录

# 2. 查找缺失记录
SQL> SELECT order_id FROM fgedu.fgedu_orders@source
MINUS
SELECT order_id FROM fgedu.fgedu_orders;

ORDER_ID
———-
10001
10002
10003
10004
10005

# 3. 分析原因
# – 可能是Replicat进程跳过或丢弃了这些记录
# – 可能是目标端约束导致插入失败

# 4. 查看Replicat报告
GGSCI (fgedu-target.net.cn) 1> view report rep01

2026-04-03 15:00:00 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep01.prm: Aborted grouped transaction.
2026-04-03 15:00:00 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep01.prm: Error mapping from FGEDU.FGEDU_ORDERS to FGEDU.FGEDU_ORDERS.
2026-04-03 15:00:00 ERROR OGG-01161 Oracle GoldenGate Delivery for Oracle, rep01.prm: Bad column index.

# 1. 从源端导出缺失数据
SQL> CREATE TABLE fgedu.fgedu_orders_temp AS
SELECT * FROM fgedu.fgedu_orders@source
WHERE order_id IN (10001, 10002, 10003, 10004, 10005);

Table created.

# 2. 插入目标端
SQL> INSERT INTO fgedu.fgedu_orders
SELECT * FROM fgedu.fgedu_orders_temp;

5 rows created.

SQL> COMMIT;

Commit complete.

# 3. 验证修复结果
SQL> SELECT COUNT(*) FROM fgedu.fgedu_orders;

COUNT(*)
———-
10000000

# 解决方案2：使用GoldenGate修复

# 1. 创建修复Extract
GGSCI (fgedu.net.cn) 1> add extract fix_ext, sourceistable

GGSCI (fgedu.net.cn) 2> edit params fix_ext

EXTRACT fix_ext
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin
RMTHOST 192.168.1.100, MGRPORT 7809
RMTTASK REPLICAT, GROUP fix_rep

TABLE fgedu.fgedu_orders, FILTER (order_id IN (10001, 10002, 10003, 10004, 10005));

# 2. 创建修复Replicat
GGSCI (fgedu-target.net.cn) 1> add replicat fix_rep, specialrun

GGSCI (fgedu-target.net.cn) 2> edit params fix_rep

REPLICAT fix_rep
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_target

HANDLECOLLISIONS
MAP fgedu.fgedu_orders, TARGET fgedu.fgedu_orders;

# 3. 执行修复
GGSCI (fgedu.net.cn) 3> start extract fix_ext

# 4. 验证修复结果
SQL> SELECT COUNT(*) FROM fgedu.fgedu_orders;

COUNT(*)
———-
10000000