Content Comparison

Before starting replication, source and target environment must be in sync. If source objects arenot empty then source data must be loaded into target (MySQL) database. You can use Navicat Premium to load data from Oracle to MySQL.

Setup Wizard Example

This example shows an Oracle-to-MySQL one-way replication using the following information.

Throughout the wizard, defaults are shown in [brackets]. Just press enter to accept them. The replication name in this example is "reptest1".

First of all start the Dbvisit Reporting Replicate command line console.

oracle@dbvldemo101[/usr/local/dbvisit/replicate]: ./dbvrep

Initializing.....done

Dbvisit Replicate 

Copyright (C) Dbvisit Software Limited. All rights reserved.

No DDC file loaded.

Run "setup wizard" to start the configuration wizard or try "help" to see all

commands available.
dbvrep>

Then start the setup wizard and follow the instructions. Most of the defaults can be chosen.

dbvrep> setup wizard 

This wizard configures Dbvisit Replicate to start a replication process.

The setup wizard creates configuration scripts, which need to be run after the wizard ends. No changes to the databases are made

before that.

The progress is saved every time a list of databases, replications, etc. is shown. It will be re-read if wizard is restarted and

the same DDC name and script path is selected.

Run the wizard now? [yes] 

Accept end-user license agreement? (view/yes/no) [view] yes

Before starting the actual configuration, some basic information is needed. The DDC name and script path determines where all

files created by the wizard go (and where to reread them if wizard is rerun) and the license key determines which options are

available for this configuration.

(DDC_NAME) - Please enter a name for this replication (suggestion: use the name of the source database): [] reptest1 

(LICENSE_KEY) - Please enter your license key (or just enter "(trial)"): [(trial)] 

(SETUP_SCRIPT_PATH) - Please enter a directory for location of configuration scripts on this machine: [/home/oracle/reptest1]

Network configuration files were detected on this system in these locations:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

<n directory for this machine: [/u01/app/oracle/product/11.2.0/dbhome_1/network/admin]

 
Specify databases participating in the replication in the first step of setup wizard.
 Section
 Column
width
5%
 
 Column
width
95%
 Panel No Format
bgColor
CCC
Step 1 - Describe databases


========================================


The first step is to describe databases used in the replication. There are usually two of them (source and target); however,


there can be just one (when source and target is the same) or more than two (one-to-many or other complex configurations.)


Let's configure the database, describing it's type, connectivity, user names etc.


What type of database is this? (Oracle/MySQL/MSSQL): [Oracle] 


Please enter database TNS alias: [] reptest1 

Please enter SYSDBA user name: [SYS] 

Please enter password for this user: [change_on_install] ******


Please enter user with DBA role: [SYSTEM] 

Please enter password for this user: [manager] ******

Connecting to database reptest1 as SYSTEM to query list of tablespaces and to detect ASM (by looking whether any redo logs or


archived logs are stored in ASM).


Enter the Dbvisit Replicate owner and apply user (this user will be created by this script): [dbvrep] 

Please enter password for this user: [dbvpasswd]

Permanent tablespaces detected on the database: DATA, USERS.


Please enter default permanent tablespace for this user: [DATA] USERS

Temporary tablespaces detected on the database: TEMP.


Please enter default temporary tablespace for this user: [TEMP]

Following databases are now configured:


1: Oracle reptest1, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:NO, TZ: +00:00


Enter number of database to modify it, or "add", or "done": [add] 


Let's configure the database, describing it's type, connectivity, user names etc.


What type of database is this? (Oracle/MySQL/MSSQL): [Oracle] MySQL 

Please enter database hostname: [localhost] dbvldemo102 

Please enter user name of an administrator: [root] 


Please enter password for this user: [] ******


Enter the user to log into apply database: [root] 


Please enter password for this user: [password] ******


Enter the database (schema) to use for Dbvisit Replicate internal data (will be created in the script): [dbvrep]

Following databases are now configured:


1: Oracle reptest1, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:NO, TZ: +00:00


2: MySQL database=dbvrep;host=dbvldemo102, root/***, root/***, root/***, /, dbvrep/, ASM:n/a, TZ: 


Enter number of database to modify it, or "add", or "done": [done]
 
In the second step of setup wizard,we configure replication pair by specifying source and target databases. Only Oracle database can be the source here.
 Section
 Column
width
5%
 
 Column
width
95%
 Panel No Format
bgColor
CCC
Step 2 - Replication pairs

========================================


The second step is to set source and targets for each replication pair. This is usually just choosing the first database as


source and the second one as target, but many more configurations are possible.


Let's configure the replication pair, selecting source and target.


Following databases are described:


1: reptest1 (Oracle) 


2: database=dbvrep;host=dbvldemo102 (MySQL) (cannot be source, is not Oracle) 


Select source database: [1] 

Select target database: [2] 

Use fetcher to offload the mining to a different server? (yes/no) [no] 


(NETWORK_QUALITY) - Please specify your network type (LAN or WAN). Autoconfigures timeouts, use of compression etc. [LAN] 

Lock and copy the data initially one-by-one or at a single SCN? (one-by-one/single-scn/ddl-only/resetlogs) [single-scn]

Following replication pairs are now configured:


1: reptest1 (Oracle) ==> database=dbvrep;host=dbvldemo102 (MySQL), DDL: no, fetcher: no, process suffix: (no suffix), network:


LAN, prepare type: single-scn, data load: ddl_file


Enter number of replication pair to modify it, or "add", or "done": [done]
 
In the third step of setup wizard, specify the tables/schema to be replicated. In this example, it is emp table of test schema.
 Note
In this step,rename schema and table feature is used to map replication from sample table of avi schema of Oracle to sample table of reptest1 database of MySQL. You can ignore this feature if database name in MySQL is same as schema name of Oracle.
 Section
 Column
width
5%
 
 Column
width
95%
 Panel No Format
bgColor
CCC
Step 3 - Replicated tables

========================================


The third step is to choose the schemas and tables to be replicated. If the databases are reachable, the tables are checked for


existence, datatype support, etc., schemas are queried for tables. Note that all messages are merely hints/warnings and may be


ignored if issues are rectified before the scripts are actually executed.

Note the following assumptions are made in this wizard 
–
- which can be modified by editing the resulting script:


1. All replicated tables have a primary key defined.


2. All columns of the tables and all tables of the specified schemas are replicated, and if DDL support is enabled, whenever a


new column/table is added, this should be replicate as well.


3. If an apply conflict arises, the default option is to try again repeatedly, until a different option is given or the


underlying issue is resolved. 


4. If an apply conflict arises, no more data will be replicated until the issue is resolved or ignored.

You can also specify some advanced options:


1. Rename schemas or tables.


2. Specify filtering conditions.


3. (Tables only) Configure Change Data Capture; this does not maintain a copy of the source table, but logs all operations as


separate entries. This is useful for ETL or as an audit trail. This usually requires adding of new columns (timestamps, old/new


values etc.) to the target table. Dbvisit Replicate can create the target table with the additional columns during setup by


selecting "ddl_run" in the data copy section of Step 2.

Following tables are defined for replication pairs:


1: reptest1 (Oracle) ==> database=dbvrep;host=dbvldemo102 (MySQL), DDL: no, suffix: (no suffix), prepare: single-scn


 No tables defined.


Enter number of replication pair to modify it, or "done": [1]

Please enter list of all individual tables to be replicated. Enter schema name(s) only to replicate all tables in that schema.


Use comma or space to delimit the entries.


Enter the tables and schemas: [] avi.sample 


Selected tables: AVI.SAMPLE


Add more tables or schemas? (YES/NO) [NO] 

Specify rename name, filter condition, CDC/Audit/ETL for any of the specified tables (yes/no): [no] yes 


Rename SCHEMA name for AVI.SAMPLE (empty means no rename): [] reptest1 

Rename TABLE name for AVI.SAMPLE (empty means no rename): [] sample 

Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditions for each operation? (NO/ALL/CUSTOM) [NO] 


Configure change data capture for change auditing or real-time BI? (NO/YES) [NO]

Following tables are defined for replication pairs:


1: reptest1 (Oracle) ==> database=dbvrep;host=dbvldemo102 (MySQL), DDL: no, suffix: (no suffix), prepare: single-scn


 AVI.SAMPLE => reptest1.sample


Enter number of replication pair to modify it, or "done": [done]
 
In the forth and the final step of setup wizard, we can configure MINE and APPLY process parameters.
 Section
 Column
width
5%
 
 Column
width
95%
 Panel No Format
bgColor
CCC
Step 4 - Process configuration

========================================


The fourth step is to configure the replication processes for each replication. Although most options have reasonable defaults,


manual input is required.

Following processes are defined:


1: MINE on reptest1


 Not configured.


2: APPLY on database=dbvrep;host=dbvldemo102


 Not configured.


Enter number of process to modify it, or "done": [1] 


Fully qualified name of the server for the process (usually co-located with the database, unless mine is offloaded using fetcher): [dbvldemo101] 

Server type (Windows/Linux/Unix): [Linux] 


Enable email notifications about problems (yes/no)? [YES] no 


Enable SNMP traps/notifications about problems (yes/no)? [NO] 


Directory with DDC file and default where to create log files etc. (recommended: same as global setting, if possible)? [/home/oracle/reptest1]

Following settings were pre-filled with defaults or your reloaded settings:


----------------------------------------


[MINE_REMOTE_INTERFACE]: Network remote interface: dbvldemo101:7901 


[MINE_DATABASE]: Database TNS: reptest1 


[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin 

[MINE_USER]: Dbvisit Replicate database username: dbvrep 


[MINE_PASSWORD]: Dbvisit Replicate database password: ********* 

[MINE_PLOG]: Filemask for generated plogs: /home/oracle/reptest1/mine/%S.%E (%S is sequence, %T thread, %F original filename


(stripped extension), %P process type, %N process name, %E default extension)


[LOG_FILE]: General log file: /home/oracle/reptest1/log/dbvrep_%N_%D.%E 


[LOG_FILE_TRACE]: Error traces: /home/oracle/reptest1/log/trace/dbvrep_%N_%D_%I_%U.%E

Checking that these settings are valid...


Do you want change any of the settings? [no]

Following processes are defined:


1: MINE on reptest1


 Host: dbvldemo101, SMTP: NO, SNMP: NO


2: APPLY on database=dbvrep;host=dbvldemo102


 Not configured.


Enter number of process to modify it, or "done": [2] 


Fully qualified name of the server for the process (usually co-located with the database, unless mine is offloaded using fetcher): [dbvldemo102] 


Server type (Windows/Linux/Unix): [] Linux 


Enable email notifications about problems (yes/no)? [YES] no 


Enable SNMP traps/notifications about problems (yes/no)? [NO] 


Directory with DDC file and default where to create log files etc. (recommended: same as global setting, if possible)? [/home/oracle/reptest1]

Following settings were pre-filled with defaults or your reloaded settings:


----------------------------------------


[APPLY_REMOTE_INTERFACE]: Network remote interface: dbvldemo102:7902 


[APPLY_DATABASE]: Database MySQL connection string: database=dbvrep;host=dbvldemo102 

[TNS_ADMIN]: tnsnames.ora path: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin 

[APPLY_USER]: Dbvisit Replicate database username: root 


[APPLY_PASSWORD]: Dbvisit Replicate database password: ****** 


[APPLY_SCHEMA]: Dbvisit Replicate database (schema): dbvrep 


[APPLY_STAGING_DIR]: Directory for received plogs: /home/oracle/reptest1/apply 


[LOG_FILE]: General log file: /home/oracle/reptest1/log/dbvrep_%N_%D.%E 


[LOG_FILE_TRACE]: Error traces: /home/oracle/reptest1/log/trace/dbvrep_%N_%D_%I_%U.%E

Checking that these settings are valid...


Do you want change any of the settings? [no]

Following processes are defined:


1: MINE on reptest1


 Host: dbvldemo101, SMTP: NO, SNMP: NO


2: APPLY on database=dbvrep;host=dbvldemo102


 Host: dbvldemo102, SMTP: NO, SNMP: NO


Enter number of process to modify it, or "done": [done] 


Created file /home/oracle/reptest1/reptest1-APPLY.ddc.


Created file /home/oracle/reptest1/reptest1-MINE.ddc.


Created file /home/oracle/reptest1/reptest1-setup.dbvrep.


Created file /home/oracle/reptest1/reptest1-dbsetup_reptest1.sql.


Created file /home/oracle/reptest1/reptest1-dbsetup_database_dbvrep_host_dbvldemo102.sql.


Created file /home/oracle/reptest1/reptest1-grants_reptest1.sql.


Created file /home/oracle/reptest1/reptest1-grants_database_dbvrep_host_dbvldemo102.sql.


Created file /home/oracle/reptest1/reptest1-onetime.ddc.


Created file /home/oracle/reptest1/start-console.sh.


Created file /home/oracle/reptest1/reptest1-run-dbvldemo101.sh.


Created file /home/oracle/reptest1/reptest1-run-dbvldemo102.sh.


Created file /home/oracle/reptest1/Nextsteps.txt.


Created file /home/oracle/reptest1/reptest1-all.sh.


===============================================================================================================================

Dbvisit Replicate wizard completed

Script /home/oracle/reptest1/reptest1-all.sh created. This runs all the above created scripts. Please exit out of dbvrep, review


and run script as current user to setup and start Dbvisit Replicate.


===============================================================================================================================


Optionally, the script can be invoked now by this wizard.


Run this script now? (yes/no) [NO]
 
Exit out of Dbvisit Replicate and start the script that has been generated by the setup wizard. These scripts configures source and target database for replication and creates dbvrep schema in Oracle and dbvrep database in MySQL databases. Out of all scripts, the only script to be executed is <replication-name>-all.sh that actually executes other scripts in desirable order. 
 Section
 Column
width
5%
 
 Column
width
95%
 Panel No Format
bgColor
CCC
/home/oracle/reptest1/./reptest1-all.
sh 
sh 
Setting up Dbvisit Replicate configuration

Configure database reptest1...

This check fails if the DBID is not the expected one...

Ok, check passed.

Configure database database=dbvrep

Object grants for database reptest1...

Object grants for database database=dbvrep

Setting up the configuration

Initializing......done

WARN-1850: No DDC DB available, dictionary table does not exist.

DDC loaded from database (0 variables).

Dbvisit Replicate version 2.
4.21.2687
7
Copyright (C) Dbvisit Software Limited. All rights reserved.

DDC file /home/oracle/reptest1/reptest1-onetime.ddc loaded.

MINE: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists)

APPLY: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists)

dbvrep> set ON_WARNING SKIP

Variable ON_WARNING set to SKIP for process *.

dbvrep> set ON_ERROR EXIT

Variable ON_ERROR set to EXIT for process *.

dbvrep> ENGINE SWITCH_REDOLOG

dbvrep> ENGINE SETUP MINE DROP DICTIONARY

0 dictionary objects dropped.

dbvrep> ENGINE SETUP MINE CREATE DICTIONARY

dbvrep> ENGINE SETUP MINE LOAD DICTIONARY

Supplemental logging on database set.

Loading dictionary table DBRSCOL$

Loading dictionary table DBRSIND$

Loading dictionary table DBRSOBJ$

Loading dictionary table DBRSTAB$

Loading dictionary table DBRSUSER$

Loading dictionary table DBRSV_$DATABASE

dbvrep> ENGINE SETUP APPLY DROP DICTIONARY

0 dictionary objects dropped.

dbvrep> ENGINE SETUP APPLY CREATE DICTIONARY

dbvrep> ENGINE SETUP APPLY LOAD DICTIONARY

dbvrep> ENGINE SETUP PAIR MINE AND APPLY

ID of mine proces is 09694DAE-9045-11E2-B3F5-D0118AF7C5BA. If not using DDC in database, set MINE_UNIQUE_ID to this value.

1 applier SCN set.

dbvrep> SET APPLY.INSTANTIATE_SCN NOW

Variable INSTANTIATE_SCN set to NOW for process APPLY.

dbvrep> memory_set IGNORE_APPLY_DDL_DIFFERENCES YES

Variable IGNORE_APPLY_DDL_DIFFERENCES set to YES for process *.

dbvrep> ENGINE SUPPLEMENTAL LOGGING TABLE AVI.SAMPLE ENABLE PRIMARY KEY

dbvrep> ENGINE SWITCH_REDOLOG

dbvrep> ENGINE WAIT_SCN_FLIP

Waited 3 seconds until scn_to_timestamp changed.

dbvrep> #single-scn instantiation: lock all tables and schemas

dbvrep> ENGINE LOCK TABLES AVI.SAMPLE

Locking all tables.

Lock done.

dbvrep> #single-scn instantiation: unlock all tables and schemas, but keep the SCN

dbvrep> ENGINE LOCK RELEASE LOCKS

dbvrep> 

dbvrep> #prepare the tables (we use OFFLINE as neither MINE not APPLY is running; with OFFLINE we won't wait on network timeout)

dbvrep> PREPARE OFFLINE TABLE AVI.SAMPLE NODDL RENAME TO reptest1.sample

Table AVI.SAMPLE instantiated at SCN 36261160

dbvrep> #single-scn instantiation: unlock all tables and schemas, forget the SCN (so it does not affect any further PREPARE

statements)

dbvrep> ENGINE LOCK CLEAR SCN

dbvrep> ENGINE SWITCH_REDOLOG

dbvrep> #prepare script for instantiation

dbvrep> ENGINE PREPARE_DP WRITE DDL_FILE FILE /home/oracle/reptest1/APPLY.sql USERID SYSTEM/
odb10g@reptest1
password@reptest1
Created DDL script /home/oracle/reptest1/APPLY.sql.

dbvrep> create ddcdb from ddcfile

DDC loaded into database (227 variables).

dbvrep> set ON_WARNING SKIP

Variable ON_WARNING set to SKIP for process *.

dbvrep> set ON_ERROR SKIP

Variable ON_ERROR set to SKIP for process *.

OK-0: Completed successfully.

These steps are required after the reptest1-all.sh script runs:1) Create the necessary directory(ies) on the servers:

dbvldemo102: /home/oracle/reptest12) Copy the DDC files to the server(s) where the processes will run:

dbvldemo102: /home/oracle/reptest1/reptest1-APPLY.ddc

dbvldemo101: /home/oracle/reptest1/reptest1-MINE.ddc3) Review that path to dbvrep executable is correct in the run scripts:

/home/oracle/reptest1/reptest1-run-dbvldemo101.sh

/home/oracle/reptest1/reptest1-run-dbvldemo102.sh4) Copy the run script to the server(s) where the processes will run:

/home/oracle/reptest1/reptest1-run-dbvldemo101.sh

/home/oracle/reptest1/reptest1-run-dbvldemo102.sh5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes.6) Ensure firewall is open for remote access to mysql (usually port 3306) on dbvldemo102.7) Make sure the data on apply are in sync as of time when setup was run.

Scripts for Data Pump/export/DDL were created as requested:

/home/oracle/reptest1/APPLY.sql

Create referenced database links (if any) before running the scripts.8) Start the replication processes on all servers:

/home/oracle/reptest1/reptest1-run-dbvldemo101.sh

/home/oracle/reptest1/reptest1-run-dbvldemo102.sh9) Start the console to monitor the progress:

chmod 0750 /home/oracle/reptest1/start-console.sh

/home/oracle/reptest1/start-console.shThe above list is stored in /home/oracle/reptest1/Nextsteps.txt.
Starting MINE and APPLY process requires their respective DDC config file and script that actually starts these processes in background. ( On Windows system, MINE and APPLY act as services) Copy reptest1-APPLY.ddc and reptest1-run-dbvldemo102.sh to dbvldemo102  and then start MINE and APPLY on dbvldemo101 and dbvldemo102 respectively. 
Starting MINE on dbvldemo101
columnoracle@dbvldemo101
 Sectionnoformat
bgColor
width
1%
 
 Column
width
99%
 Panel
bgColor
CCC
CCC
oracle@dbvldemo101[/home/oracle/reptest1]: ./reptest1-run-dbvldemo101.sh 

Initializing......done


DDC loaded from database (227 variables).


Dbvisit Replicate version 2.
4.21.2687
7
Copyright (C) Dbvisit Software Limited. All rights reserved.


DDC file /home/oracle/reptest1/reptest1-MINE.ddc loaded.


Starting process MINE...Created directory /home/oracle/reptest1/ddc_backup


Created directory /home/oracle/reptest1/log/


Created directory /home/oracle/reptest1/log/trace/


Created directory /home/oracle/reptest1/mine/


started
Starting APPLY on dbvldemo102
 Section
 Column
width
1%
 
 Column
width
99%
panel Note
The default transaction isolation level needs to be changed before starting the apply process. Execute following command in MySQL to set default transaction isolation level to READ-COMMITTED.
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED
 
Starting APPLY on dbvldemo102
 No Format
bgColor
CCC
oracle@dbvldemo102[/home/oracle/reptest1]: ./reptest1-run-dbvldemo102.sh 


Initializing......done


DDC loaded from database (227 variables).


Dbvisit Replicate version 2.
4.21.2687
7
Copyright (C) Dbvisit Software Limited. All rights reserved.


DDC file /home/oracle/reptest1/reptest1-APPLY.ddc loaded.


Starting process APPLY...Created directory /home/oracle/reptest1/ddc_backup


Created directory /home/oracle/reptest1/log/


Created directory /home/oracle/reptest1/log/trace/


Created directory /home/oracle/reptest1/apply
started
started
 
Dbvisit Replicate has now been configured and started in the background. To monitor replication progress start console on dbvldemo101 by executing start-console.sh script
 Section
 Column
width
5%
 
 Column
width
95%
panel No Format
bgColor
CCC
$ /usr/local/dbvisit/replicate/dbvrep" --ddcfile /home/oracle/reptest1/reptest1-MINE.ddc


Initializing.....done


DDC loaded from database (134 variables).


Dbvisit Replicate version 2.
1.04.1047
7
Copyright (C) Dbvisit Software Limited. All rights reserved.


-MINE IS running, initialization NOT yet complete. Currently at plog 264 and SCN 6355055 (08/12/2011 20:11:24).


APPLY IS running, initialization NOT yet complete. Currently at plog 263 and SCN 6353874 (08/12/2011 20:11:20).
 
Once the APPLY and MINE process has been started, there are in the INITIALIZATION state. In this state, Dbvisit Replicate replicates the contents of the Dbvisit Replication repository from the source to the target database.To monitor the progress of initialization, the LIST PROGRESS ALL command can be used.
Replication cannot be started until the initialization has been completed. This is completed when the console show MINE and APPLY is running.
 Section
 Column
width
1%
 
 Column
width
99%
 Panel No Format
bgColor
CCC
MINE IS running. Currently at plog 266 and SCN 6356159 (08/12/2011 20:15:45).

APPLY IS running,Currently at plog 266 and SCN 6356159 (08/12/2011 20:15:45).
 
Now, all insert, update and delete operation on avi.sample table of Oracle will be replicated to reptest1.sample table of MySQL.
 Note
Database and table names are case sensitive in MySQL
Scitps Scripts generated by setup wizard contains plain text passwords. You can delete these scripts once replication is configured and running.