13 Replies Latest reply: Sep 9, 2013 11:54 PM by stevencallan RSS

    OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process

    JuanAraoz

      Hello Everyone

       

      I want to replicate tables between MS SQLServer 2008 and Oracle 10g

      I have Windows XP / MS SQLServer 2008 and Oracle Database 10g R 10.2.0.1.0 running  on Red Hat Linux

       

      I followed the steps from the white paper (http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html)

      and Installed Oracle Golden Gate Version 11.2.1.0.1 (for Linux) and the  Version 11.2.1.0.1 32bits (for Windows XP)

       

      I followed all steps of the white paper without problem and I could do the initial load in

      the Oracle Database from SQL Server sucessfully.

       

      But when I tried to set and start the Extract process from SQL SERVER

      a error was generated in the file c:\gg\dirrpt\MSEXT.rpt and the process is ABENDING

       

      GGSCI >  info all

       

      Program     Status      Group       Lag at Chkpt  Time Since Chkpt

       

      MANAGER     RUNNING

      EXTRACT     STOPPED     MSEXT       00:00:00      03:22:29

       

      2013-08-25 11:59:41  ERROR   OGG-00868  Executing statement for select operation Database error 213 ([Microsoft][SQL Server Native Client 10.0][SQL Server]Column name or number of supplied values does not match table definition.

      [Microsoft][SQL Server Native Client 10.0][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

      [Microsoft][SQL Server Native Client 10.0][SQL Server]Column name or number of supplied values does not match table definition.

      [Microsoft][SQL Server Native Client 10.0][SQL Server]DBCC execution completed. If DBCC printed error messag).

       

      2013-08-25 11:59:41  ERROR   OGG-01668  PROCESS ABENDING.

       

      If somebody has a clue or faced something similar please let me how could solve it.

       

      Thanks

      Juan

        • 1. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
          Mackbell-Oracle

          Can you include the full report from the Extract?  Also, after you ADD TRANDATA for the table in question, did you then at some point make sturcture changes to the table?

          • 2. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
            JuanAraoz

            Hello

             

            Below is the report MSEXT.rpt

            and did not change the structure of the table in any moment

             

            Thanks

            Juan

             


            ***********************************************************************
                           Oracle GoldenGate Capture for SQL Server
                 Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
               Windows (optimized), Microsoft SQL Server on Jul 25 2012 03:49:54

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


                                Starting at 2013-08-25 11:59:41
            ***********************************************************************

            Operating System Version:
            Microsoft Windows XP Professional, on x86
            Version 5.1 (Build 2600: Service Pack 2)

            Process id: 3556

            Description:

            ***********************************************************************
            **            Running with the following parameters                  **
            ***********************************************************************

            2013-08-25 11:59:41  INFO    OGG-03035  Operating system character set identified as windows-1252. Locale: en_US, LC_ALL:.
            EXTRACT MSEXT

            SOURCEDB HR


            2013-08-25 11:59:41  INFO    OGG-03036  Database character set identified as windows-1251. Locale: ru_RU.

            2013-08-25 11:59:41  INFO    OGG-03037  Session character set identified as windows-1252.
            TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

            RMTHOST OCM, MGRPORT 7809

            RMTTRAIL /u01/app/oracle/gg/dirdat/ms

            TABLE HRSCHEMA.EMP;


            2013-08-25 11:59:41  INFO    OGG-01815  Virtual Memory Facilities for: COM
                anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
                file alloc: MapViewOfFile  file free: UnmapViewOfFile
                target directories:
                C:\gg\dirtmp.

            CACHEMGR virtual memory values (may have been adjusted)
            CACHESIZE:                                1G
            CACHEPAGEOUTSIZE (normal):                4M
            PROCESS VM AVAIL FROM OS (min):        1.60G
            CACHESIZEMAX (strict force to disk):   1.44G

            Database Version:
            Microsoft SQL Server
            Version 10.00.1442
            ODBC Version 03.52.0000

            Driver Information:
            sqlncli10.dll
            Version 10.00.1442
            ODBC Version 03.52

            Source Context :
              SourceModule            : [ggvam.param]
              SourceID                : [../gglib/ggvam/cvamparams.cpp]
              SourceFunction          : [com_goldengate_vam::validateIfSqlServer]
              SourceLine              : [1955]

            2013-08-25 11:59:41  ERROR   OGG-00868  Executing statement for select operation Database error 213 ([Microsoft][SQL Server Native Client 10.0][SQL Server]Column name or number of supplied values does not match table definition.

            [Microsoft][SQL Server Native Client 10.0][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

            [Microsoft][SQL Server Native Client 10.0][SQL Server]Column name or number of supplied values does not match table definition.

            [Microsoft][SQL Server Native Client 10.0][SQL Server]DBCC execution completed. If DBCC printed error messag).

            2013-08-25 11:59:41  ERROR   OGG-01668  PROCESS ABENDING.

            • 3. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
              Mackbell-Oracle

              Ok, still going to need some more info from you.  Can you confirm that the DSN that you are using called HR is a System DSN and not a User DSN, and that the default database selected in the DSN is the database that you want to extract data from?

               

              Also, walk me through the steps that you did to enable TRANDATA and create the Extract.  All of the GGSI commands that you used.  Finally, that article is missing an important step, and that is that the source database needs to be in FULl recovery and have a FULL backup taken.  So if you just created the database from scratch you should do these steps.

               

              1) Create database and make sure it's in FULL recovery mode

              2) create table

              3) take a full database backup

              4) add trandata to the table(s)

              5) add extract msext, tranlog, begin now

              6) add exttrail ....  and the rest of the components.  You are actually using a remote trail in your Extract which is fine for testing, but you wouldn't want to use that in production, you'd want an Exttrail instead.  Like this: add exttrail ./dirdat/et, extract msext  and then you would create a pump that read that trail and wrote to a remote trail on the target server.

              7) Start extract

              • 4. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                JuanAraoz

                Hello mb_ogg,

                 

                Yes, I had created a Data Source  HR Driver= SQL Server (second Tab System DSN) and I also had tried with
                the driver SQL Server Native Client 10.0 but I faced the same error.

                 

                The database by default is EMP

                 

                Below is my configuration

                 

                Microsoft SQL Server ODBC Driver Version 03.85.1117

                Data Source Name: HR
                Data Source Description:
                Server: JUAN-XP
                Database: EMP
                Language: (Default)
                Translate Character Data: Yes
                Log Long Running Queries: No
                Log Driver Statistics: No
                Use Integrated Security: Yes
                Use Regional Settings: No
                Prepared Statements Option: Drop temporary procedures on disconnect
                Use Failover Server: No
                Use ANSI Quoted Identifiers: Yes
                Use ANSI Null, Paddings and Warnings: Yes
                Data Encryption: No

                 

                I also clicked on the button "Test Data Source" with the below messages:

                 

                Microsoft SQL Server ODBC Driver Version 03.85.1117

                Running connectivity tests...

                Attempting connection
                Connection established
                Verifying option settings
                Disconnecting from server

                TESTS COMPLETED SUCCESSFULLY!

                 

                Then I went to Database Properties (Right Click) /Oprtion  and Recovery model field has Full value

                 

                I also made a Backup (Backup Type = Full)

                The Backups was made to Disk:  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\EMP.bak


                --------------------------------------------------------------------------------------------------------
                Today I repeated the same steps and I got the same error (I Copied at the end):

                I also have the follow question:

                Is necessary define the location of the backup of MS SQL Server to say to GG where it can get it?

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

                GSCI (juan-xp) 1> DBLOGIN SOURCEDB HR

                2013-08-27 21:16:32  INFO    OGG-03036  Database character set identified as win
                dows-1252. Locale: en_US.

                2013-08-27 21:16:32  INFO    OGG-03037  Session character set identified as wind
                ows-1252.
                Successfully logged into database.


                GGSCI (juan-xp) 3> ADD TRANDATA HRSCHEMA.EMP

                Logging of supplemental log data is enabled for table hrschema.emp

                GGSCI (juan-xp) 4> EDIT PARAMS DEFGEN


                GGSCI (juan-xp) 5> exit


                C:\gg>defgen paramfile c:\gg\dirprm\defgen.prm

                ***********************************************************************
                      Oracle GoldenGate Table Definition Generator for SQL Server
                     Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
                   Windows (optimized), Microsoft SQL Server on Jul 25 2012 03:37:58

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


                                    Starting at 2013-08-27 21:18:23
                ***********************************************************************

                Operating System Version:
                Microsoft Windows XP Professional, on x86
                Version 5.1 (Build 2600: Service Pack 2)

                Process id: 3236

                ***********************************************************************
                **            Running with the following parameters                  **
                ***********************************************************************
                defsfile c:\gg\dirdef\emp.def

                Source Context :
                  SourceModule            : [defgen.main]
                  SourceID                : [defgen/defgen.c]
                  SourceFunction          : [create_defgen_file]
                  SourceLine              : [808]

                2013-08-27 21:18:23  ERROR   OGG-00037  DEFSFILE file c:\gg\dirdef\emp.def alrea
                dy exists.

                2013-08-27 21:18:23  ERROR   OGG-01668  PROCESS ABENDING.

                C:\gg>defgen paramfile c:\gg\dirprm\defgen.prm

                ***********************************************************************
                      Oracle GoldenGate Table Definition Generator for SQL Server
                     Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
                   Windows (optimized), Microsoft SQL Server on Jul 25 2012 03:37:58

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


                                    Starting at 2013-08-27 21:19:03
                ***********************************************************************

                Operating System Version:
                Microsoft Windows XP Professional, on x86
                Version 5.1 (Build 2600: Service Pack 2)

                Process id: 3312

                ***********************************************************************
                **            Running with the following parameters                  **
                ***********************************************************************
                defsfile c:\gg\dirdef\emp.def
                sourcedb hr

                2013-08-27 21:19:05  INFO    OGG-03036  Database character set identified as win
                dows-1252. Locale: en_US.

                2013-08-27 21:19:05  INFO    OGG-03037  Session character set identified as wind
                ows-1252.
                table hrschema.emp;
                Retrieving definition for hrschema.emp

                 

                Definitions generated for 1 table in c:\gg\dirdef\emp.def


                C:\gg>ggsci

                Oracle GoldenGate Command Interpreter for SQL Server
                Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
                Windows (optimized), Microsoft SQL Server on Jul 25 2012 02:57:42

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

                 

                GGSCI (juan-xp) 1> start manager
                MGR is already running.


                GGSCI (juan-xp) 2> info all

                Program     Status      Group       Lag at Chkpt  Time Since Chkpt

                MANAGER     RUNNING


                GGSCI (juan-xp) 3> ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW
                EXTRACT added.


                GGSCI (juan-xp) 4> ADD RMTTRAIL /u01/app/oracle/gg/dirdat/ms, EXTRACT MSEXT
                RMTTRAIL added.


                GGSCI (juan-xp) 5> EDIT MSEXT
                ERROR: Invalid command.

                GGSCI (juan-xp) 6> edit params msext


                GGSCI (juan-xp) 7> START EXTRACT MSEXT

                Sending START request to MANAGER ('GGSMGR') ...
                EXTRACT MSEXT starting


                GGSCI (juan-xp) 8> show all

                Parameter settings:

                SET SUBDIRS    ON
                SET DEBUG      OFF

                Current directory: C:\gg

                Using subdirectories for all process files

                Editor:  notepad

                Reports (.rpt)                 C:\gg\dirrpt
                Parameters (.prm)              C:\gg\dirprm
                Replicat Checkpoints (.cpr)    C:\gg\dirchk
                Extract Checkpoints (.cpe)     C:\gg\dirchk
                Process Status (.pcs)          C:\gg\dirpcs
                SQL Scripts (.sql)             C:\gg\dirsql
                Database Definitions (.def)    C:\gg\dirdef


                GGSCI (juan-xp) 9> info all

                Program     Status      Group       Lag at Chkpt  Time Since Chkpt

                MANAGER     RUNNING
                EXTRACT     STOPPED     MSEXT       00:00:00      00:02:34


                GGSCI (juan-xp) 10> EDIT MSEXT

                -----------------------------------------------------------------------------------------------------
                Report MSEXT.rpt with same error
                -----------------------------------------------------------------------------------------------------
                ***********************************************************************
                               Oracle GoldenGate Capture for SQL Server
                     Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
                   Windows (optimized), Microsoft SQL Server on Jul 25 2012 03:49:54

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


                                    Starting at 2013-08-27 21:30:26
                ***********************************************************************

                Operating System Version:
                Microsoft Windows XP Professional, on x86
                Version 5.1 (Build 2600: Service Pack 2)

                Process id: 2452

                Description:

                ***********************************************************************
                **            Running with the following parameters                  **
                ***********************************************************************

                2013-08-27 21:30:26  INFO    OGG-03035  Operating system character set identified as windows-1252. Locale: en_US, LC_ALL:.
                EXTRACT MSEXT

                SOURCEDB HR


                2013-08-27 21:30:28  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

                2013-08-27 21:30:28  INFO    OGG-03037  Session character set identified as windows-1252.
                TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

                RMTHOST OCM, MGRPORT 7809

                RMTTRAIL /u01/app/oracle/gg/dirdat/ms

                TABLE HRSCHEMA.EMP;


                2013-08-27 21:30:28  INFO    OGG-01815  Virtual Memory Facilities for: COM
                    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
                    file alloc: MapViewOfFile  file free: UnmapViewOfFile
                    target directories:
                    C:\gg\dirtmp.

                CACHEMGR virtual memory values (may have been adjusted)
                CACHESIZE:                                1G
                CACHEPAGEOUTSIZE (normal):                4M
                PROCESS VM AVAIL FROM OS (min):        1.57G
                CACHESIZEMAX (strict force to disk):   1.41G

                Database Version:
                Microsoft SQL Server
                Version 10.00.1442
                ODBC Version 03.52.0000

                Driver Information:
                SQLSRV32.DLL
                Version 03.85.1117
                ODBC Version 03.52

                Source Context :
                  SourceModule            : [ggvam.param]
                  SourceID                : [../gglib/ggvam/cvamparams.cpp]
                  SourceFunction          : [com_goldengate_vam::validateIfSqlServer]
                  SourceLine              : [1955]

                2013-08-27 21:30:28  ERROR   OGG-00868  Executing statement for select operation Database error 213 ([Microsoft][ODBC SQL Server Driver][SQL Server]Column name or number of supplied values does not match table definition.
                [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
                [Microsoft][ODBC SQL Server Driver][SQL Server]Column name or number of supplied values does not match table definition.
                [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system admi).

                2013-08-27 21:30:28  ERROR   OGG-01668  PROCESS ABENDING.
                --------------------------------------------------------------------------------------------------------

                Thanks

                Juan

                • 5. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                  Mackbell-Oracle

                  I've tried to reproduce this issue and I am not able to.  I even set the database collation for Russian.  Can you run SQL Profiler and see if you can capture a bad query that is being executed when Extract abends?

                  • 6. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                    JuanAraoz

                    I run the SQL Profiler and then

                     

                    ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW

                     

                    EXTRACT added.

                     

                    ADD RMTTRAIL /u01/app/oracle/gg/dirdat/ms, EXTRACT MSEXT

                     

                    RMTTRAIL added.

                     

                    START EXTRACT MSEXT

                     

                    But I did not find anything in trace file.

                     

                    Then inserted a new row in the table emp

                     

                    But the Extract program always has a status of Stopped

                     

                     

                    • 7. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                      Mackbell-Oracle

                      Juan, try a local trail instead, just to remove the possibility that there is a problem with connecting to the remote host.

                       

                      Change the Extract parameter file to this:

                       

                      EXTRACT MSEXT

                      SOURCEDB HR

                      TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

                      EXTTRAIL ./dirdat/ms

                      TABLE HRSCHEMA.EMP;

                       

                      and drop and recreate the Extract with this:

                      DELETE RMTTRAIL u01/app/oracle/gg/dirdat/ms

                      DELETE EXTRACT MSEXT

                      ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW

                      ADD EXTTRAIL ./dirdat/ms, EXTRACT MSEXT

                      START EXTRACT MSEXT

                       

                      I just want to see if it can get any further, so if it fails again, please paste the entire report output for review.

                      Thanks.

                       

                      • 8. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                        jmbien

                        Hello Juan and mb_ogg,

                         

                        Juan, can you check and confirm that's your account used to connect to database is member of the SQL Server fixed server role sysadmin.

                        (refer to "SQL Server Installation and Setup Guide E27293-01 chapter "System requirements and preinstallation instructions Table 1)

                        This role is mandatory to be able to read MS SQL logs file.

                         

                        Best regards

                        Jmbien

                        • 9. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                          stevencallan

                          Isn't this the error?


                          Column name or number of supplied values does not match table definition.

                          • 10. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                            JuanAraoz

                            Yes the error is:

                            [Microsoft][SQL Server Native Client 10.0][SQL Server]Column name or number of supplied values does not match table definition.

                            • 11. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                              jmbien

                              Juan,

                               

                              As mentioned in your original post, the initial load works fine.

                               

                              An initial load reads data in the table. (SOURCEISTABLE)

                              An extract reads transactions in CDC tables and transactions logs ( option TRANLOG in the ADD EXTRACT command)

                               

                              In your case, if you didn't modify the source table structure between the initial load and the start extract, the difference is on the accessed tables.

                               

                              Only a "SQL Server fixed server role sysadmin" can read CDC tables and transaction logs. Can you confirm that's your login account has this role ?

                               

                              Did you use a Enterprise Edition of MS SQL Server ?

                              If you can't answer Yes to one of theses two questions, you can't use OGG to replicate from MS SQL server.

                              • 12. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                                JuanAraoz


                                Yes I have Enterprise Edition and I have SQL Server fixed server role sysadmin , first I was  using Windows Authentication that had the role sysadmin

                                then  I created a new user with the sysadmin role and included the new user and password in the parameter file, but still have the same error in

                                the report file of the extract.

                                • 13. Re: OGG-00868 SQL Server 2008 has problem with ODBC in  the EXTRACT process
                                  stevencallan

                                  What are the table definitions/mappings between the source and target? If you're trying to pour 5 pounds of sand into 3-pound bag, or, not enough values supplied during the insert. That is what the error message is about. You are also going from MSSQL to Oracle. Where is your defgen step for the target?

                                   

                                  When replicating from any type of Windows or UNIX-based database system to any

                                  other Windows or UNIX-based system, the mapping and conversion can be performed

                                  by Extract, a data-pump Extract, or Replicat, but is usually performed by Replicat on

                                  the target system. However, if Oracle GoldenGate must convert between different

                                  character sets, the mapping and conversion must be performed by Replicat on the

                                  target. See “Understanding the effect of character sets on definitions files”.

                                   

                                  To perform column mapping and conversion on the source, use a target-definitions file

                                  that was generated on the target to supply target definitions to the primary Extract or

                                  a data-pump Extract, depending on which process does the conversion.

                                   

                                  Try the tutorial first and get that working. GoldenGate does not appreciate creativity or variations of required steps/procedures.

                                   

                                  http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/11g/GGS_Sect_Config_WIN_MSS_2008_to_WinUX_ORA.p…