In Oracle to SQL Server replication, if the Oracle source Oracle database is running on Linux then setup wizard is executed on target Windows machine where SQL Server database is running. 3rd party products such as MS SSMA or Navicat Premium (Navicat is recommended only for small tables) can be used to load data from oracle to SQL Server in order to sync source and target environment before initiating replication.
...
This example shows an Oracle-to-SQL Server one-way replication using the following information.
| Server/Database | Name |
|---|---|
| Source database | reptest2 (Oracle) |
| Source server | dbvldemo102 (Linux) |
| Target database | w420g (SQL Server) |
| Target server | dbvisit420 (Windows 2008) |
| TNS alias for source database | reptest2 |
| System DSN | mstest |
First of all open the Dbvisit Replicate command console from the start menu and execute 'setup wizard' command.
...
| No Format | ||
|---|---|---|
| ||
Let's configure the database, describing it'sits type, connectivity, user names etc. What type of database is this? (Oracle/MySQL/MSSQL): [Oracle] MSSQL Please enter server name (as specified in "sqlcmd -S", e.g. WINSVR1\SQLEXPRESS): [localhost] dbvisit420 Please enter database ODBC DNS: [] mstest Please enter user name of an administrator: [SA] dbvuser Please enter password for this user: [password] ******* Enter the user to log into apply database: [SA] dbvuser Please enter password for this user: [password] ******* Enter the database to use: [dbvrep] Enter the schema the table are created by the user specified: [dbo] Enter the database with the user data: [] w420g Following databases are now configured: 1: Oracle reptest2, SYS/***, SYSTEM/***, dbvrep/***, USERS/TEMP, dbvrep/, ASM:NO, TZ: +00:00 2: MSSQL mstest, dbvuser/***, dbvuser/***, dbvuser/***, /, dbvrep/dbo, ASM:n/a, TZ: Enter number of database to modify it, or "add", or "done": [done] |
...
During this step the source and target databases are set for each replication pair. In Oracle to SQL Server replication, Only Oracle database can ne be source.
| No Format | ||
|---|---|---|
| ||
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 a 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: reptest2 (Oracle) 2: mstest (MSSQL) (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: reptest2 (Oracle) ==> mstest (MSSQL), 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] |
...