0 / 0
Replicating Oracle data

Replicating Oracle data

You can replicate data from Oracle with Data Replication.

To set up replication for Oracle data, first configure your Oracle database as a source, and then create a connection.

Supported versions

Oracle 19c R1 (19.1)+

Restriction

You can replicate only some Oracle data types. See Supported Oracle data types.

Configuring Oracle as a source

Before you start a replication with an Oracle connection, you need to ensure that database, user accounts, and schema requirements are satisfied.

  1. Configure an Oracle database. When you configure an Oracle connection, you are prompted for the name of the Oracle database from which you want Data Replication to replicate data. Before creating the connection, ensure that this Oracle database exists and that you create and set up a database user that has access to it. Data Replication supports mapping tables that are found in multiple schemas within the same database. Ensure that Oracle XStream is enabled through the enable_goldengate_replication parameter.

    Some of the Oracle XStream configuration must be prepared in advance before you create an Oracle connection.

  2. Enable enable_goldengate_replication. You need to set the enable_goldengate_replication system parameter on your source database to enable XStream replication capability in Oracle. You might use the following statement:

    ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH
    

All instances in Oracle RAC must have the same setting. Refer to the Oracle documentation to understand the impact of enabling this parameter on your database.

  1. Create a user account for the Oracle database.

    When you replicate data from Oracle databases, Data Replication processes the database logs. You can configure Data Replication for local capture, where logs are processed in the same database as the schemas you are replicating. Alternatively, you can configure Data Replication to use a separate Oracle XStream connection to process logs in a different database. If you use local capture, you need a single Oracle connection. If you use a separate Oracle XStream database for log processing, then you will need two Oracle connections: one to connect to the database that contains the schema you are replicating, and one to connect to the database where Oracle XStream processes the logs. This log processing database can be an Oracle downstream database.

    Review required privileges for Oracle users. Before you configure Oracle connections, review the list of privileges that are required by Oracle users. You are required to grant these privileges to users by running SQL scripts.

    The following sample scripts can help provide appropriate privileges to the Oracle database user and the Log Processing Database user.

Sample script createuser-ora-nodba.sql

Copy this script, and replace the values in angle brackets. Run this script to create an Oracle database user for replicating data from Oracle.

/* _______________________________________________________ {COPYRIGHT-TOP} _____
** Licensed Materials - Property of IBM
** IBM Data Replication Cartridge for IBM Cloud Pak for Data
** 5900ASF
**
** (c) Copyright IBM Corp. 2001, 2022 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
* ________________________________________________________ {COPYRIGHT-END} _____*/

--This script does not grant dba to the data replication user.

-- create user
CREATE user <user> identified by <password>
default tablespace <tablespace_name> temporary tablespace <temporary_tablespace_name>;

-- Grant basic roles
grant connect to <user>;
grant resource to <user>;
grant select_catalog_role to <user>;
grant unlimited tablespace to <user>;

-- Table DDL permissions
grant create any table to <user>;
grant alter any table to <user>;
grant drop any table to <user>;
grant lock any table to <user>;

-- Table DML permissions
grant select any table to <user>;
grant flashback any table to <user>;
grant insert any table to <user>;
grant update any table to <user>;
grant delete any table to <user>;

-- Index and view DDL permissions
grant create any index to <user>;
grant alter any index to <user>;
grant drop any index to <user>;
grant create any view to <user>;
grant drop any view to <user>;

-- Procedure permissions
grant create any procedure to <user>;
grant execute any procedure to <user>;

-- Permission to perform select on the v_$ tables
grant select any dictionary to <user>;

-- General system views
grant select on sys.v_$database to <user>;
grant select on sys.v_$controlfile to <user>;
grant select on sys.v_$version to <user>;
grant select on sys.nls_database_parameters to <user>;

-- Archive and redo logs
grant select on sys.v_$log to <user>;
grant select on sys.v_$logfile to <user>;
grant select on sys.v_$archived_log to <user>;
grant select on sys.v_$log_history to <user>;

-- Sessions and transactions
grant alter session to <user>;
grant select on sys.v_$session to <user>;
grant select on sys.gv_$session to <user>;
grant select on sys.v_$transaction to <user>;
grant select on sys.v_$mystat to <user>;

-- Tables, indexes, columns and related views
grant select on sys.all_coll_types to <user>;
grant select on sys.all_type_attrs to <user>;
grant select on sys.dba_tables to <user>;
grant select on sys.dba_tab_comments to <user>;
grant select on sys.dba_tab_columns to <user>;
grant select on sys.dba_col_comments to <user>;
grant select on sys.dba_indexes to <user>;
grant select on sys.dba_ind_columns to <user>;
grant select on sys.all_constraints to <user>;
grant select on sys.dba_constraints to <user>;
grant select on sys.all_cons_columns to <user>;
grant select on sys.dba_cons_columns to <user>;
grant select on sys.tab$ to <user>;
grant select on sys.ind$ to <user>;
grant select on sys.lob$ to <user>;
grant select on sys.col$ to <user>;
grant select on sys.icol$ to <user>;
grant select on sys.coltype$ to <user>;
grant select on sys.attrcol$ to <user>;
grant select on sys.ccol$ to <user>;
grant select on sys.cdef$ to <user>;

-- Miscellaneous other objects
grant select on sys.obj$ to <user>;
grant select on sys.dba_mviews to <user>;
grant select on sys.dba_objects to <user>;
grant select on sys.dba_sequences to <user>;
grant select on sys.hist_head$ to <user>;
grant select on sys.resource_cost to <user>;

-- Storage
grant select on sys.dba_tablespaces to <user>;
grant select on sys.dba_rollback_segs to <user>;

-- Permissions
grant select on sys.dba_users to <user>;
grant select on sys.dba_sys_privs to <user>;
grant select on sys.dba_tab_privs to <user>;
grant select on sys.dba_profiles to <user>;
grant select on sys.dba_roles to <user>;
grant select on sys.user$ to <user>;
grant select on user_role_privs to <user>;

exit;

Sample script createuser-ora-xstream.sql

Copy this script and replace the values in angle brackets. Run the sample script createuser-ora-nodba.sql before you run createuser-ora-xstream.sql. If you configure Data Replication to use a local capture where logs are processed in the same database as the schemas you replicate, run createuser-ora-nodba.sql and createuser-ora-xstream.sql against the same database and use the same value for <user>. If you prefer to have Oracle XStream process logs in a separate database from the schemas you replicate, then optionally specify a different user and run the script against the database.

/* _______________________________________________________ {COPYRIGHT-TOP} _____
** Licensed Materials - Property of IBM
** IBM Data Replication Cartridge for IBM Cloud Pak for Data
** 5900ASF
**
** (c) Copyright IBM Corp. 2001, 2022 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
* ________________________________________________________ {COPYRIGHT-END} _____*/

-- This script is a continuation of createuser-ora-nodba.sql script with
-- additional changes needed for data replication user as well as XStream Log
-- Processing Database user. Process all needed privileges from the script
-- createuser-ora-nodba.sql prior to continuing with this script.

-- XStream privileges on data replication User

-- Expected to be used for DBMS_CAPTURE_ADM.BUILD
-- to define XStream Capture initialization points

grant execute on DBMS_CAPTURE_ADM to <user>;

-- Optional privilege that is expected to be used on
-- the target engine required for Recursion Prevention
-- in Bidirectional replication.

grant execute on DBMS_XSTREAM_ADM to <user>;

-- Enable enable_goldengate_replication

-- You need to set the enable_goldengate_replication system parameter on your source
-- database to enable XStream replication capability in Oracle.

ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;

-- to enable the enable_goldengate_replication system parameter.
-- All instances in Oracle RAC must have the same setting. Refer to Oracle documentation
-- for further understanding on the impact of enabling this parameter on your database.

-- Setting up Oracle XStream user account.

-- Most work in data replication is done using the Oracle user account.
-- When data replication needs to interact with Oracle XStream it will use the Oracle XStream account.
-- An Oracle XStream account needs sufficient privileges to comply with Oracle XStream user requirements.
-- In a Pluggable database environment, the Oracle user account has all the granted access to the
-- Pluggable database when replication is configured for a pluggable database, whereas the XStream
-- user account has to be defined in the root container database as per Oracle XStream requirements.
-- All Oracle XStream related objects are defined and managed in the container database in this case.

CALL DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
  grantee                 => <Log Processing Database User>,
  privilege_type          => 'CAPTURE');

exit;
  1. Configure the log processing database. When you create a Data Replication asset, you are prompted for the name of the Oracle log processing database that you want Data Replication to use. This database serves an XStream outbound database to which Data Replication connects and attaches to process Logical Change Records that are supplied by the Oracle XStream API. For local Oracle XStream capture and outbound server, this is the same database as before. For pluggable databases, the log processing database is the root container database. For downstream capture, the log processing database is the downstream database. The log processing database also requires enable_goldengate_replication.

  2. Optional: Configure a downstream log processing database. A number of steps are required to configure either real-time downstream capture or archive-log downstream capture. Review the Oracle XStream documentation to configure database links and log file transfer.

  3. Set up Oracle XStream user account. Most work in Data Replication is done by using the Oracle user account. When Data Replication needs to interact with Oracle XStream, it uses the Oracle XStream account. An Oracle XStream account needs sufficient privileges to comply with Oracle XStream user requirements. In a pluggable database environment, the Oracle user account has all the granted access to the pluggable database when replication is configured for a pluggable database. However, the XStream user account must be defined in the root container database according to Oracle XStream requirements. All Oracle XStream related components are defined and managed in the root container database in this case. You might use DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE to grant XStream user account privileges. Oracle XStream outbound database credentials are case-sensitive. For more information, review the Oracle documentation.

  4. Create a schema or choose an existing schema for your database metadata tables. You have to specify this schema when you configure Data Replication.

Connecting to Oracle in a project

For Private connectivity, to connect to a database that is not externalized to the internet (for example, behind a firewall), you must set up a secure connection.

To connect to Oracle in a project in Cloud Pak for Data as a Service, see Oracle connection.

Starting Data Replication

To create a Data Replication asset:

  1. Click the Assets tab in the project.
  2. Click New asset > Replicate data.
  3. Enter a name.
  4. Click Connections.
  5. On the Source options page, select Oracle from the list of connections or click Add connection to create a new connection.
  6. Oracle connections require additional parameters. You have two Oracle XStream options: a. Select Local capture to process the logs in the same database as the source connection. a. Choose a separate Oracle XStream connection to process logs in a separate database. i. If you do not select Real-time downstream capture, your options include PDB/CDB and archived log downstream capture. i. If you select Real-time downstream capture, a separate Oracle XStream connection is used to process logs in a different database.
  7. Click Select data, select a schema, and optionally a table from the schema.
  8. Click Target options and select the Oracle connection from the list.
  9. Click Review.
  10. Review the summary, then click Create.

Parent topic: Supported Data Replication connections

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more