Configure Oracle data source

Procedure

1. Create an account and configure account permissions.

   Create a database account with the required Oracle permissions for subsequent operations.

   1. Create an account.

      Run the following command to create a database account:

      create user test identified by example;

   2. Configure permissions.

      Grant the required permissions to the account by running the following commands. Replace 'sync account' with the account name you created:

      grant create session to 'sync account';  // Allows the sync account to log on to the database.
      grant connect to 'sync account';  // Allows the sync account to connect to the database.
      grant select on nls_database_parameters to 'sync account';  // Allows the sync account to query the nls_database_parameters system configuration.
      grant select on all_users to 'sync account';  // Allows the sync account to query all users in the database.
      grant select on all_objects to 'sync account';  // Allows the sync account to query all objects in the database.
      grant select on DBA_MVIEWS to 'sync account';  // Allows the sync account to view the database's materialized views.
      grant select on DBA_MVIEW_LOGS to 'sync account';  // Allows the sync account to view the materialized view logs.
      grant select on DBA_CONSTRAINTS to 'sync account';  // Allows the sync account to view all tables' constraint information.
      grant select on DBA_CONS_COLUMNS to 'sync account';  // Allows the sync account to view constraint-related column information.
      grant select on all_tab_cols to 'sync account';  // Allows the sync account to view column information in tables, views, and clusters.
      grant select on sys.obj$ to 'sync account';  // Allows the sync account to view objects in the sys.obj$ table, which stores all Oracle objects.
      grant select on SYS.COL$ to 'sync account';  // Allows the sync account to view column definitions in SYS.COL$.
      grant select on sys.USER$ to 'sync account';  // Allows the sync account to view the sys.USER$ system table, the default service for user sessions.
      grant select on sys.cdef$ to 'sync account';  // Allows the sync account to view the sys.cdef$ system table.
      grant select on sys.con$ to 'sync account';  // Allows the sync account to view constraint information in sys.con$.
      grant select on all_indexes to 'sync account';  // Allows the sync account to view all database indexes.
      grant select on v_$database to 'sync account';  // Allows the sync account to view the v_$database view.
      grant select on V_$ARCHIVE_DEST to 'sync account';  // Allows the sync account to view the V_$ARCHIVE_DEST view.
      grant select on v_$log to 'sync account';  // Allows the sync account to view the v_$log view, displaying log file information.
      grant select on v_$logfile to 'sync account';  // Allows the sync account to view the v_$logfile view, containing Redo log file information.
      grant select on v_$archived_log to 'sync account';  // Allows the sync account to view the v$archived_log view, containing archived log information.
      grant select on V_$LOGMNR_CONTENTS to 'sync account';  // Allows the sync account to view the V_$LOGMNR_CONTENTS view.
      grant select on DUAL to 'sync account';  // Allows the sync account to view the DUAL table, a virtual table used in select syntax.
      grant select on v_$parameter to 'sync account';  // Allows the sync account to view the v_$parameter view, a dynamic dictionary table.
      grant select any transaction to 'sync account';  // Allows the sync account to view any transaction in the database.
      grant execute on SYS.DBMS_LOGMNR to 'sync account';  // Allows the sync account to use the Logmnr tool for transaction analysis and data recovery.
      grant alter session to 'sync account';  // Allows the sync account to modify the database connection.
      grant select on dba_objects to 'sync account';  // Allows the sync account to view all database objects.
      grant select on v_$standby_log to 'sync account';  // Allows the sync account to view the v_$standby_log view, containing standby database logs.
      grant select on v_$ARCHIVE_GAP to 'sync account';  // Allows the sync account to query missing archived logs.

2. Enable archived logs, supplemental logs, and switch Redo log files.

   Run the following operations on the primary database:

   1. Enable archived logs. Run the following SQL statements:

      shutdown immediate;
      startup mount;
      alter database archivelog;
      alter database open;

   2. Enable supplemental logs.

      Enable the appropriate supplemental logs by running the following SQL statements:

      alter database add supplemental log data(primary key) columns; // Enable supplemental logs for primary key columns.
      alter database add supplemental log data(unique) columns; // Enable supplemental logs for unique index columns.

   3. Switch Redo log files.

      After you enable supplemental logs, switch Redo log files multiple times (typically 5 times) by running the following command:

      alter system switch logfile;

      Note

      Switching Redo log files multiple times ensures that the current log file is filled and transitions to the next, which prevents operation record loss and aids data recovery.

3. Check the character encoding of the database.

   Run the following command to check the database character encoding. Change any encoding that is not UTF8, AL32UTF8, AL16UTF16, or ZHS16GBK before you start data synchronization:

   select * from v$nls_parameters where PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

   • The v$nls_parameters view stores database parameter settings.

   • NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET represent the character set and national character set, which are the two primary character data storage types in Oracle.

4. Check the data types of the database tables.

   Exclude tables that contain LONG, BFILE, LONG RAW, or NCLOB fields from the real-time sync task list, or change the field types before synchronization. Run the following SQL statement to check the data types of a table. Replace 'tablename' with the actual table name:

   select COLUMN_NAME, DATA_TYPE from all_tab_columns where TABLE_NAME = 'tablename';

   • COLUMN_NAME: The column name in the table.

   • DATA_TYPE: The data type of the column.

   • all_tab_columns: A view that contains information about all table columns.

   • TABLE_NAME: The name of the table to query. Replace 'tablename' with the actual table name.

   Alternatively, run select * from 'tablename'; to view all columns of the specified table and determine the data types.