Data Guard Broker 활성화

db_broker_start 파라미터 설정

ALTER SYSTEM SET dg_broker_start=true;

Primary

SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
dg_broker_start                      boolean                           TRUE

Standby

SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
dg_broker_start                      boolean                           TRUE



Data Guard Broker 환경 구성

Oracle Data Guard command-line interface(이하 DGMGRL) 접속

dgmgrl sys/<sys 계정 암호>@[primary 네트워크 서비스명]
[oracle@orcl ~]$ dgmgrl sys/oracle@orcl_primary
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 13 14:27:06 2024
Version 19.11.0.0.0

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

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



데이터베이스 등록

CREATE CONFIGURATION [Data Guard 구성 이름] AS PRIMARY DATABASE IS [primary 글로벌 DB명] CONNECT IDENTIFIER IS [primary 네트워크 서비스명];
ADD DATABASE [standby 글로벌 DB명] AS CONNECT IDENTIFIER IS [standby 네트워크 서비스명] MAINTAINED AS PHYSICAL;
DGMGRL> CREATE CONFIGURATION dg_orcl AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl_primary;
Configuration "dg_orcl" created with primary database "orcl"

DGMGRL> ADD DATABASE orcl_stby AS CONNECT IDENTIFIER IS orcl_standby MAINTAINED AS PHYSICAL;
Database "orcl_stby" added

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

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

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED



Data Guard Broker 설정 활성화

ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
Enabled.



Data Guard Broker 등록 정보 조회

SHOW CONFIGURATION;
SHOW DATABASE [primary 글로벌 DB명];
SHOW DATABASE [standby 글로벌 DB명];
DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Members:
  orcl      - Primary database
    orcl_stby - Physical standby database
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 2 seconds ago)

... << 로그 적용을 위해 잠시 대기 >> ...

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_orcl

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 12 seconds ago)

DGMGRL> SHOW DATABASE orcl;

Database - orcl

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

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE orcl_stby;

Database - orcl_stby

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

Database Status:
SUCCESS



정적 연결 식별자 설정

SHOW DATABASE [primary 글로벌 DB명] StaticConnectIdentifier;
EDIT DATABASE [primary 글로벌 DB명] SET PROPERTY StaticConnectIdentifier = '[primary 접속 정보]';
SHOW DATABASE [standby 글로벌 DB명] StaticConnectIdentifier;
EDIT DATABASE [standby 글로벌 DB명] SET PROPERTY StaticConnectIdentifier = '[standby 접속 정보]';
DGMGRL> show database orcl StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

DGMGRL> edit database orcl set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

DGMGRL> show database orcl StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seoul)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
DGMGRL> show database orcl_stby StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

DGMGRL> edit database orcl_stby set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
Property "staticconnectidentifier" updated

DGMGRL> show database orcl_stby StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=busan_dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_stby_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

기본 포트(1521)이 아닌 포트 번호를 사용하거나, 서비스에 사용되는 네트워크와 다른 설정을 사용할 경우에 Data Guard Broker 내의 접속 설정을 변경해야 할 수 있습니다.

Data Guard Broker 설정 백업

EXPORT CONFIGURATION TO dg_config.xml
DGMGRL> EXPORT CONFIGURATION TO dg_config.xml
Succeeded.



데이터베이스 상태 확인

SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;

Primary

SQL> set lines 200
SQL> col db_unique_name form a20
SQL> col open_mode form a20
SQL> col database_role form a20
SQL> col switchover_status form a20
SQL> col dataguard_broker form a20
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;

DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS    DATAGUARD_BROKER
-------------------- -------------------- -------------------- -------------------- --------------------
orcl                 READ WRITE           PRIMARY              TO STANDBY           ENABLED

Standby

SQL> set lines 200
SQL> col db_unique_name form a20
SQL> col open_mode form a20
SQL> col database_role form a20
SQL> col switchover_status form a20
SQL> col dataguard_broker form a20
SQL> SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;

DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE        SWITCHOVER_STATUS    DATAGUARD_BROKER
-------------------- -------------------- -------------------- -------------------- --------------------
orcl_stby            MOUNTED              PHYSICAL STANDBY     NOT ALLOWED          ENABLED



Local listener 설정

alter system set local_listener='<서비스 tns명>,<DG 전용 tns명>' scope=both;

Primary 설정

SQL> alter system set local_listener='ORCL,ORCL_PRIMARY' scope=both;

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.

SQL> show parameter local_listener

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener                       string                            ORCL,ORCL_PRIMARY

Standby 설정

SQL> alter system set local_listener='ORCL_STBY,ORCL_STANDBY' scope=both;

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.

SQL> show parameter local_listener

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener                       string                            ORCL_STBY,ORCL_STANDBY


기존 설정된 local listener 파라미터에 DG 전용 리스너를 추가합니다.