2. 首页 > Oracle教程 > 正文

Oracle教程FG255-Oracle GoldenGate与ADG集成实战

教程整理：风哥教程  |  更新时间：2026-01-09  |  教程分类：Oracle教程  |  文档学习：18

本文档风哥主要介绍Oracle GoldenGate与Active Data Guard(ADG)集成的实战操作，包括GoldenGate与ADG集成的概念、架构、场景、配置方法、切换操作等内容，由风哥教程参考Oracle官方文档GoldenGate和ADG文档，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 Oracle ADG概念

Oracle Active Data Guard(ADG)是Oracle数据库的高可用和灾难恢复解决方案，它通过实时应用主库的Redo日志到备库，保持备库与主库的数据同步。ADG支持在备库上进行只读查询，实现读写分离。更多视频教程www.fgedu.net.cn

Oracle ADG的主要特性：

  • 实时数据保护：备库实时应用Redo日志
  • 读写分离：备库支持只读查询
  • 快速故障切换：支持主备切换和故障转移
  • 自动修复：自动检测和修复数据差异
  • 多备库支持：支持一主多备架构

1.2 GoldenGate与ADG集成概念

Oracle GoldenGate与ADG集成是指将GoldenGate部署在ADG环境中，利用ADG的高可用特性，实现GoldenGate的高可用部署。GoldenGate可以从ADG主库或备库读取数据，也可以向ADG主库或备库写入数据。

1.3 GoldenGate与ADG集成场景

Oracle GoldenGate与ADG集成主要场景：

  • 从主库读取：GoldenGate Extract从ADG主库读取Redo日志
  • 从备库读取：GoldenGate Extract从ADG备库读取Redo日志，减轻主库压力
  • 写入主库：GoldenGate Replicat向ADG主库写入数据
  • 写入备库：GoldenGate Replicat向ADG备库写入数据（需要ADG级联）
  • 双向复制：GoldenGate在ADG主备库之间双向复制
风哥提示：GoldenGate从ADG备库读取数据可以显著减轻主库压力，特别是在大数据量复制场景下。但需要确保备库的日志应用延迟在可接受范围内。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 GoldenGate与ADG集成规划

GoldenGate与ADG集成规划要点：

# 架构规划
场景1：GoldenGate从主库读取
– 适用：主库资源充足，复制延迟要求低
– 优点：配置简单，延迟最低
– 缺点：增加主库负载

场景2：GoldenGate从备库读取
– 适用：主库资源紧张，可接受一定延迟
– 优点：减轻主库压力
– 缺点：复制延迟增加

场景3：GoldenGate写入备库
– 适用：需要向备库写入数据
– 优点：减轻主库写入压力
– 缺点：需要ADG级联配置

# 资源规划
主库资源：
– CPU：预留20%用于GoldenGate
– 内存：预留4GB用于GoldenGate
– I/O：预留10%用于日志读取

备库资源：
– CPU：预留30%用于GoldenGate和日志应用
– 内存：预留6GB用于GoldenGate和缓冲
– I/O：预留20%用于日志应用和数据读取

# 网络规划
主备复制带宽：根据日志生成量计算
GoldenGate传输带宽：根据数据量计算
建议：网络带宽要有30%冗余

2.2 GoldenGate与ADG架构设计

GoldenGate与ADG集成架构设计：

# 架构1：GoldenGate从主库读取
[主库] <--ADG同步--> [备库]
|
| GoldenGate Extract
v
[目标库]

特点：
– GoldenGate Extract部署在主库
– 直接从主库读取Redo日志
– 复制延迟最小
– 增加主库负载

# 架构2：GoldenGate从备库读取
[主库] <--ADG同步--> [备库]
|
| GoldenGate Extract
v
[目标库]

特点：
– GoldenGate Extract部署在备库
– 从备库读取Redo日志
– 减轻主库压力
– 复制延迟增加（取决于备库应用延迟）

# 架构3：GoldenGate双向复制+ADG
[主库A] <--GoldenGate--> [主库B]
| |
| ADG | ADG
v v
[备库A] [备库B]

特点：
– 两个ADG环境之间双向复制
– 实现双活架构
– 复杂度较高
– 需要处理冲突

2.3 GoldenGate与ADG集成最佳实践

GoldenGate与ADG集成最佳实践：

  • 监控备库延迟：确保备库日志应用延迟在可接受范围
  • 配置自动切换：ADG切换时GoldenGate自动切换
  • 资源隔离：GoldenGate与数据库进程资源隔离
  • 网络优化：优化主备之间和GoldenGate传输网络
  • 定期演练：定期进行ADG切换和GoldenGate切换演练
  • 文档记录：记录所有配置和切换流程
生产环境建议：建议从备库读取数据，以减轻主库压力。但要确保备库应用延迟不超过5分钟，否则会影响GoldenGate复制延迟。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 主库GoldenGate配置

3.1.1 主库数据库配置

# 1. 开启归档和强制日志
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fgedudb’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;

SQL> ALTER DATABASE FORCE LOGGING;

# 2. 开启补充日志
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

# 3. 创建GoldenGate用户
SQL> CREATE USER fgedu_ggadmin IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

SQL> GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW TO fgedu_ggadmin;

SQL> GRANT CONNECT, RESOURCE, DBA TO fgedu_ggadmin;

SQL> GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO fgedu_ggadmin;

SQL> GRANT ALTER SYSTEM TO fgedu_ggadmin;

SQL> GRANT FLASHBACK ANY TABLE TO fgedu_ggadmin;

# 4. 配置ADG参数
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME=’fgedudb_primary’ SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(fgedudb_primary,fgedudb_standby)’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=fgedudb_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=fgedudb_standby’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER=’fgedudb_standby’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

# 5. 创建Standby日志文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/oradata/fgedudb/standby_redo04.log’ SIZE 500M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/oradata/fgedudb/standby_redo05.log’ SIZE 500M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/oradata/fgedudb/standby_redo06.log’ SIZE 500M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/oradata/fgedudb/standby_redo07.log’ SIZE 500M;

3.1.2 主库GoldenGate配置

# 1. 配置Manager
GGSCI (fgedu-primary.net.cn) 1> edit params mgr

PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5
LAGREPORTMINUTES 10
LAGINFOMINUTES 5
LAGCRITICALMINUTES 10
PURGEOLDEXTRACTS /oracle/goldengate/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7

# 2. 配置Extract
GGSCI (fgedu-primary.net.cn) 2> edit params ext01

EXTRACT ext01
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin
EXTTRAIL /oracle/goldengate/dirdat/et

TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 4, max_sga_size 512)

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 3. 注册Extract
GGSCI (fgedu-primary.net.cn) 3> dblogin useridalias fgedu_ggadmin

GGSCI (fgedu-primary.net.cn) 4> register extract ext01 database

# 4. 添加Extract
GGSCI (fgedu-primary.net.cn) 5> add extract ext01, integrated tranlog, begin now

GGSCI (fgedu-primary.net.cn) 6> add exttrail /oracle/goldengate/dirdat/et, extract ext01, megabytes 500

# 5. 配置数据泵
GGSCI (fgedu-primary.net.cn) 7> edit params dp01

EXTRACT dp01
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
PASSTHRU
RMTHOST 192.168.1.100, MGRPORT 7809
RMTTRAIL /oracle/goldengate/dirdat/rt

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 6. 添加数据泵
GGSCI (fgedu-primary.net.cn) 8> add extract dp01, exttrailsource /oracle/goldengate/dirdat/et

GGSCI (fgedu-primary.net.cn) 9> add rmttrail /oracle/goldengate/dirdat/rt, extract dp01, megabytes 500

# 7. 启动进程
GGSCI (fgedu-primary.net.cn) 10> start mgr

GGSCI (fgedu-primary.net.cn) 11> start extract ext01

GGSCI (fgedu-primary.net.cn) 12> start extract dp01

# 8. 验证状态
GGSCI (fgedu-primary.net.cn) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:03
EXTRACT RUNNING DP01 00:00:00 00:00:05

3.2 备库GoldenGate配置

3.2.1 从备库读取配置

# 1. 备库数据库配置
# 确保备库已开启补充日志
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MI
————————
YES

# 2. 创建GoldenGate用户
SQL> CREATE USER fgedu_ggadmin IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

SQL> GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW TO fgedu_ggadmin;

SQL> GRANT CONNECT, RESOURCE, DBA TO fgedu_ggadmin;

SQL> GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO fgedu_ggadmin;

SQL> GRANT FLASHBACK ANY TABLE TO fgedu_ggadmin;

# 3. 备库GoldenGate配置
GGSCI (fgedu-standby.net.cn) 1> edit params mgr

PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 5
LAGREPORTMINUTES 10
LAGINFOMINUTES 5
LAGCRITICALMINUTES 10
PURGEOLDEXTRACTS /oracle/goldengate/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7

# 4. 配置从备库读取的Extract
GGSCI (fgedu-standby.net.cn) 2> edit params ext01_standby

EXTRACT ext01_standby
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin
EXTTRAIL /oracle/goldengate/dirdat/es

# 从备库读取Redo日志
TRANLOGOPTIONS MINEFROMACTIVEDG

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 5. 注册Extract
GGSCI (fgedu-standby.net.cn) 3> dblogin useridalias fgedu_ggadmin

GGSCI (fgedu-standby.net.cn) 4> register extract ext01_standby database

# 6. 添加Extract
GGSCI (fgedu-standby.net.cn) 5> add extract ext01_standby, integrated tranlog, begin now

GGSCI (fgedu-standby.net.cn) 6> add exttrail /oracle/goldengate/dirdat/es, extract ext01_standby, megabytes 500

# 7. 配置数据泵
GGSCI (fgedu-standby.net.cn) 7> edit params dp01_standby

EXTRACT dp01_standby
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
PASSTHRU
RMTHOST 192.168.1.100, MGRPORT 7809
RMTTRAIL /oracle/goldengate/dirdat/rs

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 8. 添加数据泵
GGSCI (fgedu-standby.net.cn) 8> add extract dp01_standby, exttrailsource /oracle/goldengate/dirdat/es

GGSCI (fgedu-standby.net.cn) 9> add rmttrail /oracle/goldengate/dirdat/rs, extract dp01_standby, megabytes 500

# 9. 启动进程
GGSCI (fgedu-standby.net.cn) 10> start mgr

GGSCI (fgedu-standby.net.cn) 11> start extract ext01_standby

GGSCI (fgedu-standby.net.cn) 12> start extract dp01_standby

# 10. 验证状态
GGSCI (fgedu-standby.net.cn) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXT01_STANDBY 00:00:00 00:00:03
EXTRACT RUNNING DP01_STANDBY 00:00:00 00:00:05

3.3 切换后GoldenGate配置

3.3.1 ADG切换后GoldenGate切换脚本

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# gg_adg_switchover.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# GoldenGate与ADG切换脚本
# 在ADG切换后自动切换GoldenGate

PRIMARY_HOST=”fgedu-primary.net.cn”
STANDBY_HOST=”fgedu-standby.net.cn”
GG_HOME=”/oracle/goldengate”
SWITCH_LOG=”$GG_HOME/ha/adg_switchover_$(date +%Y%m%d%H%M%S).log”

# 记录日志
log_message() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’): $1” | tee -a $SWITCH_LOG
}

# 检查当前主备角色
check_database_role() {
HOST=$1
ROLE=$(ssh $HOST “sqlplus -s / as sysdba << EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT database_role FROM v\$database;
EXIT;
EOF” | tr -d ‘[:space:]’)
echo $ROLE
}

# 停止原主库GoldenGate
stop_primary_gg() {
log_message “Stopping GoldenGate on original primary…”

# 停止Extract
ssh $PRIMARY_HOST “echo ‘stop extract dp01’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5
ssh $PRIMARY_HOST “echo ‘stop extract ext01’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5
ssh $PRIMARY_HOST “echo ‘stop mgr’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5

log_message “GoldenGate stopped on original primary”
}

# 启动新主库GoldenGate
start_new_primary_gg() {
log_message “Starting GoldenGate on new primary…”

# 同步配置
log_message “Synchronizing configuration…”
rsync -avz $PRIMARY_HOST:$GG_HOME/dirprm/ $STANDBY_HOST:$GG_HOME/dirprm/ 2>/dev/null
rsync -avz $PRIMARY_HOST:$GG_HOME/dirchk/ $STANDBY_HOST:$GG_HOME/dirchk/ 2>/dev/null

# 修改Extract配置（从主库读取）
ssh $STANDBY_HOST “cat > $GG_HOME/dirprm/ext01.prm << 'EOF' EXTRACT ext01 SETENV (NLS_LANG=\"AMERICAN_AMERICA.AL32UTF8\") USERIDALIAS fgedu_ggadmin EXTTRAIL /oracle/goldengate/dirdat/et TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 4, max_sga_size 512) TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;
EOF”

# 启动Manager
ssh $STANDBY_HOST “echo ‘start mgr’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5

# 启动Extract
ssh $STANDBY_HOST “echo ‘start extract ext01’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5

# 启动数据泵
ssh $STANDBY_HOST “echo ‘start extract dp01’ | $GG_HOME/ggsci” >> $SWITCH_LOG 2>&1
sleep 5

log_message “GoldenGate started on new primary”
}

# 验证GoldenGate状态
verify_gg_status() {
log_message “Verifying GoldenGate status…”

STATUS=$(ssh $STANDBY_HOST “echo ‘info all’ | $GG_HOME/ggsci”)
log_message “GoldenGate status:\n$STATUS”

if echo “$STATUS” | grep -q “ABENDED\|STOPPED”; then
log_message “WARNING: Some GoldenGate processes are not running”
return 1
fi

log_message “GoldenGate verification passed”
return 0
}

# 主程序
main() {
log_message “ADG switchover and GoldenGate switch started”

# 检查当前角色
PRIMARY_ROLE=$(check_database_role $PRIMARY_HOST)
STANDBY_ROLE=$(check_database_role $STANDBY_HOST)

log_message “Original primary role: $PRIMARY_ROLE”
log_message “Original standby role: $STANDBY_ROLE”

# 确认角色已切换
if [ “$PRIMARY_ROLE” == “PHYSICALSTANDBY” ] && [ “$STANDBY_ROLE” == “PRIMARY” ]; then
log_message “ADG switchover confirmed”

# 执行GoldenGate切换
stop_primary_gg
start_new_primary_gg
verify_gg_status

# 发送通知
echo “GoldenGate switchover completed after ADG switchover.” | \
mail -s “GoldenGate Switchover Completed” dba@fgedu.net.cn

log_message “ADG switchover and GoldenGate switch completed”
else
log_message “ERROR: ADG switchover not confirmed. Primary: $PRIMARY_ROLE, Standby: $STANDBY_ROLE”
exit 1
fi
}

main

风哥提示：GoldenGate与ADG集成时，要特别注意切换流程。建议编写完整的切换脚本，并定期进行演练，确保切换过程顺利。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 GoldenGate从ADG备库读取案例

以下是一个完整的从ADG备库读取案例：

# 场景：生产系统压力大，需要从ADG备库读取数据

# 1. 环境信息
# 主库：fgedu-primary.net.cn (192.168.1.10)
# 备库：fgedu-standby.net.cn (192.168.1.11)
# 目标库：fgedu-target.net.cn (192.168.1.100)

# 2. 检查ADG状态
SQL> SELECT DATABASE_ROLE, OPEN_MODE, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
—————- ——————– ——————–
PRIMARY READ WRITE TO STANDBY

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE#
——— ———— ———- ———-
ARCH CLOSING 1 1234
ARCH CLOSING 1 1235
MRP0 APPLYING_LOG 1 1236
RFS IDLE 1 0

# 3. 检查备库应用延迟
SQL> SELECT NAME, VALUE, UNIT, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME=’apply lag’;

NAME VALUE UNIT TIME_COMPUTED
———- ———- —— ——————-
apply lag +00 00:00:00 day(2) to second(0) interval 04/03/2026 10:00:00

# 4. 备库GoldenGate配置
GGSCI (fgedu-standby.net.cn) 1> edit params ext_standby

EXTRACT ext_standby
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin
EXTTRAIL /oracle/goldengate/dirdat/es

# 从ADG备库读取Redo日志
TRANLOGOPTIONS MINEFROMACTIVEDG
TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 4)

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;
TABLE fgedu.fgedu_products;

# 5. 注册和添加Extract
GGSCI (fgedu-standby.net.cn) 2> dblogin useridalias fgedu_ggadmin

GGSCI (fgedu-standby.net.cn) 3> register extract ext_standby database
Extract EXT_STANDBY successfully registered with database at SCN 12345678.

GGSCI (fgedu-standby.net.cn) 4> add extract ext_standby, integrated tranlog, begin now
Extract EXT_STANDBY added.

GGSCI (fgedu-standby.net.cn) 5> add exttrail /oracle/goldengate/dirdat/es, extract ext_standby, megabytes 500
EXTTRAIL added.

# 6. 启动Extract
GGSCI (fgedu-standby.net.cn) 6> start extract ext_standby

Sending START request to MANAGER …
EXTRACT EXT_STANDBY starting

# 7. 验证状态
GGSCI (fgedu-standby.net.cn) 7> info extract ext_standby

EXTRACT EXT_STANDBY Last Started 2026-04-03 10:30:00 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2026-04-03 10:30:00 SCN 12345678

# 8. 监控性能
GGSCI (fgedu-standby.net.cn) 8> stats extract ext_standby

Sending STATS request to EXTRACT EXT_STANDBY …

Start of Statistics at 2026-04-03 11:00:00.

Extracting from FGEDU.FGEDU_ORDERS to FGEDU.FGEDU_ORDERS:
*** Total statistics since 2026-04-03 10:30:00 ***
Total inserts 5000.00
Total updates 2000.00
Total deletes 500.00
Total operations 7500.00

# 9. 对比主库和备库Extract性能
# 主库Extract TPS：约1000
# 备库Extract TPS：约950（略有下降，但在可接受范围）

# 10. 监控备库应用延迟
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# monitor_standby_lag.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

STANDBY_HOST=”fgedu-standby.net.cn”
ALERT_THRESHOLD=300 # 5分钟

# 获取备库应用延迟
APPLY_LAG=$(ssh $STANDBY_HOST “sqlplus -s / as sysdba << EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT EXTRACT(DAY FROM apply_lag)*86400 + EXTRACT(HOUR FROM apply_lag)*3600 + EXTRACT(MINUTE FROM apply_lag)*60 + EXTRACT(SECOND FROM apply_lag) FROM v\$dataguard_stats WHERE name='apply lag';
EXIT;
EOF” | tr -d ‘[:space:]’)

if [ -n “$APPLY_LAG” ] && [ “$APPLY_LAG” -gt “$ALERT_THRESHOLD” ]; then
echo “ALERT: Standby apply lag is ${APPLY_LAG} seconds, exceeds threshold ${ALERT_THRESHOLD}”
echo “Standby apply lag is ${APPLY_LAG} seconds” | mail -s “Standby Lag Alert” dba@fgedu.net.cn
fi

4.2 ADG切换后GoldenGate自动切换案例

以下是一个完整的ADG切换后GoldenGate自动切换案例：

# 场景：ADG主备切换后，GoldenGate自动切换到新的主库

# 1. 执行ADG切换
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

# 在备库执行
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

# 2. 验证角色切换
SQL> SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE
—————- ——————–
PRIMARY READ WRITE

# 3. 执行GoldenGate自动切换
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# auto_switchover.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

ORIGINAL_PRIMARY=”fgedu-primary.net.cn”
ORIGINAL_STANDBY=”fgedu-standby.net.cn”
GG_HOME=”/oracle/goldengate”
LOG_FILE=”$GG_HOME/ha/auto_switchover_$(date +%Y%m%d%H%M%S).log”

exec > >(tee -a $LOG_FILE)
exec 2>&1

echo “$(date): Starting automatic GoldenGate switchover”

# 检查新主库角色
NEW_PRIMARY_ROLE=$(ssh $ORIGINAL_STANDBY “sqlplus -s / as sysdba << EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT database_role FROM v\$database;
EXIT;
EOF” | tr -d ‘[:space:]’)

if [ “$NEW_PRIMARY_ROLE” != “PRIMARY” ]; then
echo “ERROR: Original standby is not primary. Role: $NEW_PRIMARY_ROLE”
exit 1
fi

echo “$(date): Confirmed new primary: $ORIGINAL_STANDBY”

# 停止原主库GoldenGate
echo “$(date): Stopping GoldenGate on original primary…”
ssh $ORIGINAL_PRIMARY “echo ‘stop extract dp01’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5
ssh $ORIGINAL_PRIMARY “echo ‘stop extract ext01’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5
ssh $ORIGINAL_PRIMARY “echo ‘stop mgr’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5
echo “$(date): GoldenGate stopped on original primary”

# 在新主库启动GoldenGate
echo “$(date): Starting GoldenGate on new primary…”

# 同步检查点文件
rsync -avz $ORIGINAL_PRIMARY:$GG_HOME/dirchk/ $ORIGINAL_STANDBY:$GG_HOME/dirchk/ 2>/dev/null

# 启动Manager
ssh $ORIGINAL_STANDBY “echo ‘start mgr’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5

# 启动Extract
ssh $ORIGINAL_STANDBY “echo ‘start extract ext01’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5

# 启动数据泵
ssh $ORIGINAL_STANDBY “echo ‘start extract dp01’ | $GG_HOME/ggsci” 2>/dev/null
sleep 5

echo “$(date): GoldenGate started on new primary”

# 验证状态
echo “$(date): Verifying GoldenGate status…”
STATUS=$(ssh $ORIGINAL_STANDBY “echo ‘info all’ | $GG_HOME/ggsci”)
echo “$STATUS”

if echo “$STATUS” | grep -q “ABENDED\|STOPPED”; then
echo “$(date): WARNING: Some processes are not running”
echo “GoldenGate switchover completed with warnings” | mail -s “GoldenGate Switchover Warning” dba@fgedu.net.cn
else
echo “$(date): GoldenGate switchover completed successfully”
echo “GoldenGate switchover completed successfully” | mail -s “GoldenGate Switchover Success” dba@fgedu.net.cn
fi

echo “$(date): Automatic GoldenGate switchover completed”

4.3 GoldenGate与ADG双向复制案例

以下是一个完整的GoldenGate与ADG双向复制案例：

# 场景：两个ADG环境之间双向复制，实现双活架构

# 架构说明
# 站点A：ADG Primary (fgedu-sitea-primary) + ADG Standby (fgedu-sitea-standby)
# 站点B：ADG Primary (fgedu-siteb-primary) + ADG Standby (fgedu-siteb-standby)
# GoldenGate：站点A <-> 站点B 双向复制

# 1. 站点A配置
# 1.1 Extract配置（从站点A主库读取）
GGSCI (fgedu-sitea-primary) 1> edit params ext_a

EXTRACT ext_a
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_a
EXTTRAIL /oracle/goldengate/dirdat/ea

TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 4)

# 只复制站点A产生的数据
TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 1.2 Replicat配置（向站点A主库写入）
GGSCI (fgedu-sitea-primary) 2> edit params rep_a

REPLICAT rep_a
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_a
DISCARDFILE /oracle/goldengate/dirrpt/rep_a.dsc, APPEND, MEGABYTES 100

# 冲突检测和处理
HANDLECOLLISIONS
MAP fgedu.fgedu_orders, TARGET fgedu.fgedu_orders, KEYCOLS (order_id);
MAP fgedu.fgedu_customers, TARGET fgedu.fgedu_customers, KEYCOLS (customer_id);

# 2. 站点B配置
# 2.1 Extract配置（从站点B主库读取）
GGSCI (fgedu-siteb-primary) 1> edit params ext_b

EXTRACT ext_b
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_b
EXTTRAIL /oracle/goldengate/dirdat/eb

TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 4)

# 只复制站点B产生的数据
TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 2.2 Replicat配置（向站点B主库写入）
GGSCI (fgedu-siteb-primary) 2> edit params rep_b

REPLICAT rep_b
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_b
DISCARDFILE /oracle/goldengate/dirrpt/rep_b.dsc, APPEND, MEGABYTES 100

# 冲突检测和处理
HANDLECOLLISIONS
MAP fgedu.fgedu_orders, TARGET fgedu.fgedu_orders, KEYCOLS (order_id);
MAP fgedu.fgedu_customers, TARGET fgedu.fgedu_customers, KEYCOLS (customer_id);

# 3. 数据泵配置
# 站点A数据泵
GGSCI (fgedu-sitea-primary) 3> edit params dp_a

EXTRACT dp_a
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
PASSTHRU
RMTHOST fgedu-siteb-primary.net.cn, MGRPORT 7809
RMTTRAIL /oracle/goldengate/dirdat/ra

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 站点B数据泵
GGSCI (fgedu-siteb-primary) 3> edit params dp_b

EXTRACT dp_b
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
PASSTHRU
RMTHOST fgedu-sitea-primary.net.cn, MGRPORT 7809
RMTTRAIL /oracle/goldengate/dirdat/rb

TABLE fgedu.fgedu_orders;
TABLE fgedu.fgedu_customers;

# 4. 启动双向复制
# 站点A
GGSCI (fgedu-sitea-primary) 4> start mgr
GGSCI (fgedu-sitea-primary) 5> start extract ext_a
GGSCI (fgedu-sitea-primary) 6> start extract dp_a
GGSCI (fgedu-sitea-primary) 7> start replicat rep_a

# 站点B
GGSCI (fgedu-siteb-primary) 4> start mgr
GGSCI (fgedu-siteb-primary) 5> start extract ext_b
GGSCI (fgedu-siteb-primary) 6> start extract dp_b
GGSCI (fgedu-siteb-primary) 7> start replicat rep_b

# 5. 验证双向复制
# 在站点A插入数据
SQL> INSERT INTO fgedu.fgedu_orders (order_id, customer_id, order_amount) VALUES (10001, 1000, 1500.00);

SQL> COMMIT;

# 验证站点B是否收到数据
SQL> SELECT * FROM fgedu.fgedu_orders WHERE order_id = 10001;

ORDER_ID CUSTOMER_ID ORDER_AMOUNT
———- ———– ————
10001 1000 1500.00

# 在站点B插入数据
SQL> INSERT INTO fgedu.fgedu_orders (order_id, customer_id, order_amount) VALUES (10002, 1001, 2000.00);

SQL> COMMIT;

# 验证站点A是否收到数据
SQL> SELECT * FROM fgedu.fgedu_orders WHERE order_id = 10002;

ORDER_ID CUSTOMER_ID ORDER_AMOUNT
———- ———– ————
10002 1001 2000.00

# 6. 冲突处理配置
# 当两个站点同时更新同一行数据时，需要冲突处理
GGSCI (fgedu-sitea-primary) 8> edit params rep_a

REPLICAT rep_a
SETENV (NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″)
USERIDALIAS fgedu_ggadmin_a
DISCARDFILE /oracle/goldengate/dirrpt/rep_a.dsc, APPEND, MEGABYTES 100

# 冲突解决规则：以时间戳为准，保留最新的更新
MAP fgedu.fgedu_orders, TARGET fgedu.fgedu_orders, KEYCOLS (order_id),
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_update_time))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_update_time))),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

MAP fgedu.fgedu_customers, TARGET fgedu.fgedu_customers, KEYCOLS (customer_id),
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_update_time))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_update_time))),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

# 7. 监控双向复制
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# monitor_bidirectional.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

SITEA_HOST=”fgedu-sitea-primary.net.cn”
siteb_HOST=”fgedu-siteb-primary.net.cn”
GG_HOME=”/oracle/goldengate”

echo “=== Bidirectional Replication Status ===”
echo “Time: $(date)”

echo -e “\n— Site A Status —”
ssh $SITEA_HOST “echo ‘info all’ | $GG_HOME/ggsci”

echo -e “\n— Site B Status —”
ssh $siteb_HOST “echo ‘info all’ | $GG_HOME/ggsci”

echo -e “\n— Site A Statistics —”
ssh $SITEA_HOST “echo ‘stats extract ext_a’ | $GG_HOME/ggsci”

echo -e “\n— Site B Statistics —”
ssh $siteb_HOST “echo ‘stats extract ext_b’ | $GG_HOME/ggsci”

echo -e “\n=== Check Completed ===”

生产环境建议：GoldenGate与ADG集成可以显著提高系统可用性和性能。建议根据业务需求选择合适的集成方案，并建立完善的监控和切换机制。from oracle:www.itpux.com

Part05-风哥经验总结与分享

5.1 GoldenGate与ADG集成总结

GoldenGate与ADG集成的关键要点：

  • 架构选择：根据业务需求选择合适的集成架构
  • 延迟监控：密切监控备库应用延迟和GoldenGate复制延迟
  • 自动切换：实现ADG切换后GoldenGate自动切换
  • 冲突处理：双向复制时要配置冲突检测和处理机制
  • 定期演练：定期进行切换演练，确保切换流程正确

5.2 GoldenGate与ADG集成检查清单

GoldenGate与ADG集成检查清单：

# 部署前检查清单
□ ADG主备库配置正确
□ 补充日志已开启
□ GoldenGate用户权限正确
□ 网络连通性测试通过
□ 切换脚本已编写和测试

# 部署后检查清单
□ GoldenGate进程状态正常
□ 复制延迟在可接受范围
□ 备库应用延迟正常
□ 监控告警配置完成
□ 切换流程文档化

# 日常维护检查清单
□ ADG同步状态正常
□ GoldenGate进程状态正常
□ 复制延迟监控正常
□ 备库应用延迟正常
□ 切换脚本定期测试

5.3 GoldenGate与ADG集成工具推荐

GoldenGate与ADG集成推荐工具：

  • Data Guard Broker：ADG管理和切换工具
  • GoldenGate Veridata：数据一致性校验工具
  • Oracle Enterprise Manager：综合监控管理平台
  • 自定义脚本：切换和监控脚本
  • 监控工具：Zabbix、Prometheus等
风哥提示：GoldenGate与ADG集成是高可用架构的重要组成部分。要充分理解两者的协同工作机制，建立完善的监控和切换流程，确保系统的高可用性。更多视频教程www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息