<Insert Picture Here> Enterprise Manager 를활용한 Active Data Guard 강송희 TSC본부 DB 기술팀한국오라클
Agenda Active Data Guard 도입배경 기존 DR 솔루션대비 Active Data Guard 의특장점 Active Data Guard 의구성및동작원리 EM 을이용한설치 EM 을이용한모니터링및관리 요약
<Insert Picture Here> Active Data Guard 도입배경 두마리토끼 ( 재난대비 + 업무활용 )
재난대비솔루션의투자가치? 투자한만큼활용가능한가? DR only Solution (Remote Mirroring) 활용도 0 불확실 현재 Remote Mirroring 솔루션의한계 DR 은 Standby 로 inactive 로대기 재난발생될때정상동작이될지의심스러움 테스트가힘들다 Oracle Data Guard 활용도 0 No! DR 서버를 Active 하게업무에활용가능 불확실 No! Switchover, Failover 로정상동작확인가능 테스트가쉽다. DR Systems Gather Dust
<Insert Picture Here> 기존 DR 솔루션대비 Active Data Guard 의특장점 -Remote Mirroring -Data Guard 특징 -Data Guard Advantages -Active Data Guard Benefits
Remote Mirroring Most of the storage vendors (Veritas, EMC, Network Appliance, Hitachi, Legato, etc) updates Updates Production DBMS Control Files fil Online Logs Archive Logs Flashback Logs Data Files SYSTEM USER TEMP UNDO Network I/O InActive 장애발생시매뉴얼하게기동 Standby DBMS Control Files fil Online Logs Archive Logs Flashback Logs Data Files SYSTEM USER TEMP UNDO
Data Guard vs Remote Mirror Redo Transport,Apply updates Updates Production DBMS Control Files fil Online Logs Archive Logs Flashback Logs Network I/O 7X less data volume* 27X fewer network I/Os* Control Files fil Standby Redo Logs Archive Logs Flashback Logs Standby DBMS Apply data validation Data Files SYSTEM USER TEMP UNDO Data Files SYSTEM USER TEMP UNDO *www.oracle.com/technology/deploy/availability/htdocs/dataguardremotemirroring.html
Data Guard vs Remote Mirror Zero Data Loss Over Long Distance & Delayed apply 기능 국지적인재난도피할수있을만큼의충분한거리를두고구성가능 Zero data loss mode 로도구현도가능 100 miles 200 miles 300+ miles Data Guard: Synchronous Redo Shipping Synchronous Disk Mirroring Delayed Apply 기능지원 (optional): Redo data 를일정시간이지난다음반영하도록설정가능 - Operational Error 를감지후 standby site 를이용해복구가능 : flash back 기능으로대체가능
Data Protection Protection from data corruptions 어떤시스템이라도장애가발생할가능성이있고이로인한 data corruptions 이발생할수있다 : NIC, Memory, Volume Manager, File System, OS, HBA, SAN Switch, Disk Controller, RAID, Storage Array 등등 DR solution 의이중화된데이터에는이러한 data corruptions 이반영되어서는안된다. Data Guard: database 간의의존성이약한 Architecture 로 disk block 을직접 copy 하는것이아니라전달된 Redo 를검증하면서 DR site 에반영하므로 data corruptions 이전달되지않도록보호할수있다. Remote Mirroring & Stretch Clusters : storage stack / corrupted bits 에서발생한에러가 DR 로전달되어영향을미친다
Data Guard Advantages 1. Network Efficiency Data Guard:Redo data 만전송 Remote mirroring solutions: datafiles, archivelog files, redolog file 들모두복제 3. Better Data Protection logical and physical corruption 들로부터보호가능 4. Functionality 7x 27x DBA 가이해하기쉬운 DR solution :backup/recovery 와유사 Redo Apply/SQL Apply Network Bandwidth Network I/Os Data Guard 2. Better suited for WANs Standard TCP/IP 이용 - no protocol converters needed Remote Mirroring 5. Flexibility storage vendor 종속적이지않음 6. Maximum ROI DR 투자효과증대 reporting/query 용으로활용
Active Data Guard Benefits Primary DB 의부하감소및성능향상 Primary 의 Workload(Reporting, Backup) 를 Standby DB 로분산 DR + Up-to-Date Reporting 3rd Party Solution 은구현불가 단방향 Replication 솔루션 기존 Replicaton Solution 에비해부하가매우적고관리가단순 비용절감 하나또는복수개의 Physical Standby DB 로 HA, Backup 수행 (, Reporting, Test 서버구현
Without Active Data Guard All Workloads Run on Production 2000 1500 Read-write service 480 tps 1000 500 Read-only service 640 tps 0 Production Database Data Guard Standby Primary server at 100% of capacity
Active Data Guard 11g Easily Optimize Performance for All Workloads 2000 1500 1000 read-write service 1,680 tps +250% 500 0 Production Database Active Data Guard Standby read-only service 1,350 tps +110% Primary server at 60% of capacity
Active Data Guard Use Examples Education 학교성적보고서, 교칙, 과목소개 Financial - transaction 조회, 시장가격, 거래내역 Healthcare 의료정보조회, 의사 / 간호사조회, 의료시설조회 Legal 법률보고서, 재판내역, 배심원평결내역 Telecommunications 사용내역, 미사용시간, 과금요율 Transportation 배송사태추적, 운송요율조회 Web-business 카탈로그조회, 가격비교, 주문현황 Bottom Line 많은업무에서 read-only 작업을많이수행함. Active Data Guard 를사용하면 : Read-only 성작업을 physical standby 에서수행가능하고 production database 의부하를덜어줄수있음.
백업속도향상 With Oracle Active Data Guard Offload fast incremental backups to standby Real-time Queries Production Database Standby Database standby database 에서 RMAN 의 block 변경추적기능을이용하면 incremental backups 20 배빠르게수행가능
테스트환경으로활용 With Snapshot Standby Physical Standby Apply Redo Open Database Back out Changes Snapshot Standby Perform Testing Continuous Redo Shipping Standby Database 를 Test 나개발환경으로사용 DR 구축비용을줄일수있음 DR 환경에서전반적 test 가가능 Data loss 가없도록유지 그러나 Snapshot mode 로전환후에는 redo 적용이잠시보류되어실시간조회는제한됨 Storage snapshots 과유사하지만 : 동시에 DR 기능유지하고 하나의 storage 복사본만사용
<Insert Picture Here> Active Data Guard 구성및동작원리 - Active Data Guard Deployment - Data Guard Transport Services - Data Guard Apply Services - Data Protection Mode
Active Data Guard Deployment Example Scale-out Query Performance to Web-Scale* updates queries queries Active Data Guard Reader Farm queries queries queries Production Database Standby Databases Reporting, web content browsing DR included *
Data Guard Transport Services Transactions Production Standby Production Database Online Redo Logs LGWR LNS LNS Synchronous Asynchronous RFS RFS Standby Redo Logs ARCH Gap Resolution RFS ARCH Archived Redo Logs Archived Redo Logs
Data Guard Apply Services Standby Redo Data From Site A RFS Standby Redo Logs Physical (Redo Apply MRP) Apply ARCH Logical (SQL Apply LSP) Standby Database Archived Redo Logs
Flexible Data Protection Modes Protection Mode Risk of Data Loss Redo Shipment Maximum Protection Zero Data Loss Double Failure Protection Synchronous redo shipping Maximum Availability Zero Data Loss Single Failure Protection Synchronous redo shipping Maximum Performance Potential for Minimal Data Loss Asynchronous redo shipping Balance cost, availability, performance, and transaction protection
Maximum Protection Mode Protection Mode Risk of Data Loss Redo Shipment Maximum Protection Zero Data Loss Double Failure Protection Synchronous redo shipping to 2 sites Highest level of data protection Configuration: LGWR SYNC, SRLs Enforces protection of every transaction If last standby is unavailable, processing stops at primary Good for financial systems where no data loss is acceptable ALTER DATABASE SET STANDBY TO MAXIMIZE PROTECTION;
Maximum Availability Mode Protection Mode Risk of Data Loss Redo Shipment Maximum Availability Zero Data Loss Single Failure Protection Synchronous redo shipping Enforces protection of every transaction Configuration: LGWR SYNC, SRLs If last standby is unavailable, processing continues at primary When the standby becomes available again, synchronization with the primary is automatic ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;
Maximum Performance Mode Protection Mode Risk of Data Loss Redo Shipment Maximum Performance Minimal data loss usually 0 to few seconds Asynchronous redo shipping Highest level of performance Configuration: LGWR ASYNC, or ARCH, SRLs Protects from failure of any single component Least impact on production system Useful for applications that can tolerate some data loss ALTER DATABASE SET STANDBY TO MAXIMIZE PERFORMANCE;
<Insert Picture Here> EM 을이용한 Data Guard Setup
Data Guard 구성 Manual 구성방식 Data Guard 구성절차 [Primary Site] DB Init Parameter 예제 ] 1. *.DB_NAME=ora101r Archivelog mode *.DB_UNIQUE_NAME=ora101r tns_psb = 2. Password File 생성 *.SERVICE_NAMES=ora101r (DESCRIPTION *.DB_NAME=ora101r = tns_psb = 3. Init 파일수정 4. Net Service TCP)(HOST (DESCRIPTION 등록 = = 5. Primary 1521)) DB Backup TCP)(HOST = 6. Backup (CONNECT_DATA Copy 생성 = 1521)) [Standby Site] ) (CONNECT_DATA = 1. O/S User 및환경변수설정 tns_ora101r oles) = 2. Backup Copy ) (DESCRIPTION 전송= 3. Oracle mandatory Software tns_ora101r (ADDRESS_LIST= oles) reopen=30 설치 = (DESCRIPTION = 4. Standby Control File 생성 =tns_psb 5. Standby TCP)(HOST (ADDRESS_LIST= Parameters = 설정 6. Net ary_role) Service 1521)) =tns_psb TCP)(HOST Name 등록= 7. Password DB_UNIQUE_NAME=psb 파일생성 optional ary_role) reopen=30' 8. Standby TCP)(HOST 1521)) DB Log Apply = 운영 [Primary DB Net Service 예제 ] [Stand DB Init Parameter 예제 ] [Stand DB Net Service 예제 ] O10R1.INSTANCE_NAME=O10R1 (ADDRESS *.DB_UNIQUE_NAME=ora101r = (PROTOCOL = *.SERVICE_NAMES=ora101r O10R2.INSTANCE_NAME=O10R2 O10R1.INSTANCE_NAME=O10R1 node1.kr.oracle.com)(port (ADDRESS = (PROTOCOL = = *.LOG_ARCHIVE_CONFIG='send,rece ive,dg_config=(ora101r,psb)' O10R2.INSTANCE_NAME=O10R2 node1.kr.oracle.com)(port = *.LOG_ARCHIVE_DEST_1= LOCATIO *.LOG_ARCHIVE_CONFIG='send,rece (SERVICE_NAME = psb)) N=/nfs1/common/oradata/arch/ ive,dg_config=(ora101r,psb)' VALID_FOR=(online_logfiles,all_r *.LOG_ARCHIVE_DEST_1= LOCATIO (SERVICE_NAME = psb)) N=/nfs1/common/oradata/arch/ DB_UNIQUE_NAME=ora101r VALID_FOR=(online_logfiles,all_r *.LOG_ARCHIVE_DEST_2='SERVICE DB_UNIQUE_NAME=ora101r (ADDRESS = (PROTOCOL = mandatory reopen=30 VALID_FOR=(online_logfiles,prim *.LOG_ARCHIVE_DEST_2='SERVICE node1.kr.oracle.com)(port (ADDRESS = (PROTOCOL = = VALID_FOR=(online_logfiles,prim (ADDRESS node1.kr.oracle.com)(port = (PROTOCOL = = *.LOG_ARCHIVE_FORMAT=rac_%t_ DB_UNIQUE_NAME=psb node2.kr.oracle.com)(port (ADDRESS = (PROTOCOL = = %s_%r.arc optional reopen=30' 1521)) TCP)(HOST =. *.LOG_ARCHIVE_FORMAT=rac_%t_ ) node2.kr.oracle.com)(port = %s_%r.arc 1521)). ) Configuration 자동설정 EM 의 Wizard 방식 (Parameters & Net Service)
<Insert Picture Here> EM 을이용한 Data Guard 모니터링및관리
Data Guard 모니터링 [Data Guard Views] DBA_LOGSTDBY_EVENTS [Script 예제 ][ DBA_LOGSTDBY_LOG DBA_LOGSTDBY_NOT_UNIQUE log_dest.sql] DBA_LOGSTDBY_PARAMETERS select dest_id, dest_name, status, target, DBA_LOGSTDBY_PROGRESS error, remote_template DBA_LOGSTDBY_UNSUPPORTED from v$archive_dest V$LOGSTDBY where schedule='active' ; V$LOGSTDBY_STATS V$DATAGUARD_STATUS [log_wait.sql] V$ARCHIVE_DEST select process, status, thread#, sequence#, V$ARCHIVE_DEST_STATUS block#, blocks V$ARCHIVE_GAP from v$managed_standby ; V$ARCHIVED_LOG V$DATABASE [archive_gap.sql] V$DATABASE_INCARNATION select * from v$archive_gap ; V$DATAFILE V$DATAGUARD_CONFIG [standby_archivelog.sql] V$DATAGUARD_STATUS V$LOGFILE select thread#,sequence#,applied V$LOG_HISTORY from v$archived_log order by first_change# ; Script 를이용한모니터링 - GUI 모니터링 - Top-down 원인분석 EM 의 Data Guard 성능모니터링
Data Guard 관리 Manual 방식의관리 EM 의 Data Guard 관리화면 - 관리작업실수예방 - 민첩하고편리한관리
<Insert Picture Here> Demo : Data Guard 구성및관리
요약 Active Data Guard Provides 저비용고품질 HA 시스템 (TCO 감소 ) Business 연속성을보장 (BCM) 운영 DB 의작업을분산시켜성능향상 Active Data Guard with EM Includes Wizard 방식의손쉬운 Setup 직관적인 GUI 모니터링 관리의민첩성과신뢰성이증가