March 16, 2009 at 9:42 am
Does anyone have any experience with Oracle to SQL transactional replication (Oracle 10.2.0.3.0, SQL 2008) with a very active OLTP publisher? I have such a replication instance set up, but there's massive latency from the Log Reader, up to the point of it timing out sometimes.
It timed out today and, after restarting logged the following
Batch processing is in progress. The current batch consists of 243373 commands and 0 transactions.
Naturally, SQL's getting the blame 😉
If anyone's run into this before, I would really appreciate some suggestions on where to start looking in Oracle. My knowledge of Oracle is, quite frankly, almost non-existent.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2009 at 8:28 am
I would be interested in some more information. I've worked with Oracle over 14 years... I'm assuming SS is the master replicating down to Oracle. Did this latency just start happening or has it been like this all along? Are both database engines on the same machine? (is there any kind of Virus software slowing things down)?
The machine itself, how much memory? How much memory for SS and the SGA for Oracle? Did the backlog of trx's complete or are they still massively backed up?
March 20, 2009 at 8:47 am
gbrabham (3/20/2009)
I would be interested in some more information. I've worked with Oracle over 14 years... I'm assuming SS is the master replicating down to Oracle.
No. Oracle to SQL transactional replication. SQL Server replication with an Oracle publisher. Distributor and subscriber on the same SQL Server.
Did this latency just start happening or has it been like this all along?
It turned out that it wasn't latency. The log reader was actually hung (more than 12 hours attempting to do a read of one of the replication table)
The Oracle DBA couldn't see exactly what the command was that was running although he could do a select count(*) from the same table and get an immediate result. DBA said there were no locks nor anything else that he could see that could have stopped a read from completing
If the log reader was stopped and restarted it hung again in just a few minutes.
Are both database engines on the same machine?
Oracle's on a Unix OS. Gigabit network between the two.
The machine itself, how much memory? How much memory for SS and the SGA for Oracle?
No idea. I think it's 2GB given to Oracle. 2 processors.
The SQL Server is (I believe) a 4 processor, 4 GB box.
Did the backlog of trx's complete or are they still massively backed up?
Client dropped the entire publication and recreated it. The issue has not reoccurred, yet. Currently the replication's running smoothly.
I'm kinda hoping that the issue does reoccur so that I can get a trace both from SQL and from Oracle to see exactly what lead up to the hang.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply