When testing Oracle Golden gate replication for one of our clients I've encountered problem: once or twice per day extract abends whit following error:
ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, EXT_XXX.prm: OCI Error executing single row select (status = 3113-ORA-03113: end-of-file on communication channel
Process ID: 234060
Session ID: 1728 Serial number: 17333), SQL< SELECT u.name, o.name, o.dataobj#, o.type#, (SELECT bitand(t.property, 1) FROM sys.tab$ t WHERE t.obj# = :ora_object_id) FROM sys.obj$ o, sys.user$ u WHERE o.obj# = :ora_object_id AND decode(bitand(o.flags, 128), 128, 'YES', 'NO') = 'NO' AND o.owner# = u.user# AND decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N') = 'N' AND (o.type# in (1, 19, 20, 34) OR EXISTS (SELECT 'x' FROM sys.tab$ t WHERE t.obj# = :ora_object_id AND decode(bitand(t.property,8192),8192,'YES','NO')='NO')) AND o.name not like 'OGGQT$%' and o.name not like 'AQ$%' >.
2013-03-23 10:00:01 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, EXT_BYT.prm: PROCESS ABENDING.
------------ Manager parameter file is:
USERID ggadmin, PASSWORD XXXX
AUTORESTART ER ,RETRIES 60, WAITMINUTES 1, RESETMINUTES 60
PURGEOLDEXTRACTS .\dirdat\*, USECHECKPOINTS, MINKEEPDAYS 2
------- extract parameter files:
userid ggadmin, password XXXX
TRANLOGOPTIONS EXCLUDEUSER ggadmin &
WARNLONGTRANS 1h, CHECKINTERVAL 5m, SKIPEMPTYTRANS
discardfile .\dirrpt/ext_byt.dsc, purge
exttrail .\dirdat\ss mappings
-------- datapump extract:
userid ggadmin, password xxxx
discardfile .\dirrpt/ext_dprm.dsc, purge
rmthost centr, mgrport 7809, compress, compressthreshold 512
rmttrail ./dirdat/rm mappings
Environment consists of two remote servers Window 2008 (source) and Solaris 10 (destination).
Any help would be nice.
Can provide other info if needed.
thanks in advance.
Are you actually using an Integrated Capture? If so, I would not alter the PARALLELISM to 1 unless you're running Oracle Standard Edition. The default for EE is 2, and 1 for SE.
Also, EOFDELAYCSECS might be extreme to set it to 0. Where did that recommendation come from, or was it from tuning/testing?
The other two posters have some great ideas, but if they don't work, I have seen in very rare occasions that this is due to the fetch failing due to the flashback segment not having the data in it.
Try adding FETCHOPTIONS MISSINGROW to see if this is a fetching issue. You'll get a different error message in the report file, and then you'll be able to adjust the database values or something else to change.
Thanks for the replies.
Yes. I'm using integrated capture.
EOFDELAYCSECS 0 was set due to recommendation of a colleague, who said that without it he has recovery problems. He's far more experienced then me, so I took it "as is". Now I really can't find any "metalinks" that recommend to have EOFDELAYCSECS = 0 on regular basis.
I've set PARALLELISM to 2 and EOFDELAYCSECS to 1 will see how it goes.
Unable to find anything new with FETCHOPTIONS MISSINGROW REPORT.
as usual EXTRACT hanged today. I was able to find that it's happen in same time - about 8 a.m. Haven't got any ideas about what can mess with extract at this time.
As for net services log. What connection problems there can be with local extract?