Oracle Data Guard 配置与同步指南

一、主库配置修改

1. 检查当前归档日志配置

SHOW PARAMETER log_archive_config;

2. 修改归档日志配置

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(ora01,ora02,ora03,ora04)' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_4='SERVICE="ora04", ASYNC NOAFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME="ora04" DELAY=0 OPTIONAL COMPRESSION=DISABLE MAX_FAILURE=0 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)';

3. 在C88DC02、C88DC03、C88DC04上执行

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(ora01,ora02,ora03,C88DC04)' SCOPE=SPFILE;

二、从库配置修改

1. 修改参数文件

ora04.__data_transfer_cache_size=0
ora04.__db_cache_size=274877906944
ora04.__pga_aggregate_target=79993765888
ora04.__sga_target=312458870784
ora04.__shared_pool_size=34359738368
ora04.__large_pool_size=2684354560
*.audit_file_dest='/oracle/app/oracle/admin/C88DC04/adump'
*.compatible='19.0.0'
*.control_files='/oradata/ora04/controlfile/control_file1.ctl','/oradata/flash_recover_area/ora04/controlfile/control_file2.ctl'
*.db_name='ora01'
*.db_unique_name='ora04'
*.fal_client='ora04'
*.fal_server='ora01'
*.log_archive_config='DG_CONFIG=(ora01,ora02,ora03,ora04)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

2. 配置环境变量

export ORACLE_SID=ora04
export ORACLE_UNQNAME=ora04
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin

3. 复制密码文件

orapwora04
mkdir -p /oracle/app/oracle/admin/ora04/adump
mkdir -p /oradata/flash_recover_area

三、监听与TNS配置

1. 配置监听器（listener.ora）

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.41)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora04)
     (ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
     (SID_NAME = ora04)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = ora04_DGMGRL)
     (ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
     (SID_NAME = ora04)
    )
)

2. 配置TNS（tnsnames.ora）

ora01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora01)
    )
  )

四、Data Guard 启动流程

1. 启动到 NOMOUNT 状态

STARTUP NOMOUNT PFILE='/oracle/app/oracle/product/19.3.0/db_1/dbs/initora04.ora';

2. 复制主库数据到备库

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

3. 启动到 READ ONLY 模式

ALTER DATABASE OPEN READ ONLY;

4. 开启同步

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

五、日志同步监控

1. 查询 GAP 信息

SELECT al.thrd, almax, log1sttime, alscn1, alnscn,
   lhmax, hist1sttime, lhscn1, lhnscn, almax-lhmax diff
FROM (SELECT thread# thrd, MAX(sequence#) almax, MAX(FIRST_CHANGE#) alscn1,
   MAX(next_change#) alnscn, TO_CHAR(MAX(first_time),'yyyy/mm/dd hh24:mi:ss') log1sttime
FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
   (SELECT thread# thrd, MAX(sequence#) lhmax, MAX(FIRST_CHANGE#) lhscn1,
   MAX(next_change#) lhnscn, TO_CHAR(MAX(first_time),'yyyy/mm/dd hh24:mi:ss') hist1sttime
FROM v$log_history
WHERE first_time=(SELECT MAX(first_time) FROM v$log_history)
GROUP BY thread#) lh
WHERE al.thrd(+) = lh.thrd;

2. 查看 alert.log

find / -name "alert*.log" 2>/dev/null | xargs -n 1 -I {} echo {} | head -n 1

六、故障处理

1. 处理日志 GAP 问题

BACKUP ARCHIVELOG FROM SEQUENCE 1336581 UNTIL SEQUENCE 1336596;

在主库手动恢复缺失的日志，并在备库应用：

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/path_to_archivelog.log';

2. 检查 Data Guard 进程状态

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

3. 检查归档日志是否正确应用

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC;