ORACLE 11g R2 Data Guard Installation v1.0

ORACLE 11g R2 Data Guard Installation

 

Date	Ver	Etc.
11.05.22	1.0

 

 

1.    Data Guard

11g R2 의 Data Guard Active Standby Database 를 사용할 수 있다. 기존의 DataGuard 는 Database 를  Open 하게 되면 Redo Apply 가 중지되었다.

 

하지만 11g 는 Standby Database 를 OPEN 해도 Redo log applying 되며 이는 11g NF 이다.

 

2.    Configuration for Data Guard using RMAN

이 문서에서 이야기 할 내용은 RMAN 을 이용한 Data Guard 구성이다. 여기서 Primary 가 될 DB 는 이미 구성되어 있는 상태로 가정하고 이야기 하겠다.

 

이미 구성되어 있는 시스템, DB 의 정보는 다음과 같다.

 

시스템 정보

OS : Enterprise Linux Enterprise Linux Server release 5 (Carthage) RAM : 4GB

 

DB 정보

Version : 11.2.0.1.0   memory_target    804M db_name          orcl db_unique_name   orcl

 

Listener 정보

SID_LIST_ORCL =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = orcl)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = orcl)     )   )   ORCL =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.11)(PORT = 1521))   )   ADR_BASE_LISTENER = /u01/app/oracle

 

Tnsnames 정보

ORCL =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.11)(PORT = 1521))     )     (CONNECT_DATA =       (SID = orcl)       (SERVER = DEDICATED)     )   )

 

3.    SCENARIO

가드는 같은 서버에 구성한다.

DBNAME 은 둘다 ORCL 을 가져가되 DB_UNIQUE_NAME 은 Primay=ORCL STANDBY=STBY 로 설정한다.

MEMORY 관련된 값은 동일하게 가져간다.

LISTENER 는 1522 로 ‘STBY’ 로 별도로 가져간다.

DB Engine 은 기존의 것 (11.2.0.1.0) 을 그대로 사용한다.

 

설치 개괄은 다음과 같다.

A.     Listener 의 추가

B.     Tnsnames.ora 의 추가

C.     Directory 의 추가

D.     Standby Logfile 의 생성

E.     DB Parameter / Status 변경

F.     Archive log 모드 활성화

G.    Password File / Paramer File 의 복사

H.     Alias 의 추가

I.       stby Listener 시작

J.      stby Database nomount

K.     RMAN Script 제작

L.     RMAN Script 실행

 

4.    PRACTICE

A.     Listener 의 추가

STBY =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.11)(PORT = 1522))   )   ADR_BASE_STBY = /u01/app/oracle   SID_LIST_STBY =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = stby)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)       (SID_NAME = stby)     )   )

 

Standby Database 가 사용할 Listener 를 추가하였다. 같은 장비에 있기 때문에 IP 는 동일하나 Port 를 1521 이 아닌 1522 를 사용한다.

 

B.     Tnsnames.ora 의 추가

STBY =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.11)(PORT = 1522))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SID = stby)     )   )

 

tnsnames.ora 에 ‘stby’ alias 를 추가한다. 이 정보를 통해 standby database 에 대해 적용된log_archive_dest_n 이 사용하게 되며, 이상없이 구성되었다면 Archived Redo log 를 전송하게 된다.

 

C.     Directory 의 추가

mkdir -p /u01/app/oracle/oradata/orcl/arc mkdir -p /u01/app/oracle/oradata/stby/arc mkdir -p /u01/app/oracle/admin/adump mkdir -p /u01/app/oracle/admin/dpdump mkdir -p /u01/app/oracle/admin/pfile mkdir -p /u01/app/oracle/flash_recovery_area/stby mkdir -p /u01/app/oracle/diag/rdbms/stby/stby/trace mkdir -p /u01/app/oracle/admin/stby/adump

 

Standby Database 구성에 사용할 디렉토리를 사전 생성한다.

디렉토리가 없는 경우 RMAN 수행시 에러가 발생한다.

 

D.     Standby Logfile 의 생성

alter database add standby logfile '/u01/app/oracle/oradata/orcl/arc/stby01.log' size 200m; alter database add standby logfile '/u01/app/oracle/oradata/orcl/arc/stby02.log' size 200m; alter database add standby logfile '/u01/app/oracle/oradata/orcl/arc/stby03.log' size 200m; alter database add standby logfile '/u01/app/oracle/oradata/orcl/arc/stby04.log' size 200m;

 

Standby database logfile 을 생성한다. 이에 대한 자세한 설명은 ORACLE Data Guard Architecture (http://h391106.tistory.com/231) 를 참고한다.

 

E.     DB Parameter / Status 변경

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/arc mandatory' scope=both; alter system set fal_client=orcl scope=both; alter system set fal_server=stby scope=both; alter system set standby_file_management = auto scope=both; alter system set log_archive_config='dg_config=(orcl,stby)' scope=both; alter system set log_archive_dest_2='service=stby async valid_for=(online_logfile,primary_role) db_unique_name=orcl' scope=both;   grant sysdba to system;      ALTER DATABASE FORCE LOGGING;

 

DB Guard 를 설정함에 있어 가장 중요한 parameter 는 log_archive_dest_n 이다. 이 값의 설정을 통해 Redo Log 가 정상적으로 전달되냐가 판가름 나며 이것이 정상 동작하지 않는다는 것은 Primary 와 Standby 간에 데이터 싱크가 맞지 않다란 이야기다.

 

F.     Archive log 모드 활성화

shutdown immediate; alter database archivelog;

Primary 가 Archivelog 모드가 아니면 Data Guard 구성이 불가능하다.

 

G.    Password File / Parameter File 의 복사

cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl  /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstby     vi /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstby.ora   db_name=orcl db_unique_name=stby

 

/*+ Password file 을 복사하는 이유에 대해 ? */

 

H.     Alias 의 추가

vi .bash_profile   export PS1='$ORACLE_SID@`pwd`>'   vi .bashrc   alias orcl='export ORACLE_SID=orcl' alias stby='export ORACLE_SID=stby'

 

Alias 는 단순히 관리상 편의를 위해 추가했다.

 

I.       stby Listener 시작

stby@/home/oracle>lsnrctl start stby

 

리스터를 시작하지 않으면 추후 RMAN Script 를 사용할 때 Primary Database 의 복제될 위치 지정이 불가능하다.

 

J.      stby Database nomount

stby@/home/oracle>sqlplus "/as sysdba"   SQL*Plus: Release 11.2.0.1.0 Production on Sun May 22 15:56:51 2011   Copyright (c) 1982, 2009, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> startup nomount;        ORACLE instance started.   Total System Global Area  217157632 bytes Fixed Size                  2211928 bytes Variable Size             159387560 bytes Database Buffers           50331648 bytes Redo Buffers                5226496 bytes

 

Standby DB를 nomount 단계까지 올린다.

 

K.     RMAN Script 제작

connect auxiliary system/oracle@stby run {                                                                                                                                                                                                                                                                                                                                                                                                                                            duplicate target database for standby from active database                                                                                                                                                                                                                                                                                                                        dorecover                                                                                                                                                                                                                                                                                                                                                                         spfile                                                                                                                                                                                                                                                                                                                                                                            parameter_value_convert 'orcl','stby'                                                                                                                                                                                                                                                                                                                                  set db_unique_name='stby'                                                                                                                                                                                                                                                                                                                                                   set control_files='/u01/app/oracle/oradata/stby/control01.ctl'         set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'         set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'         set cluster_database='FALSE'                                                                                                                                                                                                                                                                                                                                                      set fal_client='stby'                                                                                                                                                                                                                                                                                                                                                       set fal_server='orcl'                                                                                                                                                                                                                                                                                                                                                        set log_archive_dest_1='location=/u01/app/oracle/oradata/stby/arc'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl'                                                                                                                                                                                                                                                           set log_archive_config='dg_config=(orcl,stby)'                                                                                                                                                                                                                                                                                                                         set service_names='stby';                                                                                                                                                                                                                                                                                                                                                   sql "alter system archive log current";                                                                                                                                                                                                                                                                                                                                   CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;                                                                                                                                                                                                                                                                                                                }

 

RMAN 에서 수행할 Script 를 사전에 생성한다. 기존에 수동으로 DataGuard 를 구성하는 경우 Parameter File 을 일일히 수정해야 하나 RMAN 의 경우 편리하게 작업할 수 있다. (parameter file 을 수정하는 경우도 *conver 를 사용이 가능하다.)

 

L.     RMAN Script 실행

다음의 스크립트를 ORACLE_SID 가 orcl 인 상태에서 수행한다.

rman target /   @rman_script

 

5.    Check Configuration

Primary 에서 log_archive_dest_n 에 대해 status 를 확인할 수 있다.

아래에서 standby database 에 Redo Log 전송을 설정한 LOG_ARCHIVE_DEST_2 의 상태가 ERROR 임을 알 수 있다.

 

이와 관련해 value 를 조회한 결과 타겟에 해당하는 db_unique_name 설정이 잘못 된 것을 확인하여 수정하였고 다시 조회를 했을 때 정상임을 확인했다.

 

SQL> select dest_name,status from v$archive_dest_status   2  where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2')   3  /   DEST_NAME                      STATUS ------------------------------ --------- LOG_ARCHIVE_DEST_1             VALID LOG_ARCHIVE_DEST_2             ERROR   SQL> show parameter log_archive_dest_2   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2                   string      service=stby async valid_for=(                                                  online_logfile,primary_role) d                                                  b_unique_name=orcl ~~(중략)~~   SQL> alter system set log_archive_dest_2='service=stby lgwr async valid_for=(online_logfile,primary_role) db_unique_name=stby' scope=both;   SQL> select dest_name,status from v$archive_dest_status where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2');   DEST_NAME                      STATUS ------------------------------ --------- LOG_ARCHIVE_DEST_1             VALID LOG_ARCHIVE_DEST_2             VALID

 

Log Archive Dest 가 정상인 것을 확인하였고 MRP 를 기동해보았다.

 

SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH      CONNECTED ARCH      CONNECTED ARCH      CONNECTED ARCH      CONNECTED RFS       IDLE RFS       IDLE MRP0      WAIT_FOR_LOG   7 rows selected.

 

이번에는 11g NF Active Standby Database 를 사용해보겠다. 먼저 MRP 를 STOP (REDO APPLY 를 CANCEL) 한 후 진행하였다.

 

SQL> alter database open read only;   Database altered.   SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> select database_role, open_mode from v$database;   DATABASE_ROLE    OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY   SQL> select status from v$instance;   STATUS ------------ OPEN   SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH      CONNECTED ARCH      CONNECTED ARCH      CONNECTED ARCH      CONNECTED MRP0      WAIT_FOR_LOG

 

위의 내용을 다시 이야기 하면 read only mode 로 open 하였고 MRP 를 기동했다는 것이 요지이다. MRP 프로세스는 Redo Log Applying 시 기동되는 Process 로 이것이 동작중이란 것은 Primary Database 와 동기화 되고 있다는 뜻이다.

 

6.    References