1. Data Guard Broker 활성화
A) db_broker_start 파라미터 설정
sys 유저로 primary와 standby에서 모두 수행
ALTER SYSTEM SET dg_broker_start=true;
SQL> ALTER SYSTEM SET dg_broker_start=true; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ dg_broker_start boolean TRUE
SQL> ALTER SYSTEM SET dg_broker_start=true; System altered. SQL> show parameter dg_broker_start NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ dg_broker_start boolean TRUE
{}
2. Data Guard Broker 환경 구성
A) Oracle Data Guard command-line interface(이하 DGMGRL) 접속
sysdba 권한으로 접속
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.
B) 데이터베이스 등록
Data Guard 대상 데이터베이스 등록
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
C) Data Guard Broker 설정 활성화
Data Guard Broker 활성화
ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION; Enabled.
D) Data Guard Broker 등록 정보 조회
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
E) Standby 데이터베이스 재기동 (옵션)
sys 유저로 standby에서 수행
SHUTDOWN IMMEDIATE; STARTUP MOUNT;
sqlplus / as sysdba SHUTDOWN IMMEDIATE; STARTUP MOUNT;
F) 데이터베이스 상태 확인
Primary DB와 Standby DB에서 수행
SELECT db_unique_name, open_mode, database_role, switchover_status, dataguard_broker FROM v$database;
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
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
G) Local listener 설정
서버별 TNS 설정에 맞게 적용
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
