7 Replies Latest reply: Apr 1, 2013 1:41 AM by 998517 RSS

    OGG extract abends

    998517
      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:
      PORT 0000
      USERID ggadmin, PASSWORD XXXX
      AUTOSTART ER
      AUTORESTART ER ,RETRIES 60, WAITMINUTES 1, RESETMINUTES 60
      PURGEOLDEXTRACTS .\dirdat\*, USECHECKPOINTS, MINKEEPDAYS 2
      LAGREPORTMINUTES 5
      LAGCRITICALMINUTES 10
      -------
      extract parameter files:
      extract ext_XXX
      SETENV (ORACLE_HOME="c:\oracle\ora11")
      SETENV (ORACLE_SID="orcl")
      userid ggadmin, password XXXX
      TRANLOGOPTIONS EXCLUDEUSER ggadmin &
                INTEGRATEDPARAMS(parallelism 1)
      WARNLONGTRANS 1h, CHECKINTERVAL 5m, SKIPEMPTYTRANS
      eofdelaycsecs 0
      discardfile .\dirrpt/ext_byt.dsc, purge
      exttrail .\dirdat\ss
      mappings
      --------
      datapump extract:
      extract ext_dpsa
      userid ggadmin, password xxxx
      discardfile .\dirrpt/ext_dprm.dsc, purge
      passthru
      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.

      Edited by: 995514 on 25.03.2013 5:20
        • 1. Re: OGG extract abends
          stevencallan
          Looks like the connection was lost - ORA-03113 error happens in SQL*Plus sessions the same way. Look at your net services setup for troubleshooting.
          • 2. Re: OGG extract abends
            Mackbell-Oracle
            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?
            • 3. Re: OGG extract abends
              Nick.W-Oracle
              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.
              • 4. Re: OGG extract abends
                user12684265
                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.

                Edited by: user12684265 on 26.03.2013 2:58
                • 5. Re: OGG extract abends
                  998517
                  Unfortunately both didn't solve the problem.
                  will try FETCHOPTIONS thing now.
                  • 6. Re: OGG extract abends
                    998517
                    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?
                    • 7. Re: OGG extract abends
                      998517
                      I found that during this hangs server get large "Streams:flow control events". Is there anything useful I can get out of this?