[Oracle] Data Gurad Switchover

🔄 Switchover란?

• 장애 없이 Primary ↔ Standby
• 데이터 유실없이 역할을 서로 교체
• 서비스 중단 거의 없음

📌 전제 조건

• Primary, Standby 둘 다 정상
• Redo 전송 / Apply 정상
• Data Guard Broker 구성 완료 ✅

---

🧭 Switchover 전체 흐름 (한눈에)

1️⃣ 현재 상태 확인
2️⃣ Switchover 가능 여부 점검
3️⃣ Switchover 실행
4️⃣ 역할 변경 확인
5️⃣ 클라이언트 관점에서 검증

---

1️⃣ 현재 상태 확인 (반드시 먼저)

Primary에서:

dgmgrl sys/[패스워드]@DGDB_PRI

SHOW CONFIGURATION;

 

정상 예:

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  dgpri  - Primary database
    dgstby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

---

2️⃣ Switchover 가능 여부 점검

VALIDATE DATABASE DGPRI;

 

결과:

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    dgpri:  Off

  Managed by Clusterware:
    dgpri:  NO
    Validating static connect identifier for the primary database dgpri...
    The static connect identifier allows for a connection to database "dgpri".

 

❗중요 포인트:

• Ready for Switchover: Yes
  → 이 문구 무조건 있어야 함

Standby도 확인:

VALIDATE DATABASE DGSTBY;

 

결과:

  Database Role:     Physical standby database
  Primary Database:  dgpri

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    dgpri :  Off
    dgstby:  Off

  Managed by Clusterware:
    dgpri :  NO
    dgstby:  NO
    Validating static connect identifier for the primary database dgpri...
    The static connect identifier allows for a connection to database "dgpri".

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        18 minutes 52 seconds (computed 56 seconds ago)
    Apply Delay:      0 minutes

 

 

만약 아래와 같은 경고가 뜬다면 참고

DGMGRL> validate database dgstby

    .
    .
    .

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (dgstby)                (dgpri)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on dgpri

[Oracle] Data Guard Broker SRL 경고

 

---

3️⃣ Switchover 실행 (핵심)

📌 Broker가 다 알아서 처리함

• 세션 정리
• Redo Apply 동기화
• Role 전환

SWITCHOVER TO DGSTBY;

 

 

Performing switchover NOW, please wait...
Operation requires a connection to database "dgstby"
Connecting ...
Connected to "DGSTBY"
Connected as SYSDBA.
New primary database "dgstby" is opening...
Operation requires start up of instance "DGDB" on database "dgpri"
Starting instance "DGDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "DGPRI"
Database mounted.
Database opened.
Connected to "DGPRI"
Switchover succeeded, new primary is "dgstby"

👉 에러 없으면 성공

---

4️⃣ 역할 변경 확인

 

SHOW CONFIGURATION;

아래와 같이 바뀌어야 함:

DGSTBY - Primary database
DGPRI - Physical standby database

즉,

• 기존 Standby → Primary
• 기존 Primary → Standby

---

5️⃣ SQL로도 직접 확인 (양쪽 다)

현재 Primary가 된 서버에서

select name, database_role, open_mode from v$database;

 

결과:

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
DGDB      PRIMARY          READ WRITE

현재 Standby가 된 서버의 결과:

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
DGDB      PHYSICAL STANDBY READ ONLY WITH APPLY

---

6️⃣ Redo Apply 재확인 (중요)

새 Standby 서버에서:

SELECT process, status FROM v$managed_standby;

MRP0 APPLYING_LOG → 정상

---

🧪 실무 검증 테스트 (강추)

Switchover 전 생성한 테스트 테이블 → dg_test

CREATE TABLE sw_test (id NUMBER); INSERT INTO sw_test VALUES (1);
COMMIT;

insert into dg_test values(6, 'dgtest6', systimestamp);
commit;
alter system switch logfile;

 

Switchover 후 (새 Primary에서)

 

SELECT * FROM sw_test;

👉 데이터가 삽입 되어서 조회 되면 완벽 성공

 

만약 아래와 같은 증상이 나타나면 참고

DGMGRL> show database dgstby

Database - dgstby

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    DGDB
      Error: ORA-16736: unable to find the destination entry of member "dgpri" in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property RedoRoutes found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error

[ORACLE] ORA-16736: unable to find the destination entry of member "dgpri" in V$ARCHIVE_DEST

 

---

⚠️ 자주 나오는 실수

❌ “Active sessions exist”

→ 실제 서비스 환경이면 세션 종료 필요
→ 실습 환경이면 거의 안 뜸

❌ Apply Lag 발생

→ Standby Apply 상태 먼저 확인 후 재시도

---

🔜 다음 단계

👇

1️⃣ Failover 실습 (Primary 장애 상황 가정)
2️⃣ Failover 후 Reinstate (Standby 복구)