3 Replies Latest reply: Mar 6, 2013 6:19 PM by -joe RSS

    Problem in Initial load

    misterimran
      Dear All,

      I am trying to initial load one table from MySQL database to Oracle.
      Version of MySQL is 5.1.61-log on 64-Bit Linux and Database is 11gR2 RAC on 64-Bit Linux

      Golden Gate for MySQL 11.1 is installed on MySQL server and Golden Gate for Oracle 11.1 on Oracle Server manager for both are running.

      This is my extract:

      GGSCI>ADD EXTRACT exp, SOURCEISTABLE
      GGSCI>INFO EXTRACT *, TASKS
      GGSCI>edit params exp

      EXTRACT exp
      SOURCEDB netfors@10.168.20.14:5019, USERID "*******", PASSWORD "**********"
      RMTHOST 10.168.20.17, MGRPORT 7852
      RMTTASK REPLICAT, GROUP imp, format release 11.1
      TABLE netfors.edr;

      This is my Replicat
      GGSCI>ADD REPLICAT imp, SPECIALRUN

      GGSCI>edit params imp

      replicat imp
      ASSUMETARGETDEFS
      USERID **********, PASSWORD *********
      SOURCEDEFS /u01/app/oracle/oradata/GG/dirdef/edr.def
      DISCARDFILE /u01/app/oracle/oradata/GG/dirrpt/netfors.dsc, PURGE
      MAP netfors.edr, TARGET netfors.edr, COLMAP (usedefaults,
      ICPRE_RAWEDRID=seqno,
      ICPRE_SENDER=sender,
      ICPRE_RECEIVER=receiver,
      ICPRE_ICCARRIERID=incoming,
      ICPRE_OGCARRIERID=outgoing,
      ICPRE_QUEUE=queue,
      NE_ELEMENTID=18,
      ICPRE_CREATETIME=createtime,
      ICPRE_TIMEOFDAYBEGIN=timeofdaybegin,
      ICPRE_TIMEOFDAYEND=timeofdayend,
      ICPRE_UPDATETIME=updatetime,
      ICPRE_STATE=state,
      ICPRE_MODE=mode,
      ICPRE_BATCHID=batchid,
      ICPRE_REFERENCE=reference,
      ICPRE_MSG_REFERENCE=msg_reference,
      ICPRE_SEGMENT=segment,
      ICPRE_CSMSID=csmsid,
      ICPRE_GMSC=gmsc,
      ICPRE_IMSI=imsi,
      ICPRE_SMPP_MESSAGE_ID=smpp_message_id,
      ICPRE_RETRIES=retries,
      ICPRE_ELOCATION=elocation,
      ICPRE_ECODE1=ecode1,
      ICPRE_ECODE2=ecode2,
      ICPRE_FLAGS=flags);

      When I run extract exp i get this
      Sending STATS request to EXTRACT EXP ...
      Start of Statistics at 2013-02-12 16:13:11.
      Output to imp:
      Extracting from netfors.edr to netfors.edr:
      *** Total statistics since 2013-02-12 16:13:01 ***
      No database operations have been performed.
      *** Daily statistics since 2013-02-12 16:13:01 ***
      No database operations have been performed.
      *** Hourly statistics since 2013-02-12 16:13:01 ***
      No database operations have been performed.
      *** Latest statistics since 2013-02-12 16:13:01 ***
      No database operations have been performed.
      End of Statistics.

      When I view Report, last lines are:

      2013-02-12 16:13:11 INFO OGG-01021 Command received from GGSCI: STATS.

      Processing table netfors.edr
      ***********************************************************************
      * ** Run Time Statistics ** *
      **********************************************************************
      Report at 2013-02-12 16:13:11 (activity since 2013-02-12 16:13:01)
      Output to imp:
      From Table netfors.edr:
      # inserts: 6298
      # updates: 0
      # deletes: 0
      # discards: 0

      Last lines from extract error file
      2013-02-12 16:13:00 INFO OGG-00975 Oracle GoldenGate Manager for MySQL, mgr.prm: EXTRACT EXP starting.
      2013-02-12 16:13:00 INFO OGG-01017 Oracle GoldenGate Capture for MySQL, exp.prm: Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
      2013-02-12 16:13:00 INFO OGG-00992 Oracle GoldenGate Capture for MySQL, exp.prm: EXTRACT EXP starting.
      2013-02-12 16:13:01 INFO OGG-00993 Oracle GoldenGate Capture for MySQL, exp.prm: EXTRACT EXP started.
      2013-02-12 16:13:11 INFO OGG-01021 Oracle GoldenGate Capture for MySQL, exp.prm: Command received from GGSCI: STATS.
      2013-02-12 16:13:11 INFO OGG-00991 Oracle GoldenGate Capture for MySQL, exp.prm: EXTRACT EXP stopped normally.

      REPLICAT

      When i run stats imp
      ERROR: sending message to REPLICAT IMP (Connection reset by peer).

      When i run view report
      NO ERROR

      when i view the error file last lines are:
      2013-02-12 16:21:03 INFO OGG-00973 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started replicat task process (Port 8274).
      2013-02-12 16:21:03 INFO OGG-01025 Oracle GoldenGate Delivery for Oracle: REPLICAT task started by manager (port 8274).
      2013-02-12 16:21:13 INFO OGG-01229 Oracle GoldenGate Delivery for Oracle: Connected to 10.168.20.40:40821.
      2013-02-12 16:21:13 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, imp.prm: REPLICAT IMP starting.
      2013-02-12 16:21:13 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, imp.prm: REPLICAT IMP started.
      2013-02-12 16:21:18 INFO OGG-00994 Oracle GoldenGate Delivery for Oracle, imp.prm: REPLICAT IMP stopped normally.

      Yet no replication :( Data is not being transferred to Oracle database. What should I check?

      The only thing I am able to figure out up till now is that when i run extract and the extract process is finished, it seems that it is not staring the replicat process when i see the results of extract and replicat like this:

      GGSCI (smsc.etisalcom.net) 4> nfo extract exp, tasks
      EXTRACT EXP Last Started 2013-02-12 16:20 Status STOPPED
      Checkpoint Lag Not Available
      Log Read Checkpoint Table netfors.edr
      *2013-02-12 16:20:59 Record 6306* -- Current time
      Task SOURCEISTABLE

      GGSCI (racnode1) 2> info replicat imp, tasks
      REPLICAT IMP Initialized 2013-02-12 16:11 Status STOPPED
      Checkpoint Lag 00:00:00 (updated 00:17:57 ago) -- while extract was called around 18 minutes back
      Log Read Checkpoint Not Available
      Task SPECIALRUN

      I tried again and even not it show that the replicat was Initialized at 2013-02-12 16:11 same as above
      GGSCI (racnode1) 15> info replicat imp, tasks
      REPLICAT IMP Initialized 2013-02-12 16:11 Status STOPPED
      Checkpoint Lag 00:00:00 (updated 00:39:37 ago)
      Log Read Checkpoint Not Available
      Task SPECIALRUN


      Kindly help in this

      Regards, Imran

      Edited by: misterimran on Feb 12, 2013 6:47 PM
        • 1. Re: Problem in Initial load
          stevencallan
          Maybe I missed this in the output you posted, but did you issue a START EXTRACT <your extract name> command?
          • 2. Re: Problem in Initial load
            misterimran
            Yeah all the information I have provided above is collected after issuing the start extract exp command.
            • 3. Re: Problem in Initial load
              -joe
              Did the target take those inserts? Stats command doesn't work on a process that is stopped and may not simply work on specialrun processes. If exp report shows X inserts and imp (replicat) shows no inserts then it should probably be showing some errors and a discard file entry as well.

              Also note that LOBs and UDTs are not supported with direct path initial loads. A work around or perhaps even a better all around option is to use MAXFILES to write this all out to a sequenced set of trail files and then use a "normal" replicat to read from the trail and apply. This way you also get the ability to restart if, for example, the target runs out of space. Example:

              GGSCI>ADD EXTRACT exp, SOURCEISTABLE

              Parameter file:

              EXTRACT exp
              SOURCEDB netfors@10.168.20.14:5019, USERID "*******", PASSWORD "**********"
              RMTHOST 10.168.20.17, MGRPORT 7852
              EXTFILE ./dirdat/aa, maxfiles 999999, megabytes 1000, purge
              TABLE netfors.edr;

              Good luck,
              -joe