Sync Oracle data

Notes

• Data synchronization is supported only for Oracle 9i, 10g, 11g, 12c, and 19c.

• If the `connect_time` parameter is set for the Oracle database, real-time synchronization may fail.

  Run the following command to check the `connect_time` parameter settings. Replace username with the database account name.

  ,

  select resource_name, limit from dba_profiles where profile=( select profile from dba_users where username = 'username');

• You must reserve enough storage space for archived logs. This prevents the storage from becoming full and affecting Oracle database operations.

Procedure

To sync data from an Oracle database to Tablestore using Tapdata Cloud, perform the following steps:

1. Configure the Oracle database and an authorized account. For more information, see Step 1: Configure the Oracle database and an authorized account.

2. Deploy Tapdata Agent on the machine where the Oracle database resides or on a machine in the same network. For more information, see Step 2: Deploy Tapdata Agent.

   The Tapdata Agent (Agent) is a core component for data synchronization, data development, and processing heterogeneous data. It uses streaming technology to retrieve data from the source, process and transform it, and send it to the target.

   Important

   Your data does not pass through Tapdata Cloud, and Tapdata Cloud does not upload or store your data.

3. Use Tapdata Cloud to connect to the source and destination databases. For more information, see Step 3: Connect to the source and destination databases.

4. Create a data replication task to replicate data from the source database to the destination database. For more information, see Step 4: Configure a data replication task.

Step 1: Configure the Oracle database and an authorized account

Before you start data synchronization, you must enable ARCHIVELOG mode, supplemental logging, identification key logging, and full supplemental logging for the Oracle database. Then, you must create an account for the data sync task.

1. Log on to the Oracle database as a user with DBA permissions.

2. Enable ARCHIVELOG mode.

   Run the following command to check if ARCHIVELOG mode is enabled.

   select log_mode from v$database;

   If the command returns ARCHIVELOG, the mode is already enabled and you can skip this step. Otherwise, perform the following steps to enable it.

   1. Run the following command to shut down the database.

      Important

      Perform this operation during off-peak hours to avoid affecting your business.

      shutdown immediate;

   2. Run the following command to start and mount the database.

      startup mount;

   3. Run the following command to enable archiving and open the database.

      alter database archivelog;
      alter database open;

3. Enable supplemental logging. Run the command that corresponds to your database version.

   Database version	Command
   Oracle 9i	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
   Oracle 10g	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER system switch logfile; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   Oracle 11g
   Oracle 12c	Run the following command to confirm whether supplemental logging is enabled. SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database; If the first two columns of the result are Yes or Implicit, it means that only Identification Key Logging is enabled. You also need to enable Full Supplemental Logging.

4. Enable identification key logging.

   You can choose to enable it for a single table or for all tables as needed.

   • To enable identification key logging for a single table:

     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
     ALTER TABLE schema name.table name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

   • To enable identification key logging for all tables:

     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

5. Enable full supplemental logging.

   You can choose to enable it for a single table or for all tables as needed.

   • To enable full supplemental logging for a single table:

     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
     ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

   • To enable full supplemental logging for all tables:

     ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

6. Run the following command to commit the changes.

   ALTER SYSTEM SWITCH LOGFILE;

7. Create and grant permissions to an account for the data sync task. Run the command that corresponds to your database version.

   Database version	Command
   Oracle 10g	CREATE USER username IDENTIFIED BY password; GRANT create session, alter session, execute_catalog_role, select any dictionary, select any transaction, select any table, create any table, create any index, unlimited tablespace to user name;
   Oracle 11g
   Oracle 12c (standard mode)	CREATE USER username IDENTIFIED BY password; GRANT create session, alter session, select any dictionary, select any transaction, logmining, execute_catalog_role, create any table, create any index, unlimited tablespace TO username; Run the `GRANT` command repeatedly to grant `SELECT` permissions as needed.
   Oracle 12c (multi-tenant mode)	Important In Oracle 12c multi-tenant mode, create the user in the CDB. The username must follow the c##name convention. ALTER SESSION SET CONTAINER=cdb$root; CREATE USER username IDENTIFIED BY password CONTAINER=all; GRANT create session, alter session, set container, select any dictionary, select any transaction, logmining, execute_catalog_role, create any table, create any index, unlimited tablespace TO username CONTAINER=all; ALTER SESSION SET CONTAINER=pdb; Run the `GRANT` command repeatedly to grant `SELECT` permissions as needed. When the Oracle database is used as the source database, use this account for Java Database Connectivity (JDBC) identity verification. Important When you use this account for JDBC identity verification, you must use the full username, including c##, as the username for the JDBC connection.

Step 2: Deploy Tapdata Agent

Data forwarding is often time-sensitive. We recommend that you deploy Tapdata Agent on the same machine as the Oracle database or on a machine in the same network. This greatly reduces the impact of network latency.

1. Log in to the Tapdata Cloud platform.

2. In the left-side navigation pane, click Agent Management.

3. On the Agent Management page, click Create Agent.

4. In the Agent Download and Installation dialog box, select Linux (64 bit), and then follow the on-screen instructions to install the Agent.

   Important

   Before you begin, ensure that Java 1.8 is installed in your deployment environment and that the required environment variables are configured correctly.

   1. Log in to the machine where you plan to deploy the Agent.

   2. Run the following command to create a tapdata directory for the Tapdata Agent installation and deployment.

      mkdir tapdata

   3. Click Copy for the command in step 3 of the dialog box, and then run it in the tapdata directory.

      Note

      Running this command automatically downloads, deploys, and starts the Tapdata Agent.

   Wait for the command to complete. The Tapdata Agent is then installed and started.

   The following output indicates a successful Tapdata Agent startup.

   WORK DIR:/root/tapdata
   Init tapdata...
   Download components...
   Downloading 100.00% 225.48 MB, Total size: 225.48 MB, avg speed: 12.3 MB/s, remaining time: 0m 0s
   Downloading 100.00% 0.00 KB .Total size: 1.07 KB
   Downloading 100.00% 0.00 KB .Total size: 0.14 KB
   organize files...
   WORK DIR:/root/tapdata
   Testing JDK...
   java version:1.8
   Java environment OK.
   The max memory will be used by Tapdata is 1119M
   Waiting for the flow engine to start \
   FlowEngine is startup at : 2022-12-22 14:54

Step 3: Connect to the source and destination databases

Use Tapdata Cloud to connect to the source and destination databases.

1. Log on to the Tapdata Cloud platform.

2. In the navigation pane on the left, click Connection Management.

3. Connect to the Oracle database that serves as the source database.

   1. On the Connection Management page, click Create Connection.

   2. In the Create Connection dialog box, on the Certified Data Source tab, click Oracle.

   3. On the Create Connection page, configure the data source parameters as described in the following table.

      Parameter	Description
      Connection Name	The name of the data connection. Set it as needed. Connection names must be unique.
      Connection Type	You can use Oracle as a source or a destination. Set this to Source and Destination or Source.
      Connection Method	The method to connect to the Oracle database. Valid values: SID: The database instance name. This is the name used for interaction between the database and the operating system. Service Name: The service name. If the database has a domain name, the Service Name is the same as the Global DB Name. If the database does not have a domain name, the Service Name is the same as the database name.
      Database Endpoint	The database endpoint.
      Port	The service port of the database.
      SID	The database instance name. This parameter is required when you set Connection Method to SID.
      Service Name	The service name. This parameter is required when you set Connection Method to Service Name.
      Schema	The schema name. One connection corresponds to one schema. Note To connect to multiple schemas, create multiple data connections.
      Other Connection String Parameters	Additional connection parameters. The default value is empty.
      Account	The database account.
      Password	The database password.
      Multi-tenant Mode	If your Oracle database is in multi-tenant mode, turn on the Multi-tenant Mode switch and enter the PDB information.
      Log Plug-in	Keep the default value, logMiner.
      Time Zone for Time Data Types	By default, this is the time zone used by the database. You can also specify a time zone based on your business needs.
      Included Tables	The tables to include. Valid values: All (default): Includes all tables in the database. Custom: Specifies the tables to include. Separate multiple tables or expressions with commas (,). Expressions can use an asterisk (*) as a wildcard for any number of characters.
      Excluded Tables	Excludes specified tables from the tables matched by the inclusion rule. Turn on the Excluded Tables switch and specify the tables to exclude. Separate multiple tables or expressions with commas (,). Expressions can use an asterisk (*) as a wildcard for any number of characters.
      Agent Settings	The Agent to use. Valid values: Platform-assigned (default): The platform assigns a node for the connection. User-specified: Set this parameter to User-specified and select the Agent to use.
      Model Loading Frequency	If the number of models in the data source exceeds 10,000, Tapdata Cloud periodically refreshes the models based on this parameter.

   4. Click Connection Test. After the test passes, click Save.

      Note

      If the connection test fails, follow the on-screen instructions to fix the issue.

4. Connect to the destination Tablestore instance.

   1. On the Connection Management page, click Create Connection.

   2. In the Create Connection dialog box, on the Beta Data Sources tab, click Tablestore.

   3. On the Create Connection page, configure the data source parameters as follows.

      Parameter	Description
      Connection Name	The name of the data connection. This name must be unique.
      Connection Type	The value is fixed to Target. This data connection can only be used as a destination in Tapdata Cloud, not as a source.
      Endpoint	The endpoint of the Tablestore instance. For more information, see Endpoints.
      Instance Name	The name of the Tablestore instance. For more information, see Instances.
      AccessKey ID	The AccessKey ID and AccessKey Secret of the RAM user. Note When using a temporary access credential from STS, use the AccessKey ID and AccessKey Secret from that credential.
      AccessKey Secret
      AccessKey Token	The token for the temporary STS credential. This parameter is required only when using an STS credential.
      Agent Settings	The agent to use for the connection. Valid values: Automatic allocation by the platform (Default): The platform allocates nodes to provide access. Manual specification: After you set this parameter to Manual specification, select the Agent to use.
      Model Loading Frequency	If a data source contains more than 10,000 models, Tapdata Cloud periodically refreshes them at the specified frequency.

   4. Click Connection Test. If the test is successful, click Save.

      Note

      If the connection test fails, follow the on-screen instructions to resolve the issue.

Step 4: Configure a data replication task

You can configure a data replication task to achieve real-time synchronization between homogeneous or disparate data sources. This feature applies to scenarios such as data migration, data synchronization, data disaster recovery, and read performance scaling.

1. Log on to the Tapdata Cloud platform.

2. In the navigation pane on the left, click Data Replication.

3. On the Data Replication page, click Create.

4. On the left side of the page, drag the Oracle and Tablestore databases to the canvas on the right, and then connect them.

5. Click the Oracle database and configure the parameters in the right-side panel as described in the following table.

   Parameter	Description
   Node Name	The default value is the connection name. Set it as needed.
   DDL Event Collection	DDL statement synchronization to Tablestore is not supported. You do not need to configure this parameter.
   Select Tables	Select the tables to replicate. Valid values: Select by table name: Selects tables to replicate by their names. In the Tables to Replicate area, select the tables and click the icon to complete the setting. You can also click Batch Select Tables and enter multiple table names to select them in a batch. Separate multiple table names with commas (,). Match by regular expression: Uses a regular expression to match tables to replicate. If you use this method, new tables in the source database that match the expression are automatically synced to the destination database.
   Batch Read Size	The number of records to read in each batch during full synchronization. The default value is 100. In most cases, you can use the default value.
   Continuous Log Mining	Specifies whether to enable continuous log mining. This is enabled by default. Configure this based on your Oracle version. If your Oracle version is earlier than 19c, enable this option for lower latency. If your Oracle version is 19c or later, you must disable this option. Important If your Oracle version is 19c or later, make sure to disable this option.
   Log Backlog Size	The maximum log backlog size for continuous log mining. This parameter can be set only when the Continuous Log Mining option is enabled. Set this based on the source update frequency. If the source update frequency is low, set a lower value for lower latency. If the source update frequency is high, set a higher value for higher throughput. You can calculate this value using the formula: Source data change QPS / 10. The recommended value is 1 to 1,000.
   Mining Threads	The number of threads for log mining. This increases the log mining speed at the cost of higher database CPU usage. This parameter can be set only when the Continuous Log Mining option is disabled. If the source update frequency exceeds 10,000, set this value to a maximum of 8.
   Large Transaction Event Threshold	The threshold at which a transaction event is defined as a large transaction. The default value is 10,000. When the number of transaction events exceeds this value, the large transaction logic is triggered. Important Setting this value too high may affect memory. Large transactions are cached to the local disk. If the replication task fails, make sure to clear the disk.
   Associated Key Update	Specifies whether to update associated keys. This is enabled by default. Disabling the Associated Key Update option can improve performance, but updates to associated keys are ignored.
   Enable LOB Type Sync (BLOB, CLOB, NCLOB)	Specifies whether to sync LOB types. This is enabled by default. Disabling the Enable LOB Type Sync (BLOB, CLOB, NCLOB) option can improve performance, but LOB type parsing becomes unreliable.
   Uncommitted Transaction Lifetime (minutes)	The maximum retention period for uncommitted transactions. The default value is 60. The unit is minutes. Long-running uncommitted transactions cause the task to start mining from that transaction every time it starts or stops, which affects performance. Therefore, transactions that are not committed within this period are cleared.

6. Click the Tablestore database to preview the data structure and configure advanced settings.

   Parameter	Description
   Node Name	The default value is the connection name. Set it as needed.
   Inferred Result	View the table schema, such as field names and field types, and set the field types as needed. Note To adjust a field type, click the icon in the Destination Field Type column and complete the settings in the dialog box that appears. A single Tablestore table can have a maximum of 32 columns. If the Oracle source table has more than 32 columns, drag a field editing node from the left of the page. Connect this node between the Oracle source and the Tablestore destination. Then, in the field editing node, mask the business-irrelevant columns to meet the requirement.
   Advanced Settings
   Duplicate Data Processing Policy	The policy for handling existing data in the destination table. The default value is Keep Original Schema and Data in Destination. If the destination table is empty and its schema is different from the source table, you can select Purge Original Table Schema and Data in Destination.
   Data Write Mode	The method for writing data to Tablestore. Valid values: Process by event type: Sets the data write policy for insert, update, and delete events separately. Append-only: Processes only insert events and discards update and delete events.
   Data Write Policy	The policy for writing data when processing by event type. This parameter can be set only when you set Data Write Mode to Process by event type. The policy for insert events is Update on Existing. The policy for update and delete events is Discard on Non-existing.
   Full Multi-threaded Write	The number of concurrent threads for full data writes. The default value is 8. You can adjust this value based on the write performance of the destination.
   Incremental Write Threads	The number of concurrent threads for incremental data writes. This is disabled by default. After you enable the Incremental Write Threads option, you can adjust the value based on the write performance of the destination.

7. Optional: Click the icon at the top of the canvas and configure the task properties as described in the following table.

   Parameter	Description
   Task name	A custom name for the synchronization task.
   Synchronization type	Specifies the data synchronization method. The following options are available: Full + incremental: After completing a full data synchronization, the task synchronizes incremental data. Full: Copies all existing data from the source to the target. Incremental: Copies new data and data changes from the source to the target in real time.
   Task description	An optional description for the task.
   Advanced settings
   Scheduled start time	Specifies when the task should start. To specify a start time, turn on the Scheduled start time switch and select a date and time.
   Data validation	If you turn on the Data validation switch, the task automatically performs full and incremental validation on the synchronized data to ensure consistency. The number of inconsistent rows is displayed on the monitoring page. Important If a task meets any of the following conditions, the task will not be validated, even if you enable the Data Validation switch. The task includes an intermediate processing node. The source connection does not support validation. The target connection does not support validation.
   Incremental concurrent write	If you turn on the Incremental concurrent write switch, the task writes source data to the target concurrently during incremental synchronization.
   Incremental lag threshold	Helps you quickly determine if the replication task is lagging. Turn on the Incremental lag threshold switch and set a threshold. During incremental synchronization, the task is considered lagging if its latency exceeds this threshold. The default value is 1 second.
   Processor threads	Specifies the number of processor threads.
   Incremental data processing mode	The processing mode for incremental data during synchronization. Valid values are Batch and Row by row.
   Agent settings	Specifies which agent to use. The following options are available: Platform-assigned (default): The platform automatically assigns an agent for the connection. Manually specify: If you set this parameter to Manually specify, select the agent to use.

8. After verifying the settings, click Start.

   After the task starts, you can monitor its status, including metrics such as QPS, latency, and task duration.