I. Data Guard Broker
1. 네트워크 설정
A) Primary의 listener.ora 파일 수정
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_LIST_LISTENER의 SID_LIST에 추가
(SID_DESC =
(GLOBAL_DBNAME = ORCL_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
B) Standby의 listener.ora 파일 수정
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_LIST_LISTENER의 SID_LIST에 추가
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
2. Data Guard Broker 환경 구성
A) Data Guard Broker 활성화
sys 유저로 primary와 standby에서 모두 수행
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를 기동시킵니다.
B) DGMGRL 접속
sys 유저로 접속
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 파일을 사용할 수 있도록 암호를 입력하는 방식으로 접속합니다.
C) 데이터베이스 등록
Data Guard 대상 데이터베이스 등록
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에 등록합니다.
D) 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를 활성화합니다.
E) Data Guard Broker 등록 정보 조회
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 명령어로 조회할 수 있습니다.
3. Switchover
A) Standby로 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;
4. Failover
A) Standby로 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로 접속하여 명령을 수행합니다.
Failover 후 상태 확인
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로 복구가 필요한 상태입니다.
B) Disable된 primary를 reinstate하기
Primary DB의 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 명령어로 간단하게 복구할 수 있습니다.
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를 수행하면 됩니다.
