'active dataguard'에 해당되는 글 2건

Active DataGuard 11gR2 Configuration

1. IT Story/DB 2013. 4. 9. 15:28


1.ADG Configuration

1-1 Primary DB

Primary

   

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

  

   

1-2 Standby DB

Standby(mount)

   

SQL> select process,status from v$managed_standby;

   

PROCESS STATUS

--------- ------------

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

RFS IDLE

RFS IDLE

   

7 rows selected.

   

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 CLOSING

RFS IDLE

RFS IDLE

RFS IDLE

MRP0 APPLYING_LOG

   

8 rows selected.

  

   

1-3 Active Standby Database startup

Standby(mount)

   

SQL> alter database recover managed standby database cancel;

   

Database altered.

   

SQL> alter database open;

   

Database altered.

   

SQL> select process,status from v$managed_standby;

   

PROCESS STATUS

--------- ------------

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

RFS IDLE

RFS IDLE

   

7 rows selected.

   

SQL> alter database recover managed standby database using current logfile disconnect ;

   

Database altered.

   

SQL> select process,status from v$managed_standby;

   

PROCESS STATUS

--------- ------------

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

RFS IDLE

RFS IDLE

MRP0 APPLYING_LOG

   

  1. rows selected.

   

2.ADG 동작여부확인

Primary DB

SQL> select database_role, open_mode from v$database;

   

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PRIMARY READ WRITE

   

SQL> select i.instance_name, i.status instance_status, d.name dbname, d.database_role db_role,

2 d.switchover_status switchover_status , d.protection_mode

3 from v$database d, v$instance i;

   

INSTANCE_NAME INSTANCE_STA DBNAME DB_ROLE SWITCHOVER_STATUS

---------------- ------------ --------- ---------------- --------------------

PROTECTION_MODE

--------------------

orcl OPEN ORCL PRIMARY TO STANDBY

MAXIMUM PERFORMANCE

  

Standby DB

   

SQL> select database_role, open_mode from v$database;

   

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

   

SQL> select i.instance_name, i.status instance_status, d.name dbname, d.database_role db_role,

2 d.switchover_status switchover_status , d.protection_mode

3 from v$database d, v$instance i;

   

INSTANCE_NAME INSTANCE_STA DBNAME DB_ROLE SWITCHOVER_STATUS

---------------- ------------ --------- ---------------- --------------------

PROTECTION_MODE

--------------------

stby OPEN ORCL PHYSICAL STANDBY NOT ALLOWED

MAXIMUM PERFORMANCE

   

  • Primary alert Log file /Standby alert Log file에서 sequence number가 일치하는지 확인 한다.

       

3.ADG 동기화 확인

  • Primary 에서 유저 등록 및 테이블생성과 같은 DB작업을 할 경우, Standby에도 잘 반영이되는지 확인해야 합니다.

SQL > create user test identified by test

SQL > create table test (a char(10)

   

   

[참고문헌]

http://h391106.tistory.com/231

http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm

http://h391106.tistory.com/238

http://download.oracle.com/docs/cd/E11882_01/server.112/e17023/dgmgrl.htm

   

'1. IT Story > DB' 카테고리의 다른 글

Oracle Data Masking Constraints/Index TEST1  (0) 2013.04.30
Oracle SQL Performance Analyzer (EM만 사용)  (0) 2013.04.30
Oracle Datapump를 이용한 Expdp/Impdp백업!!  (0) 2013.04.15
Active DataGuard 11gR2 DGMGRL  (0) 2013.04.09
Active DataGuard 11gR2 개념 및 Installation  (0) 2013.04.09
Oracle Golden Gate Configuration(단방향 DDL,DML)  (0) 2013.04.08
Oracle Golden Gate 개념 및 Installaton  (2) 2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption)  (0) 2013.03.31
블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,

Active DataGuard 11gR2 개념 및 Installation

1. IT Story/DB 2013. 4. 9. 15:27

 

1.Oracle Active DataGuard

   

 

   

  • Primary Database 와 Standby Database 로 구성되며 하나의 Primary Database 에 최대 9개의 Standby Database 가 구성될 수 있다.

       

  • Primary 측의 변경사항은 Redo Logs 에 기록되는데 이는 백그라운드 프로세스 (LGWR 혹은 ARCn) 에 의해 Standby 측에 Oracle Net 을 통해 전파된다.

   

  • 전파된 변경 내역은 Standby Redo Log 에 쓰였다가 Database 에 적용된다.


[참고 사이트]   

http://h391106.tistory.com/231

   

2.ADG Installation

   

- Environment(vmware)

구분

Host IP

OS Version

Oracle SID

DB Version

Priamry

192.168.22.3

GNU / LINUX

orcl

Oracle 11.2.0.3

Standby

192.168.22.10

GNU / LINUX

Stby

Oracle 11.2.0.3

   

2-1 Listener / tns정보(Priamry/Standby)

Priamry

   

ORCL =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ocp.mycorpdomain.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

   

SID_LIST_ORCL =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

orcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.3)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

   

  

Standby

   

STBY =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

   

SID_LIST_STBY =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = stby)

)

)

   

   

stby =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.10)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stby)

)

)

  

   

2-2 ORCL / STBY 각 서버별 DB 설치 Directory 일치(Priamry/Standby)

  • Source DB(ORCL)의 데이터를 그대로 옮겨와야 되기 때문에 거의 동일하게 구성하되 Oracle SID만 변경하여 생성할 것.
  • Vmware이기 때문에 ORCL 서버를 복사하여 DBCA로 DB만 제거한 뒤 해당 디렉토리의 SID 명만 변경하여 세팅해도 됨.

[Oracle 유저]

$Mkdir -p /home/oracle/arch

$Mkdir -p u01/app/oracle/admin/stby/adump

$Mkdir -p u01/app/oracle/admin/stby/dpdump

$Mkdir -p u01/app/oracle/admin/stby/pfile

$mkdir -p /u01/app/oracle/diag/rdbms/stby/stby/Trace

$Mkdir -p /u01/app/oracle/oradata/stby

   

   

2-3 Standby logfile 생성

  • 기존에 가지고있는 Redolog file의 크기와 같아야 되고 개수가 하나 더 많이 생성해 해야 된다.(Redolog file =3 /Standby logfile = 4)

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby01.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby02.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby03.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby04.log' size 50m;

   

2-4 DB Parameter / status 변경

   

SQL> alter system set log_archive_dest_1='location= /home/oracle/arch mandatory' scope=both;

   

SQL> alter system set fal_client=orcl scope=both;

   

SQL> alter system set fal_server=stby scope=both;

   

SQL> alter system set standby_file_management = auto scope=both;

   

SQL> alter system set log_archive_config='dg_config=(orcl,stby)' scope=both;

   

SQL> alter system set log_archive_dest_2='service=stby async valid_for=(online_logfile,primary_role) db_unique_name=stby' scope=both;

   

SQL> ALTER DATABASE FORCE LOGGING;

   

2-5 Archive mode 설정

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

   

2-6 Password file / Parameter file 복사(ORCL->STBY)

ORCL-> STBY

$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl

   

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwstby

$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/initstby.ora

db_name=orcl

db_unique_name=stby

   

2-7 Data 이관 및 DB startup(ORCL->STBY)

  • Orcl의 정보를 바탕으로 Oracle SID를 STBY로 변경하여 DB를 Startup 시켜준다.
  • 해당 방안에는 HOT Backup을 이용한 방법, Rman을 이용한 방법 외 다양한 방법으로 ORCL과 동일하게 DB를 구성하여 올려준다.(db_unique_name 만 STBY로 변경)

   

[pfile 참조]

localhost@stby : /u01/app/oracle/product/11.2.0/db_1/dbs> vi initstby.ora

   

orcl.__db_cache_size=121634816

stby.__db_cache_size=125829120

orcl.__java_pool_size=4194304

stby.__java_pool_size=4194304

orcl.__large_pool_size=4194304

stby.__large_pool_size=4194304

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

stby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=79691776

stby.__pga_aggregate_target=79691776

orcl.__sga_target=234881024

stby.__sga_target=234881024

orcl.__shared_io_pool_size=0

stby.__shared_io_pool_size=0

orcl.__shared_pool_size=100663296

stby.__shared_pool_size=96468992

orcl.__streams_pool_size=0

stby.__streams_pool_size=0

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/stby/adump'

*.audit_trail='db'

*.cluster_database=FALSE

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/stby/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'

*.db_name='orcl'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/home/oracle/arch'

*.db_unique_name='stby'

*.dg_broker_start=TRUE

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'

*.fal_client='stby'

*.fal_server='orcl'

*.log_archive_config='dg_config=(orcl,stby)'

*.log_archive_dest_1='location=/home/oracle/arch'

*.log_archive_dest_2='service=stby lgwr async valid_for=(online_logfile,primary_role) db_unique_name=stby'

*.log_archive_dest_state_2='ENABLE'

   

[RMAN 스크립트 참조]

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=/home/oracle/arch'

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;

}

   

[참고문헌]

http://h391106.tistory.com/231

http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm

http://h391106.tistory.com/238

http://download.oracle.com/docs/cd/E11882_01/server.112/e17023/dgmgrl.htm

'1. IT Story > DB' 카테고리의 다른 글

Oracle SQL Performance Analyzer (EM만 사용)  (0) 2013.04.30
Oracle Datapump를 이용한 Expdp/Impdp백업!!  (0) 2013.04.15
Active DataGuard 11gR2 DGMGRL  (0) 2013.04.09
Active DataGuard 11gR2 Configuration  (0) 2013.04.09
Oracle Golden Gate Configuration(단방향 DDL,DML)  (0) 2013.04.08
Oracle Golden Gate 개념 및 Installaton  (2) 2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption)  (0) 2013.03.31
Oracle 10g CRS 제거  (0) 2013.03.31
블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,

공지사항

카테고리

IT & 인생 (473)
1. IT Story (397)
2. Knowledge (37)
3. Cultural life (24)
4. IT Product Reviews (0)
99.Event (12)

글 보관함

달력

«   2024/12   »
일 월 화 수 목 금 토
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

티스토리툴바