Requesting help on an issue with post-upgrade downstream capture (11g)

From: Charles Schultz <sacrophyte@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 2 Aug 2012 09:21:34 -0500
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


--
//www.freelists.org/webpage/oracle-l
Requesting help on an issue with post-upgrade downstream capture (11g)

Other related posts:
