DB / Oracle / 高可用与容灾

Oracle Data Guard 搭建实战详解

2026.09.06

Oracle Data Guard 搭建实战详解

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

1. 概述

Data Guard 搭建实战[1]：

详细见：Oracle Data Guard 架构详解、Oracle 物理备库详解。

2. 准备

2.1 主库

- 归档模式
- Force Logging
- Standby Redo Log

2.2 备库

- 相同 OS
- 相同 Oracle 版本
- 软件

2.3 网络

- 主备互通
- TNS 配置
- 密码文件

3. 主库配置

3.1 归档

ARCHIVE LOG LIST;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

3.2 Force Logging

ALTER DATABASE FORCE LOGGING;

3.3 Standby Redo Log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 
  ('/u01/redo/s04.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 
  ('/u01/redo/s05.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 
  ('/u01/redo/s06.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 
  ('/u01/redo/s07.log') SIZE 50M;

3.4 参数

ALTER SYSTEM SET log_archive_config='dg_config=(prod,stby)';
ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby';
ALTER SYSTEM SET fal_server='stby';
ALTER SYSTEM SET standby_file_management='AUTO';
ALTER SYSTEM SET db_file_name_convert='stby','prod';
ALTER SYSTEM SET log_file_name_convert='stby','prod';

3.5 密码

orapwd file=$ORACLE_HOME/dbs/orapwprod password=******
scp orapwprod stby:$ORACLE_HOME/dbs/orapwstby

4. 网络

4.1 tnsnames.ora

prod = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod-host)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = prod))
  )

stby = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = stby-host)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = stby))
  )

4.2 listener.ora

# 备库
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = stby_DGMGRL)(ORACLE_HOME=...)(SID_NAME=stby))
  )

4.3 验证

tnsping prod
tnsping stby
sqlplus sys/xxx@prod as sysdba
sqlplus sys/xxx@stby as sysdba

5. 备库创建

5.1 参数文件

# initstby.ora
db_name='prod'
db_unique_name='stby'

5.2 启动

sqlplus / as sysdba
STARTUP NOMOUNT PFILE='initstby.ora';

5.3 RMAN DUPLICATE

rman target sys/xxx@prod auxiliary sys/xxx@stby
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
  SPFILE
    PARAMETER_VALUE_CONVERT ('prod','stby')
    SET DB_UNIQUE_NAME='stby'
    SET LOG_ARCHIVE_DEST_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
    SET FAL_SERVER='prod'
  NOFILENAMECHECK;

6. 启动 Redo Apply

6.1 备库

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6.2 验证

SELECT database_role, open_mode FROM v$database;
-- PHYSICAL STANDBY / MOUNTED

SELECT process, status, sequence# FROM v$managed_standby;
-- RFS/LNS 运行

7. Active Data Guard

7.1 只读

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7.2 验证

SELECT open_mode FROM v$database;
-- READ ONLY WITH APPLY

8. Broker 配置

8.1 启用

ALTER SYSTEM SET dg_broker_start=true;

8.2 创建

dgmgrl sys/xxx@prod
DGMGRL> CREATE CONFIGURATION dg_cfg AS
  PRIMARY DATABASE IS prod CONNECT IDENTIFIER IS prod;
DGMGRL> ADD DATABASE stby AS
  CONNECT IDENTIFIER IS stby MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;

8.3 验证

DGMGRL> SHOW CONFIGURATION;

详细见：Oracle Data Guard Broker 详解。

9. 保护模式

9.1 最大可用

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

9.2 验证

SELECT protection_mode, protection_level FROM v$database;

详细见：Oracle 保护模式详解。

10. 验证

10.1 同步

-- 主库
ALTER SYSTEM SWITCH LOGFILE;
-- 备库
SELECT max(sequence#) FROM v$archived_log WHERE applied='YES';
-- 一致

10.2 数据

-- 主库
CREATE TABLE test_dg AS SELECT * FROM dual;
-- 备库（Active）
SELECT * FROM test_dg;
-- 一致

10.3 延迟

SELECT * FROM v$dataguard_stats;

11. 常见问题

11.1 ORA-12154

- TNS
- 检查

11.2 ORA-16191

- 密码文件
- 一致

11.3 GAP

- FAL
- 手动注册

12. 最佳实践

  1. Force Logging：启用
  2. Standby Redo：必备
  3. Broker：使用
  4. 密码：一致
  5. 网络：冗余
  6. 验证：同步
  7. 监控：延迟
  8. 文档：配置
  9. 演练：切换
  10. 测试：数据

13. 参考资料

[1] Oracle Data Guard 19c, “Creating a Standby Database” https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/