data guard
1.Maximum Protection 모드
Primary DB와 Standby DB의 redo log를 동기화 시킨다.
Standby DB가 네트워크 이상 등의 이유로 standby로의 전송이 안될 경우 primary를 halt 시킨다.
Data가 서로 동기화되어 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지
primary DB에서 commit 완료를 하지 않는다.
성능에는 문제를 줄 소지가 있으나 failover 상황이 오더라도 데이터 손실이 없다.
Physical standby database에서만 가능하다.
2.Maximum Availability 모드
Maximum Protection과 마찬가지로 primary DB와 standby DB를 동기화시킨다.
단, standby DB가 네트워크 문제 등의 이유로 전송이 안될지라도 halt되지는 않는다.
Data는 maximum protection과 마찬가지로 primary DB에서 commit을 하게 되면 standby DB에서
commit이 완료될 때까지 primary DB에서 commit 완료를 하지 않는다.
만약 standby DB가 unavailable상태일 경우 임시로 불일치 시킨다.
또 다시 standby DB가 available하면 자동으로 동기화 시킨다.
성능에는 문제를 줄 소지가 있으나 failover상황이 오더라도 data손실은 거의 없다.
3.Maximum Performance
Default protection mode이다. Primary data에 대한 protection이 가장 낮다.
Primary database에 transaction이 수행되면 이것을 asynchronous하게 standby DB에 적용한다.
즉 maximum protection, maximum availability의 경우에는 standby DB에 적용(commit)될 때까지
primary DB의 transaction이 적용(commit) 되지 않았으나, 기다리지 않는다.
즉 standby 유의 문제로 인해서 primary DB에 성능영향이 가지 않는다.
단, failover시에는 약간의 데이터 손실을 가져올 수 있다.
<Data Guard 구축>
1.init.ora 설정
-Primary
#******************
# Primary Role
#******************
log_archive_start=true
log_archive_format=m11mes_%t_%s_%r.arc
log_archive_dest_1="location=/oracle/arch"
log_archive_dest_2='SERVICE=SGD9A MANDATORY LGWR SYNC AFFIRM'
log_archive_dest_state_1 = ENABLE
log_archive_dest_state_2 = ENABLE
remote_archive_enable=TRUE
log_archive_config='DG_CONFIG=(M11MES,M11MES_STB)'
log_archive_dest_2='SERVICE=M11MES_STB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=M11MES_STB LGWR ASYNC NOAFFIRM DELAY=
60 REOPEN=10'
#*******************
# Standby Role
#*******************
log_archive_start=true
LOG_ARCHIVE_CONFIG='DG_CONFIG=(M11MES,M11MES_STB)'
## if modify, note that TINA has a Length checking problem on LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_1='LOCATION=/messarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=M11MES_STB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
log_archive_format=m11mes_%t_%s_%r.arc
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/oracle/arch'
FAL_SERVER=M11MES1
FAL_CLIENT=M11MES_PHYSTB
DB_FILE_NAME_CONVERT=('/dev/vgpmes11','/dev/vgsmes11',
'/dev/vgpmes12','/dev/vgsmes12',
'/dev/vgpmes13','/dev/vgsmes13',
'/dev/vgpmes14','/dev/vgsmes14')
LOG_FILE_NAME_CONVERT=('/dev/vgpmes11','/dev/vgsmes11',
'/dev/vgpmes12','/dev/vgsmes12',
'/dev/vgpmes13','/dev/vgsmes13',
'/dev/vgpmes14','/dev/vgsmes14')
##Maximum Performance##
##LOG_ARCHIVE_DEST_2='SERVICE=M11MES_STB
## VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
## DB_UNIQUE_NAME=M11MES_STB
## LGWR ASYNC DELAY=60 REOPEN=10'
##Maximum Availability##
##LOG_ARCHIVE_DEST_2='SERVICE=M11MES_STB
## VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
## DB_UNIQUE_NAME=M11MES_STB
## LGWR SYNC AFFIRM DELAY=60 REOPEN=10 NET_TIMEOUT=10'
2.리스너 설정
-Primary
STANDBY_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST = sf-a)(PORT = 1523))
)
)
)
SID_LIST_STANDBY_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /ora9/product/920)
(SID_NAME = GD9A)
)
)
-Standby
STANDBY_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST = sf-b)(PORT = 1523))
)
)
)
SID_LIST_STANDBY_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /ora9/product/920)
(SID_NAME = GD9A)
)
)
3.TNSNAMES.ora 설정(Standby)
GD9A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=sf-a)(PORT=1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = GD9A)
)
)
SGD9A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=sf-b)(PORT= 1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = GD9A)
)
)
3.Standby controlfile 생성 (Primary)
- alter database create standby controlfile as '/ora9/oradata/GD9A_back/standby.ctl';
- shutdown immediate
4.standby DB copy
datafile, controlfile, redo file,standby controlfile copy(primary .. standby)
v$log, dba_data_files, v$controlfile 등을 통해 datafile, controlfile, redo file 위치 파악
[sf-a:ora9:/ora9/oradata]$ rcp -r /ora9/oradata/GD9A sf-b:/ora9/oradata/.
SQL> startup (Primary)
5.standby DB mount
SQL> startup nomount
SQL> alter database mount standby database;
6.Standby redo log 생성
SQL> alter database add standby logfile '/ora9/oradata/GD9A/standbylog01.log' size 10m;
SQL> alter database add standby logfile '/ora9/oradata/GD9A/standbylog02.log' size 10m;
SQL> alter database add standby logfile '/ora9/oradata/GD9A/standbylog03.log' size 10m;
7.logfile 확인
SQL> select group#,status,type from v$logfile;
SQL> select group#,thread#,archived,status from v$log;
SQL> select group#, thread#, archived, status from v$standby_log;
8.standby database를 managed recovery mode로 설정
SQL> recover managed standby database disconnect;
9.Primary DB, Standby DB 확인
(Primary DB)
SQL> select database_role, protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
-------------------------------- -------------------------------------
PRIMARY MAXIMUM PERFORMANCE
(Standby DB)
SQL> select database_role, protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
-------------------------------- ------------------------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE
10.Archive 전송 TEST
(Primary DB)
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select dest_id,status,error from v$archive_dest where dest_id <=2;
DEST_ID STATUS ERROR
---------- ------------------ --------------------
1 VALID
2 VALID
11.Archive 전송확인 alert.log
(Primary DB)
Thread 1 advanced to log sequence 32
Current log# 2 seq# 32 mem# 0: /ora9/oradata/GD9A/redo02.log
Tue Mar 27 14:28:45 2007
ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed
ARC0: Beginning to archive log 1 thread 1 sequence 31
Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/GD9A/archive/1_31.dbf'
ARC0: Completed archiving log 1 thread 1 sequence 31
(Standby DB)
ARC1: Evaluating archive log 5 thread 1 sequence 29
ARC1: Beginning to archive log 5 thread 1 sequence 29
Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/GD9A/archive/1_29.dbf'
Tue Mar 27 14:28:29 2007
RFS: Successfully opened standby logfile 4: '/ora9/oradata/GD9A/standbylog01.log'
12.Data 전송 TEST
(Primary DB)
SQL> create table twins (a number);
SQL> insert into twins values(407);
SQL> commit;
SQL> alter system switch logfile;
(Standby DB)
SQL>recover managed standby database cancel;
ORA-03114: not connected to ORACLE
SQL> alter database open read only; (Standby DB)
13. Standby Database 상태변경
1. shut down => managed recovery mode
SQL> startup nomount
SQL> alter database mount standby database;
SQL> recover managed standby database disconnect(from session);
2. shutdown => read only Standby
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database open read only;;
3. managed recovery mode => read only Standby
SQL> recover managed standby database cancel;
SQL>alter database open read only;
4. read only Standby => managed recovery mode(모든 session 종료)
SQL> recover managed standby database disconnect(from session);