Data Guard Broker

네트워크 설정

Primary의 listener.ora 파일 수정

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_act)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Data Guard Broker를 사용한다면 static listener인 DGMGRL을 별도로 등록해줍니다.

서비스를 등록하지 않을 경우, DGMGRL에서 데이터베이스를 재기동하거나 switchover하는 과정에 서비스를 찾지 못해 실패하게 됩니다.

    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )

Standby의 listener.ora 파일 수정

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_stb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_STB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_STB_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Standby 리스너도 마찬가지로 DGMGRL 를 추가합니다.

    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_STB_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )

Data Guard Broker 환경 구성

Data Guard Broker 활성화

ALTER SYSTEM SET dg_broker_start=true;
SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.

Primary와 Standby 양쪽에 dg_broker_start를 true로 변경하여, Data Guard Broker를 기동시킵니다.

DGMGRL 접속

dgmgrl sys/oracle@orcl
[oracle@orcl ~]$ dgmgrl sys/oracle@orcl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

원격 접속이 구성된 환경에서 오라클 클라이언트를 통해 DGMGRL에 접속할 수 있습니다.

원격 접속에서 password file 파일을 사용할 수 있도록 암호를 입력하는 방식으로 접속합니다.

데이터베이스 등록

CREATE CONFIGURATION orcl_dg AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl;
ADD DATABASE orcl_stb AS CONNECT IDENTIFIER IS orcl_stb MAINTAINED AS PHYSICAL;
DGMGRL> CREATE CONFIGURATION orcl_dg AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl;
Configuration "orcl_dg" created with primary database "orcl"
DGMGRL> ADD DATABASE orcl_stb AS CONNECT IDENTIFIER IS orcl_stb MAINTAINED AS PHYSICAL;
Database "orcl_stb" added

Primary 데이터베이스와 standby 데이터베이스를 Broker에 등록합니다.

Data Guard Broker 설정 활성화

ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;

Configuration - orcl_dg

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

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


DGMGRL> ENABLE CONFIGURATION;
Enabled.

등록된 설정값을 갖고 Data Guard Broker를 활성화합니다.

Data Guard Broker 등록 정보 조회

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

Configuration - orcl_dg

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
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)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    orcl

Database Status:
SUCCESS

Data Guard Broker에 등록된 내용을 SHOW 명령어로 조회할 수 있습니다.

Switchover

Standby로 switchover하기

SWITCHOVER TO orcl_stb;
DGMGRL> SWITCHOVER TO orcl_stb;
Performing switchover NOW, please wait...
New primary database "orcl_stb" is opening...
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl_stb"

DGMGRL> SHOW CONFIGURATION;

Configuration - orcl_dg

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Switchover 명령어를 이용하면 standby로 서비스를 switch over할 수 있습니다.

Primary로 원복을 하려면 다음의 명령어를 입력하면 됩니다.

SWITCHOVER TO orcl;

Failover

Standby로 Failover하기

FAILOVER TO orcl_stb;
[oracle@orcl ~]$ dgmgrl sys/oracle@orcl_stb
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
 
DGMGRL> FAILOVER TO orcl_stb;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl_stb"

Standby로 failover를 수행합니다.

가급적 standby로 접속하여 명령을 수행합니다.

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

Database - orcl_stb

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

Database Status:
SUCCESS
 
DGMGRL> SHOW CONFIGURATION;

Configuration - orcl_dg

  Protection Mode: MaxPerformance
  Databases:
    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

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

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

Disable된 primary를 reinstate하기

REINSTATE DATABASE orcl;
DGMGRL> REINSTATE DATABASE orcl;
Reinstating database "orcl", please wait...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl" ...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl" ...
Reinstatement of database "orcl" succeeded

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

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

Configuration - orcl_dg

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> SHOW DATABASE orcl;

Database - orcl

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

Database Status:
SUCCESS

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

다시 primary로 전환하려면 switchover를 수행하면 됩니다.