How to set up CDC in Oracle XStream mode - 6.1

Talend Big Data Studio User Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
task
Design and Development
EnrichPlatform
Talend Studio

The XStream mode is only available for Oracle v12 with OCI in Talend Studio. The following sections give detailed information about the prerequisites for using CDC in XStream mode for Oracle databases and provide an example of how to configure the CDC environment using the XStream mode in Talend Studio.

Prerequisites for the XStream mode

Before configuring CDC using XStream mode in Talend Studio, the administrator of the Oracle database should do the following:

Activate the archive log mode in Oracle

Connect to the Oracle database as an administrative user and run the following statement to display its archiving information:

archive log list;

If the database is not operating in the archive log mode, run the following statements to activate the archive log mode:

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
Open all PDBs for a CDB in Oracle

During XStream configuration, if the Oracle database is a container database (CDB), you need to ensure that all pluggable databases (PDBs) in the CDB are in open read/write mode.

To view the open mode of PDBs, connect to the Oracle database as an administrative user and run the following statement.

select con_id, dbid, guid, name, open_mode from v$pdbs;

To open PDBs, connect to the Oracle database as an administrative user and run the following statement.

alter pluggable database all open;
Configure an XStream administrator

To configure an XStream administrator, connect to the Oracle database as an administrative user with the right to create users, grant privileges, and create tablespaces, and then proceed with the following steps.

  1. Create a tablespace for the XStream administrator by running the following statement. Skip this step if you want to use an existing tablespace.

    CREATE TABLESPACE xstream_tbs DATAFILE '$ORACLE_HOME/dbs/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  2. Create a new user to act as the XStream administrator by running the following statements. Skip this step to identify an existing user.

    CREATE USER username IDENTIFIED BY password
    DEFAULT TABLESPACE xstream_tbs
    QUOTA UNLIMITED ON xstream_tbs;

    Note

    • If you are creating an XStream administrator in a CDB, the XStream administrator must be a common user. The name of a common user must begin with c## or C##, and you need to include the CONTAINER=ALL clause in the statement.

    • If you are creating an XStream administrator using the Oracle default tablespace, you need to remove the DEFAULT TABLESPACE and QUOTA UNLIMITED ON clauses in the statement.

  3. Grant privileges to the XStream administrator by running the following statements and procedures:

    GRANT DBA TO username;
    GRANT CONNECT, CREATE TABLE TO username;
    GRANT RESOURCE TO username;
    GRANT CREATE TABLESPACE TO username;
    GRANT UNLIMITED TABLESPACE TO username;
    GRANT SELECT_CATALOG_ROLE TO username;
    GRANT EXECUTE_CATALOG_ROLE TO username;
    GRANT CREATE SEQUENCE TO username;
    GRANT CREATE SESSION TO username;
    GRANT CREATE ANY VIEW TO username;
    GRANT CREATE ANY TABLE TO username;
    GRANT SELECT ANY TABLE TO username;
    GRANT COMMENT ANY TABLE TO username;
    GRANT LOCK ANY TABLE TO username;
    GRANT SELECT ANY DICTIONARY TO username;
    GRANT EXECUTE ON SYS.DBMS_CDC_PUBLISH to username;
    ALTER USER username QUOTA UNLIMITED ON USERS;
    GRANT CREATE ANY TRIGGER TO username;
    GRANT ALTER ANY TRIGGER TO username;
    GRANT DROP ANY TRIGGER TO username;
    BEGIN
       DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
          grantee => 'username',
          privilege_type => 'CAPTURE',
          grant_select_privileges => TRUE);
    END;
    /
    BEGIN
       DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
          grantee => 'username',
          privilege_type => 'APPLY',
          grant_select_privileges => TRUE);
    END;
    /

Note that if you are granting privileges to a common user, you need to include the CONTAINER=ALL clause in the above GRANT statements and procedures.

How to configure CDC using XStream mode

This section provides detailed information about configuring the XStream Out and XStream In in Talend Studio.

Configure XStream Out in Talend Studio

To configure XStream Out in Talend Studio, do the following:

  1. In the Repository tree view, set up a database connection using OCI connection type to an Oracle database, and then retrieve the schema of the source table in which data changes are to be captured. In this example, the source table is PERSON. For detailed information about how to set up a database connection and retrieve table schemas, see Centralizing database metadata.

  2. Right-click CDC Foundation under the newly created Oracle database connection and select Create CDC from the contextual menu. The [Create Change Data Capture] dialog box opens up.

  3. Select XStream mode and click Show sample initialization script. The [Sample Initialization Script] dialog box opens up.

    Note that this is only a sample script for configuring XStream on an Oracle 12c server, you need to update the username, password, and tablespace information according to your settings and run the statements and procedures in Oracle. For detailed information, see Prerequisites for the XStream mode.

    Click OK to close the [Sample Initialization Script] dialog box.

    Click Finish to create CDC in Oracle and close the [Create Change Data Capture] dialog box.

  4. Right-click the source table and select add CDC from the contextual menu.

  5. Right-click the source table and select Generate XStreamsOut Script from the contextual menu. The [XStreamsOut generation script] dialog box opens up.

  6. Fill in the XStreams server name field with the outbound server name. The name must be a unique one.

    Identify the source table(s) by selecting the check box(es) in the corresponding Include in script column.

    Click Generate Script. The [XStreamsOut Script] dialog box pops up.

  7. Click Execute to create the XStream outbound server in Oracle.

    Note that if the script execution fails, you can connect to the Oracle database as an XStream administrator and run the script in Oracle.

  8. Connect to the Oracle database as an XStream administrator and check the status of the outbound server by running the following statement:

    select apply_name, status from dba_apply;

    If you need to remove an outbound server, run the following statements:

    exec DBMS_XSTREAM_ADM.DROP_OUTBOUND('xout'); 
    exec DBMS_XSTREAM_ADM.REMOVE_XSTREAM_CONFIGURATION(container => 'ALL');
Configure XStream In in Talend Studio

To configure XStream In in Talend Studio, do the following:

  1. In the Repository tree view, set up a database connection using OCI connection type to an Oracle database, and then retrieve the schema of the target table to which data changes will be replicated. In this example, the target table is PERSON_BAK. For detailed information about how to set up a database connection and retrieve table schemas, see Centralizing database metadata.

  2. Right-click CDC Foundation under the newly created Oracle database connection and select Create CDC from the contextual menu. The [Create Change Data Capture] dialog box opens up.

  3. Select XStream mode in the Options area and click Show sample initialization script. The [Sample Initialization Script] dialog box opens up.

    Note that this is only a sample script for configuring XStream on an Oracle 12c server, you need to update the username, password, and tablespace information according to your settings and run the statements and procedures in Oracle. For detailed information, see Prerequisites for the XStream mode.

    Click OK to close the [Sample Initialization Script] dialog box.

    Click Finish to create CDC and close the [Create Change Data Capture] dialog box.

  4. Right-click the target table and select add CDC from the contextual menu.

  5. Right-click the target table and select Generate XStreamsIn Script from the contextual menu. The [XStreamsIn generation script] dialog box opens up.

  6. Fill in the XStreams server name field with the inbound server name.

    Fill in the Queue name field with the name of the inbound server's queue.

    Click Generate script. The XStream In script will be generated and displayed.

  7. Click Execute to create the XStream inbound server in Oracle.

    Note that if the script execution fails, you can connect to the Oracle database as an XStream administrator and run the script in Oracle.

  8. Connect to the Oracle database as an XStream administrator and check the status of the inbound server by running the following statement:

    select apply_name, status from dba_apply;

    If the inbound server is disabled, start it by running the following statement:

    exec DBMS_APPLY_ADM.START_APPLY('xin');

    If you need to remove an inbound server, run the following statements:

    exec DBMS_XSTREAM_ADM.DROP_INBOUND('xin');
    exec DBMS_XSTREAM_ADM.REMOVE_QUEUE('xin_queue'); 
    exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'xin');

How to extract and synchronize data changes in XStream mode

After setting up the CDC environment using XStream mode, you can now design a Job in Talend Studio using the Oracle CDC component tOracleCDC to extract data changes from the source system and tOracleCDCOutput to replicate the data changes to the target system.

For an example of how to use the Oracle CDC components and for more information about the properties and the parameters of the Oracle CDC components, see Talend Components Reference Guide.