locked
CDC for Oracle not capturing changes RRS feed

  • Question

  • As a test I have set up CDC for Oracle to capture changes from a single table in an Oracle database. I have an Oracle CDC Service configured and an Oracle CDC Service instance up and running. I have no errors reported in the diagnostics log (with the "trace:SOURCE" property set). If I insert, delete or update something in the table the CDC Designer show an active transaction in the "Currently Processing" section under the "Status" tab. When I commit the changes the active transactions goes to 0 but nothing else happens. The "Counters" section is not updated and the changes are not propagated to the SQL Server database. Also, the "Last change timestamp" field is displaying "N/A".

    Restarting the CDC instance or doing an "alter system switch log file" in Oracle does not help. The table has only varchar2 and number columns. 

    As there are no errors reported anywhere I have no real idea as to what is wrong. Where do I start? 

    N.B. this is a small test of the CDC for Oracle functionality to see if we can use this as part of a migration strategy to move a database from Oracle to SQL Server with minimal downtime. 

    Wednesday, September 12, 2012 12:27 PM

Answers

  • Shulei,

    Thanks for the answer. That blog, however, does not address my problem as such. I think that I have solved the issue as I now have changes propagating from Oracle to SQL Server. The actual problem seem to have been some missing permissions for the Oracle user. The other problem is that these requirements are not given in the documentation for "CDC for Oracle" (other than "talk to your Oracle DBA") and that there are no warnings given for the missing permissions.

    Having refreshed my knowledge of Oracles LogMiner here are the role and permissions needed for the Oracle user running LogMiner (the lazy can just grant the "execute_catalog_role" and the "DBA" roles to the user):

    execute_catalog_role
    
    select any transaction
    select any table
    select on sys.v_$database
    select on sys.v_$logmnr_contents
    select on sys.v_$logmnr_dictionary
    select on sys.v_$logmnr_logfile
    select on sys.v_$logmnr_logs
    select on sys.v_$logmnr_parameters
    select on sys.v_$logmnr_session
    select on sys.v_$logmnr_transaction
    select on sys.v_$log
    select on sys.v_$logfile
    select on sys.v_$archived_log
    

    In a production environment it would also be advisable to assign a dedicated tablespace to LogMiner to avoid filling up the SYSTEM (pre 10g) or SYSAUX (10g and later) tablespaces which is otherwise the default for LogMiner data. 

    CREATE TABLESPACE LOGMNR_DATA ...;
    
    EXECUTE SYS.DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_DATA');
    This will actually move all LogMiner tables and their data from whatever tablespace they were in before to the new tablespace.

    Now I just need to figure out an efficient way to deal with changes coming from 600+ tables :/

    • Marked as answer by ThisIsAnAlias Friday, September 14, 2012 11:42 AM
    Friday, September 14, 2012 11:42 AM

All replies

  • Hi,

    Thanks for the post.

    One of the new features introduced in SQL Server 2012 is the ability to use SQL Server CDC for tracking changes on an Oracle table. You can take a look at this blog which introduces how to setup CDC service for tracking changes on your Oracle tables: CDC for Oracle in SQL Server 2012. Also if you have any problems, you can get help from there.


    Best Regards,
    Ray Chen

    Friday, September 14, 2012 9:02 AM
  • Shulei,

    Thanks for the answer. That blog, however, does not address my problem as such. I think that I have solved the issue as I now have changes propagating from Oracle to SQL Server. The actual problem seem to have been some missing permissions for the Oracle user. The other problem is that these requirements are not given in the documentation for "CDC for Oracle" (other than "talk to your Oracle DBA") and that there are no warnings given for the missing permissions.

    Having refreshed my knowledge of Oracles LogMiner here are the role and permissions needed for the Oracle user running LogMiner (the lazy can just grant the "execute_catalog_role" and the "DBA" roles to the user):

    execute_catalog_role
    
    select any transaction
    select any table
    select on sys.v_$database
    select on sys.v_$logmnr_contents
    select on sys.v_$logmnr_dictionary
    select on sys.v_$logmnr_logfile
    select on sys.v_$logmnr_logs
    select on sys.v_$logmnr_parameters
    select on sys.v_$logmnr_session
    select on sys.v_$logmnr_transaction
    select on sys.v_$log
    select on sys.v_$logfile
    select on sys.v_$archived_log
    

    In a production environment it would also be advisable to assign a dedicated tablespace to LogMiner to avoid filling up the SYSTEM (pre 10g) or SYSAUX (10g and later) tablespaces which is otherwise the default for LogMiner data. 

    CREATE TABLESPACE LOGMNR_DATA ...;
    
    EXECUTE SYS.DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_DATA');
    This will actually move all LogMiner tables and their data from whatever tablespace they were in before to the new tablespace.

    Now I just need to figure out an efficient way to deal with changes coming from 600+ tables :/

    • Marked as answer by ThisIsAnAlias Friday, September 14, 2012 11:42 AM
    Friday, September 14, 2012 11:42 AM