This discussion is archived
13 Replies Latest reply: Sep 9, 2013 9:54 PM by stevencallan RSS

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

JuanAraoz Newbie
Currently Being Moderated

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
    mb_ogg Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    mb_ogg Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    mb_ogg Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    mb_ogg Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated


    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 Expert
    Currently Being Moderated

    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…

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points