Monthly Archives: September 2008

Determining the network bandwidth required for a dataguard physical standby implementation


Network bandwidth Implications of Oracle Dataguard

Dataguard Redo Transport & network configuration

Determine the redo generation rate

– You can query dba_hist_sysstat to find out your redo generation rate in bytes.
– You can choose to use either your average redo generation per hour or peak redo generation per hour (I would recommend that you size for the peak redo generation).
– Let us say that you determined that the redo generation rate, in Bytes, PER DAY, happened to be RedoBytes.
– You have to add a 30% overhead for tcp. So RedoBytesPlusOverhead=RedoBytes*1.3

Convert redo generation rate to Mbps (Megabitspersecond)

– (RedoBytesPlusOverhead*8)/((24*60*60)*(1024*1024))
– This is the theoretical minimum bandwidth that you are going to require.

Other important considerations

– Network latency is a huge factor in the amount of redo you will be able to transport from your primary site to the standby site. This value is unique to your network, so if you have a high latency network you might not be able to sustain the required rate of redo shipping.
– Usually the wide area network between the primary site and standby site is used by more than just dataguard (eg: e-mail etc). So those bandwidth requirements have to be factored in.
– The above two points is why customers should not rely too much on theoretical calculations and to actually deploy dataguard and test the actual redo generation and redo transport performance statistics.
– If you do not deploy a network that can ship redo at a rate of 45 mbps, all that means is that, at times your redo shipping will fall behind (ie your standby site will be behind the primary site) but dataguard still works. In a lot of cases this is acceptable (Based on the customers recovery point objective and recovery time objective).
– There are network tuning best practices outlined in “Dataguard Redo Transport & network configuration” , that you are optimizing the redo transport mechanism and the network. These have to be followed to achieve the best possible network performance.
– There are other techniques like network compression (Hardware compression using wan compression devices, or actual software compression in dataguard 11g) which enable you to reduce the network bandwidth requirements.


– You can run the following script to extract redo generation information from dba_hist_sysstat.

set pages 0
set head off
set lines 132
set colsep ~
col curval format 9999999999999999999999
col prevval format 9999999999999999999999
,to_char(sn.begin_interval_time,’DD-MON-YY HH24′)
,to_char(sn.end_interval_time,’DD-MON-YY HH24′)
,cur_stat.value curval
,prev_stat.value prevval
,(cur_stat.value-prev_stat.value) RedoGen
from dba_hist_snapshot sn,
(select snap_id,value from dba_hist_sysstat
where stat_name = ‘redo size’) cur_stat
,(select snap_id,value from dba_hist_sysstat where
stat_name = ‘redo size’) prev_stat
Where sn.snap_id = cur_stat.snap_id
and cur_stat.snap_id = prev_stat.snap_id + 1 order by 1;

– Spool the contents into a file RedoInfo.dat

– Create a table in the oracle database named RedoInfo

create table redoinfo (
inid    number,
bdate    date,
edate    date,
totredo    number

– Use sql*loader to load the contents of the spool file into redoinfo (At this point some would ask, “why dont i just do a create table as in the same database”, my assumption is that you probably dont want to be creating these temp tables in a production env.).

load data
infile ‘RedoInfo.dat’
append into table RedoInfo
fields terminated by “~” optionally enclosed by ‘”‘
field1 filler,
field2 filler,
bdate  Date “DD-MON-YY HH24”,
edate  Date “DD-MON-YY HH24”,
field3 filler,
field4 filler,

– Then you can run all kinds of queries on this to learn the different charachteristics of your redo generation
– The query below gives you the total redo generation per day and  the Mbps

select to_char(edate,’dd-mon-yy’) Day,sum(totredo)/(1024*1024) TotRedoMb
,(sum(totredo)*1.3)/(1024*1024) RedoPlusOvrHd,((sum(totredo)*1.25)*8)/(1024*1024) Mbits
,round(((sum(totredo)*1.25)*8)/(24*60*60*1024*1024)) Mbps FROM RedoInfo
group by to_char(edate,’dd-mon-yy’)
order by 1;

Installing Grid control with oracle database for the repository

Below is what i think is the quickest (And possibly the only supported way to do this directly (instead of installing, finishing the grid control install and upgrading to of steps to install grid control, while using oracle database version for the grid control management repository on Linux x86.

Typically you would install grid control, which installs a database as the management repository, then you would upgrade enterprise manager to and then eventually upgrade the database to Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install and then it takes even more time updating all this configuration when you install the patch. In the method outlined below all the configuration is done when you run the configuration script in the end, saving you precious time.

  • Install Oracle RDBMS
  • Upgrade Oracle RDBMS to
  • Create a listener
  • Create a Database
    • When you create/configure the database make sure that, you DO NOT choose the option to enable database control for the instance. (If you choose to enable database control, you will have to de-configure it).
  • Configure the Initialization parameters
  • Install Oracle Grid Control, software only.
    • Install Oracle Grid Control, software only, Subsection titled ” Enterprise Manager Grid Control Using an Existing Database“.
    • Here be extremely careful when you are configuring the em_using_existing_db.rsp file. When you are setting the value for the parameter s_reposPort=”1521″, remember to use the double quotes around the 1521 or else you will have issues with the installation.
  • Install Oracle Grid Control, software only.
    • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the patch set to OMS
  • Apply the patch set to the Agent on the management server
    • Follow instructions in “Step 9, “Apply the patch set to Agent
  • Run the configuration scripts to configure grid control to use the already created database.
    • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the….

Instructions on how to upgrade this installation to Enterprise Manager can be found in my followup post , Upgrading Enterprise Manager to

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for Install and Upgrades

Installing Enterprise Manager using an 11g Database for the repository

Steps to upgrade or higher Repository to 11g

Install grid control on enterprise linux 4

Install grid control on enterprise linux 5

You can find samples of the rsp files below



Grid control agent secure

I recently had some trouble installing and configuring grid control. I was installing grid control using a pre-existing database. I somehow messed up something in the automated response files and for the life of me i could not figure out the password to use for securing the agent (It was definitely not using the passwords i had thought, i used in the response file).

The security best practice is to leave the management server secured. The procedure below is just adhoc, so that you can unsecure and continue working and later reset your password.

So i had to do the following to unsecure the management server.

First figure out if the OMS is secured.

./emctl status oms -secure

If the output of the command shows HTTPS, then you know that the oms is secured .

In order to fix the agent

First stop the agent

On the Oracle Management server

cd <OMS_ORACLE_HOME>/opmn/bin
./opmnctl stopall
./emctl secure unlock
cd <OMS_ORACLE_HOME>/opmn/bin
./opmnctl startall

Once this is done you can unsecure the agent

cd <AGENT_HOME>/bin
./emctl stop agent
./emctl unsecure agent
./emctl start agent

For further information (and to understand how to secure the OMS) please refer the following metalink notes.

How To Secure / Unsecure The Grid Control Components (Agent / OMS) In 10g
Problem: Agent Upload Fails: OMS VERSION NOT CHECKED YET

Real application testing for 9i and 10g

The real applications testing database option that oracle introduced in Oracle 11g, has now been back ported for use with oracle 9i and 10g. Real application testing has two main components

  • Database Replay
  • Sql performance analyzer.

The database replay component helps customers capture actual real life workloads and gives them the ability to replay them on test or development systems to guage the effect of changes they are about to make (eg: change initializtion parameter, upgrade database version, move to new storage etc).

The Sql performance analyzer component helps customers to understand the effect of changes on sql statements. This component allows customers to capture sql statements and execution plans from a system and re-execute the sql statements on a test system (Where changes have been made) and then compare the performance of sql statements before and after the change. So if any sql statement regressions are found, the customer can then use the sql tuning assistant to tune such sql statements.

Based on popular demand oracle has backported the components of real applications testing to earlier releases.

The “Capture” portion of “Database Replay” has been backported for 9i and 10g. Meaning workloads can be captured from 9i or 10g databases and “Replay”‘ed on 11g databases. Please note that workloads cannot be “Replay”‘ed on 9i and 10g.

The “Sql performance Analyzer” component has been backported and made available for use with 9i and 10gR1 customers upgrading to 10gR2 or higher. So sql statements can be captured from 9i and 10gR1 and Re-run in 10gR2 and 11g and then the performance of both executions can be compared to identify regressions.

In any case where your target database is a 10g database, an intermediatory 11g database is required so that the “Database Replay” and “Sql performance analyzer” tasks can be executed on the 10gR2 database.

For details regarding using Real Application Testing with Pre-11g databases check the following documents

Technical note: Real application testing for earlier releases

Technical white paper: Testing the sql performance impact of an oracle 9i to oracle 10gR2 upgrade with SPA

Real Application testing for earlier releases (Metalink note)

Licensing Details