Re: Oracle Advanced Replication Downtime Issues with System Tablespace

Thiko,
I am sure that it is too late to do this now....

But Oracle recommends moving the replication related tables out of the SYSTEM tablespace to avoid this exact problem. Here is a script I run during replication setup which resolves this issue:

http://www.dotcomsolutionsinc.net/products/repgen/repgen_example_repsetup_pr od3_1.html

--
David Simpson
www.dotcomsolutionsinc.net
"Thiko" <biwombi_at_hotmail.com> wrote in message
news:b8d3b40c.0303120146.3fe8bc1e_at_posting.google.com...


> 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!