Oracle Streams Question - Problems With Apply Process

Hello everyone.  I am having a problem with my apply process.  I have 2 databases which are both capturing changes, and propagating to one another.  My problem is, one of the databases is not applying the changes that have propagated from the other.  The changes are just sitting in the apply queue.  If I go to the alert log, I see this error:

A005: warning -- apply server 1, sid 283 waiting on user sid 293 for event (since 601 seconds):

If I try and stop the apply process, I get a timeout error:

begin dbms_apply_adm.stop_apply ('APLY_FROM_CNTRL'); end;

ORA-26672: timeout occurred while stopping STREAMS process APLY_FROM_CNTRL
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 628
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 55
ORA-06512: at line 1

This whole process had been working perfectly until a few days ago, when out of nowhere it just decided to quit.  Does anyone have any ideas?  Thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I'm not where I can look this up but I remember there is a force option to stop the apply process.

Try forcing it down
And restart it.
marksmithy69Author Commented:
Yep, I tried that earlier (I forgot to add that to the original thread),  and I was able to stop the apply process, but upon restart I still get the same error in the alert log.  This is the command FYI...

SQL> exec dbms_apply_adm.stop_apply ('APLY_FROM_CNTRL', true);
slightwv (䄆 Netminder) Commented:
I suggest you open an SR on this so they have ample time to start looking into it.

I'm wondering if there's an in-doubt transaction on something similar possibly hanging the apply process.  What session is associated with sid 293?

Luckily I've never had to do this but I'm not sure how a point-in-time recovery would affect this since there would be an SCN change.  By chance was there a recovery performed recently?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

marksmithy69Author Commented:
There wasn't a recovery performed lately, but there was a Dataguard failover. Our primary went down, and it failed over to the standby.  After the primary came back up, I switched back over to the original primary.  It was around this time when things started to go downhill with the apply process.
slightwv (䄆 Netminder) Commented:
I'm not a dataguard person.

There aren't many streams folks around here (I use it but am far from an expert with it).

There are a few dataguard Experts around so they might be able to add some insight.

I've never thought about how to configure a dataguard setup with streams replication but I can see how it would be interesting to try.

Did you instantiate the primary and failover with streams or just the primary?  I can see a dataguard failover messing up apply if the failover wasn't also replicating.

Did you by chance try a dataguard failover with streams before you went into production with it?

I'm definitely thinking Support SR now.
marksmithy69Author Commented:
Yes, this was tested before we went to production.  We've had a failover occur before, with no problems with streams in the past.  We use a virtual ip address, which checks the dataguard configuration.  Whichever is the primary database is where the virtual ip points.
slightwv (䄆 Netminder) Commented:
That's cool.  I know very little about how dataguard works under the scenes but could imagine it confusing Streams.
DavidSenior Oracle Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.