8 Replies Latest reply: Feb 5, 2013 5:58 AM by Mackbell-Oracle RSS

    Extract and Replicat Runnng, yet no Syncronization

    misterimran
      Dear All,

      I am trying replication between MySQL and Oracle.

      Version of my MySQL is Server version: 5.5.19 MySQL Enterprise Server - Advanced Edition
      Version of Oracle is : Oracle 11gR2

      Both extract and replicat process on source and target respectively are RUNNING with no errors.

      But there is no synchronization among the databases.

      When i run send extract netfors, stats on source it says:

      Sending STATS request to EXTRACT NETFORS ...
      No active extraction maps.

      Same is the case with replicate.

      The trail files that are being transferred to target are just *1KB* in size

      This is my Golden Gate Configuration:

      -----
      -----
      -----

      # User specific environment and startup programs

      MYSQL_HOME=/usr/src/5.5.19/bin
      export MYSQL_HOME

      LD_LIBRARY_PATH=/root/sandboxes/GG
      export LD_LIBRARY_PATH

      PATH=$PATH:$HOME/bin:/root/sandboxes/GG

      export PATH
      unset USERNAME

      #yum install mysql-connector-odbc

      Edit my.cnf file

      [client]
      user = msandbox
      password = ******
      port = 6629
      socket = /tmp/mysql.sock

      [mysqld]
      server-id=33
      log-bin=mysql-bin
      log-error=msandbox.err
      binlog_format = row
      max_binlog_size = 4096
      socket =      /tmp/mysql.sock

      vi /etc/odbc.ini

      [netfors]
      Driver = /usr/lib/libmyodbc3.so
      Description = Connector/ODBC 3.51 Driver DSN
      Server = 10.168.20.226
      Port = 6629
      User = "replication"
      Password = "********"
      Database = "netfors"
      Option = 3
      Socket = /tmp/mysql.sock

      10. Configure change capture on source
      GGSCI>ADD EXTRACT NETFORS, TRANLOG, BEGIN NOW
      GGSCI>INFO EXTRACT NETFORS
      GGSCI>EDIT PARAMS NETFORS
      EXTRACT NETFORS
      DBOPTIONS HOST 10.168.20.226, CONNECTIONPORT 6629
      SOURCEDB netfors@10.168.20.226:6629, USERID "replication", PASSWORD "******"
      RMTHOST 10.168.20.31, MGRPORT 7809
      RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2
      TRANLOGOPTIONS ALTLOGDEST /root/sandboxes/msb_5_5_19/data/mysql-bin.index
      TABLE netfors.edr;

      11. Define golden gate trail
      GGSCI>ADD RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2, EXTRACT NETFORS, MEGABYTES 5
      GGSCI>INFO RMTTRAIL *
      GGSCI>START EXTRACT NETFORS
      GGSCI>INFO EXTRACT NETFORS
      GGSCI>VIEW REPORT NETFORS

      GGSCI>ALTER RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2, EXTRACT NETFORS, MEGABYTES 5

      Configration on target (Oracle) server

      1)
      GGSCI>edit params ./GLOBAL
      GGSCHEMA ggs_owner
      CHECKPOINTTABLE ggs_owner.checkpoint

      2) Add replicat
      GGSCI>ADD REPLICAT NETFORS, EXTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2, checkpointtable ggs_owner.checkpoint

      3) edit params NETFORS
      REPLICAT NETFORS
      USERID ggs_owner, PASSWORD ******
      HANDLECOLLISIONS
      SOURCEDEFS /u01/app/oracle/oradata/GG/dirdef/mysql.def
      DISCARDFILE /u01/app/oracle/oradata/GG/dirrpt/netfors.dsc, PURGE
      MAP "netfors.edr",TARGET etislbilling.sdr_tblicprawedr;


      GGSCI>START EXTRACT NETFORS
      GGSCI>START REPLICAT NETFORS

      Kindly help on this.

      Regards, Imran
        • 1. Re: Extract and Replicat Runnng, yet no Syncronization
          MikeN
          The message "No active extraction maps" means that there is no "table" statement in your extract prm file matching the activity in the database. Check the prm file, note the case & quotes, make any necessary changes & restart extract. Compare to your defgen (source def's) file. Try from ggsci "dblogin" followed by "list tables sch.*" and verify that it is why you expect. (Note: on the target, it should be ./GLOBALS, I believe; rename & restart ggsci & replicat. But it is unrelated to the problem at hand.)
          • 2. Re: Extract and Replicat Runnng, yet no Syncronization
            misterimran
            Dear Mike,

            Thank you for your reply. I have checked the case and quotes and also the spelling of the table, they are correct. list table command also shows the table I want to replicat.
            Still, I am unable to find the actual problem. Both extract and replicat are running. No errors in report and log files.

            The trail files shifted on the target are just 1.1 kb, I don't think they have data in them. Though I have set TRANLOGOPTIONS ALTLOGDEST to the log-bin parameter in my.cnf file.

            Regards, Imran
            • 3. Re: Extract and Replicat Runnng, yet no Syncronization
              stevencallan
              What does your extract parameter file show? (you posted the repicat, why not the extract?)

              10. Configure change capture on source
              GGSCI>ADD EXTRACT NETFORS, TRANLOG, BEGIN NOW
              • 4. Re: Extract and Replicat Runnng, yet no Syncronization
                misterimran
                It is there:

                10. Configure change capture on source
                GGSCI>ADD EXTRACT NETFORS, TRANLOG, BEGIN NOW
                GGSCI>INFO EXTRACT NETFORS
                GGSCI>EDIT PARAMS NETFORS
                EXTRACT NETFORS
                DBOPTIONS HOST 10.168.20.226, CONNECTIONPORT 6629
                SOURCEDB netfors@10.168.20.226:6629, USERID "replication", PASSWORD "******"
                RMTHOST 10.168.20.31, MGRPORT 7809
                RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2
                TRANLOGOPTIONS ALTLOGDEST /root/sandboxes/msb_5_5_19/data/mysql-bin.index
                TABLE netfors.edr;

                11. Define golden gate trail
                GGSCI>ADD RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2, EXTRACT NETFORS, MEGABYTES 5
                GGSCI>INFO RMTTRAIL *
                GGSCI>START EXTRACT NETFORS
                GGSCI>INFO EXTRACT NETFORS
                GGSCI>VIEW REPORT NETFORS

                Thanks
                • 5. Re: Extract and Replicat Runnng, yet no Syncronization
                  -joe
                  Few small suggestions:

                  1. Check that you're connecting to the right database:
                  GGSCI> dblogin sourcedb <dbname>@<hostname>:<portNo>, userid <userName>, password <password>
                  GGSCI> capture tabledef netfors.edr

                  2. Check if anything is in your trail file on the target (from OGG Home):
                  $ echo "count detail /u01/app/oracle/oradata/GG/dirdat/n2*"| $OGG_HOME/logdump

                  3. Be explicit. Is netfors.edr really lowercase? Then put it in "double quotes".

                  Good luck,
                  -joe
                  • 6. Re: Extract and Replicat Runnng, yet no Syncronization
                    misterimran
                    Thanks Joe,

                    When i run:

                    GGSCI>dblogin sourcedb "", userid "", password ""
                    it returns

                    Successfully logged into database.

                    GGSCI (mysql-multi-node-replica.etisalcom.private) 52>

                    GGSCI (mysql-multi-node-replica.etisalcom.private) 52> capture tabledef netfors.edr
                    Table definitions for netfors.edr:
                    seqno NUMBER NOT NULL
                    createtime DATETIME
                    timeofdaybegin DATETIME NOT NULL

                    Trail files are being shifted to the target but they are very few and less than 1 KB, it means data is not being shifted.

                    I have checked the case, also have added double quotes but no difference.

                    Still the same :( both extract and replicat running but no synchronization.

                    Regards, Imran
                    • 7. Re: Extract and Replicat Runnng, yet no Syncronization
                      misterimran
                      This is the view report of my extract:

                      GGSCI (mysql-multi-node-replica.etisalcom.private) 26> view report netfors


                      ***********************************************************************
                      Oracle GoldenGate Capture for MySQL
                      Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700
                      Linux, x86, 32bit (optimized), MySQL Enterprise on Jul 29 2011 22:59:29

                      Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.


                      Starting at 2013-01-14 16:33:11
                      ***********************************************************************

                      Operating System Version:
                      Linux
                      Version #1 SMP Fri Apr 2 14:58:35 EDT 2010, Release 2.6.18-194.el5
                      Node: mysql-multi-node-replica.etisalcom.private
                      Machine: i686
                      soft limit hard limit
                      Address Space Size : unlimited unlimited
                      Heap Size : unlimited unlimited
                      File Size : unlimited unlimited
                      CPU Time : unlimited unlimited

                      Process id: 21580

                      Description:

                      ***********************************************************************
                      ** Running with the following parameters **
                      ***********************************************************************
                      EXTRACT NETFORS
                      DBOPTIONS HOST 10.168.20.226, CONNECTIONPORT 6629
                      SOURCEDB "*********", USERID "*********", PASSWORD "*********"
                      RMTHOST 10.168.20.31, MGRPORT 7809
                      RMTTRAIL /u01/app/oracle/oradata/GG/dirdat/n2
                      TRANLOGOPTIONS ALTLOGDEST /root/sandboxes/msb_5_5_19/data/mysql-bin.index
                      TABLE "netfors"."edr";

                      CACHEMGR virtual memory values (may have been adjusted)
                      CACHEBUFFERSIZE: 64K
                      CACHESIZE: 2G
                      CACHEBUFFERSIZE (soft max): 4M
                      CACHEPAGEOUTSIZE (normal): 4M
                      PROCESS VM AVAIL FROM OS (min): 2.89G
                      CACHESIZEMAX (strict force to disk): 2.66G

                      Database Version:
                      MySQL
                      Server Version: 5.5.19-enterprise-commercial-advanced-log
                      Client Version: 6.0.0
                      Host Connection: Localhost via UNIX socket
                      Protocol Version: 10

                      2013-01-14 16:33:16 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

                      2013-01-14 16:33:21 INFO OGG-01055 Recovery initialization completed for target file /u01/app/oracle/oradata/GG/dirdat/n2000004, at RBA 1057.

                      2013-01-14 16:33:21 INFO OGG-01478 Output file /u01/app/oracle/oradata/GG/dirdat/n2 is using format RELEASE 10.4/11.1.

                      2013-01-14 16:33:21 INFO OGG-01026 Rolling over remote file /u01/app/oracle/oradata/GG/dirdat/n2000005.

                      2013-01-14 16:33:21 INFO OGG-01053 Recovery completed for target file /u01/app/oracle/oradata/GG/dirdat/n2000005, at RBA 995.

                      2013-01-14 16:33:21 INFO OGG-01057 Recovery completed for all targets.

                      2013-01-14 16:33:21 INFO OGG-00182 VAM API running in single-threaded mode.

                      2013-01-14 16:33:21 INFO OGG-01513 Positioning to Log Number: 130
                      Record Offset: 4176.

                      2013-01-14 16:33:21 INFO OGG-01516 Positioned to LogNum - 130
                      Record Offset - 4176, Jan 14, 2013 3:56:30 PM.

                      ***********************************************************************
                      ** Run Time Messages **
                      ***********************************************************************


                      2013-01-15 03:11:03 INFO OGG-01517 Position of first record processed Log Number: 131
                      Record Offset: 186, Jan 15, 2013 3:11:02 AM.


                      GGSCI (mysql-multi-node-replica.etisalcom.private) 27> stats netfors

                      Sending STATS request to EXTRACT NETFORS ...

                      No active extraction maps.

                      GGSCI (mysql-multi-node-replica.etisalcom.private) 10> lag netfors

                      Sending GETLAG request to EXTRACT NETFORS ...
                      No records yet processed.
                      At EOF, no more records to process.

                      Edited by: misterimran on Jan 15, 2013 3:06 PM
                      • 8. Re: Extract and Replicat Runnng, yet no Syncronization
                        Mackbell-Oracle
                        Silly question, but you did actually perform some DML for this table after adding the EXTRACT with the BEGIN NOW clause didn't you?