Dataphin:Configure Oracle data source

Procedure

1. Create an account and configure account permissions.

   Plan a database login account for subsequent operations, ensuring it has the necessary Oracle operation permissions.

   1. Create an account.

      Use the following command to create a database account:

      create user test identified by example;

   2. Configure permissions.

      Assign the necessary permissions to the account using the following commands, replacing 'sync account' with the previously created account name:

      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.

   Perform the following operations on the primary database:

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

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

   2. Enable supplemental logs.

      Select the appropriate supplemental logs to enable, using 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 enabling supplemental logs, switch Redo log files multiple times (typically 5 times) using the following command:

      alter system switch logfile;

      Note

      Switching Redo log files multiple times ensures the current log file is filled and transitions to the next, preventing operation record loss and aiding data recovery.

3. Check the character encoding of the database.

   Execute the following command to check the database's character encoding. Modify any encodings that are not UTF8, AL32UTF8, AL16UTF16, or ZHS16GBK before data synchronization:

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

   • v$nls_parameters stores database parameter settings.

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

4. Check the data types of the database tables.

   Exclude tables with LONG, BFILE, LONG RAW, and NCLOB data type fields from the real-time sync task list or modify the field types before synchronization. Use SQL statements (SELECT) to query table data types. The following statement checks the 'tablename' table's data types:

   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 containing information about all table columns.

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

   Alternatively, execute select * from 'tablename'; to view all information for the specified table and determine the data types.