Oracle迁移实战：从评估到切换的五个关键阶段

# 伪代码：Oracle迁移兼容性检查
def analyze_oracle_objects(conn):
    # 检查存储过程
    procs = get_all_stored_procedures(conn)
    for proc in procs:
        issues = []
        # 检查Oracle特有语法
        if 'PRAGMA AUTONOMOUS_TRANSACTION' in proc.text:
            issues.append('独立事务需要人工改写')
        if 'UTL_FILE' in proc.text:
            issues.append('文件操作接口需要替换')
        
        # 检查数据类型
        if 'LONG' in proc.text:
            issues.append('LONG类型已废弃，建议迁移到CLOB')
        
        if issues:
            report.append({
                'object': proc.name,
                'type': 'procedure',
                'issues': issues,
                'estimated_effort': estimate_rewrite_effort(issues)
            })
    
    return report

-- 对大表做分区拆分，并行导出
-- 以order表为例，按create_time的月份拆成24个时间段
SELECT MIN(create_time), MAX(create_time) FROM orders;

-- 并行启动24个导出任务，每个任务负责一个月的数据
-- 任务1: WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'
-- 任务2: WHERE create_time >= '2024-02-01' AND create_time < '2024-03-01'
-- ...

// 增量同步的核心逻辑（简化版）
public class IncrementalSync {
    private static final String ORACLE_LOG_QUERY = 
        "SELECT SCN, SQL_REDO, OPERATION FROM V$LOGMNR_CONTENTS " +
        "WHERE SCN > ? AND OPERATION IN ('INSERT','UPDATE','DELETE')";
    
    public void startSync(long startScn) {
        while (running) {
            List<ChangeLog> logs = fetchLogs(startScn);
            for (ChangeLog log : logs) {
                // 将Oracle的DML转换为目标库语法
                String targetSql = convertToTargetSyntax(log);
                executeOnTarget(targetSql);
            }
            startScn = getLatestScn();
            Thread.sleep(1000); // 轮询间隔
        }
    }
    
    private String convertToTargetSyntax(ChangeLog log) {
        // 这里需要处理大量语法差异：
        // - ROWNUM → LIMIT
        // - DECODE → CASE WHEN
        // - SYSDATE → CURRENT_TIMESTAMP
        // - 空字符串与NULL的处理差异
        // ...
    }
}

-- 一致性校验SQL示例
-- 对orders表做行级校验

-- 第一步：总量校验
SELECT COUNT(*), SUM(amount), MIN(order_id), MAX(order_id) 
FROM oracle_orders;

SELECT COUNT(*), SUM(amount), MIN(order_id), MAX(order_id) 
FROM target_orders;

-- 第二步：逐行哈希校验（分批进行）
WITH order_hashes AS (
    SELECT order_id, 
           MD5(order_no || customer_id || amount || status || update_time) as row_hash
    FROM oracle_orders
    WHERE order_id BETWEEN 1000000 AND 2000000
)
SELECT * FROM order_hashes 
WHERE row_hash NOT IN (
    SELECT MD5(order_no || customer_id || amount || status || update_time)
    FROM target_orders
    WHERE order_id BETWEEN 1000000 AND 2000000
);

-- 定位具体差异字段
SELECT a.order_id,
       CASE WHEN a.amount != b.amount THEN '金额' ELSE '' END as diff_amount,
       CASE WHEN a.status != b.status THEN '状态' ELSE '' END as diff_status
FROM oracle_orders a 
JOIN target_orders b ON a.order_id = b.order_id
WHERE a.order_id IN (不一致的ID列表);

#!/bin/bash
# 切换脚本的核心流程

echo "========== 开始切换 =========="

# 1. 停止应用对Oracle的写入（read-only模式）
curl -X POST http://app-service/admin/set-readonly
sleep 10

# 2. 确认增量同步已追平（延迟=0）
check_sync_lag() {
    lag=$(curl -s http://sync-service/api/lag)
    if [ "$lag" != "0" ]; then
        echo "同步延迟: ${lag}s，等待..."
        return 1
    fi
    return 0
}
while ! check_sync_lag; do
    sleep 5
done

# 3. 创建切换前快照（应急兜底）
curl -X POST http://target-db/api/snapshot \
    -d '{"name":"pre_switch_snapshot"}'

# 4. VIP漂移到新库
vip_migrate() {
    # 调用网络设备API，将虚拟IP从Oracle服务器指向新库
    curl -X POST http://network-api/vip/migrate \
        -d '{"vip":"192.168.1.100","target":"target-db"}'
}
vip_migrate

# 5. 启动业务验证脚本
./verify_services.sh &

# 6. 验证通过后，开启新库的归档日志
curl -X POST http://target-db/api/archive/enable

echo "========== 切换完成 =========="

时间线：
T0: 切换，流量切到新库
T0 ~ T+N: 双轨并行期
T+N: 确认新库稳定后，下线Oracle

双轨期间需要做的事：
- 新库写入的数据，实时同步回Oracle（反向同步）
- 持续监控新库的性能指标
- 对比新旧库的响应时间、错误率
- 如果新库出现问题，可以立即切回Oracle，且Oracle数据是最新的

# 反向同步监听新库的binlog/logminer
def reverse_sync_listener():
    while in_parallel_period:
        change = wait_for_change_on_target()
        # 转换为Oracle语法
        oracle_sql = convert_to_oracle_syntax(change)
        # 写回Oracle
        execute_on_oracle(oracle_sql)
        # 记录双向同步延迟
        log_bidirectional_lag()