Non Oracle (target) Replication

Owned by Copy Space for Confluence
Jan 22, 2017
2 min read

Dbvisit Replicate can be configured to replicate data from Oracle to MySQL and SQL Server.  You can add MySQL and SQL Server database in the first step of setup wizard. There are following limitations when configuring non-Oracle databases with Dbvisit Replicate.

  • In all replications, source must be an Oracle database. Thus, two-way replication with MySQL and SQL Server as target is not supported.
  • DDL replication is not supported for MySQL and SQL Server as target.  The one DDL command that is supported is TRUNCATE.
  • Datatypes must be mapped between Oracle to MySQL/SQL Server otherwise replication may fail. Example: timestamp datatype of Oracle is not available in SQL Server and MySQL (unless >= version 5.6.4).
  • Deferrable constraints do not work on MySQL and SQL Server.  If you have them on the Oracle side you should disable them.

Note that even though DDL replication is not supported from Oracle to MySQL/SQL Server with Dbvisit Replicate, the setup wizard 
can generate target table creation scripts if the DDL-ONLY/DDL_FILE options are selected. The default datatype mappings are broadly 
compatible, but the APPLY.sh script should be carefully reviewed to ensure that the desired level of precision is in place in your 
environment, before it is run. Note also that unsupported datatypes will not be excluded from this script, but will generate errors 
at the prepare phase if the IGNORE_APPLY_DDL_DIFFERENCES parameter is set to YES (the default is NO).

 

Configuring TNS_ADMIN For Non Oracle Replication

When starting, Dbvisit Replicate processes need to connect to the DDC database to load the DDC DB settings. This is done by reading the DDC file (specific for each process) that contains credentials to the DDC Database (which is usually placed within the source (Oracle) database). Dbvisit Replicate comes with its own Oracle Instant Client to allow the connection from the target server where no Oracle client is installed. Therefore, on the target server or the server hosting APPLY process,TNS_ADMIN directory and tnsnames.ora files must be created to enable APPLY process to communicate with source Oracle database. 

 

To configure the APPLY process properly, pay attention to the following:

When running the Setup Wizard - Step 4 - Process configuration, many settings are pre-filled with defaults:

Following settings were pre-filled with defaults or your reloaded settings:
----------------------------------------
[APPLY_REMOTE_INTERFACE]: Network remote interface: mysqlsrv:7902 
[APPLY_DATABASE]: Database MySQL connection string: database=dbvrep456;host=mysqlsrv 
[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/10.2.0/db_1/network/admin 
[APPLY_USER]: Dbvisit Replicate database username: root 
[APPLY_PASSWORD]: Dbvisit Replicate database password: ****** 
[APPLY_SCHEMA]: Dbvisit Replicate database (schema): dbvrep456 
[APPLY_STAGING_DIR]: Directory for received plogs: /home/mysql/mysql_test_repl/apply 
[LOG_FILE]: General log file: /home/mysql/mysql_test_repl/log/dbvrep_%N_%D.%E 
[LOG_FILE_TRACE]: Error traces: /home/mysql/mysql_test_repl/log/trace/dbvrep_%N_%D_%I_%U.%E
Checking that these settings are valid...
Do you want change any of the settings? [no] yes

You can either set correct TNS_ADMIN in setup wizard or by manually editing APPLY.ddc file later. You have to create a new tnsnames.ora file and placed it to the directory specified by the TNS_ADMIN parameter and add an entry for in the tnsnames.ora file pointing to the source oracle