ISCDC for Oracle

IBM

InfoSphere Change Data Capture

Version 6.5

InfoSphere Change Data Capture for

Oracle, Version 6.5

End-User Documentation

IBM

InfoSphere Change Data Capture

Version 6.5

InfoSphere Change Data Capture for

Oracle, Version 6.5

End-User Documentation

Note

Before using this information and the product it supports, read the information in “Notices” on page 93.

First edition

This edition applies to version 6, release 5 of IBM InfoSphere Change Data Capture (product number 5724-U70) and to all subsequent releases and modifications until otherwise indicated in new editions.

Contents

About InfoSphere CDC and InfoSphere

CDC Management Console

. . . 1

System requirements . . . 3

Supported operating systems and processors . . . 3

Supported databases. . . 3

Hardware requirements. . . 4

Port requirements. . . 4

Before you install

. . . 5

Required database, user accounts, and schemas. . . 5

Allocating disk quota for capture components . . . 6

Enabling supplemental logging . . . 6

Enabling InfoSphere CDC to use a bulk load refresh 7 Setting the database instance to ARCHIVELOG mode 7 Starting the Oracle listener. . . 8

Setting up a remote connection . . . 8

To setup a remote connection . . . 8

Installing InfoSphere CDC

. . . 11

To install InfoSphere CDC (UNIX and Linux) . . . 11

To override the locale for the installation (UNIX and Linux) . . . 12

InfoSphere CDC and Automatic Storage Management (ASM) . . . 12

Installing InfoSphere CDC using a silent installation 12 To perform a silent installation of InfoSphere CDC (UNIX and Linux) . . . 12

Upgrading Transformation Server for

Oracle to InfoSphere CDC for Oracle . . 15

To upgrade from Transformation Server for Oracle to InfoSphere CDC for Oracle . . . 15

Installing InfoSphere CDC in an Oracle

Real Application Clusters (RAC)

environment . . . 17

Defining a service for InfoSphere CDC in tnsnames.ora. . . 18

Creating a failover script for InfoSphere CDC in an Oracle RAC environment . . . 19

Configuring InfoSphere CDC (UNIX and

Linux) . . . 21

Configuring InfoSphere CDC instances (UNIX and Linux) . . . 21

To add a new instance of InfoSphere CDC (UNIX and Linux) . . . 21

To edit an instance of InfoSphere CDC (UNIX and Linux) . . . 22

To delete an instance of InfoSphere CDC (UNIX and Linux) . . . 23

Configuring InfoSphere CDC for OS

(operating system) clustering (UNIX

and Linux) . . . 25

Performing a forced or manual failover of InfoSphere CDC . . . 26

Preparing for a failover of InfoSphere CDC . . . . 26

After you install and configure . . . . 29

Starting InfoSphere CDC . . . 29

To start InfoSphere CDC (UNIX and Linux) . . 29

Stopping InfoSphere CDC . . . 29

To stop InfoSphere CDC (UNIX and Linux). . . 29

Maintaining active TCP connections in a network environment . . . 30

To maintain active TCP connections . . . 30

Enabling SQL statements in Management Console 31 To enable SQL statements in Management Console. . . 31

Privileges for Oracle DBA and InfoSphere CDC users . . . 31

Privileges required by the Oracle DBA . . . . 31

Privileges required by users . . . 32

Metadata tables . . . 37

Commands for InfoSphere CDC . . . . 39

Using the InfoSphere CDC commands . . . 39

Setting the TSINSTANCE environment variable . . 40

Controlling replication commands . . . 40

dmendreplication - End replication . . . 40

dmrefresh - Refresh subscription . . . 44

dmstartmirror - Start mirroring . . . 45

Database transaction log commands . . . 47

dmarchivelogavailable - Mark archive log as available . . . 47

dmarchivelogremoved - Mark archive log as removed . . . 48

dmdecodebookmark - Display verbose information bookmark . . . 49

dmsetbookmark - Set bookmark . . . 50

dmshowbookmark - Display bookmark information . . . 51

dmshowlogdependency - Show Log Dependency 51 Managing tables for replication commands . . . . 53

dmdescribe - Describe source tables . . . 54

dmflagforrefresh - Flag for Refresh . . . 54

dmmarktablecapturepoint - Mark a table capture point on a source table . . . 55

dmpark - Park table . . . 56

dmreaddtable - Update source table definition. . 57

dmreassigntable - Update target table definition 58 dmsetreplicationmethod - Set replication method 59 Monitoring replication commands . . . 60

dmclearevents - Clear events . . . 60

dmgetsubscriptionstatus - Get subscription status 61 dmshowevents - Display InfoSphere CDC events 62 Exporting and importing configuration commands 63

dmexportconfiguration - Export InfoSphere CDC

Configuration . . . 64

dmimportconfiguration - Import InfoSphere CDC Configuration . . . 64

Continuous Capture and staging store commands 65 dmenablecontinuouscapture - Enable Continuous Capture . . . 66

dmdisablecontinuouscapture - Disable Continuous Capture . . . 66

dmgetstagingstorestatus - Retrieve Staging Store status . . . 66

dmclearstagingstore - Remove cached operations from the staging store . . . 67

Other commands . . . 67

dmbackupmd - Backup Metadata . . . 68

dmconfigurets - Configure InfoSphere CDC . . 68

dmmdcommander . . . 69

dmmdconsole . . . 69

dmset - Set InfoSphere CDC system parameter 69 dmshowversion - Show InfoSphere CDC version 70 dmshutdown - Shut down InfoSphere CDC . . 70

dmsupportinfo - Collect IBM Support information . . . 72

dmterminate - Terminate InfoSphere CDC processes . . . 73

dmts32 - Start InfoSphere CDC . . . 74

dmts64 - Start InfoSphere CDC . . . 74

dmverifycommunications . . . 75

User exits for InfoSphere CDC

. . . . 77

Stored procedure user exits for table and row level operations . . . 77

Defining a stored procedure user exit. . . 77

Stored procedure user exit database connections 78 Retrieving data with a stored procedure user exit 78 Example of a stored procedure user exit . . . . 84

Sample Java class user exits for InfoSphere CDC . . 85

To compile the sample Java class user exits (UNIX and Linux) . . . 85

InfoSphere CDC API reference – Javadocs . . . . 86

Conflict resolution audit table . . . 87

Structure of the conflict resolution audit table . . . 87

Row image format . . . 89

Truncated images . . . 89

Unaudited data types . . . 89

Troubleshooting and contacting IBM

Support . . . 91

Notices

. . . 93

About InfoSphere CDC and InfoSphere CDC Management

Console

IBM®InfoSphere™Change Data Capture, or simply InfoSphere CDC, is a replication solution that captures database changes as they happen and delivers them to target databases, message queues, or an ETL solution such as InfoSphere DataStage®based on table mappings configured in the InfoSphere CDC

Management Console GUI application.

InfoSphere CDC provides low impact capture and fast delivery of data changes for key information management initiatives including dynamic data warehousing, master data management, application consolidations or migrations, operational BI, and enabling SOA projects. InfoSphere CDC also helps reduce processing

overheads and network traffic by only sending the data that has changed. Replication can be carried out continuously or periodically. When data is

transferred from a source server, it can be remapped or transformed in the target environment.

The following diagram illustrates the key components of InfoSphere CDC.

The key components of the InfoSphere CDC architecture are described below: v _{Access Server—Controls all of the non-command line access to the replication}

environment. When you log in to Management Console, you are connecting to Access Server.

v _{Admin API—Operates as an optional Java}™_{-based programming interface that} you can use to script operational configurations or interactions. After you have set up replication, Management Console can be closed on the client workstation without affecting active data replication activities between source and target servers.

v _{Apply agent—Acts as the agent on the target that processes changes as sent by} the source.

v _{Command line interface—Allows you to administer datastores and user} accounts, as well as to perform administration scripting, independent of Management Console.

v _{Communication Layer (TCP/IP)—Acts as the dedicated network connection} between the Source and the Target.

v _{Datastore—The Source and Target datastores represent the data files and} InfoSphere CDC instances required for data replication. Each datastore

represents a database to which you want to connect and acts as a container for your tables. Tables made available for replication are contained in a datastore. v _{InfoSphere CDC Management Console—The interactive application that you}

use to configure and monitor replication. It allows you to manage replication on various servers, specify replication parameters, and initiate refresh and mirroring operations from a client workstation. Management Console also allows you to monitor replication operations, latency, event messages, and other statistics supported by the source or target datastore. The monitor in Management Console is intended for time-critical working environments that require continuous analysis of data movement.

v _{Metadata—Represents the information about the relevant tables, mappings,} subscriptions, notifications, events, and other particulars of a data replication instance that you set up.

v _{Mirror—Performs the replication of changes to the target table or accumulation} of source table changes used to replicate changes to the target table at a later time. If you have implemented bidirectional replication in your environment, mirroring can occur to and from both the source and target tables.

v _{Refresh—Performs the initial synchronization of the tables from the source} database to the target. This is read by the Refresh reader.

v _{Replication Engine—Serves to send and receive data. The process that sends} replicated data is the Source Capture Engine and the process that receives

replicated data is the Target Engine. An InfoSphere CDC instance can operate as a source capture engine and a target engine simultaneously.

v _{Single Scrape—Acts as a source-only log reader and a log parser component. It} checks and analyzes the source database logs for all of the subscriptions on the selected datastore.

v _{Source transformation engine—Used to process row filtering, critical columns,} column filtering, encoding conversions, and other data to propagate to the target datastore engine.

v _{Source database logs—Maintained by the source database for its own recovery} purposes. The InfoSphere CDC log reader inspects these in the mirroring process, but only looks for those tables that are mapped for replication. v _{Target transformation engine—Used to process data and value translations,}

encoding conversions, user exits, conflict detections, and other data on the target datastore engine.

There are two types of target-only destinations for replication that are not databases:

v _{JMS Messages—Acts as a JMS message destination (queue or topic) for} row-level operations that are created as XML documents.

v _{InfoSphere DataStage—Processes changes delivered from InfoSphere CDC that} can be used by InfoSphere DataStage jobs.

For more information on how to install Management Console and Access Server, see Access Server and Management Console - Installation Guide. For information on how to install your source and target replication engines, see the end-user documentation for your replication engine platform.

System requirements

Before you install InfoSphere CDC, ensure that the system you choose meets the necessary operating system, hardware, software, communications, disk, and memory requirements.

In this section, you will learn:

“Supported operating systems and processors” “Supported databases”

“Hardware requirements” on page 4 “Port requirements” on page 4

Supported operating systems and processors

Operating system and processor

One of the following:

v _AIX®_{, version 5.3.0.8 or later—POWER}®_processor

v _{HP-UX, version 11.11 or later—PA-RISC processor} v _{HP-UX, version 11.23 or later—Itanium}®_processor

v _Linux®_{, Kernel version 2.6.x—Intel}®_{or AMD x86-32, x86-64 processor} v _{Sun Solaris, version 2.8 or later—SPARC processor}

Note: If you are upgrading your operating system to a more recent version, you should also consider upgrading InfoSphere CDC to the latest version to ensure support for the upgraded operating system.

Supported databases

Database

Install Oracle Client software and one of the following versions of the Oracle database: v _{Oracle 9i (release 9.2.0.5 or later)}

v _{Oracle 10g (release 10.1.0.4 or later)} v _{Oracle 11g (release 11gR1 and 11gR2)}

If you are configuring InfoSphere CDC for an Oracle RAC environment, the following versions of Oracle are supported:

v _{Oracle 9i (release 9.2.0.5 or later)} v _{Oracle 10gR2}

v _{Oracle 11g (release 11gR1 and 11gR2)}

Note: If you are upgrading your database to a more recent version, you should also consider upgrading InfoSphere CDC to the latest version to ensure support for the upgraded database.

Hardware requirements

RAM Disk space

Oracle tablespace for InfoSphere CDC metadata

1 GB

Available for IBM processes.

5 GB—For installation files. 25 MB—This is only an estimate and depends on the size of your replication configuration.

Port requirements

InfoSphere CDC requires that you allocate a port for communication with client workstations running Management Console and other servers. The port must be accessible through a firewall, although you do not require access to the internet.

Protocol Default port Purpose

TCP 11111 Accepts connections from:

v _{Management Console} v _{Other installations of}

InfoSphere CDC as a source of replication v _{Command line utilities} For more information on how to install Management Console, see Management Console and Access Server -Installation Guide.

Related concepts

Before you install

This section contains information on the tasks that you must complete before installing InfoSphere CDC. This section assumes that you have met all of the hardware, software, database, and port requirements. You must complete all of the tasks below before installing InfoSphere CDC.

In this section, you will learn:

“Required database, user accounts, and schemas”

“Allocating disk quota for capture components” on page 6 “Enabling supplemental logging” on page 6

“Enabling InfoSphere CDC to use a bulk load refresh” on page 7 “Setting the database instance to ARCHIVELOG mode” on page 7 “Starting the Oracle listener” on page 8

“Setting up a remote connection” on page 8

Required database, user accounts, and schemas

Configuring an Oracle database

When you configure InfoSphere CDC, you are prompted for the name of the Oracle database you want InfoSphere CDC to connect to and replicate data. Before installing InfoSphere CDC, ensure that this Oracle database exists and that you have created and set up a database user that has access to it.

Setting up a UNIX

user account

If you are installing InfoSphere CDC on a UNIX system, you must set up a new, or decide on an existing UNIX account that you will use to install, configure, or upgrade InfoSphere CDC. You can install InfoSphere CDC in the directory of your choice, however, it must be owned by the UNIX account.

Setting up an Oracle user account with DBA privileges

Create a user account that has DBA privileges for the Oracle instance. When you configure InfoSphere CDC, you are prompted for the name of the Oracle database you want InfoSphere CDC to connect to and the user name and password (neither are case-sensitive) of the Oracle user that has access to this database. Before installing InfoSphere CDC, make sure you review the specific grants required by the Oracle DBA. Use the sample ora-createuser.sql SQL script located in the installation directory to create an Oracle user with all the necessary DBA privileges that are required.

Optionally, you can create a user account that has read-only database connection to the source database. Specify that you want read only access and provide this user name when installing and configuring InfoSphere CDC. Read-only database connection to the source database indicates that the user can only view data or mirror subscribed tables. The user cannot change any information. If you use a read-only user, you should also ensure you have enabled supplemental logging at the database table level, prior to installing and configuring InfoSphere CDC.

Note: If you plan to use a read-only database connection, ensure that you have the DBMS_FLASHBACKOracle supplied package installed. By default, this package is installed when you create an Oracle database and run the CATPROC.SQL script. No further action is required for this package. For more information about this package, refer to your Oracle documentation.

Setting up a user name and password for an ASM instance

If your database instance is managed by Oracle Automatic Storage Manager (ASM), then you should already have an Oracle account for the ASM instance to which you want to connect. InfoSphere CDC requires a user name and password so that it can connect to the ASM instance that corresponds to the node in the cluster. The ASM user must have SYSDBA privileges in order to log into ASM.

Configuring an Oracle schema

Create a schema or choose an existing schema for your database metadata tables. You will have to specify this schema when you configure InfoSphere CDC.

Related concepts

“Enabling supplemental logging”

Allocating disk quota for capture components

InfoSphere CDC uses specific components to capture changes from the database log. These components accumulate uncommitted transactions and data changes in memory. InfoSphere CDC dynamically allocates memory between these

components as required and only persists data to disk in the following scenarios: v _{An especially large uncommitted transaction is being accumulated.}

v _{A latent subscription. For example, you may know of a subscription or set of} subscriptions that will only run periodically which will cause the change log to grow. If sufficient memory to store the data for this latent subscription is not available, the data will be persisted to disk. Also, on shutdown or when this subscription is stopped, InfoSphere CDC persists the data to disk.

You can optionally choose to bound disk space utilization by setting a quota using the mirror_global_disk_quota_mb system parameter. When the disk quota is met, InfoSphere CDC will stop all running subscriptions and generate an event in the Event Log. No data is lost in this scenario.

Enabling supplemental logging

Oracle supplemental logging simply means that all columns or selected columns are specified for extra logging. InfoSphere CDC requires supplemental logging on the source database at both the database level and table level.

v _{Database level supplemental logging This is an Oracle requirement that ensures} that the Oracle redo log contains the information required to describe all data changes completely. The appropriate setting for database level supplemental logging (by database version) is:

– Oracle 9i—enable minimal supplemental logging. This is the default level of supplemental logging in this version of Oracle.

– Oracle 10g or higher—enable minimal database level supplemental logging. You must set this value explicitly in this version of Oracle because the default level of supplemental logging is not sufficient. To check if minimal

following SQL statement: select suppplemental_log_data_min from v$database; If supplemental logging is enabled, the returned value will be YES or IMPLICIT.

v _{Table level supplemental logging—InfoSphere CDC also requires full}

supplemental logging at the table level for those tables you have selected for InfoSphere CDC to replicate using the mirroring replication method. This supplemental logging is typically handled by InfoSphere CDC, unless you are using a read-only database connection to the source database. For a read-only database environment, before configuring subscriptions that involve tables for mirroring, ensure that you have sufficient supplemental logging enabled for those tables. During InfoSphere CDC subscription configuration, the application checks to see if the required logging is enabled. If sufficient table supplemental logging is not enabled, thenInfoSphere CDC will return errors and does not complete the configuration. The appropriate setting for table level supplemental logging (by database version) is:

– Oracle 9i—enable full supplemental logging with conditional supplemental log groups.

– Oracle 10g or higher— enable full supplemental logging with conditional or unconditional supplemental log groups.

To enable supplemental logging at the database level and table level, contact your Oracle database administrator. For more information on the command that enables supplemental logging, see your Oracle documentation.

Enabling InfoSphere CDC to use a bulk load refresh

If you want InfoSphere CDC to use a bulk load refresh when applying data to the target database, then you must do the following:

1. Install an Oracle Client on the same server where you have installed and configured InfoSphere CDC. The Oracle Client must be able to connect to the Oracle database.

2. Add the same TNS names entry for your database to the tnsnames.ora file and select this database when configuring InfoSphere CDC.

Setting the database instance to ARCHIVELOG mode

InfoSphere CDC requires uninterrupted access to Oracle redo logs. Therefore, you must enable the archiving of Oracle redo logs. Make sure that the source database instance is operating in ARCHIVELOG mode. This lets InfoSphere CDC read data from archived Oracle redo logs instead of online redo logs, in the event that excessive latency occurs during mirroring. To set the source database instance to ARCHIVELOG mode, contact your database administrator. You can also verify if archive logging is enabled for the source database by issuing the following SQL statement: select log_mode from v$database; If archive logging is enabled, the returned value will be instance to ARCHIVELOG mode. For more information, see your Oracle documentation. CAUTION: If you do not set the database instance to ARCHIVELOG mode, you may experience data loss.

InfoSphere CDC must have direct access to the archive log files. You can install InfoSphere CDC on the same node that has access to the archive log files. However, if your database instance is managed by Oracle Automatic Storage Manager (ASM), then you can install InfoSphere CDC any node. Please be aware that reading archive and redo logs from ASM can be significantly slower than

reading the logs through the file system. If the database produces high volumes of logs, you should consider multiplexing the logs by configuring a log destination outside ASM.

Starting the Oracle listener

InfoSphere CDC requires the Oracle listener to connect to the database. You must start the Oracle listener before installing InfoSphere CDC. For more information on starting the Oracle listener, see your Oracle documentation.

Setting up a remote connection

If you want to install InfoSphere CDC and the Oracle database on different machines, then you must setup a connection to the remote machine (where the Oracle database is installed) using SQL*Net.

See also:

“To setup a remote connection”

To setup a remote connection

1. Ensure you have installed an Oracle client on the local server and have added the TNS names entry to the tnsnames.ora file.

2. Ensure you have installed and configured an instance of InfoSphere CDC on the local server. When configuring InfoSphere CDC, you must specify the same TNS names entry.

3. Ensure you have started InfoSphere CDC.

4. At the command line on the local server, mount the archive log path from the remote server:

mount <host>:<ArchiveLogDirectory> <local_archive_log> where:

v _{<host>is the name of the remote server where the Oracle database is} installed.

v _{<ArchiveLogDirectory> is the absolute path of the archive log directory on} the remote server.

v _{<local_archive_log> is the mount point on the local server where InfoSphere} CDC is installed.

5. If the absolute paths of the directories containing the archive logs differ between the local server and the remote server, add the

mirror_archive_log_directorysystem parameter in Management Console: 6. Set the value of this system parameter to match the mount point on the local

server.

7. At the command line on the local server, mount the redo log path from the remote server:

mount <host>:<OnlineLogDirectory> <local_online_log> where:

v _{<host>is the name of the remote server where the Oracle database is} installed.

v _{<OnlineLogDirectory>is the absolute path of the online Redo Log directory} on the remote server.

v _{<local_archive_log> is the mount point on the local server where InfoSphere} CDC is installed.

8. If the absolute paths of the directories containing the online logs differ between the local server and the remote server, add the mirror_online_log_directory system parameter in Management Console:

9. Set the value of this system parameter to match the mount point on the local server.

Installing InfoSphere CDC

This section provides step-by-step instructions on how to install InfoSphere CDC. If you are installing a fix pack, you must already have an installation of InfoSphere CDC in order to successfully complete the installation.

Note: When upgrading to InfoSphere CDC version 6.5, you must also upgrade both Management Console and Access Server to version 6.5.

In this section, you will learn:

“To install InfoSphere CDC (UNIX and Linux)”

“To override the locale for the installation (UNIX and Linux)” on page 12 “InfoSphere CDC and Automatic Storage Management (ASM)” on page 12 “Installing InfoSphere CDC using a silent installation” on page 12

Related concepts

“Before you install” on page 5

To install InfoSphere CDC (UNIX and Linux)

Note the following before you install or upgrade InfoSphere CDC on UNIX or Linux:

v _{Do not install or upgrade InfoSphere CDC as a root user.}

v _{The installation directory requires file system permissions of 600 if you plan on} using the same user account to install the product, create and configure

instances, or upgrade the product.

v _{The installation directory requires file system permissions of 660 if you plan on} using different user accounts to install the product, create and configure instances, or upgrade the product.

1. Log on to the account you set up for InfoSphere CDC.

2. Copy the InfoSphere CDC installation file for your UNIX or Linux platform from the InfoSphere CDC CD-ROM or download it from the InfoSphere CDC web site.

3. Make the installation binary file executable.

4. Run the installation program by typing the following command: ./<installation_binary_name>.bin

If you already have InfoSphere CDC installed, the installation program will prompt you to upgrade.

5. Press Enter on the Introduction screen to display the license agreement. Follow the instructions on the screen to navigate through the license agreement.

6. To accept the license agreement, type 1.

7. Type the absolute path to your installation directory or press Enter to accept the default.

Note: The directory that you specify must be owned by the account you are using for the installation. If the installation program cannot create the directory, you are prompted to specify a different directory.

8. Review the installation summary. Press Enter to start the installation. 9. After completing the installation, InfoSphere CDC gives you the option of

launching the configuration tool for InfoSphere CDC. 10. Type 1 to launch the configuration tool.

Note: If you have X-Windows installed, the installation program will launch the configuration tool in a graphical environment.

Related concepts

“Configuring InfoSphere CDC (UNIX and Linux)” on page 21

To override the locale for the installation (UNIX and Linux)

Use the following procedure to override the locale for the installer. English and Japanese are supported.

1. Navigate to the directory that contains the InfoSphere CDC installation file. 2. Start the installer with the following flags to override the locale of the

installation:

v _{English—<installation_file_name>.bin -l en} v _{Japanese—<installation_file_name>.bin -l ja} where:

v _{<installation_file_name>is the name of the installation file.}

After the installation is complete, you have the option of launching the InfoSphere CDC configuration tool. The configuration tool will use the locale settings for your system.

InfoSphere CDC and Automatic Storage Management (ASM)

If you are using ASM to manage your database instance, then the InfoSphere CDC installation program prompts you for log in information for the ASM instance (the name of the ASM user and password). The ASM user must have database SYSDBA privileges to log into ASM.

Installing InfoSphere CDC using a silent installation

A silent installation allows you to automatically install InfoSphere CDC by specifying a command with various parameters. You can use this type of

installation method for large-scale deployments of InfoSphere CDC by embedding the silent installation command in a script.

See also:

“To perform a silent installation of InfoSphere CDC (UNIX and Linux)”

To perform a silent installation of InfoSphere CDC (UNIX and

Linux)

1. Log on to the account you set up for InfoSphere CDC.

2. Copy the InfoSphere CDC installation binary from the InfoSphere CDC CD-ROM or download it from the InfoSphere CDC Web site.

3. Make the installation binary executable.

4. Install InfoSphere CDC and generate a response file with the following command:

<installation_binary_name> -r <response-file> where:

v _{<response-file> is the full path to the installation file.}

5. On another system, perform the silent installation by running the following command:

<installation_binary_name> -i silent -f <response-file> where:

v _{<response-file> is the full path to the installation file.}

Related tasks

“To install InfoSphere CDC (UNIX and Linux)” on page 11

Upgrading Transformation Server for Oracle to InfoSphere

CDC for Oracle

There is no direct upgrade from Transformation Server® for Oracle version 6.0 (Service Pack 3 TFE 28 and later) to InfoSphere CDC for Oracle version 6.5. You must install InfoSphere CDC for Oracle version 6.5 on the same database server where your existing installation of Transformation Server resides. The database server must be the same so that you can transfer existing subscriptions from Transformation Server to InfoSphere CDC instance. After a successful installation of InfoSphere CDC for Oracle version 6.5, you can transfer and upgrade

subscriptions in Management Console.

Earlier versions of the software must first be upgraded to Transformation Server for Oracle version 6.0 Service Pack 3 TFE 28 before attempting a migration to InfoSphere CDC for Oracle version 6.5.

In this section, you will learn:

“To upgrade from Transformation Server for Oracle to InfoSphere CDC for Oracle”

To upgrade from Transformation Server for Oracle to InfoSphere CDC

for Oracle

1. Ensure you have an existing installation of Transformation Server for Oracle version 6.0 (Service Pack 3 and later) on a database server.

2. Install InfoSphere CDC for Oracle version 6.5 on the same database server where you have installed Transformation Server for Oracle version 6.0 (Service Pack 3 TFE 28 and later).

3. Ensure both instances of Transformation Server for Oracle and InfoSphere CDC are started.

4. End any active replication.

5. Upgrade existing subscriptions and transfer existing bookmarks from

Transformation Server for Oracle version 6.0 (Service Pack 3 TFE 28 and later) to InfoSphere CDC for Oracle version 6.5 in Management Console. For more information, see Upgrading existing Transformation Server subscriptions to

InfoSphere CDC in your Management Console documentation.

Related reference

“dmts64 - Start InfoSphere CDC” on page 74 “dmts32 - Start InfoSphere CDC” on page 74 “dmendreplication - End replication” on page 40

Installing InfoSphere CDC in an Oracle Real Application

Clusters (RAC) environment

To deploy InfoSphere CDC in an Oracle RAC environment, you can install the product on one node in the cluster or on a separate machine outside of the cluster. In both scenarios you must install InfoSphere CDC on a shared storage device and InfoSphere CDC must have access to all archived and online redo log files

generated by all nodes in the cluster. Installing the product on a machine outside of your RAC environment is the optimum configuration for failover scenarios where a node may fail.

To integrate InfoSphere CDC into your RAC environment, you must first define an Oracle service for the RAC environment in the tnsnames.ora file and then select this service when creating an instance of InfoSphere CDC for your RAC

environment in the configuration tool. You must also create a failover script that automates several InfoSphere CDC commands.

An overview of InfoSphere CDC configuration in a RAC environment (with Active-Passive configuration) is provided in the following diagram:

Behavior of InfoSphere CDC in a RAC environment

InfoSphere CDC queries the v$archived_log and v$log Oracle views to locate the database log files and attempts to access the files in the first log destination as defined in your Oracle database. If the logs are unavailable in the first destination, the product proceeds to the next destination until it finds the required files. To avoid latency, configure your Oracle database so that the first destination contains the required log files.

InfoSphere CDC replicates data from all RAC nodes at once and the stream of data from individual nodes is sorted (to account for data that is scrambled by log parallelism) and merged together for transaction consistency. InfoSphere CDC does not replicate data if the main node is closed. While replicating data, InfoSphere CDC opens connections to the source database and if these connections are closed for any reason, InfoSphere CDC stops replication.

InfoSphere CDC takes approximately 21 seconds to detect that a node has failed. After the node fails, InfoSphere CDC continues to replicate data if the Oracle Cluster Ready Services (CRS) is running and recovers and finalizes the online logs from the failed node. InfoSphere CDC ensures data integrity when replicating data from all other nodes in your RAC environment. Once the node is restored,

InfoSphere CDC automatically starts replicating from the restored node. If your RAC nodes have an unbalanced load, InfoSphere CDC may experience a latency of several times the Oracle checkpoint interval (3 seconds).

Note:

If you are using ASM to manage your database instance and redo log files, then you must ensure that you have specified the Oracle SID of the local node where InfoSphere CDC is installed. You can specify the Oracle SID in the InfoSphere CDC configuration tool when you are prompted for the TNS name. The configuration tool can be launched from the command line by issuing the dmconfigurets command.

In this section, you will learn:

“Defining a service for InfoSphere CDC in tnsnames.ora”

“Creating a failover script for InfoSphere CDC in an Oracle RAC environment” on page 19

Related concepts

“Installing InfoSphere CDC” on page 11

Defining a service for InfoSphere CDC in tnsnames.ora

Define a service in the tnsnames.ora file that will be used by InfoSphere CDC to connect to your RAC environment. For example:

CDCSID =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = racsystemvip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racsid.racsystem.com) (INSTANCE_NAME = instancesid) ) )

Where:

v _{racsystemvip—virtual IP for your RAC environment.}

v _{racsid.racystem.com—service name for your RAC environment.} v _{instancesid—unique name of this instance in your RAC environment.} When creating an InfoSphere CDC instance for your RAC environment in the InfoSphere CDC configuration tool, select the service that you defined in this procedure when prompted to select a TNS. You must select a single node (considered the main node by InfoSphere CDC), not the entire RAC instance.

Related concepts

“Installing InfoSphere CDC in an Oracle Real Application Clusters (RAC) environment” on page 17

“Installing InfoSphere CDC” on page 11

Related tasks

“To add a new instance of InfoSphere CDC (UNIX and Linux)” on page 21 “Creating a failover script for InfoSphere CDC in an Oracle RAC environment”

Creating a failover script for InfoSphere CDC in an Oracle RAC

environment

If an Oracle RAC node fails but the machine containing the node is still responsive, develop a recovery script for InfoSphere CDC that automates the following actions:

v _{Stops replication on all subscriptions with the dmendreplication command.} v _{Shuts down all InfoSphere CDC processes with the dmshutdown command.} v _{Modifies the INSTANCE_NAME parameter in the tnsames.ora file to point to the}

next available RAC node.

v _{If required, restarts the Oracle service listener.}

v _{Deletes the following staging area files in the InfoSphere CDC installation} directory:

– <instance_name>/conf/txq* – <instance_name>/stagingstore/* – <instance_name>/tmp/*

v _{Restarts your InfoSphere CDC instance on a different RAC node with the dmts32} or dmts64 commands.

v _{Restarts replication on all subscriptions with the dmstartmirror command.}

Related concepts

“Installing InfoSphere CDC in an Oracle Real Application Clusters (RAC) environment” on page 17

Related tasks

“Defining a service for InfoSphere CDC in tnsnames.ora” on page 18

Related reference

“dmendreplication - End replication” on page 40

“dmshutdown - Shut down InfoSphere CDC” on page 70 “dmts32 - Start InfoSphere CDC” on page 74

“dmts64 - Start InfoSphere CDC” on page 74 “dmstartmirror - Start mirroring” on page 45

Configuring InfoSphere CDC (UNIX and Linux)

After installing InfoSphere CDC, the installation program launches a configuration tool. The configuration tool allows you to configure one or more InfoSphere CDC instances for your environment. You must configure InfoSphere CDC before you can start replication.

In this section, you will learn:

“Configuring InfoSphere CDC instances (UNIX and Linux)”

Configuring InfoSphere CDC instances (UNIX and Linux)

You can add, edit, or delete an instance of InfoSphere CDC. Use the InfoSphere CDC configuration tool to work with instances. You do not have to start and stop instances.

Before you add, edit, or delete an instance, ensure logging is turned on for each database from which you intend to capture data changes.

See also:

“To add a new instance of InfoSphere CDC (UNIX and Linux)”

“To edit an instance of InfoSphere CDC (UNIX and Linux)” on page 22 “To delete an instance of InfoSphere CDC (UNIX and Linux)” on page 23

To add a new instance of InfoSphere CDC (UNIX and Linux)

If you are configuring the first instance of InfoSphere CDC after installation, you can proceed to Step 2 of this procedure.

1. At the command prompt, launch the configuration tool by issuing the following command in the specified directory:

\<InfoSphere CDC Installation Directory>\bin\dmconfigurets

2. At the welcome message, press Enter to continue.

3. Type the name of the instance and press Enter to add a new instance of InfoSphere CDC.

4. Type the port number which InfoSphere CDC uses for communication with client workstations running Management Console and other servers. InfoSphere CDC displays a default port of 11001. Press Enter.

Note: This port number cannot be used by other applications installed on the same server. You will use this port number when specifying access parameters for your datastore in the Access Manager perspective in Management Console. For more information, see your Management Console documentation.

Note: For more information on the port requirements for InfoSphere CDC, see “Port requirements” on page 4.

5. If you are using the auto-discovery feature in Access Manager, then enable the this feature by typing the UDP port number that you set in Access Server. The default port is 2222. InfoSphere CDC uses this UDP port number for

auto-discovery broadcasts sent from Access Server. Otherwise, press Enter to disable.

6. Depending on the bit version of your server, type 32 or 64 and press Enter. 7. Type the amount of physically available RAM that you want to allocate for

this instance of InfoSphere CDC and press Enter. By default, the configuration tool allocates 512 MB of RAM for each 32-bit instance and 1024 MB of RAM for each 64-bit instance.

Note: Using values other than the defaults or allocating more RAM than is physically available on your server should only be undertaken after

considering the impacts on product performance.

8. If you want to connect to a read-only source database, then type y. If you use a database with read-only privileges, in the step 12, specify the read-only user for that database. If you want read and write access, then type n.

Note: Once you have created the instance, this option is set and you cannot connect to a source database with read and write access.

9. Type the path of the Oracle database you want to replicate data to or from and contains all of the tables for replication. This is the database that you configured as part of the pre-installation tasks. Press Enter.

10. Select the TNS name for your Oracle database that you defined in the tnsnames.orafile.

If you are configuring an instance for an Oracle RAC environment, consider the following:

v _{With non-ASM managed redo log files, select the Oracle service name that} you defined in tnsnames.ora.

v _{With ASM-managed redo log files, select the Oracle SID of the local node} where InfoSphere CDC is installed.

11. If you want to specify extra JDBC parameters, then type y.

12. Type the user name (case-sensitive) for the specified database and press Enter. 13. Type the password (case-sensitive) for the specified database and press Enter.

The configuration tool will now search the database for schemas.

14. Type the number that corresponds to the database schema used by InfoSphere CDC for metadata tables and press Enter. You can specify any schema except those in use by other installed instances of InfoSphere CDC for the given database.

Note: InfoSphere CDC metadata tables contain important configuration information and should be backed up as part of your database backup strategy.

15. The configuration tool creates the InfoSphere CDC instance and prompts you to start the instance. Type y to start the instance.

Note: The configuration tool will prompt you if your configuration is about to overwrite the metadata for an existing instance.

Related reference

“dmbackupmd - Backup Metadata” on page 68

To edit an instance of InfoSphere CDC (UNIX and Linux)

1. Stop InfoSphere CDC if it is started by using the dmshutdown command.

2. At the command prompt, launch the configuration tool by issuing the following command from the <InfoSphere CDC Installation Directory>/bin directory: ./dmconfigurets

3. Type 1 and press Enter to list the installed instances of InfoSphere CDC. Record the name of the instance you want to modify.

4. Type 3 and press Enter to modify an instance of InfoSphere CDC. 5. Type the instance name that you want to modify and press Enter.

The configuration tool allows you to edit a number of values that you specified when adding an instance.

6. After making your changes, type 5 and press Enter to apply your changes and return to the main menu. Type 6 and press Enter to discard your changes.

To delete an instance of InfoSphere CDC (UNIX and Linux)

1. Stop InfoSphere CDC if it is started by using the dmshutdown command. 2. At the command prompt, launch the configuration tool by issuing the following

command from the <InfoSphere CDC installation directory>/bin directory: ./dmconfigurets

3. Type 1 and press Enter to list the installed instances of InfoSphere CDC. Record the name of the instance you want to delete.

4. Type 4 and press Enter to delete an instance of InfoSphere CDC. 5. Type the instance name that you want to delete and press Enter.

Configuring InfoSphere CDC for OS (operating system)

clustering (UNIX and Linux)

InfoSphere CDC supports Active/Passive two-node clusters on the UNIX and Linux platforms. Clustering provides continuous access to resources in the event of a hardware failure, software failure, or some other interruption.

To implement InfoSphere CDC clustering support in your environment, you must complete all of the following pre-requisite tasks.

Note: Prerequisites that only apply to InfoSphere CDC as a clustered source or a clustered target are specified.

v _{Install InfoSphere CDC on the shared drive of the cluster.} v _{Add a new instance of InfoSphere CDC.}

v _{Ensure that the server port you specify during configuration of the instance is} available and persistent on both nodes of the cluster. InfoSphere CDC listens on this port.

v _{Ensure that InfoSphere CDC connects to the database in the same way on both} nodes of the cluster. In other words, database connection parameters such as the path to the database and services must be identical on both nodes of the cluster. v _{Ensure that all of the database logs required for replication are available. This}

prerequisite only applies to InfoSphere CDC as a clustered source.

v _{Ensure that the path to the database logs required for replication is identical on}

bothnodes of the cluster. This prerequisite only applies to InfoSphere CDC as a clustered source.

v _{Ensure that every InfoSphere CDC source that connects to the target sees the} target in the same way. The target must have a clustered IP address or use the same host name for both nodes of the cluster. This prerequisite only applies to InfoSphere CDC as a clustered target.

v _{Optionally, schedule a regular backup of your InfoSphere CDC metadata and} event log messages. Note that metadata will only change when you add or modify subscriptions. You can find more information about this prerequisite in the failover procedure for InfoSphere CDC in this section.

Note: You can run the dmshowlogdependency command with the –i flag to list the database logs required for replication.

In this section, you will learn:

“Performing a forced or manual failover of InfoSphere CDC” on page 26 “Preparing for a failover of InfoSphere CDC” on page 26

Related concepts

“Configuring InfoSphere CDC instances (UNIX and Linux)” on page 21

Related tasks

“To install InfoSphere CDC (UNIX and Linux)” on page 11

Performing a forced or manual failover of InfoSphere CDC

A forced or manual failover is often necessary in a clustered environment for software upgrades, maintenance, or other reasons. The tasks in the following steps must be included in your manual failover script.

1. Stop all instances of InfoSphere CDC on the current active node with the following command:

dmshutdown -I <instance_name>

2. Manually failover your clustered environment with the scripts or procedures that are specific to your environment.

3. Restart all instances on the new active node with the following commands for 32-bit and 64-bit operating systems:

dmts32 - I <instance_name> or dmts64 - I <instance_name>

4. Start all subscriptions on the new active node with the dmstartmirror command.

Related concepts

“Starting InfoSphere CDC” on page 29 “Stopping InfoSphere CDC” on page 29

Related tasks

“Preparing for a failover of InfoSphere CDC”

Related reference

“dmstartmirror - Start mirroring” on page 45

Preparing for a failover of InfoSphere CDC

To prepare for a failover such as a hardware or software failure, your clustering environment will require a script that performs InfoSphere CDC tasks on the new active node. The tasks in the following steps must be included in your failover script.

1. Clean the transaction queues for each instance by removing all files that begin with txqueue* in the <InfoSphere CDC installation directory>/instance/

<instance name>/conf directory.

2. Back up your metadata for each instance by archiving all files that begin with md* in the <InfoSphere CDC installation directory>/instance/<instance

name>/confdirectory. If the metadata database does not recover after a failover, restore these files to the same directory on the new active node. This task is optional.

3. Back up your Event Log messages for each instance by archiving all files in the

<InfoSphere CDC installation directory>/instance/<instance name>/events

directory. If the events database does not recover after a failover, restore these files to the same directory on the new active node. This task is optional. 4. Start each instance on the new active node with the following commands for

32-bit and 64-bit operating systems:

5. Run the dmclearstagingstore command on the new active node for all instances.

6. Start all subscriptions on the new active node with the dmstartmirror command.

Related concepts

“Starting InfoSphere CDC” on page 29

Related tasks

“Performing a forced or manual failover of InfoSphere CDC” on page 26

Related reference

“dmclearstagingstore - Remove cached operations from the staging store” on page 67

“dmstartmirror - Start mirroring” on page 45

After you install and configure

Once you have installed and configured InfoSphere CDC, you can start using InfoSphere CDC.

In this section, you will learn: “Starting InfoSphere CDC” “Stopping InfoSphere CDC”

“Maintaining active TCP connections in a network environment” on page 30 “Enabling SQL statements in Management Console” on page 31

“Privileges for Oracle DBA and InfoSphere CDC users” on page 31

Starting InfoSphere CDC

After you install InfoSphere CDC on a supported UNIX or Linux server, you can issue a command to start it so that you can create a datastore for the instance in Management Console. You can also start the instance by using the configuration tool.

See also:

“To start InfoSphere CDC (UNIX and Linux)”

To start InfoSphere CDC (UNIX and Linux)

Depending on the operating system you are running, issue one of the following start commands:

v _{dmts32 - I <instance_name>} v _{dmts64 - I <instance_name>}

Stopping InfoSphere CDC

It may be necessary to stop InfoSphere CDC when you want to change the configuration settings, take a server or database offline for maintenance purposes, or if you want to upgrade InfoSphere CDC. You can use the configuration tool or commands to stop InfoSphere CDC.

See also:

“To stop InfoSphere CDC (UNIX and Linux)”

To stop InfoSphere CDC (UNIX and Linux)

1. End replication on all subscriptions in Management Console. For more

information on how to end replication on subscriptions, see your Management Console documentation.

2. Depending on how you want to stop InfoSphere CDC, issue one of the following stop commands in the bin directory in your InfoSphere CDC installation directory:

Option Description

dmshutdown -I <instance_name> Use this command to gracefully shut down InfoSphere CDC.

If you have multiple active InfoSphere CDC installations on the same UNIX or Linux server, and you want to shut them all down, run this command from the installation directory for each InfoSphere CDC instance.

dmterminate -I <instance_name> Use this command to terminate all InfoSphere CDC processes for all instances running on a UNIX or Linux server. Use this command when you cannot completely shut down InfoSphere CDC using the dmshutdown command.

Maintaining active TCP connections in a network environment

If your deployment of InfoSphere CDC is in a network environment that uses a firewall, VPN gateway, or local system tools to detect idle TCP connections, it may be necessary to configure the product to prevent these connections from being closed during periods of application inactivity between the source and target. By default, InfoSphere CDC sends a message over TCP connections every 20 seconds to ensure these connections remain active during periods of inactivity. If your network policies close TCP connections for idle periods of less than 20 seconds, you must change the configuration of each instance of InfoSphere CDC to ensure the TCP connections remain open.

See also:

“To maintain active TCP connections”

To maintain active TCP connections

1. For each instance of InfoSphere CDC, navigate to the following directory: UNIX or Linux:

<CDC_installation_directory>/instance/<instance_name>/conf

2. Open the comms.ini file in a text editor.

3. Change the KEEP_ALIVE_TIMEOUT parameter to a value that is lower than the time used to detect idle connections in your network. For example, if your network disables idle TCP connections after 15 seconds, you can change the

KEEP_ALIVE_TIMEOUT parameter to a value of 10 seconds:

KEEP_ALIVE_TIMEOUT=10

4. Save the comms.ini file.

5. For the changes to take effect, use the configuration tool to restart all instances of InfoSphere CDC.

InfoSphere CDC will now send messages over the TCP connection every 10 seconds.

Enabling SQL statements in Management Console

InfoSphere CDC lets you execute SQL statements after it applies a table-level clear or refresh operation to a target table. You can specify SQL statements in the

Additional SQLdialog box in Management Console. By default, this feature is disabled in InfoSphere CDC for security reasons. You can enable this feature by creating a table called TS_SQL_EXECAUTH in the database where you installed InfoSphere CDC. The structure of the table is unimportant, and you must create the table using the same schema as the metadata tables during the configuration of InfoSphere CDC. For more information about specifying SQL statements in

Management Console, see Specifying SQL to control refresh operations in your Management Console documentation.

See also:

“To enable SQL statements in Management Console”

To enable SQL statements in Management Console

1. Locate the database on the target server that you created for InfoSphere CDC. Depending on how you are using InfoSphere CDC, this is the database you want InfoSphere CDC to replicate to or from.

Note: During installation, InfoSphere CDC places metadata tables in the database necessary for InfoSphere CDC processes.

2. If you want to enable the specification of SQL statements, create a table named TS_SQL_EXECAUTH in the database.

Note: The table can have any structure and must be created in the schema you specified when you configured InfoSphere CDC.

Related concepts

“Metadata tables” on page 37

Privileges for Oracle DBA and InfoSphere CDC users

After installing and configuring InfoSphere CDC on Oracle, you also need to set privileges for the Oracle DBA and users.

See also:

“Privileges required by the Oracle DBA” “Privileges required by users” on page 32

Privileges required by the Oracle DBA

The Oracle DBA must be explicitly granted SELECT privileges on the following dynamic system views with the grant option:

v _{sys.all_coll_types} v _{sys.all_constraints} v _{sys.all_cons_columns} v _{sys.all_type_attrs} v _{sys. attrcol$} v _sys.ccol$ v _sys.cdef$ v _sys.col$

v _sys.coltype$ v _{sys.dba_constraints} v _{sys.dba_cons_columns} v _{sys.dba_indexes} v _{sys.dba_ind_columns} v _{sys.dba_mviews} v _{sys.dba_objects} v _{sys.dba_profiles} v _{sys.dba_roles} v _{sys.dba_rollback_segs} v _{sys. dba_sequences} v _{sys.dba_tables} v _{sys.dba_tablespaces} v _{sys.dba_tab_columns} v _{sys.dba_users} v _{sys.hist_head$} v _sys.icol$ v _sys.ind$ v _sys.lob$ v _{sys.nls_database_parameters} v _sys.obj$ v _{sys.resource_cost} v _sys.tab$ v _sys.user$ v _{sys.user_role_privs} v _{sys.v_$archived_log} v _{sys.v_$controlfile} v _{sys.v_$database} v _{sys.v_$log} v _{sys.v_$logfile} v _{sys.v_$log_history} v _{sys.v_$mystat} v _{sys.v_$session} v _{sys.v_$version}

Privileges required by users

The installation program grants Oracle DBA privileges to the InfoSphere CDC user and these include the privileges required users to work with InfoSphere CDC for Oracle. However, if you decide to alter these DBA privileges or revoke the DBA role from the InfoSphere CDC user, then you must grant specific privileges so that users can replicate data and perform table operations.

See also:

“Operation privileges” on page 33 “View privileges” on page 33

Operation privileges

By default, the installation program grants the Oracle DBA role to the user and these include the privileges required for users to perform required operations. However, if you decide to alter these DBA privileges or revoke the DBA role from the user, then you must grant the following privileges so that users can perform required operations.

Note: For privileges that specify ANY TABLE, you may choose to grant privileges only on a specific table. As tables get added to the InfoSphere CDC configuration, you may need to monitor the tables on which users have privileges.

Privilege Required For

CREATE ANY INDEX Creating tables on the target for a user other than the InfoSphere CDC user.

CREATE ANY TABLE Creating tables that belong to a user other than the InfoSphere CDC user.

DELETE ANY TABLE Replicating to tables that belong to a user other than the InfoSphere CDC user.

EXECUTE ANY PROCEDURE Executing stored procedures that belong to a user other than the InfoSphere CDC user.

INSERT ANY TABLE Replicating to tables that belong to a user other than the InfoSphere CDC user.

LOCK ANY TABLE Refresh target tables that belong to a user other than the InfoSphere CDC user.

SELECT ANY TABLE Use tables that belong to a user other than the InfoSphere CDC user.

UPDATE ANY TABLE Replicate to tables that belong to a user other than the InfoSphere CDC user.

View privileges

Users requires special privileges on the following views: v _{dba_col_comments} v _{dba_constraints} v _{dba_cons_columns} v _{dba_indexes} v _{dba_ind_columns} v _{dba_log_groups} v _{dba_objects} v _{dba_sys_privs} v _{dba_tables} v _{dba_tab_columns} v _{dba_tab_comments} v _{dba_tab_privs} v _{and dba_users}

These views are equivalent to the all_views (for example all_col_comments and all_constraints) but the all_views exclude those tables for which the user executing a query does not have permissions. This filtering imposes a noticeable performance cost on systems with large numbers of tables and users.

You must grant users SELECT privileges on the following dynamic system views with the grant option:

v _{dba_tab_cols} v _sys.con$ v _{sys.v_$transaction} v _{sys.v_$archived_log} v _{sys.v_$database} v _{sys.v_$controlfile} v _{sys.v_$log} v _{sys.v_$log_history} v _{sys.v_$logfile} v _{sys.v_$mystat} v _{sys.v_$session} v _{sys.v_$version} v _{sys.all_coll_types} v _{sys.all_cons_columns} v _{sys.all_constraints} v _{sys.all_type_attrs} v _{sys.dba_constraints} v _{sys.dba_cons_columns} v _{sys.dba_indexes} v _{sys.dba_ind_columns} v _{sys.dba_mviews} v _{sys.dba_objects} v _{sys.dba_profiles} v _{sys.dba_roles} v _{sys.dba_rollback_segs} v _{sys.dba_sequences} v _{sys.dba_tab_columns} v _{sys.dba_tables} v _{sys.dba_tablespaces} v _{sys.dba_users} v _{sys.nls_database_parameters} v _{sys.resource_cost} v _{sys.attrcol$, sys.ccol$} v _{sys.cdef$, sys.col$} v _sys.coltype$ v _{sys.hist_head$} v _sys.icol$ v _sys.ind$ v _sys.lob$ v _sys.obj$ v _sys.tab$ v _sys.user$ v _{uuser_role_privs}

For information about privileges and the grant command, see your Oracle documentation.

Metadata tables

InfoSphere CDC maintains a set of metadata tables that represent data about your current replication configuration. These tables are created in the schema and database that you specify in the configuration tool and should be part of the backup strategy for your database. InfoSphere CDC will not replicate these tables. Do not modify the contents of these tables unless requested to do so by your IBM representative.

The names of the metadata tables created by InfoSphere CDC are as follows: v _{DRSESSIONHISTORY}

v _{TS_AUTH}

Note: For all users you added in the Access Manager perspective in

Management Console, make sure you give GRANT SELECT privileges to the TS_AUTH metadata table. For more information on how to add users in the Access Manager perspective in Management Console. For more information, see your Management Console documentation.

v _{TS_BOOKMARK}

v _{TS_CONFAUD—The conflict resolution audit table records information about} conflicts that were resolved using conflict detection and resolution.

Related concepts

“Configuring InfoSphere CDC (UNIX and Linux)” on page 21

Commands for InfoSphere CDC

This section discusses the commands available with InfoSphere CDC. Using these commands you can control replication, manage your tables for replication, monitor replication, and perform various other tasks.

In this section, you will learn:

“Using the InfoSphere CDC commands”

“Setting the TSINSTANCE environment variable” on page 40 “Controlling replication commands” on page 40

“Database transaction log commands” on page 47 “Managing tables for replication commands” on page 53 “Monitoring replication commands” on page 60

“Exporting and importing configuration commands” on page 63 “Continuous Capture and staging store commands” on page 65 “Other commands” on page 67

Using the InfoSphere CDC commands

You can issue InfoSphere CDC commands at a command line prompt or as part of a batch file or shell script. Commands are case-sensitive and are located in the bin directory of your InfoSphere CDC installation directory. You must run the

commands from this directory.

Note: Use the -? flag to list the available parameters for a command and a short description of each parameter. For example, dmstartmirror -?.

Command formats

For each command, the following items of information are provided:

v _{Syntax—Identifies the name of the command and lists the command parameters.} v _{Parameters—Describes each parameter in the command and identifies the values}

that can be specified.

v _{Result—Indicates the values that are returned by the command if it is}

successful. These values can be useful for scripting. This section also specifies the information that is displayed on the screen, if any, as a result of executing the command.

v _{Examples—Provides one or more examples of invoking the command.}

Parameter formats

Note the following conventions in the definition of the command parameters: v _{Angle brackets ( < > ) indicate a mandatory parameter.}

v _{Square brackets ( [ ] ) indicate an optional parameter. If you omit the parameter,} InfoSphere CDC uses a default value.

v _{A vertical bar ( | ) separating one or more parameters indicate that only one of} the parameters in the list can be used. When one or more vertical bars appear in

a list of parameters that is enclosed by square brackets [ ], the choices are limited to the parameters in the list, but you have the option to not specify any of the parameters.

v _{Ellipsis ( ... ) means that a parameter or option can be repeated more than once.} v _{Unless otherwise noted, the commands apply to UNIX and Linux.}

Setting the TSINSTANCE environment variable

Before using InfoSphere CDC commands, you can set the TSINSTANCE environment variable to the name of your InfoSphere CDC instance.

After you set the TSINSTANCE environment variable, you no longer have to specify the instance name when issuing commands.

UNIX and Linux

The following command is for kshell. You can run similar commands in other shells:

export TSINSTANCE=<instance_name>

where:

v _{<instance_name>is the name of your InfoSphere CDC instance.}

Controlling replication commands

This section contains commands that control replication in InfoSphere CDC. See also:

“dmendreplication - End replication”

“dmrefresh - Refresh subscription” on page 44 “dmstartmirror - Start mirroring” on page 45

dmendreplication - End replication

Use this command to end refresh or mirroring on the specified subscriptions. Ending replication allows you to prepare for transitional activities in your business environment and allows you to move to the next step in your business processes. Here are some examples of transitional activities in your business environment that may require an end to replication:

v _{Initiating a database backup.}

v _{Performing a regularly scheduled reboot of your source database server.} v _{Quiescing your database in preparation for an upgrade.}

v _{Weekly batch processing has just completed.} v _{Preparing for off-line maintenance activities.}

If you are replicating data continuously with Continuous mirroring and business reasons arise that require an end to replication, InfoSphere CDC provides multiple options that suit most business needs. If your business requirements dictate that replication must end at a particular point in your source database log because the target database must be in a known state when replication ends, you can choose from the following Scheduled End to replication options: