Re: Advanced Replication combined with Oracle Parallel Server

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Fri, 27 Aug 1999 08:54:43 -0700
Message-ID: <37C6B4C3.2B7DC5A7@us.oracle.com>

Michael

Yes there are special considerations over and above the single instance environment. For a start, look at the attached note then get back with any specific questions. Also, you mention pushing at 6 minute intervals. Have you done any volume testing to see of the throughput of what you're replicating will meet this?

Pete

Article-ID:         <Note:40657.1>
Circulation:        REVIEWED (EXTERNAL)
Folder:             server.Rdbms.Distributed.Replication
Topic:              4. FAQs
Platform:           GENERIC  Generic issue
Subject:            Asynchronous Replication and Parallel Server
Creation-Date:      22-AUG-1996 16:21:47
Modified-Date:      25-AUG-1999 19:35:43
Revision:           1
Document-Type:      FAQ
Impact:             MEDIUM
Skill-Level:        NOVICE
Component:          RDBMS
Content-Type:       TEXT/PLAIN
Attachments:        NONE

SYMMETRIC REPLICATION AND ORACLE PARALLEL SERVER

Symmetric Replication and Oracle Parallel Server can be used together but there are some issues which you need to be aware of. These may mean that the
expected performance gains are not as great as might be hoped. The environment should be tested thoroughly before being put into production.

Updates During Replication:

In row-level asynchronous replication all changes to replicated tables are captured by an after row trigger which inserts into the deferred transaction
queue, system.def$_call.

This table will receive every change for every replicated table in the database
and will store these changes until they are pushed by the procedure, dbms_defer_sys.execute.

Potential Risks when Using Oracle Parallel Server:

If a table is being accessed from multiple instances at the same time there

is always a risk of "pinging", ie: sending the relevant blocks backwards and
forwards between the instances. Where Asynchronous Replication is involved,

system.def$_call is a prime example of a table likely to be affected in this
way.

Ways to Minimize the Effects:

The only way to avoid the "pinging" is to ensure that system.def$_call is accessed from only one instance. This can be done by allowing only one instance access to replicated tables while the others have only read-only access to the tables involved in replication.
To avoid pinging when the queued transactions are propagated, only allow

one instance to run job_queue_processes ie:

on instance 1: job_queue_processes=0 on instance 2: job_queue_processes>0

3. If more than one instance needs to connect to the replicated tables then

the effect of pinging can be reduced by using multiple freelists and freelist groups on the system.def$_call table.

Failover:

One of the main reasons that Oracle Parallel Server can be used is for failover. If the steps above have been followed to minimize pinging on the blocks in the system.def$_call table then the instance which is not running

job_queue_processes needs to know if it needs to start them up.

In version 7, the job_queue_processes parameter is not dynamic therefore the
best way to do this is to have a cron job which checks the other node at intervals to check that the Oracle instance is running. If instance failure has
occurred then the job should periodically execute dbms_defer_sys.execute to

push the deferred transaction queue.

Summary:

The above should make it clear that, while Asynchronous Replication and Oracle Parallel Server can be used together, the need for the combination should be thought through and, if the decision is made to go ahead, the implementation should be carefully planned and tested.

Oracle Release 8.0.X and 8i updates:

Starting with Oracle Release 8.0.4, job_queue_processes can now dynamically be altered using an ALTER SYSTEM command. The change in the number of SNP processes for an instance is done asynchronously. In particular, PMON will start up new SNP processes after it learns about an increase, and PMON will instruct existing SNP processes to kill themselves
after learning about a decrease. An SNP processing running jobs will not see the notification to kill itself until it runs out of jobs to run.

Starting with Oracle Release 8.1, a new feature lets the job submitter restrict the job to a particular instance or (by default) let the job run on any instance. This instance attribute can be changed dynamically.

mjwalsh_at_hotmail.com wrote:

> Hi
>
> has anybody implemented Advanced Replication in an Oracle Parallel
> Server environment. If so I would be interested to know what problems
> if any you ran into and if there are any special considerations over
> and above Advanced Replication in a single instance environment.
>
> We have a primary site running a Sun Cluster (2-nodes) with Solaris 2.6
> and Oracle 8.0.5. running in OPS configuration. We have a failover site
> which also has a Sun Cluster running 8.0.5. The advice we are getting
> is that we should run Async Replication at 6 minute intervals.
>
> We are an internet based business with most of our 'users' coming in
> over the web.
>
> Questions
>
> 1. has anybody done anything like this before.
> 2. are you aware of any limitations in terms of Oracle which imposes
> the six minute intervals
> 3. What conflict resolutions strategies do you use to get around the
> possibility of users reentering a transaction after being kicked off
> because they believe it failed as they are connected to the secondary
> site which hasnt recieved the replicated data.
> 4. Are there any other limitations in an OPS configuration to be
> considered. For instance do I need to set my 'job' init.ora parameters
> on the master node of the cluster only or one or both.
>
> Any advice, lessons learnt hints would be greatly apreciated
>
> regards
>
> Michael Walsh
> mjwalsh_at_hotmail.com
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Received on Fri Aug 27 1999 - 10:54:43 CDT