Failover 수행

DGMGRL로 Standby DB 접속

dgmgrl sys/oracle@orcl_stb
[oracle@orcl ~]$ dgmgrl sys/oracle@orcl_stb
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Sep 10 15:58:34 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ORCL_STB"
Connected as SYSDBA.

Primary에서 standby로 switchover하기 위해 DGMGRL로 standby에 접속합니다.

Standby로 failover 수행

FAILOVER TO orcl_stb;
DGMGRL> FAILOVER TO orcl_stb;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_stb"

Failover 명령으로 작업을 수행합니다.

Failover 결과 확인

SHOW CONFIGURATION;
SHOW DATABASE orcl_stb;
SHOW DATABASE orcl;
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl_stb - Primary database
    orcl     - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 102 seconds ago)

DGMGRL> SHOW DATABASE orcl_stb;

Database - orcl_stb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE orcl;

Database - orcl

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    orcl

Database Status:
ORA-16661: the standby database needs to be reinstated

Failover가 정상적으로 완료되었는지 확인합니다.

기존의 Primary는 disabled로 복구가 필요한 상태입니다.

Failover 직후 데이터베이스 상태 확인

SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL                           READ WRITE           PRIMARY          NOT ALLOWED
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL_STB                       READ WRITE           PRIMARY          NOT ALLOWED

각 데이터베이스의 상태를 살펴보면 모두 primary로 되어 있는 것을 확인할 수 있습니다.

Primary는 현재 비정상적이므로 standby에서의 switchover는 now allowed 상태로 되어 있습니다.

Primary 데이터베이스 reinstate하기

Reinstate 수행

REINSTATE DATABASE orcl;
DGMGRL> REINSTATE DATABASE orcl;
Reinstating database "orcl", please wait...
Operation requires shut down of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Connected to "ORCL"
ORACLE instance shut down.
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Database mounted.
Connected to "ORCL"
Continuing to reinstate database "orcl" ...
Reinstatement of database "orcl" succeeded

Flashback Database가 활성화 되어 있었다면, reinstate 명령어로 간단하게 복구할 수 있습니다.

Reinstate 결과 확인

SHOW CONFIGURATION;
SHOW DATABASE orcl;
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl_stb - Primary database
    orcl     - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 17 seconds ago)

DGMGRL> SHOW DATABASE orcl;

Database - orcl

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 24 seconds ago)
  Apply Lag:          0 seconds (computed 24 seconds ago)
  Average Apply Rate: 482.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    orcl

Database Status:
SUCCESS

정상적으로 reinstate가 되었는지 확인합니다.

Reinstate 후 데이터베이스 상태 확인

SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL                           MOUNTED              PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL_STB                       READ WRITE           PRIMARY          TO STANDBY

데이터베이스의 상태를 살펴보면 primary와 standby의 역할이 정상으로 되어 있는 것을 확인할 수 있습니다.

Primary로 복원

Primary로 switchover

SWITCHOVER TO orcl;
DGMGRL> SWITCHOVER TO orcl;
Performing switchover NOW, please wait...
Operation requires a connection to database "orcl"
Connecting ...
Connected to "ORCL"
Connected as SYSDBA.
New primary database "orcl" is opening...
Operation requires start up of instance "orcl" on database "orcl_stb"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Database mounted.
Connected to "ORCL_STB"
Switchover succeeded, new primary is "orcl"

복구가 된 primary로 switchover를 수행할 수 있습니다.

Switchover 결과 확인

SHOW CONFIGURATION;
SHOW DATABASE orcl;
SHOW DATABASE orcl_stb;
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl     - Primary database
    orcl_stb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 71 seconds ago)

DGMGRL> SHOW DATABASE orcl;

Database - orcl

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE orcl_stb;

Database - orcl_stb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 561.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    orcl

Database Status:
SUCCESS

정상적으로 switchover 되었는지 확인할 수 있습니다.

복원 후 데이터베이스 상태 확인

SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL                           READ WRITE           PRIMARY          TO STANDBY
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status FROM v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORCL_STB                       MOUNTED              PHYSICAL STANDBY NOT ALLOWED

데이터베이스의 상태가 모두 정상화 되었습니다.