Requesting help on an issue with post-upgrade downstream capture (11g)
Date: Thu, 2 Aug 2012 09:21:34 -0500
Message-ID: <CAPZQniXe6LtFesc6JwROx5HTeMugDMPqG1B7Mb6-dJTCs0dUfA_at_mail.gmail.com>
Good day, oracle-l,
We have had a number of issues related to upgrading an ERP backend database from 11.1.0.7 to 11.2.0.2, and have filed several SRs in relation to those issues. In a recent string of SRs, we were told to follow a certain procedure to get around some rather stubborn logminer issues by disabling and re-enabling logminer PKs. I was told that doing so would not disrupt streams at all. I am not so certain. And frankly, I am sick and tired of dealing with Oracle Support so I turn to the very intelligent, witty, knowledgeable and sometimes sarcastic oracle-l crowd. :)
*Environment:*
Oracle Server Enterprise Edition (11.1.0.7 upgraded to 11.2.0.2)
Solaris 10
In this particular case (DEV), the source database and the downstream
capture database are on the same host. The target (consumer) is on a
different host. In Production the downstream database is isolated (but we
aren't there, yet)
*Symptoms:*
When we initially upgraded 11.1.0.7 to 11.2.0.2, CATPROC was invalid and
gave us a bootstrap error when starting the database in normal mode. Two
SRs later, we hammered out a set of procedures that 1) moved the Logminer
schema to SYSAUX, 2) dropped and recreated ORALCE_OCM, 3) validates CATPROC
and runs ultrp. This works well in a stand-alone environment. I
specifically asked if this would negatively affect Streams and was told it
would not.
Yesterday we upgraded the DEV stream environment. The Downstream database was upgraded first, following the set of procedures (including the hallybaloo with logminer). And then the source was upgraded. Both databases had bootstrap issues again, and so I manually recreated ORACLE_OCM via dbmsocm.sql, reran catproc and validated CATPROC. At this point, both databases were successfully upgraded. But Streams was encountering a fatal error on a primary key. As a result of yet another SR, we disabled primary keys on the downstream capture database. Right now, capture is still failing with the following error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.LOGMNR_GTLO3", line 93
ORA-06512: at line 1
I tried to find some help in Google but no such luck, yet. As I said, sick and tired of Support, but we are trying to push forward regardless. I am hoping that others have hit something similar to this, or perhaps someone has a deep, intimate working knowledge of Streams that they might be able to quickly figure it out.
Thanks in advance,
References (for those that like such things):
- SR 3-5852128851 : HC: startup fails after upgrade
- SR 3-5905163001 : RC:Impact on Oracle streams
- SR 3-5027021423: Capture ABORTS
- SR 3-4213328231: Error while installing ODS
-- Charles Schultz -- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 02 2012 - 09:21:34 CDT