Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Applies to All SAP Releases which run on Oracle Database 10g and above. For more information, visit the Landscape Design and Architecture homepage
Summary This is a step by step guide to calculate Oracle Dataguard bandwith requirement hourly. Author:
Ufuk Yavuz
Company: IBM Global Services Turkiye. Created on: 2 December 2010
Author Bio Ufuk YAVUZ is a SAP Consultant with IBM Global Services Turkiye. He has more than 8 years experince in SAP Basis , Database and Operating Systems as an administrator and consultant.
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 1
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Table of Contents Introduction ......................................................................................................................................................... 3 Bandwidth Calculation Formula .......................................................................................................................... 3 Getting Redo Generate Per Sec. (RGPS) .......................................................................................................... 3 Recording Redo Generate Per Second Value Hourly ........................................................................................ 3 Analyzing RGPS with Excel ................................................................................................................................ 4 Related Content .................................................................................................................................................. 6 Copyright............................................................................................................................................................. 7
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 2
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Introduction Oracle Data Guard ensures high availability, data protection and disaster recovery for enterprise data. It aids in establishing and maintaining secondary "standby databases" as alternative/supplementary repositories to production "primary databases". Since it is based on log shipment between primary and secondary databases over network (generally WAN for disaster recovery solutions) one of the main challenges is the calculation of required bandwidth. In this whitepaper a bandwidth calculation method will be explained.
Bandwidth Calculation Formula The formula calculates the required bandwidth with “redo generate per second” value. Required bandwidth = ((Redo generate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps So we will aim to obtain that value.
Getting Redo Generate Per Sec. (RGPS) We will use gv$sysmetric_history view to get redo generate per second value. This view hold statistical data related to 135 different metric. “Redo generate per second” is one of them. But it only keeps data of last 1 hour with which is taken every minute with 74 samples. ( 60 sample for every minute , 14 sample for every 15 second of last 3 minutes) We will get average and maximum these 74 samples to be able to calculate hourly maximum and average bandwidth. The metric_id of “Redo generate per second” is “2016”. Generally systems activity for all day long or a week must be observed to get an clear idea about required bandwidth. So to keep the historical data of this metric we will use an operating system level script.
Recording Redo Generate Per Second Value Hourly Here is an example script prepared in AIX environment. SID of database is TST .
MAX_SQL='set heading off; \n select max(value) from gv$sysmetric_history where METRIC_ID=2016; \n exit \n' AVG_SQL='set heading off; \n select avg(value) from gv$sysmetric_history where METRIC_ID=2016; \n exit \n' MAX=`echo $MAX_SQL | sqlplus -S / as sysdba` AVG=`echo $AVG_SQL | sqlplus -S / as sysdba` HOUR=`date +%k` echo `date +%d%m%y` $HOUR $MAX $AVG >> /test/log/bandwidth.log
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 3
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
We will run this script hourly via crontab so that it records maximum an average “Redo Generate per Sec” (RGPS) values in bandwidth log. You can use crontab –e command to create crontab entry. An example crontab entry: 01 * * * *
/oracle/TST/sapreorg/bandwidth.sh
At the end of day bandwidth.log file will look like this:
Analyzing RGPS with Excel We can use an excel sheet to turn this values into bandwidth with our formula mentioned before. We pasted the values Hour,MAX ,AVG to column A,B,C respectively. Column D and E has the formula and calculates bandwidth with the values in B and C. =((B2/0,7)*8)/1000000 =((C2/0,7)*8)/1000000
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 4
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Max. Bandwidth values must be provided for maximum protection mode of Dataguard which keeps standby synchron with primary. So this database needs 56,5 Mbps to run in this modes. Average values are acceptable and must be provided for maximum availability and performance modes. At hour 17 system has a peak average value for bandwidth – 7 Mbps. So finally we got an excel which we can find hourly bandwidth requirement. Further analyses can be done based on RTO and RPO targets.
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 5
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Related Content For more information, visit the Landscape Design and Architecture homepage
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 6
Calculating Oracle Data Guard Network Bandwidth in Sap Environments
Copyright © Copyright 2010 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the U.S. and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.
SAP COMMUNITY NETWORK © 2010 SAP AG
SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com 7
