Dear Tom,
database 1 send a 200 lines text file every 10 seconds to database 2 and the database 2 again send a receipt of that file to database 1. We use MQseries at the moment but now looking forward to use Oracle Streams AQ thru DB link.
Is it wise to use Oracle Strems AQ for this purpose or something else can be tried?

Thanks in advance.

Mar 31, 2008

Hi Tom,

We have a problem with Streams: specifically queue-to-queue propagation aborting with ORA-600.

Oracle support got stuck on that, too.

I would like to INSTRUMENT the code between capture and propagation and apply processes of 2
databases so that it would be 100% clear where and under what circumstances these errors come up.

Streams are configured ONE-WAY + the source database is high volume processing OLTP.

If you have any sample of the code handy please quote:
1) an example of PL/SQL custom apply-handler
2) an example of intercepting of LCR to display or store into a TRANS_LOG table

We would liike to revisit the code and any good example of good practice design of custom
rule-based-transformation would be great

Thanks
Andre

Tom,

Sorry for placing the same question in a couple of places but when I saw it vanished - I thought I made an error.

ALL I AM ASKING IS:
Can you please suggest an INSTRUMENTATION method to localize the root cause - of the problem...?

I have managed instrumentation before - but I am not certain wbout the best approach when it comes to Streams.

I would appreciate your help.
Simple PL/SQL example that would work at TRANSFORM of LCR or at various points of contact from one queue to another. Then - I can say to support: "HERE IT IS - WHY do I get ORA-600" - Otherwise I will keep hearing: "Produce details"

I know that you are a very strong advocate for Instrumentation - I will really appreciate your help.

Regards
Andre

Dear Tom,

I understand that and I have done this before.

(1)
Knowing how much importance you personally put on code instrumentation I asked for your advice - i.e. in case that you have ever instrumented a AQ and or Streams implementation whatever the complexity. If you have you could help me/us to get this done on our site and enable us to locate the "weak link". If you have never instrumented Streams and/or AQ implementation I understand - and I would be grateful if you could suggest an approach with a very simple POC type instrumentation code.

Unless you tell me that instrumentation SHOULD NOT BE DONE when AQ and/or Streams are involved (I can hardly believe that this could be the case) - then I would be very curious to hear the reasons - especially coming from you.

(2)
Now some background info.
Oracle support (escalated etc.) had only told us to verify all environment set-up including releases of Solaris + 10g with all the patch lists on both sides.

They could do more - but this is one of the biggest banks and the security is such that even internally teams on receiving end do not access the core servers.

There are a number of people involved (several countries)and I joined in to face problems that need resolution.

And - BY NO MEANS this is an implementation "using the default apply methods all across the board" - not at all.

Actually rather complex with myriads of propagations queues - I am trying to get some idea as to who did what and for what reason.

The bank will not allow Oracle Support to connect or have any access to the core databases - there various security layers change of ownerships as the stream data travels from one que to the next etc., etc...

Hence my request to you for help + instrumentation examples + some guidance so the project will get implemented.

Kindest regards
Andre

Tom

Quite a while back I implemented AQ/Streams as out of the box. ¿KISS¿ formula.
I had no problems. There are hundreds of various projects that implemented out of the box AQ/S and only a very few report problems.

If you have several machines involved and NOT out of the box implementation where there are various queue-to-queue communications that call various rule based transformations and this breaks it is impossible to pin-point the root cause of the problem as it is not clear where this happens.

By instrumentation of this implementation I meant to intercept process at various points into LOG tables so that one could see right away:
1. Does an LCR reach these tables?
2. Does it hold the same data (some guys claimed data corruptions)
3. Does it show that transformation worked (at capture and propagation #1, #2, #3, #4 etc + at apply)?

If the stream cannot be intercepted (like FBI tapping a phone conversation) then we could move an intercept LOGGING along ¿ it would take more tests but that¿s OK.

All of that could be done on one single machine first with database backups done so that regression tests could be done.

And if the entire process works on one machine ¿ but does not work in the real life set of servers, then we could intercept data the same way.

Am I missing something?

So ¿ is it or is it NOT possible to intercept data that is streamed between:
a) capture and propagation
b) between propagation #1 and #2
c) between propagation #2 and #3 etc
d) between several propagated streams into one etc
e) between various propagations and apply streams
f) AND ¿ whether or NOT the data intercepted is what is expected ¿ transformed and not corrupted

I thought I could bet that if you found yourself in a similar spot, you would start by questioning your own implementation rather than contact one of your guys next door to solve a problem, would you?

Kindest regards
Andre

OK Tom,

So some guys at THE BANK have chosen to implement a myriad of Propagation queues (as many as several dozens - to run concurrently for "performance") - so the they hav emanaged to "prove" to Oracle that Streams would not work under such scenario + at volumes of 100,000,000 transactions + which is way out of the demo = out of the box SCOTT/TIGER EMP replication../

At the end we have entered a finger-pointing contest (I am not trying to be more to the point ...) where ...
- never mind that... no point to quote...

I have researched Internet metalink and forums etc/ etc/ etc/ etc... until I got to "Step- by-step Streams by LewisC of PriceWaterhouseCoopers plc" ... interesting

+

Frankly Tom,

I could not possibly see HOW instrumentation would mess up things and that I should just follow your advice and tell Oracle Support:
"We are getting ORA-600 + fix it or else + BTW we will not provide you with any further info other than our platform + Oracle 10gR2".

If you figured that I have reached the end of the ORA-rope - you're right.

Tom,

I understand your position as far as using support and we have tried ¿ but there is far too much politics involved. The management says that Oracle will have to reverse engineer their 11g Streams architecture back into 10gR2 or 10gR3 for us and we will then repeat the process ¿ meanwhile snapshots will be the fallback option. BTW. Their administrators have NOT set up GLOBAL_NAMES=TRUE so db-links are not set up the way Oracle says they should. And as I said earlier there are dozens of propagations ¿ history of this is not clear.

The problem needs to get resolved one way or another.
I have done Internet searching to find an article ¿Step-by-step Streams¿ published by Lewis R Cunningham, PricewaterhouseCoopers, LLP.

Almost identical text appears in Lewis C blog
http://blogs.ittoolbox.com/oracle/guide/archives/oracle-streams-configuration-change-data-capture-13501

Lewis published several articles where he demonstrates how one can create an LCR manually and use AQ to send this from one queue to another.

My point is this:
If one uses an Oracle package and provides the required parameters and all should work but it does not and the package body PL/SQL is wrapped ¿ sure Oracle support needs to deal with this.
But ¿ if it is an entire system (STREAMS in this case) where data held in LCR gets corrupted ¿ sometimes (sporadically) and nobody knows at which point, I thought it should be OK to:
1. Create a very simple streams implementation and run a single transaction through it ¿ one at a time for various objects that are required
2. Simulate the load to higher values and monitor
3. Generate various mixed transactions that process various objects with various data and monitor
4. In case of an error replay the same process for the one transaction that caused it or for a batch of transactions ¿ regression test
5. When the error(s) can be reproduced ¿ instrument the process by intercepts of the data flow ¿ that way there are 3 levels of controlling simulation
a. The transaction generator (say PL/SQL procedure) that processes apps objects ¿ this can slow down the data flow on the input
b. The CDC (change data capture) can be intercepted, then propagation, and then apply handler(s) ¿ without any transformation
c. Finally transformations are introduced
6. One may find that once some critical mass (high transfer rate) is reached there are errors that show up but only in a capture custom transformation
7. The problem may show up only when high volume of data is transferred say millions of rows processed ¿ infrequent COMMITS
8. Combination of 6+7 etc.

But if none of those errors can be generated with a single-path stream then (if really necessary) multiple queue propagations can be gradually introduced with parallel processing etc., etc. to locate the exact circumstances ¿ thus root cause of the problem.

Once it is clear what the root cause is ¿ one can opt for a different implementation ¿ say downstream capture or mining only archived logs.

If a work-around is found ¿ Oracle may fix the original problem with a patch but the project moves along with a robust deployment = preferred method.

Is Oracle Streams a suitable method for replicating a subset of tables from one database to another in near real-time i.e. if the requirement is that updates made on the source database must be made on the destination database within milliseconds?

If the answer is yes, does it require that both databases be on 11g and using the new synchronous capture feature of 11g? (It is not clear to me from the Oracle documentation.)

I'm not sure that I follow your logic about small hardware, but anyway let me explain why the requirement is there.

We have a long established database - Database A in Country A. This database is updated by feeds, by operators, by background jobs etc.

We plan to have Database B in Country B. Certain (but not all) of the data in this database must be kept in step with the data in Database A.

Legal requirements in Country B stipulate that Database B and the application that uses it must reside, physically, in Country B so we need to find an efficient method of propagating the data from A to B.

If it can be done efficiently then copying the data directly from A to B in some way has to be the best way of ensuring data matching and integrity.

Replication, Streams, Goldengate... I'm just looking for the best Oracle solution.

TOM
We are planning to use streams for schema replication to minimize the downtime for production migration to new host

Do you see any issues with approach?
Are there any limitations we would encounter with streams.

Tom
I will look at oracle docs (the link doesnt work though)
What features of oracle you would use if 11g database needs to migrated to other platform with minimum of downtime

Good Wishes