Oracle Advanced Replication Downtime Issues with System Tablespace

Hi

Background:

Ive had situations in the past where by one of the replicated databases have gone down or cannot be 'seen' by the other replicated database. Then i believe the deffered transactions (?) have built up and the system tablespace has then become full and run out of space. This in turn has caused all sorts of problems.

Current Dilema:

One of the replicated databases needs to be shutdown to apply a patch.  The steps involved to apply the patch have been carried out on a test machine and i would like to leave approx. 2 hours to apply the patch.

Replication will not be stopped or suspended for the downtime. The listeners will be stopped on the 'downed' datbase just to make sure no repliation takes place when the scripts are run for the patch after the database is back up.

Ideally i would like to know how much time i have before the system tablespace gets full and runs out of space. Will 2 hours be ok?

Looking at the GUI for replication in DBA Studio and the Topology tab you can see the number of defered transactions in the queue. Manually you can see this Number of items in the deferred transaction queue with:

SELECT COUNT(*) FROM deftrandest;

I also know that DEF$_AQCALL and DEF$_AQERROR are the base objects (qeueues and queue tables) supporting Oracle replication.

There are two different views of the deferred transaction queue:

• DEFTRAN : contains all unpurged transactions
• DEFTRANDEST : contains all transaction that have not yet been pushed to a remote master site. Transactions appear once per master site they have to be pushed to.

And that deftrandes is a view made up of selects from system.def$_aqcall and system.def$_destination and system.repcat$_repprop objects.

Using all this is there a way i can work out at what point the system tablespace will become full and hence how long approx. i can have database downtime for without stopping replication?

Any ideas appreciated :)

Many Thanks.

Thiko! Received on Wed Mar 12 2003 - 03:46:17 CST