DB / Oracle / 备份与迁移

Oracle Data Guard 架构与配置

2026.09.06

Oracle Data Guard 架构与配置

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle Data Guard 是 Oracle 的灾难恢复和高可用方案[1]：

核心特性：

  • 主备数据库同步
  • 数据保护（最大保护/最大可用/最大性能）
  • 自动故障切换（Fast-Start Failover）
  • 只读备库（Active Data Guard）
  • 远程归档

典型用途：

  • 灾难恢复
  • 读写分离
  • 数据保护
  • 报表查询

2. Data Guard 架构

2.1 主备架构

+-------------------+       +-------------------+
| Primary Database  | <---> | Standby Database  |
| (READ WRITE)      |       | (READ ONLY)       |
+-------------------+       +-------------------+
        |                           |
        | Redo Log                  | Standby Redo Log
        ↓                           ↓
+-------------------+       +-------------------+
| Archive Log       | <---> | Archive Log       |
+-------------------+       +-------------------+

2.2 同步方式

模式说明数据丢失性能
最大保护同步写入 Standby Redo Log零丢失性能影响
最大可用同步，备库不可用时降级极少丢失平衡
最大性能异步少量丢失性能最佳

3. 备库类型

3.1 物理备库

-- 与主库完全一致（block-by-block）
-- 应用 Redo Log
-- ARCHIVELOG 模式

3.2 逻辑备库

-- 通过 SQL 应用 Redo
-- 可有自己的对象
-- 可读写
-- 限制：某些数据类型不支持

3.3 快照备库

-- 临时转换为读写
-- 通过 Flashback 回滚
-- 适合测试

4. 配置 Data Guard

4.1 主库准备

-- 1. 启用归档模式
ARCHIVE LOG LIST;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 2. 启用 Force Logging
ALTER DATABASE FORCE LOGGING;

-- 3. 创建 Standby Redo Log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 
  ('/u01/oradata/orcl/srl04a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 
  ('/u01/oradata/orcl/srl05a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 
  ('/u01/oradata/orcl/srl06a.log') SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 
  ('/u01/oradata/orcl/srl07a.log') SIZE 100M;

-- 4. 设置参数
ALTER SYSTEM SET log_archive_config='dg_config=(orcl_pri,orcl_std)';
ALTER SYSTEM SET log_archive_dest_2='service=orcl_std async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_std';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET fal_server=orcl_std;
ALTER SYSTEM SET fal_client=orcl_pri;
ALTER SYSTEM SET standby_file_management=auto;
ALTER SYSTEM SET db_file_name_convert='/u01/oradata/orcl_std/','/u01/oradata/orcl_pri/' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/oradata/orcl_std/','/u01/oradata/orcl_pri/' SCOPE=SPFILE;

4.2 创建备库

# 1. RMAN 备份主库
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

# 2. 传输到备库
scp /u01/backup/* standby-host:/u01/backup/

# 3. 复制密码文件
scp $ORACLE_HOME/dbs/orapworcl standby-host:$ORACLE_HOME/dbs/

# 4. 配置备库 tnsnames.ora
# tnsnames.ora（主备都需配置）
ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary-host)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl_pri))
  )
ORCL_STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl_std))
  )

4.3 RMAN DUPLICATE 创建备库

# 在备库执行
rman target sys/pwd@orcl_pri auxiliary sys/pwd@orcl_std

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY 
  FROM ACTIVE DATABASE 
  DORECOVER 
  NOFILENAMECHECK;

4.4 启动备库

-- 在备库
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- Active Data Guard（只读 + 应用）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

5. Data Guard Broker

5.1 启用 Broker

-- 主备库都执行
ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH;

5.2 配置 Broker

# 在主库
dgmgrl sys/pwd@orcl_pri
DGMGRL> CREATE CONFIGURATION dg_config AS 
  PRIMARY DATABASE IS orcl_pri 
  CONNECT IDENTIFIER IS orcl_pri;

DGMGRL> ADD DATABASE orcl_std AS 
  CONNECT IDENTIFIER IS orcl_std 
  MAINTAINED AS PHYSICAL;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT DATABASE orcl_pri SET PROPERTY LogXptMode='SYNC';
DGMGRL> EDIT DATABASE orcl_std SET PROPERTY LogXptMode='SYNC';

DGMGRL> ENABLE CONFIGURATION;

5.3 Broker 命令

# 查看配置
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE orcl_pri;
DGMGRL> SHOW DATABASE orcl_std;

# 切换
DGMGRL> FAILOVER TO orcl_std;
DGMGRL> SWITCHOVER TO orcl_std;

# 重启
DGMGRL> STARTUP;
DGMGRL> SHUTDOWN;

6. 角色切换

6.1 Switchover（计划内切换）

# 通过 Broker
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SWITCHOVER TO orcl_std;

# 手动
-- 主库
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

-- 备库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SHUTDOWN;
STARTUP;

6.2 Failover（故障切换）

# 通过 Broker
DGMGRL> FAILOVER TO orcl_std;

# 手动
-- 备库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN;
STARTUP;

7. Fast-Start Failover

7.1 配置

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate=TRUE;
DGMGRL> ENABLE FAST_START FAILOVER;

# 启动 Observer
dgmgrl -logfile /tmp/observer.log sys/pwd@orcl_pri
DGMGRL> START OBSERVER;

7.2 工作原理

1. Observer 监控主库
2. 主库故障超过 30 秒
3. 自动 Failover 到备库
4. 原主库恢复后自动转为备库

8. Active Data Guard

8.1 只读备库

-- 备库 READ ONLY + 应用 Redo
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

8.2 用途

  • 报表查询
  • 读写分离
  • 数据校验
  • 备份

8.3 DML 重定向（19c+）

-- 19c 支持备库 DML 自动重定向到主库
ALTER SYSTEM SET adg_redirect_dml=true SCOPE=BOTH;

9. 监控 Data Guard

9.1 主库状态

-- 归档进度
SELECT 
  dest_id,
  dest_name,
  status,
  target,
  archived_seq#
FROM v$archive_dest_status
WHERE dest_id IN (1,2);

-- 进程
SELECT process, status, thread#, sequence#, block#, blocks 
FROM v$managed_standby;

9.2 备库状态

-- 应用进度
SELECT 
  process, 
  status, 
  thread#, 
  sequence#, 
  block#, 
  blocks
FROM v$managed_standby;

-- Gap
SELECT * FROM v$archive_gap;

-- 数据库角色
SELECT database_role, switchover_status, open_mode 
FROM v$database;

9.3 Broker 状态

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE orcl_pri;
DGMGRL> SHOW DATABASE orcl_std;

10. 常见坑与排错

10.1 ORA-12154: TNS 无法解析

修复：

# 1. 检查 tnsnames.ora
# 2. tnsping 测试
tnsping orcl_std

# 3. 检查监听器
lsnrctl status

10.2 ORA-16057: DG 配置错误

修复：

-- 1. 检查 log_archive_config
SHOW PARAMETER log_archive_config;

-- 2. 确认 DB_UNIQUE_NAME
SHOW PARAMETER db_unique_name;

10.3 备库 Gap

现象：备库落后主库。

修复：

-- 1. 检查 Gap
SELECT * FROM v$archive_gap;

-- 2. 手动注册归档
ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_100.arc';

-- 3. 检查网络

10.4 ORA-16816: 不正确的角色

修复：

# 通过 Broker 修复
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE orcl_std;
DGMGRL> REINSTATE DATABASE orcl_pri;

10.5 备库应用停止

修复：

-- 1. 检查 MRP 进程
SELECT process, status FROM v$managed_standby;

-- 2. 重启 MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 3. 检查 alert log

11. 最佳实践

  1. 使用 Broker：简化管理
  2. 生产用最大可用模式：平衡安全与性能
  3. 配置 Standby Redo Log：实时应用
  4. 启用 Fast-Start Failover：自动故障切换
  5. Active Data Guard：只读查询
  6. 监控 Gap：及时发现同步问题
  7. 定期演练 Switchover：验证配置
  8. 网络带宽充足：减少延迟
  9. 备库与主库相同硬件：性能一致
  10. 多备库：异地灾备

12. 参考资料

[1] Oracle Data Guard Concepts and Administration 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/

[2] Oracle Data Guard Broker 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/