Oracle Data Guard 部署详解

适用版本：Oracle Database 19c 文档版本：v1.0 / 2026-07

---

1. 概述

Data Guard 提供灾备与高可用[1]：

类型：

• 物理 Standby
• 逻辑 Standby
• Snapshot Standby
• Far Sync

详细见：Oracle Data Guard 架构详解。

---

2. 架构

┌─────────────┐          ┌─────────────┐
│  Primary    │          │  Standby    │
│  (读写)     │ ───────► │  (只读)     │
└─────────────┘  Redo    └─────────────┘
       ▲           传输         │
       │                        │
       └────────────────────────┘
              Switchover/Failover

---

3. 主备规划

角色	主机	DB_UNIQUE_NAME
Primary	pri-db	orcl_pri
Standby	std-db	orcl_std

---

4. 主库配置

4.1 归档

ALTER DATABASE FORCE LOGGING;
ARCHIVE LOG LIST;

-- 启用归档
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

4.2 Standby Redo Log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oradata/srl04.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/srl05.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/srl06.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/srl07.log') SIZE 50M;

4.3 参数

ALTER SYSTEM SET db_unique_name='orcl_pri' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl_pri,orcl_std)';
ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcl_pri';
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_1=enable;
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','/u01/oradata' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='/u01/oradata','/u01/oradata' SCOPE=SPFILE;

4.4 监听

# listener.ora (Primary)
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_pri)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

4.5 tnsnames

ORCL_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pri-db)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl_pri))
  )

ORCL_STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl_std))
  )

---

5. 备库准备

5.1 ORACLE_HOME

- 同版本
- 同补丁

5.2 密码文件

scp $ORACLE_HOME/dbs/orapworcl oracle@std-db:$ORACLE_HOME/dbs/

5.3 参数文件

# initstd.ora
DB_NAME=orcl
DB_UNIQUE_NAME=orcl_std

5.4 启动 nomount

sqlplus / as sysdba
STARTUP NOMOUNT PFILE='?/dbs/initstd.ora';

---

6. RMAN DUPLICATE

6.1 主库连接备库

rman target / auxiliary sys/Oracle123@orcl_std

6.2 Duplicate

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;

6.3 备库参数

-- 备库
ALTER SYSTEM SET db_unique_name='orcl_std' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl_pri,orcl_std)';
ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcl_std';
ALTER SYSTEM SET log_archive_dest_2='service=orcl_pri async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pri';
ALTER SYSTEM SET fal_server='orcl_pri';
ALTER SYSTEM SET fal_client='orcl_std';
ALTER SYSTEM SET standby_file_management='AUTO';

---

7. 启动 Redo Apply

7.1 物理备库

-- 启动 MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 实时应用（Standby Redo Log）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-- 停止
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

7.2 开启只读

-- 停 MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- 只读打开
ALTER DATABASE OPEN READ ONLY;

-- 同时应用（Active Data Guard）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

---

8. 验证

8.1 主库

SELECT dest_id, status, error FROM v$archive_dest WHERE dest_id <= 2;

SELECT thread#, sequence#, archived, applied 
FROM v$archived_log ORDER BY sequence# DESC;

SELECT MAX(sequence#) FROM v$archived_log;

8.2 备库

SELECT process, status, thread#, sequence# FROM v$managed_standby;

SELECT archived_thread#, archived_seq#, applied_thread#, applied_seq# 
FROM v$archive_dest_status;

SELECT name, value FROM v$dataguard_stats;

8.3 同步

-- 主库切换日志
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- 备库验证
SELECT sequence#, applied FROM v$archived_log ORDER BY sequence# DESC;

---

9. Data Guard Broker

9.1 启用

ALTER SYSTEM SET dg_broker_start=true;

9.2 配置

dgmgrl sys/Oracle123@orcl_pri

DGMGRL> CREATE CONFIGURATION orcl_dg 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> ENABLE CONFIGURATION;

9.3 查看

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

---

10. Switchover

10.1 Broker

DGMGRL> SWITCHOVER TO orcl_std;
DGMGRL> SHOW CONFIGURATION;

10.2 SQL

-- 主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- 重启
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- 备库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

-- 原 Primary 启 MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

---

11. Failover

11.1 Broker

DGMGRL> FAILOVER TO orcl_std;

11.2 SQL

-- 备库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE OPEN;

11.3 重建原 Primary

# 原 Primary 作为新 Standby
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE ...;

---

12. 保护模式

12.1 Maximum Performance（默认）

- ASYNC
- 数据可能丢失

12.2 Maximum Availability

- SYNC
- 零数据丢失
- 主库等待

12.3 Maximum Protection

- SYNC
- 零数据丢失
- 强制

12.4 切换

ALTER SYSTEM SET log_archive_dest_2='service=orcl_std sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl_std';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

---

13. 监控

13.1 进程

SELECT process, status, client_process, thread#, sequence# FROM v$managed_standby;

13.2 延迟

SELECT name, value, time_computed FROM v$dataguard_stats;
-- transport lag
-- apply lag

13.3 Gap

SELECT * FROM v$archive_gap;

---

14. 常见坑与排错

14.1 ORA-12154

- tnsnames.ora
- 监听器
- 服务名

14.2 ORA-16191

- 密码文件
- sys 密码
- 同步

14.3 ORA-16047

- DG_CONFIG 不匹配
- 检查

14.4 Gap

SELECT * FROM v$archive_gap;
-- 手动注册
ALTER DATABASE REGISTER LOGFILE '/path/to/log';

---

15. 最佳实践

1. Standby Redo Log：实时
2. FORCE LOGGING：完整
3. Broker：简化
4. 保护模式：业务
5. Active Data Guard：只读
6. 监控 lag：及时
7. 密码文件同步：必要
8. 网络优化：传输
9. 测试切换：能力
10. 文档化：流程

---

16. 参考资料

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