Replication Server - Outbound queue is not moving

[former_member246062]

Hello ,

Using Oracle source to Oracle destination by using SAP Replication Server 15.7 .

Source is transaction intense system. Most of the time getting memory warnings saying resource not available for clearing largest transaction.

OUTBOUND QUEUE in REP SERVER :

1>admin who,sqt,104

2>go

Spid State Info Closed Read Open Trunc Removed Full SQM Blocked First Trans Parsed SQM Reader Change Oqids Detect Orphans -------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------

229 Awaiting Message 104 odsdb.ODSDB 0 0 1 1 1 0 0 st:O,cmds:50807,qid:186261:2:0 1 0 0 0

Outbound queue size is 170 GB & not getting drained. When I do trace ON , SQL's are flowing.

Thanks,

Yuvaraj Subramani

[former_member246062]

Thanks Mark...

Tried increasing the dsi_sqt_max_cache_size alone as suggested in the KBA 2011312 & the warning msgs has been stopped now.

But got this below Information once ,

I. 2018/05/30 15:59:34. A parallel transaction has failed in database 'odsdb.ODSDB'. The transaction will be executed individually.

Hope it will not impact anything...

Treat the SRS/DSI connection as any other application connecting into the replicate Oracle database - Can u please elaborate more on this?

Any addtional configuration needs to be done in target/replicate oracle DB?

[Mark_A_Parsons]

From an Oracle perspective, SRS is just another application running queries in the Oracle database; point being that you may want to put on your 'Oracle P&T' hat and see if there are any performance issues (in Oracle) with the queries being submitted by SRS.

[former_member89972]

Yuvaraj

Did you get this resolved finally ? Please share your fix or workaround.

Thanks

Avinash

[former_member246062]

Hello Avinash,

Earlier Memory_limit was 40GB & then increased 50GB & tried to purge all open

(sysadmin purge_all_open,Q_no,Q_type[0|1]) transactions but failed by throwing cache size insufficient warning .

Later increased Memory_limit to 75 percentage & increased both (sqt_max_cache_size & dist/dsi_sqt_max_cache_size).

Then purged the open transactions . After adding the memory , till now it is stable from last week.

Observed Open transactions were in millions in few systems & it is causing other issues in Rep Agent . I'm opening another issue thread for the same.

Thanks,

Yuvaraj Subramani

[former_member246062]

Hello Mark,

Still facing this Open transaction issues in Inbound queue. We are using 40+ source Systems & 18 Replication servers.

Source is Oracle & Destination is also Oracle with Sybase Rep Server 16.0. In Rep Agent , we were provided the Open tran to Oracle DBA by ra_helptran cmd . Using session ID , DBA confirming the same session ID s not active in source systems.

We are using Function String Replication with Default Oracle Function class , since there are 40+ high transactional banking oracle source systems , facing this Open transaction issue everyday in multiple instances and going out-of-control. Customer started suspecting the capabilities of Rep Server itself.

Oracle Target is responding fine , since data replicating for other sources perfectly fine. Most of the times, because of this open transactions IBQ is getting struck & there by Replication is failing. Kindly suggest if there is any other workaround if I could try .

Thanks,

Yuvaraj Subramani

[Mark_A_Parsons]

If the application/user/whatever really is holding open a transaction for extended periods of time in the source ... there's nothing SRS (or RAX) is going to be able to do about it. The (obvious?) solution would be to sit down with the developer/user, explain what's happening, and (hopefully) see if they can re-design this particular process to reduce the time a transaction is held open.

------------------

Another option, though I haven't tried this anywhere but with ASE, is to see if you can setup multipath replication and route the problematic query(s) via a separate path. [At this point I have no idea of multipath rep is (fully) supported with heterogeneous replication, let alone which features are supported - eg, routing procs or filters via different paths.]

[former_member89972]

I would suggest to open a case with SAP/Sybase to get proper support you may need for this.

st:0 indicates open transction with huge number of commands on source side.

Check on source side for the open transction that needs a commit or rollback.

May need a source side restart (rep agent first, then instance if need be).

There are sysadmin commands to deal with open transaction.

USE with caution and with explicit advice from SAP/Sybase folks.

HTH

Avinash

[former_member246062]

Hi Avinash,

Tried these commands to purge open txns , but issue was not resolved.

> sysadmin purge_first_open

> sysadmin purge_all_open

As of now, moved the rep agent locator to 0 and purged all the queued transaction in Rep Server and started the replication.

very frequently we are getting this warning msg too....

_sqt_remove_largest_tran(106:0 odsdb.ODSDB): No candidate found for removal. Memory limit will be exceeded by SQM/TI thread.

[former_member89972]

My bad.

If it is outbound then you may have to see the target Oracle server/instance.

See the answer/comments by Mark.

HTH

Avinash

[former_member246062]

Thanks Avinash....

[kenneth_brozovich]

Highly suggest opening a Support incident.

If you wish to continue the battle without, you at least need to increase RepServer memory_limit and sqt cache (in this example, dsi_sqt_max_cache_size, doc link below). RepServer default configurations will not work well in high volume oltp, some tuning will need to be done.

https://help.sap.com/viewer/62f3e3a6d49342cf9b7d72ee726a48b0/15.7.1.305/en-US/00067b0bbd1d10148d27cc...

https://help.sap.com/viewer/62f3e3a6d49342cf9b7d72ee726a48b0/15.7.1.305/en-US/0015e083bd1d101485bcd7...

-Ken

[former_member246062]

Thanks Ken... It has been resolved by increasing the sqt_max_cache & DSI_sqt_max_Cache_size.

Now I'm facing different scenario in another server where Inbound Queue is moving but not coming to the outbound queue .

When I do

admin stats , backlog

Thread count is increasing and not been pushed to outbound queue. All subscriptions are in valid state. There are no open transactions. suspecting there is something to do with this SQM Blocked is 1.

Spid State Info Closed Read Open Trunc Removed Full SQM Blocked First Trans Parsed SQM Reader Change Oqids Detect Orphans
---- -------------------- ------------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- -----------
116 Awaiting Wakeup 152:1 DIST msf.MSF 0 7 1 8 0 0 1 st:R,cmds:3,qid:623093:46:22 0 0 0 0

Can anyone suggest on this?