Monthly Archives: September 2008

Determining the network bandwidth required for a dataguard physical standby implementation

References

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.

Scripts

– 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
Select
sn.snap_id
,cur_stat.snap_id
,prev_stat.snap_id
,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,
inid,
bdate  Date “DD-MON-YY HH24”,
edate  Date “DD-MON-YY HH24”,
field3 filler,
field4 filler,
totredo
)

– 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 10.2.0.4 with oracle database 10.2.0.4 for the repository

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

Typically you would install grid control 10.2.0.1, which installs a 10.1.0.4 database as the management repository, then you would upgrade enterprise manager to 10.2.0.4 and then eventually upgrade the database to 10.2.0.4. Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install 10.2.0.1 and then it takes even more time updating all this configuration when you install the 10.2.0.4 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 10.2.0.1
  • Upgrade Oracle RDBMS to 10.2.0.4
  • 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 10.2.0.1, software only.
    • Install Oracle Grid Control 10.2.0.1, 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 10.2.0.4, software only.
    • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the 10.2.0.4 patch set to OMS
  • Apply the 10.2.0.4 patch set to the Agent on the management server
    • Follow instructions in “Step 9, “Apply the 10.2.0.4 patch set to Agent
  • Run the configuration scripts to configure grid control to use the already created 10.2.0.4 database.
    • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the ConfigureGc.pl….

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

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for 10.2.0.5 Install and Upgrades

Installing Enterprise Manager 10.2.0.4 using an 11g Database for the repository

Steps to upgrade 10.2.0.2 or higher Repository to 11g

Install grid control 10.2.0.5 on enterprise linux 4

Install grid control 10.2.0.5 on enterprise linux 5

You can find samples of the rsp files below

em_using_existing_db.rsp

patchsetoms.rsp

Grid control agent secure

I recently had some trouble installing and configuring 10.2.0.4 grid control. I was installing grid control 10.2.0.4 using a pre-existing 10.2.0.4 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.

cd <OMS_ORACLE_HOME>/bin
./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
cd
./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