SQL Server 2012 shipped with a new feature named SQL Server 2012 Change Data Capture for Oracle By Attunity (shortened to SQL 2012 Oracle CDC in this blog). This feature allows using Change Data Capture in a SQL Server database, with an Oracle database as the source of data. One notable advantage of this feature is that very low data latency can be achieved; another is that knowledge of Oracle is not required for ETL development. Overall, this feature can significantly improve the quality of SQL Server data warehousing projects that source some or all of their data from Oracle. The feature was developed by Attunity and is shipping as a part of SQL Server 2012.
This blog is a review of SQL 2012 Oracle CDC implementation on a data warehousing project at one of Microsoft's customers. I will concentrate here on implementation details that are not very obvious, are not described in existing documentation, and may take some time to figure out on your own. I will also mention a few design patterns we used that may be helpful to someone implementing this feature. This blog assumes that the reader is familiar with the basics of the "native" SQL Server CDC, as well as with the basics of SQL 2012 Oracle CDC. If you are new to this topic, then it is highly recommended to first review the MSDN SQL Server CDC documentation, and then a number of other sources of information on SQL 2012 Oracle CDC that are currently available:
The implementation described in this blog is based on the requirements of a specific data warehousing project. Not everything mentioned here will necessarily be applicable or even optimal on other such projects. This blog is not a comprehensive step-by-step guide, and what I present here is not intended as the "official" implementation recommendations for SQL 2012 Oracle CDC; however, my hope is that most of this will be useful to others implementing this new feature of SQL Server 2012, particularly when dealing with advanced design and troubleshooting scenarios.
As of this writing, the most current version of SQL 2012 Oracle CDC is available as a part of SQL 2012 SP1 CU7. These bits contain several important fixes. Note that unlike other SQL Server components, SQL 2012 Oracle CDC ships as two standalone MSI files; therefore you only need these MSIs from a SQL Server SP or CU to fully install the product. For example, for this update, this means downloading the files named 2012_SP1_AOrcleSer_CU7_2894115_11_0_3393_x64 and 2012_SP1_AOrcleDes_CU7_2894115_11_0_3393_x64. Over time, updated versions of SQL 2012 Oracle CDC may be released in a similar fashion as part of regular SQL Server 2012 updates.
SQL 2012 Oracle CDC can be installed on a Windows server running on commodity hardware. This can be, but does not have to be, the ETL/SSIS server, or the data warehouse server. The server where SQL 2012 Oracle CDC is installed will run one or more instances of the CDC service (xdbcdcsvc.exe). The CDC service connects to an Oracle database, captures change data, and saves that data in a SQL Server CDC enabled database. That database, called the change data database in this blog, can be placed on any SQL Server 2012 instance, not necessarily on the same server where SQL 2012 Oracle CDC is installed. In our project, both the CDC services and the SQL Server instance hosting change data databases were installed on the same midrange server with 8 cores and 192 GB of RAM. I have not found the CDC service to be a big consumer of either CPU or RAM, so in all likelihood, a less powerful server would suffice as well.
Once the current product version is installed, two MMC consoles can be found under All Programs, Change Data Capture for Oracle by Attunity. One is the CDC Service console, typically used once at setup time to configure the CDC service(s). The other is the CDC Designer console, used to create and monitor CDC instances.
CDC Service Configuration
Setting up the CDC service is the first step to be done after installing the product. Documentation describes that process step by step, so here I will only point out a few additional details.
Preparing Oracle Database for Data Capture
Before a CDC instance can be configured, the source Oracle database must be prepared for data capture. As described in greater detail in product documentation, you have to enable and configure Oracle Log Miner, create the log mining account that will be used by the CDC service to connect and capture changes from Oracle redo logs, and grant a number of privileges to that account. These steps require holding sufficient privileges in the Oracle database; therefore, you may need to work with an Oracle DBA who can run corresponding scripts.
To enable Log Miner, the source Oracle database must be in ARCHIVELOG mode, with supplemental logging enabled at the database level. This is done by executing the following:
ALTER DATABASE ARCHIVELOG;ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
We used the following script to create the Oracle log mining account (named DW_ETL here) and grant it required privileges:
CREATE USER DW_ETL IDENTIFIED BY &Password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;GRANT CREATE SESSION TO DW_ETL;GRANT SELECT ANY TRANSACTION TO DW_ETL;GRANT EXECUTE ON DBMS_LOGMNR TO DW_ETL;GRANT SELECT ON V_$LOGMNR_CONTENTS TO DW_ETL;GRANT SELECT ON V_$ARCHIVED_LOG TO DW_ETL;GRANT SELECT ON V_$LOGMNR_LOGS TO DW_ETL;GRANT SELECT ON V_$LOG TO DW_ETL;GRANT SELECT ON V_$LOGFILE TO DW_ETL;GRANT SELECT ON V_$DATABASE TO DW_ETL;GRANT SELECT ON V_$INSTANCE TO DW_ETL;GRANT SELECT ON V_$THREAD TO DW_ETL;GRANT SELECT ON V_$PARAMETER TO DW_ETL;GRANT SELECT ON DBA_REGISTRY TO DW_ETL;GRANT SELECT ON ALL_INDEXES TO DW_ETL;GRANT SELECT ON ALL_OBJECTS TO DW_ETL;GRANT SELECT ON DBA_OBJECTS TO DW_ETL;GRANT SELECT ON ALL_TABLES TO DW_ETL;
Depending on Oracle database configuration, some of these privileges may have to be granted on views starting with V$ rather than V_$, e.g. V$THREAD instead of V_$THREAD. The documentation mentions that, however there is an apparent typo where instead of V_$ it has V $.
Even though the log mining account does not query the source Oracle tables directly, it still requires the SELECT privilege on each source table, so a separate script granting that has to be executed. If SELECT privileges are missing, data capture will fail.
In addition to enabling supplemental logging at the database level, it has to be also enabled for each source Oracle table. The CDC Designer console will generate a script to do that (more on this below).
CDC Instance Design Workflow
Once the Oracle database is prepared for data capture, the CDC Designer console can be used to create a CDC instance. A CDC instance refers to a combination of a source Oracle database, a CDC service, and a SQL Server CDC enabled database that stores change data. Note that the same CDC service may be associated with more than one CDC instance (in our case we could have used a single CDC service to capture changes from both of our source Oracle databases). It is also possible to create multiple CDC instances for the same source Oracle database, and include a different set of source tables under each CDC instance.
To design our CDC instances, we used a specific workflow that resulted in a set of deployment scripts. We then used these scripts to quickly deploy CDC instances to various environments, instead of having to use the CDC Designer console interactively every time a new CDC instance needed to be created. The workflow steps follow. For each step, I will mention some details that are relevant in the context of the step.
Start the New CDC Instance wizard and follow through the steps. The wizard does the following:
There are several details to be added here:
If you have not added any source tables while running the wizard, do that now. For a given CDC instance, use the Properties command under Actions, click on the Tables tab in the Properties dialog, and then click Add Table. Note that the Table Selection dialog may appear a bit slow and unresponsive with large Oracle databases. When searching for a table, you can use % as a wildcard in the Table name pattern box. If you have many tables to add, you don’t have to add them all at the same time. You can add tables in batches – each batch will be saved with the CDC instance.
While in the Properties dialog, click on the Advanced tab and add any advanced options you might need (available options are described in product documentation). In our implementation, we added two options:
Once all tables and advanced options have been added to the CDC instance, click on CDC Instance Deployment Script under Actions. This generates a SQL Server script that will create the change data database and configure it for data capture. We used this script as a starting point to create our own CDC instance deployment scripts, described below. A few notes on the generated script:
Split the generated deployment script into three parts:
The first script creates the change data database, sets its options as required, and enables the database for CDC. Here we adjusted database file sizes and filegroup configuration, used an appropriate collation, set database recovery model as required, and changed the database owner to a specific server principal. Other parts of the generated deployment script remained unchanged.
The second script creates mirror tables and capture instances in the change data database. This is a good point to take a step back and describe some relevant details of SQL 2012 Oracle CDC architecture.
Mirror tables are tables in the SQL Server change data database that correspond one-to-one to the source tables in the Oracle database. Each mirror table has exactly the same column names as the Oracle table, these columns are in the same order as in the Oracle table, and have data types that match the Oracle data types as closely as possible.
Mirror tables will remain empty at all times. In fact, to ensure that, the generated deployment script denies all DML permissions on each mirror table. Conceptually, this makes sense – the change data database will contain only changes, not the entire contents of source tables in the Oracle database. Mirror tables are used to create capture instances in the change data database by calling the sys.sp_cdc_enable_table stored procedure, but actual data changes occur in the corresponding Oracle tables, not in SQL Server. This is really the “secret sauce” of SQL 2012 Oracle CDC: The CDC service captures changes in the Oracle database and writes them to the SQL Server change data database, however it writes them not to the mirror tables, but to the corresponding system tables in the cdc schema (the ones with the _CT suffix), which are created when each mirror table is enabled for CDC. With this design, any consumer of change data uses the well-established API of SQL Server CDC (i.e. the fn_cdc_get_all_changes and fn_cdc_get_net_changes functions), even though the actual changes occur in an Oracle database. In other words, consumers of change data can be completely unaware that changes actually originate in an Oracle database – working with the change data database is no different from working with the regular SQL Server CDC enabled database.
One reason for having to keep all columns of the Oracle table in the mirror table is to enable detection of schema changes in Oracle. For every captured column in a mirror table, the column_id value in the cdc.captured_columns table must match the COLUMN_ID value of the corresponding column in the corresponding Oracle table (as it appears in the SYS.ALL_TAB_COLUMNS Oracle view). If the mirror table had fewer columns than the corresponding Oracle table, then its column_id values would not match the Oracle COLUMN_ID values, which would be interpreted as a schema change in the Oracle database. As noted earlier, this either causes the capture instance for the table to be dropped, or stops data capture altogether.
Now, just because the mirror tables must contain all columns of the corresponding Oracle table does not mean that the capture instance must actually capture changes in all of these columns. If you are only interested in a subset of columns in a table, you can modify the call to the sys.sp_cdc_enable_table stored procedure and change the @captured_column_list parameter to only include the columns of interest. This is what we did to avoid the overhead of capturing many unneeded changes.
Finally, we made another rather important change to the mirror table definitions. When a capture instance is created for a table in a CDC enabled database, the sys.sp_cdc_enable_table stored procedure will enable the net changes mode for the capture instance (i.e. will create the fn_cdc_get_net_changes function) only if the table has a primary key/unique constraint or a unique index. The net changes mode can simplify and improve performance of the ETL code that consumes changes in the change data database and applies them to the destination database. In the all changes mode, which is the only option when the source table does not have a unique constraint or unique index, each change must be applied to the destination database individually, one row at a time, which is both relatively complex and inefficient. In the net changes mode however, all changes processed in an incremental ETL run can be applied as a set (i.e. using the MERGE statement) – this is both faster and simpler. Now, if the source Oracle table has a primary key constraint defined, then the corresponding mirror table in the generated deployment script will have that primary key constraint as well, and the net changes mode will be enabled. However, in our source system, most tables did not have a declared primary key, but did have one or more unique indexes, typically on a sequence based not nullable numeric column. Therefore, in the corresponding mirror tables, we made the matching columns not nullable, and added primary key constraints on these columns. This allowed us to use the net changes mode for most of the source tables and greatly simplified our ETL code.
The third script drops the CDC capture job, which is created automatically when a database is enabled for CDC but is not used with SQL 2012 Oracle CDC. We added a call to the sys.sp_cdc_change_job to increase the retention period for change data from the default of three days. Then, we modified the UPDATE statement that writes CDC instance configuration info to the cdc.xdbcdc_config table in the change data database to include the encrypted password for the Oracle account (if you recall, this is not included in the generated script). The password must be encrypted by the asymmetric key that the CDC service creates in the change data database, which is done by calling the ENCRYPTBYASYMKEY() function. The CDC service will use the same key to decrypt the password in order to use it for connecting to Oracle. However, the caveat here is that it may take some time after the change data database is created for the CDC service to create the key. So by the time this script runs, the key may or may not exist. Therefore, we added a WAITFOR loop to the script to wait until the key appears in the database, before attempting to write configuration info. The part of the third script that does all this looks like this:
:setvar OracleAccountPassword "<SENSITIVE>" :setvar OracleConnectString "TNSNAME" -- Wait for the CDC service to create the encryption keyWHILE NOT EXISTS ( SELECT 1 FROM sys.asymmetric_keys WHERE name = 'xdbcdc_asym_key' ) WAITFOR DELAY '00:00:05'; UPDATE cdc.xdbcdc_config SET connect_string = N'$(OracleConnectString)', use_windows_authentication = 0, username =N'DW_ETL', password = ENCRYPTBYASYMKEY(ASYMKEY_ID('xdbcdc_asym_key'), N'$(OracleAccountPassword)'), transaction_staging_timeout = 120, memory_limit= 50, options= N'cdc_stop_on_breaking_schema_changes=1;trace=0;';
On a couple of occasions, we noticed that the WHILE loop would run indefinitely, waiting for the key to be created. While we did not find out the root cause for the CDC service not creating the key for a new change data database, in all such cases restarting the CDC service caused the key to be created, and allowed this script to finish.
At this point, we have the customized scripts needed to recreate the CDC instance with the desired configuration, so we can delete the existing "prototype" CDC instance in the CDC Designer console. Note that deleting the CDC instance also drops the associated change data database. Once deleted, we can go ahead and execute the three scripts described above in the same sequence, which should result in a new CDC instance, configured as desired. The instance status will be INITIAL (you may need to refresh the CDC Designer console to see it).
Next step is to create the Oracle supplemental logging script using the corresponding action in the CDC Designer console. Note that we deliberately postponed this step until now. If we did this as a part of initial CDC instance design, the generated script would enable supplemental logging for all columns of every source table, causing unnecessary overhead on the Oracle side. However, at this point the supplemental logging script will only enable logging for those columns that are being captured in each table, i.e. the columns specified in the @captured_column_list parameter of the sys.sp_cdc_enable_table stored procedure. The Oracle supplemental logging script can now be handed over to an Oracle DBA to be executed. Note that when running this script, database activity against a table may prevent a log group for that table from being created. If this happens, temporarily suspend the Oracle database workload, if possible, or wait until a period of low activity in the Oracle database. Then, rerun the script. The script generated by the CDC Designer console is idempotent and can be executed against the same Oracle database as many times as needed while the CDC instance is in the INITIAL status. If you have to manually edit the Oracle supplemental logging script, be aware that each Log Miner supplemental log group for a table cannot contain more than 32 columns. If you are capturing more than 32 columns per table, create additional log groups for the same table. The CDC Designer console handles this correctly, so we chose to simply regenerate the script when a change was needed. Also note that if for some reason a column that is added to a capture instance is omitted from the Oracle supplemental logging script, everything will work without error, however the column will always be NULL in the change data.
Once supplemental logging is enabled for each source table, you can start the CDC instance. It may take about a minute to start change capture – the status will be changing from INITIAL to IDLE to PROCESSING, and the monitoring counters on the console will start showing non-zero values. If you see LOGGER as the status, it could mean two things. One is that the CDC instance encountered a temporary delay mining Oracle logs, i.e. due to a spike in the amount of changes. If so, LOGGER should change to PROCESSING fairly soon, typically within seconds. However, if you see LOGGER for a relatively long time, i.e. for longer than a couple of minutes, it probably means that there is some problem with mining Oracle logs. If so, check the cdc.xdbcdc_trace table for errors. If necessary, set the trace parameter to either 1 or SOURCE to troubleshoot further.
Once the CDC instance is processing changes, you can start the initial load from the source Oracle database into the destination SQL Server database, and once that completes, start running incremental loads. For both types of loads, we used the new CDC components in SSIS 2012 (CDC Control task, CDC Source and CDC Splitter transformations) to manage LSN ranges and to consume change data from the change data database. Since these components use the standard SQL Server CDC APIs, using them for Oracle-sourced data is no different from using them against a native SQL Server CDC enabled database. For SSIS implementation details, refer to the CDC Flow Components MSDN documentation, and also to Matt Masson’s TechEd 2012 presentation, which describes several design patterns for working with CDC components in SSIS 2012.
Here are some other operational details related to the CDC Designer console:
DECLARE @status nvarchar(16), @sub_status nvarchar(64), @status_message nvarchar(512), @last_transaction_timestamp nvarchar(30), @last_change_timestamp nvarchar(30), @db_name sysname, @current_latency int, @latency_alert_threshold_minutes int = 15 -- May be adjusted as required; SELECT TOP (1) @status = ISNULL(status, '') ,@sub_status = ISNULL(sub_status, '') ,@status_message = ISNULL(status_message, '') ,@last_transaction_timestamp = ISNULL(CAST(last_transaction_timestamp AS nvarchar(30)), '') ,@last_change_timestamp = ISNULL(CAST(last_change_timestamp AS nvarchar(30)), '') ,@current_latency = DATEDIFF(minute, last_transaction_timestamp, SYSUTCDATETIME()) ,@db_name = DB_NAME()FROM cdc.xdbcdc_stateWHERE DATEDIFF(minute, last_transaction_timestamp, SYSUTCDATETIME()) > @latency_alert_threshold_minutes; -- Raise a warning if no transactions have been captured within the threshold number of minutes.-- May not be actionable during periods of low activity in the source system.IF @@ROWCOUNT > 0 RAISERROR('WARNING: Oracle CDC has not captured any transactions for at least %d minutes. Most recent captured Oracle transaction occurred %d minutes ago. Database: %s, Status: %s, SubStatus: %s, Status Message: %s, Last Transaction Timestamp (UTC): %s, Last Change Timestamp (UTC): %s.', 16, 1, @latency_alert_threshold_minutes, @current_latency, @db_name, @status, @sub_status, @status_message, @last_transaction_timestamp, @last_change_timestamp );
Here I will mention several problems that we ran into, and what the solution was for each. This list is by no means comprehensive, but may provide some helpful troubleshooting ideas and techniques.
If you run into a problem and your initial troubleshooting is unsuccessful, you may need to open a support case. If so, it is highly recommended to collect a set of diagnostic information for the support engineers (actually this may help you in your troubleshooting efforts as well). The CDC Designer console provides a convenient Collect Diagnostics link, which will create a text file with diagnostics collected from both Oracle and SQL Server databases. Note that this includes the contents of cdc.xdbcdc_trace table. If you have set the trace option to SOURCE sometime prior to using Collect Diagnostics, then the trace table may be so large that Collect Diagnostics will fail because of a timeout retrieving all that data. Therefore, while it is recommended to have trace set to SOURCE while Collect Diagnostics is running, it’s best to do that just prior to clicking on the link.
In addition to the output from Collect Diagnostics, if the problem seems to be related to the Oracle database and Log Miner, then it is also helpful to include the output from the following Oracle queries, which shows the state of online and archived logs:
SELECT * FROM V$LOG;SELECT * FROM V$LOGFILE;SELECT * FROM V$ARCHIVED_LOG;SELECT * FROM V$DATABASE;
To conclude, SQL 2012 Oracle CDC is still a relatively new feature of SQL Server, as of this writing. The purpose of this blog is to make it easier for SQL Server developers and DBAs who are working on data warehousing projects to discover, implement, and support this feature. Please post your comments and questions below.