Data Guard Broker 활성화

Data Guard Broker 설정 위치 변경

ALTER SYSTEM SET dg_broker_config_file1='[설정 파일 저장 경로 및 파일명1].dat' SCOPE=both SID='*';
ALTER SYSTEM SET dg_broker_config_file2='[설정 파일 저장 경로 및 파일명2].dat' SCOPE=both SID='*';
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'dg_broker_config_file%';
SQL> ALTER SYSTEM SET dg_broker_config_file1='/oradata/dr1orcl.dat' SCOPE=both SID='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2='/oradata/dr2orcl.dat' SCOPE=both SID='*';

System altered.

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'dg_broker_config_file%';

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------
dg_broker_config_file1         /oradata/dr1orcl.dat
dg_broker_config_file2         /oradata/dr2orcl.dat


클러스터에 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


Primary와 Standby 데이터베이스의 Data Guard Broker 시작을 활성화합니다.

반드시 양쪽 모두에서 수행해야 합니다.

Data Guard Broker 환경 구성

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

dgmgrl sys/<sys 계정 암호>@[primary 네트워크 서비스명]

[oracle@orcl ~]$ dgmgrl sys/oracle@seoul_syn
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.


원격 접속이 구성된 환경에서 DGMGRL을 통해 데이터베이스에 접속할 수 있습니다.

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

데이터베이스 등록

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 seoul_syn;
Configuration "dg_orcl" created with primary database "orcl"

DGMGRL> ADD DATABASE orcl_stby AS CONNECT IDENTIFIER IS busan_syn 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


Primary 데이터베이스로 Data Guard Broker를 구성하고, standby 데이터베이스를 Data Guard Broker에 추가합니다.

Data Guard Broker 설정 활성화

ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
Enabled.


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

Data Guard Broker 등록 정보 조회

SHOW CONFIGURATION;
SHOW DATABASE [primary 글로벌 DB명];
SHOW DATABASE [standby 글로벌 DB명];
-- VALIDATE DATABASE VERBOSE [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


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

정적 연결 식별자 설정

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_dg)(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.


Data Guard Broker 구성을 백업 받습니다. 백업은 XML 파일로 DB trace 경로에 생성됩니다.

Data Guard 구성 결과 확인 및 추가 작업

데이터베이스 상태 확인

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


각 데이터베이스에서 Data Guard Broker에 등록된 정보를 확인할 수 있습니다.

Local listener 설정

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

Primary 설정

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

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.

SQL> show parameter local_listener

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

Standby 설정

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

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.

SQL> show parameter local_listener

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


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

아카이브 로그 삭제 방지 정책 추가

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/db_1/dbs/snapcf_orcl.f'; # default


Standby에 적용되지 않은 아카이브 로그가 삭제되어 데이터 손실이 발생하지 않도록 아카이브 삭제 정책을 수정합니다.

Primary와 Standby에서 각각 수행합니다.