Working with the Oracle CDC Service
Important
Change Data Capture for Oracle by Attunity is deprecated now. For details, refer to the announcement.
This section describes some important concepts of the Oracle CDC Service. The concepts included in this section are:
-
This section describes the tables that are included in this database and how it is important to CDC.
-
This section provides a brief description of the CDC databases. These databases are created using the Oracle CDC Designer Console. See the documentation included with your installation of the CDC Designer Console for more information about the CDC databases.
Using the Command Line to Configure the CDC Service
This section describes the command-line commands that can be used to configure the Oracle CDC Service.
The MSXDBCDC Database
The MSXDBCDC (Microsoft External-Database CDC) database is a special database that is required when using the CDC Service for Oracle with a SQL Server instance.
The name of this database cannot be changed. If a database called MSXDBCDC exists on the host SQL Server instance and contains tables other than those defined by the CDC Service for Oracle, the host SQL Server instance cannot be used.
The main uses for this database are to:
Serve as a registry of Oracle CDC Services associated with a SQL Server instance. This information is used for the service configuration and design components and to support coordination of multiple CDC services by the same name on different nodes over which one is the active one.
Serve as a registry of the Oracle CDC instances contained in a SQL Server instance, the CDC service that handles each instance, and the configuration version each uses. This information is equivalent to the is_cdc_enabled column in the sys.databases table of the master database. The CDC service periodically scans the dbo.xdbcdc_databases table to identify changes made to the CDC configuration or to the list of captured instances.
Hold sysadmin-owned stored procedures that help create and maintain CDC instances. These are similar to the system procedures that are used for the implementation of the SQL Server CDC feature.
Creating the MSXDBCDC Database
An MSXDBCDC database must be created before the Oracle CDC Service can be defined. You can create only one MSXDBCDC database on a SQL Server instance. The MSXDBCDC database is created when you prepare a SQL Server database for Oracle CDC. This can be done by using the Oracle CDC Service Configuration Console or by running a creation script that is generated by the CDC Service Configuration Console.
The owner of this database is the Oracle CDC Service Administrator, who can control all of the Oracle CDC instances hosted under the SQL Server instance.
See also:
How to Prepare SQL Server for CDC
The MSXDBCDC Database Tables
This section describes the following tables in the MSXDBCDC database.
dbo.xdbcdc_trace
This table stores tracing information for the Oracle CDC Service. The information stored in this table includes notable status changes and trace records.
The Oracle CDC Service writes error records and some of the information records to both the Windows event log and the trace table. In some cases the trace table may not be accessible, in which case the error information is accessible from the event log.
The following describes the items that are included in the dbo.xdbcdc_trace table.
| Item | Description |
|---|---|
| timestamp | The exact UTC timestamp when the trace record was written. |
| type | Contains one of the following values. ERROR INFO TRACE |
| node | The name of the node on which the record was written. |
| status | The status code that is used by the state table. |
| sub_status | The substatus code that is used by the state table. |
| status_message | The status message that is used by the state table. |
| source | The name of the Oracle CDC component that produced the trace record. |
| text_data | Additional text data for cases when the error or trace record contains a textual payload. |
| binary_data | Additional binary data for cases when the error or trace record contains a binary payload. |
The Oracle CDC instance will delete old trace table rows according to the change tables retention policy.
dbo.xdbcdc_databases
This table contains the names of CDC Service for Oracle CDC databases in the current SQL Server instance. Each database corresponds to an Oracle CDC instance. The Oracle CDC Service uses this table to determine which instances to start or stop and which instances to reconfigure.
The following table describes the items that are included in the dbo.xdbcdc_databases table.
| Item | Description |
|---|---|
| name | The name of the Oracle database in the SQL Server instance. |
| config_version | The timestamp (UTC) for the last change in the corresponding CDC database xdbcdc_config table or the timestamp (UTC) for the current row in this table. The UPDATE trigger enforces a value of GETUTCDATE() for this item. config_version lets the CDC service identify the CDC instance that needs to be checked for configuration change or for enabling/disabling. |
| cdc_service_name | This item determines which Oracle CDC Service handles the selected Oracle database. |
| enabled | Indicates whether the Oracle CDC instance is active (1) or disabled (0). When the Oracle CDC Service starts only the instances marked enable (1) are started. Note: An Oracle CDC instance can become disabled due to an error that is not retryable. In this case, the instance must be restarted manually after the error is resolved. |
dbo.xdbcdc_services
This table lists the CDC services associated with the host SQL Server instance. This table is used by the CDC Designer Console to determine the list of CDC services that are configured for the local SQL Server instance. It is also used by the CDC service to ensure that only one running Windows service handles a given Oracle CDC Service name.
The following describes the capture state items that are included in the dbo.xdbcdc_databases table.
| Item | Description |
|---|---|
| cdc_service_name | The name of the Oracle CDC Service (the Windows service name). |
| cdc_service_sql_login | The name of the SQL Server login used by the Oracle CDC Service to connect to the SQL Server instance. A new SQL User named cdc_service is created and associated with this login name and is then added as a member of the db_ddladmin, db_datareader and db_datawriter fixed database roles for each CDC database handled by the service. |
| ref_count | This item counts the number of machines where the same Oracle CDC Service is installed. It gets incremented with each addition of same-named Oracle CDC service, and it is decremented when such a service is removed. When the counter reaches zero, this row is deleted. |
| active_service_node | The name of the Windows node that currently handles the CDC service. When the service is stopped correctly, this column is set to null, indicating that there is no longer an active service. |
| active_service_heartbeat | This item tracks the current CDC service to determine if it still active. This item is updated with the current database UTC timestamp for the active CDC service at regular intervals. The default interval is 30 seconds, however the interval is configurable. When a pending CDC service detects that the heartbeat was not updated after the configured interval has passed, the pending service attempts to take over the active CDC service role. |
| options | This item specifies the secondary options, such as tracing or tuning. It is written in the form of name[=value][; ]. The options string uses the same semantics as the ODBC connection string. If the option is Boolean (with a value of yes/no), the value can include the name only. trace has the following possible values. true on false off <class name>[,class name>] The default value is false. service_heartbeat_interval is the time interval (in seconds) for the service to update the active_service_heartbeat column. The default value is 30. The maximum value is 3600. service_config_polling_interval is the polling interval (in seconds) for the CDC service to check for configuration changes. The default value is 30. The maximum value is 3600. sql_command_timeout is the command timeout that works with the SQL Server. The default value is 1. The maximum value is 3600. |
The MSXDBCDC Database Stored Procedures
This section describes the following stored procedures in the MSXDBCDC database.
dbo.xcbcdc_reset_db(Database Name)
This procedure clears the data of an Oracle CDC instance. It is used:
To restart data capturing while disregarding previous data, for example following source database recovery or following inactivity where some of the Oracle transaction logs are not available.
When there is a corruption in the CDC state (specifically in the any cdc.*tables data).
The dbo.xcbcdc_reset_db procedure performs the following tasks:
Stops the CDC instance (if active).
Truncates the change tables, the cdc_lsn_mapping table, and the cdc_ddl_history table.
Clears the cdc_xdbcdc_state table.
Clears the start_lsn column for each row of the cdc_change_table.
To use the dbo.xcbcdc_reset_db procedure, the user must be a member of the db_owner database role for the CDC Instance database being named or else member of the sysadmin or serveradmin fixed server role.
For more information about the CDC tables, see The CDC Databases in the help system in the CDC Designer Console.
dbo.xdbcdc_disable_db(dbname)
The dbo.xcbcdc_disable_db procedure performs the following task:
- Removes the entry for the selected CDC database in the MSXDBCDC.xdbcdc_databases table.
To use the dbo.xcbcdc_disable_db procedure, the user must be a member of the db_owner database role for the CDC instance being named or a member of the sysadmin or serveradmin fixed server role.
For more information about the CDC tables, see The CDC Databases in the help system in the CDC Designer Console.
dbo.xcbcdc_add_service(svcname,sqlusr)
The dbo.xcbcdc_add_service procedure adds an entry to the MSXDBCDC.xdbcdc_services table and adds an increment of one to the ref_count column for the service name in the MSXDBCDC.xdbcdc_services table. When the ref_count is 0, it deletes the row.
To use the dbo.xcbcdc_add_service<service name, username> procedure, the user must be a member of the db_owner database role for the CDC instance database being named or a member of the sysadmin or serveradmin fixed server role.
dbo.xdbcdc_start(dbname)
The dbo.xdbcdc_start procedure sends a start request to the CDC service that handles the selected CDC instance to start the change processing.
To use the dbo.xcdcdc_start procedure, the user must be a member of the db_owner database role for the CDC database or be a member of either the sysadmin or serveradmin roles for the SQL Server instance.
dbo.xdbcdc_stop(dbname)
The dbo.xdbcdc_stop procedure sends a stop request to the CDC service that handles the selected CDC instance to stop the change processing.
To use the dbo.xcdcdc_stop procedure, the user must be a member of the db_owner database role for the CDC database or be a member of either the sysadmin or serveradmin roles for the SQL Server instance.
The CDC Databases
Each Oracle CDC instance used in a CDC service is associated with a specific SQL Server database called the CDC Database. This SQL Server database is hosted in the SQL Server instance associated with the Oracle CDC Service.
The CDC Database contains a special cdc schema. The Oracle CDC Service uses this schema with table names with the prefix xdbcdc_. This schema is used for security and consistency purposes.
Both the Oracle CDC instance and the CDC databases are created using the Oracle CDC Designer Console. For more information about the CDC databases, see the documentation included with your installation of the Oracle CDC Designer Console.
Using the Command Line to Configure the CDC Service
You can operate the Oracle CDC Service program (xdbcdcsvc.exe) from the command line. The CDC service program is a native 32-bit/64-bit Windows executable file.
See also
How to Use the CDC Service Command-Line Interface
Service Program Commands
The section describes the following commands that are used to configure the CDC service.
Config
Use Config to update an Oracle CDC Service configuration from a script. The command can be used to update only specific parts of the CDC service configuration (for example, only the connection string without knowing the asymmetric key password). The command must be run by a computer administrator. The following is an example of the Config command.
"<path>xdbcdcsvc.exe" config
<cdc-service-name>
[connect= <sql-server-connection-string>]
[key= <asym-key-password>]
[svcacct= <windows-account> <windows-password>]
[sqlacct= <sql-username> <sql-password>]
Where:
cdc-service-name is the name of the CDC service to be updated. This is a required parameter.
sql-server-connection-string is the connect string to be updated. If the connect string contains spaces or quotes then it must be wrapped in double-quotation marks ("). Embedded quotes are escaped by doubling the quotation marks.
asym-key-password is the password to be updated.
windows-account, windows-password are the Windows account credentials for the service that is being updated.
sql-username, sql-password are the SQL Server authentication credentials being updated. If sqlacct has both an empty username and empty password, then the Oracle CDC Service connects to SQL Server using Windows authentication.
Note: Any parameter that contains spaces or double quotes must be wrapped with double quotes ("). Embedded double quotation marks must be doubled (for example to use "A#B" D as a password enter ""A#B"" D").
Create
Use Create to create an Oracle CDC Service from a script. The command must be run by a computer administrator. The following is an example of the Create command:
"<path>xdbcdcsvc.exe" create
<cdc-service-name>
[connect= "<sql-server-connection-string>"]
[key= <asym-key-password>]
[svcacct <windows-account> <windows-password>]
[sqlacct <sql-username> <sql-password>]
Where:
cdc-service-name is the name of the newly created service. If there is already a service with this name, the program returns an error. You should not use long names or names with spaces. The characters "/" and "\" are not valid characters in a service name. This is a required parameter.
sql-server-connection-string is the connect string to use to connect to the SQL Server instance that is associated with the new Oracle CDC Service.
asym-key-password is the password that protects the asymmetric key used for storing the source database log-mining credentials.
windows-account, windows-password are the account name and password associated with the Oracle CDC Service being created.
sql-username, sql-password are the SQL Server account name and password used to connect to the SQL Server instance. If both of these parameters are empty, then CDC Service for Oracle connects to SQL Server using Windows authentication.
Note: Any parameter that contains spaces or double quotes must be wrapped with double quotes ("). Embedded double quotation marks must be doubled (for example to use "A#B" D as a password enter ""A#B"" D".
Delete
Use Delete to cleanly delete the Oracle CDC Service from a script. This command must be run by a computer administrator. The following is an example of the Delete command.
"<path>xdbcdcsvc.exe" delete
<cdc-service-name>
Where:
cdc-service-name is the name of the CDC service to be deleted.
Note: Any parameter that contains spaces or double quotes must be wrapped with double quotes ("). Embedded double quotation marks must be doubled (for example to use "A#B" D as a password enter ""A#B"" D").
See Also
How to Use the CDC Service Command-Line Interface
How to Prepare SQL Server for CDC