逻辑Standby的SQL魔法：Oracle LCR挖掘与冲突化解密

Oracle逻辑Standby的SQL Apply与LCR挖掘机制

一、逻辑Standby与SQL Apply概述

官方解释

逻辑Standby数据库是Oracle Data Guard的重要组成部分，它通过SQL Apply技术将主库的更改应用到备库。与物理Standby直接应用Redo数据不同，逻辑Standby从Redo日志中挖掘出逻辑更改记录（LCR - Logical Change Record），然后将这些LCR转换为SQL语句在备库执行。

SQL Apply进程由以下主要组件构成：

1. 日志挖掘进程：从Redo日志中提取LCR
2. LCR缓冲区：存储挖掘出的LCR
3. LCR应用进程：将LCR转换为SQL并在备库执行
4. 冲突检测与解决：处理主备库之间的数据不一致问题

通俗解释

可以将逻辑Standby想象成一个实时翻译和重演系统：

• 物理Standby：像复印机，直接复制主库的磁盘块变化
• 逻辑Standby：像翻译官，先"读懂"主库的操作（LCR挖掘），然后"用本地语言"（SQL语句）在备库重演这些操作

这样备库可以以不同的结构存储数据，甚至可以在应用Redo的同时保持打开状态供查询使用。

二、LCR挖掘与处理机制

1. LCR格式与结构

逻辑更改记录（LCR） 是SQL Apply的核心数据结构，它包含了足够的信息来重构原始SQL操作。

<!-- LCR的XML表示形式示例 -->
<LCR>
  <transaction_id>123456</transaction_id>
  <source_database>PRIMARY</source_database>
  <command_type>INSERT</command_type>
  <object_owner>SCOTT</object_owner>
  <object_name>EMP</object_name>
  <new_values>
    <column name="EMPNO">7934</column>
    <column name="ENAME">MILLER</column>
    <column name="DEPTNO">10</column>
  </new_values>
  <old_values>
    <!-- 对于UPDATE操作，会包含更改前的值 -->
  </old_values>
</LCR>

2. LCR挖掘过程

SQL Apply通过LogMiner技术从Redo日志中提取LCR：

-- 查看LogMiner会话信息
SELECT SESSION_ID, CLIENT_NAME, STATUS 
FROM V$LOGSTDBY_STATS;

-- 查看LCR挖掘进度
SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN 
FROM V$LOGSTDBY_PROGRESS;

LCR挖掘过程包括：

1. 日志读取：从主库的Redo日志或归档日志中读取数据
2. 变化提取：识别DML和DDL操作
3. LCR构建：将操作转换为标准化的LCR格式
4. LCR传递：将LCR发送到应用进程

3. SQL应用过程

三、冲突检测与解决

1. 冲突类型

逻辑Standby可能遇到多种冲突类型：

1. 唯一键冲突：备库中存在主库不存在的数据
2. 缺失行冲突：尝试更新或删除不存在的行
3. 外键约束冲突：违反引用完整性约束
4. 数据类型冲突：主备库数据类型不兼容

2. 冲突解决机制

Oracle提供DBMS_LOGSTDBY包来处理冲突：

-- 查看当前冲突解决配置
SELECT * FROM DBA_LOGSTDBY_EVENTS;

-- 添加冲突解决处理器
BEGIN
  DBMS_LOGSTDBY.ADD_PRIMARY_KEY(
    object_name => 'SCOTT.EMP',
    handling_ownername => 'SYS',
    handling_method => 'OVERWRITE'
  );
END;
/

-- 忽略特定表的应用
BEGIN
  DBMS_LOGSTDBY.SKIP(
    stmt => 'DML',
    schema_name => 'SCOTT',
    object_name => 'BONUS'
  );
END;
/

3. 自定义冲突解决

对于复杂场景，可以创建自定义冲突解决程序：

-- 创建自定义冲突解决函数
CREATE OR REPLACE FUNCTION resolve_emp_conflict (
  p_schema_name IN VARCHAR2,
  p_table_name IN VARCHAR2,
  p_old_values IN SYS.LCR$_ROW_LIST,
  p_new_values IN SYS.LCR$_ROW_LIST
) RETURN VARCHAR2 IS
BEGIN
  -- 自定义冲突解决逻辑
  IF p_old_values IS NULL THEN
    RETURN 'INSERT'; -- 允许插入
  ELSE
    RETURN 'REPLACE'; -- 替换现有行
  END IF;
END;
/

-- 注册自定义冲突解决器
BEGIN
  DBMS_LOGSTDBY.REGISTER_APPLY_PROCESS(
    object_name => 'SCOTT.EMP',
    custom_proc => 'resolve_emp_conflict'
  );
END;
/

四、限制与不支持的操作

1. 不支持的数据类型

某些数据类型不被逻辑Standby支持：

• LONG、LONG RAW（使用LOB替代）
• BFILE
• ROWID、UROWID
• 用户定义类型（某些情况下）
• Oracle提供的类型（如Spatial、Image等）

2. 不支持的DDL操作

部分DDL操作在逻辑Standby中有特殊限制：

• 表空间操作（ALTER TABLESPACE）
• 某些分区操作
• 与存储相关的子句（如PCTFREE、PCTUSED等）

3. 检查支持性

-- 检查对象是否支持逻辑Standby
SELECT OWNER, TABLE_NAME, SUPPORTED 
FROM DBA_LOGSTDBY_NOT_UNIQUE 
WHERE SUPPORTED = 'NO';

-- 查看不支持的对象详情
SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

五、监控与故障排除

1. 关键动态性能视图

-- 查看SQL Apply进度
SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN 
FROM V$LOGSTDBY_PROGRESS;

-- 查看当前应用的事务
SELECT XIDUSN, XIDSLT, XIDSQN, STATUS 
FROM V$LOGSTDBY_TRANSACTION;

-- 查看LCR统计信息
SELECT NAME, VALUE 
FROM V$LOGSTDBY_STATS 
WHERE NAME LIKE '%LCR%';

-- 查看等待事件
SELECT EVENT, TOTAL_WAITS, TIME_WAITED 
FROM V$LOGSTDBY_STATE;

2. 常见问题排查

问题1：SQL Apply停止

-- 检查错误信息
SELECT EVENT_TIME, STATUS, EVENT 
FROM DBA_LOGSTDBY_EVENTS 
ORDER BY EVENT_TIME DESC;

-- 查看详细错误
SELECT XIDUSN, XIDSLT, XIDSQN, ERROR 
FROM V$LOGSTDBY_TRANSACTION 
WHERE ERROR IS NOT NULL;

问题2：应用延迟

-- 检查延迟情况
SELECT SYSDATE - APPLIED_TIME APPLIED_LAG,
       SYSDATE - READ_TIME READ_LAG
FROM V$LOGSTDBY_PROGRESS;

-- 检查网络延迟
SELECT NAME, VALUE 
FROM V$LOGSTDBY_STATS 
WHERE NAME LIKE '%NETWORK%';

-- 检查挖掘速率
SELECT NAME, VALUE 
FROM V$LOGSTDBY_STATS 
WHERE NAME LIKE '%MINED%';

问题3：冲突检测

-- 查看冲突统计
SELECT NAME, VALUE 
FROM V$LOGSTDBY_STATS 
WHERE NAME LIKE '%CONFLICT%';

-- 查看最近冲突详情
SELECT EVENT_TIME, STATUS, EVENT 
FROM DBA_LOGSTDBY_EVENTS 
WHERE EVENT LIKE '%CONFLICT%' 
ORDER BY EVENT_TIME DESC;

3. 性能优化

-- 调整并行应用进程数
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE SET LOGICAL STANDBY MAX_SERVERS = 10;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

-- 调整LCR缓冲区大小
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'SERVICE=standby LGWR SYNC MAX_SGA=500';

-- 监控应用性能
SELECT NAME, VALUE 
FROM V$LOGSTDBY_STATS 
WHERE NAME IN ('applied messages', 'applied transactions', 'applied bytes');

六、实际应用场景与示例

场景1：报表数据库与主库结构不同

-- 在主库上，数据按范围分区
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
) PARTITION BY RANGE (sale_date) (
  PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD'))
);

-- 在逻辑Standby上，可以改为列表分区以适应报表需求
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER,
  region VARCHAR2(10)
) PARTITION BY LIST (region) (
  PARTITION east VALUES ('EAST'),
  PARTITION west VALUES ('WEST')
);

-- 使用DBMS_LOGSTDBY.SKIP跳过分区维护操作
BEGIN
  DBMS_LOGSTDBY.SKIP(
    stmt => 'ALTER TABLE',
    schema_name => 'SCOTT',
    object_name => 'SALES'
  );
END;
/

场景2：数据子集与数据脱敏

-- 在逻辑Standby上只保留部分数据
CREATE TABLE employees AS 
SELECT * FROM scott.employees WHERE department_id = 10;

-- 添加触发器进行数据脱敏
CREATE OR REPLACE TRIGGER mask_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  -- 对薪资数据进行脱敏处理
  :NEW.salary := :NEW.salary * 0.8; -- 示例脱敏规则
END;
/

-- 跳过原始表的应用
BEGIN
  DBMS_LOGSTDBY.SKIP(
    stmt => 'DML',
    schema_name => 'SCOTT',
    object_name => 'EMPLOYEES'
  );
END;
/

七、最佳实践与管理建议

1. 配置建议

-- 确保主库使用补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- 配置适当的并行度
ALTER DATABASE SET LOGICAL STANDBY MAX_SERVERS = 8;

-- 定期收集统计信息
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'SCOTT',
    options => 'GATHER AUTO'
  );
END;
/

2. 监控脚本示例

-- 综合监控脚本
SELECT 
  (SELECT MAX(sequence#) FROM v$archived_log WHERE applied = 'YES') last_applied_seq,
  (SELECT MAX(sequence#) FROM v$archived_log) last_received_seq,
  (SELECT MIN(sequence#) FROM v$archived_log WHERE applied = 'NO') min_pending_seq,
  (SELECT COUNT(*) FROM v$archived_log WHERE applied = 'NO') pending_count,
  (SELECT SYSDATE - MAX(first_time) FROM v$archived_log WHERE applied = 'YES') lag_interval
FROM dual;

-- 检查应用状态
SELECT 
  process_id, process_type, status, 
  sequence#, block#, delay_mins 
FROM v$managed_standby 
WHERE process_type = 'LSP';

3. 故障转移与切换

-- 准备切换
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

-- 执行切换
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

-- 故障后重新同步
ALTER DATABASE START LOGICAL STANDBY APPLY SKIP_FAILED TRANSACTION;

总结

Oracle逻辑Standby的SQL Apply与LCR挖掘机制提供了灵活的数据复制解决方案，允许备库以不同的物理结构存储数据，同时保持与主库的逻辑一致性。理解LCR的格式、冲突解决机制以及限制条件对于成功部署和管理逻辑Standby环境至关重要。

通过适当的监控、冲突解决策略和性能优化，逻辑Standby可以成为报表生成、数据子集、数据脱敏和高可用性解决方案的强大工具。然而，也需要认识到其复杂性，特别是在处理不支持的数据类型和DDL操作时需要格外小心。

欢迎关注我的公众号《IT小Chen》