[Oracle] Data Guard Duplicate 재구성

Data Guard Broker 구성 중 파일 경로 및 로그 파일의 구성들이 꼬여서 경고 발생

DGMGRL> show database dgstby

Database - dgstby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      12 minutes 8 seconds (computed 16 seconds ago)
  Apply Lag:          12 minutes 8 seconds (computed 16 seconds ago)
  Average Apply Rate: 863.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    DGDB

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold		# APPLY 지연 설정을 느리게 하면 경고뜸
    ORA-16855: transport lag has exceeded specified threshold	# 지연이 발생하다보니 전송지연인지 설정한 적용지연인지 
    ORA-16826: apply service state is inconsistent with the DelayMins property	# DelayMins 속성
    ORA-16789: standby redo logs configured incorrectly	#SRL 구성이 완벽하지 않아서 발생

Database Status:
WARNING

---

Standby 재구성을 위해 DUPLICATE를 재실행

 

✅  APPLY 및 Standby DB 종료

sqlplus / as sysdba
alter database recover managed standby database cancel;
shutdown immediate;

 

✅ Standby 데이터 디렉터리 정리

rm -rf /u01/app/oracle/oradata/DGPRI
mkdir -p /u01/app/oracle/oradata/DGSTBY
chown oracle:oinstall /u01/app/oracle/oradata/DGSTBY
chmod 775 /u01/app/oracle/oradata/DGSTBY

 

✅Primary에서 RMAN DUPLICATE 실행

rman target sys/[패스워드]@dgdb_pri auxiliary sys/[패스워드]@dgdb_stby

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DGSTBY'
SET audit_file_dest='/u01/app/oracle/admin/DGSTBY/adump'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DGPRI,DGSTBY)'
SET FAL_CLIENT='DGDB_STBY'
SET db_file_name_convert=
'/u01/app/oracle/oradata/DGPRI','/u01/app/oracle/oradata/DGSTBY'
SET log_file_name_convert=
'/u01/app/oracle/oradata/DGPRI','/u01/app/oracle/oradata/DGSTBY'
SET control_files='/u01/app/oracle/oradata/DGSTBY/control01.ctl'
NOFILENAMECHECK;

 기존 duplicate 명령에서 경로지정을 더 해줌

 

✅Standby 상태 확인 

select name, open_mode, database_role from v$database;

 

정상 상태 예시:

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DGDB      MOUNTED              PHYSICAL STANDBY

 

✅Broker 및 Apply 시작

Broker 설정 (양쪽 DB에서 실행):

ALTER SYSTEM SET dg_broker_start = TRUE SCOPE=BOTH;

 

Redo Apply 시작(Standby):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

✅Standby Redo Log (SRL) 확인

📍 Standby

SELECT group#, thread#, status FROM v$standby_log;

 

📍 Primary에도 SRL 필요

SELECT group#, thread#, status FROM v$standby_log;

👉 없으면 Primary에도 SRL 추가 (Switchover 대비)

 

✅Data Guard Broker 재등록

📍 Primary 서버

dgmgrl /
CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS dgpri CONNECT IDENTIFIER IS dgpri;
ADD DATABASE dgstby AS CONNECT IDENTIFIER IS dgstby MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;

 

 

✅ 최종 검증

dgmgrl /
SHOW CONFIGURATION;
SHOW DATABASE dgpri;
SHOW DATABASE dgstby;

정상 기준:

• Configuration Status: SUCCESS
• Apply Lag / Transport Lag 정상
• Error 없음