f
DBMoto
Oracle® Setup Guide
Version 8.5.0.11
Document History
Version Author Date Reviewer Date Approver Date Comments
Table of Contents
The Log Server Application for Oracle ... 2
Setup Summary ... 3
Install Oracle Client with .NET Provider ... 5
Install Oracle JDBC Driver & JRE ... 5
Oracle User Permissions... 6
Refresh with Oracle as Either Source or Target Database ... 6
Transactional Replications/Initial Refresh with Oracle as Either Source or Target Database ... 7
Download and Install DBMoto ... 11
Set Up the DBMoto Environment ... 11
1. Set Up Database Connections from DBMoto ... 11
Source Connection ... 12
Target Connection ... 21
2. Create a Target Table ... 23
3. Define the Replication... 27
4. Run the Log Server Application ... 33
• 5. Run the Replication ... 34
Database Access Security Topics ... 34
This guide describes how to set up your DBMoto environment to replicate data from an Oracle database. DBMoto allows you to replicate data in three different ways, or modes:
• Refresh
A one-time complete replication from source to target table, according to replication settings and scripts. You can control the timing of the replication, identify the columns to be replicated and add scripts to transform data during replication.
• One-way mirroring
A continuous update of a replicated table based on changes to the source database that have been recorded in the database server log. Typically, this involves an initial refresh operation, as described above, to set up the target table. Then you can define the replication settings to check the transaction log on the source database at regular intervals. Any changes found in the log would be applied to the target database.
• Synchronization (two-way mirroring)
Synchronization is appropriate when changes occur in both tables involved in the replication. Your replication settings ensure that both tables maintain the same state by checking the logs on each table and performing updates on each table as needed.
This guide describes the setup process for one-way mirroring and synchronization when replicating data from an Oracle database. For mirroring and synchronization replications using Oracle as a source, DBMoto takes advantage of the Oracle Log Miner by providing two options for replication:
• Accessing the Log Miner directly
• Accessing the Log Miner via a DBMoto Log Server component for increased performance when dealing with large amounts of data.
Choosing Between Oracle Log Miner Direct and DBMoto Log Server for
Oracle
Below is a summary of the advantages and disadvantages of replicating from Oracle using the LogMiner directly versus using the DBMoto Log Server:
Oracle LogMiner Direct DBMoto Log Server for Oracle Advantages Simple to configure Performance
The Log Server only uses ONE instance of Log Miner to query for data changes – regardless of the number of replications and groups. The Log Server queries the database for all changes in a data source at the same time and then storing them in intermediate log files. The replications and groups then read changes from the intermediate log files (very efficient) Less physical log file retention required Because the Log Server runs continuously in the background, it can capture data changes more rapidly, and it stays running independently even when DBMoto goes down. It requires the database to retain physical log files for shorter periods. Disadvantages • Query performance decreases as the number
of replications or groups increases because each replication or group requires DBMoto to create an instance of Oracle Log Miner to query the database for changes. For example, if a data source has 2 single replications and 10 group replications, DBMOTO would need to create 12 Log Miner instances to query the database for data changes.
• As performance decreases, the database has to retain physical log files longer until all changes are returned to DBMOTO.
• If DBMOTO goes off line, the database also has to retain physical log files until DBMOTO restarts and queries all the changes.
• Additional file management: an extra
application, the Log Server, and log files folder. However, the application management tasks are not complex.
The Log Server Application for Oracle
After setup, two separate DBMoto components are required for replicating data using the Log Server option: 1. The DBMoto Data Replicator (replication engine)
2. The DBMoto Log Server application
The Log Server application retrieves changed records from the database and then writes them into log files in a folder where the DBMoto Data Replicator reads.
The Log Server is a Java-based application that can be run on the same system as the DBMoto Data Replicator, or on any other computer – including the database server – that can connect to the database and share the folder where the log files are written. The Log Server is started and stopped manually.
The amount of captured data and the number of files retained are configurable.
If the DBMoto Data Replicator goes down, independently, the Log Server application would still be running and capturing data into log files, so when the Data Replicator is restarted, it can begin where it stopped without loss of data (assuming the number files retained is sufficient).
If the Log Server application is stopped for maintenance or otherwise, when restarted, it will also begin reading where it last stopped. The read will be successful if the database archived log files have not been purged.
Setup Summary
This section provides a summary of all the steps required for setting up and using DBMoto. Use the link for each step for more information.
Install Oracle .NET Provider
(See Install Oracle Client with .NET Provider)
If running DBMoto on a system other than the one where the Oracle database is installed, download and install the Oracle client. The .NET provider is needed for both Log Miner and Log Miner via Server options
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Install JDBC Driver & JRE
(see Install Oracle JDBC Driver & JRE)
For the Oracle Log Server mirroring or synchronization option, download and install the Java 7 JRE (or later), and the Oracle JDBC driver’s files.
http://www.oracle.com/technetwork/java/javase/downloads/jre7-downloads-1880261.html http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html
Also download the library files: ojdbc7.jar and orai18n.jar Oracle User
Permissions (See Oracle User Permissions below)
Permissions required for mirroring from Oracle:
• For replication setup and execution: permission to select catalogs, tables • For replication setup only:
• Permission alter the table or database to enable SUPPLEMENTAL LOG.
• Execute permission to run the logminer and access all system tables to retrieve log information and the last transaction ID
Download and Install DBMoto (See Download and Install DBMoto below)
Download DBMoto 8 from:
http://www.hitsw.com/products_services/register/register_dbmoto.html
DBMoto Setup (See Set up the DBMoto
Environment below)
In the DBMoto Management Center:
1. In the Metadata Explorer, create a source connection to Oracle. 2. Check Use Transactional Replication and use Oracle or LogServer. 3. Fill out the required fields if LogServer is selected.
4. Click Verify then Install to set up the library for log reading on the DB2 server. 5. Create a target connection.
6. Create a replication.
7. Start the Oracle Log Server application if LogServer is used. 8. Run the replication.
Install Oracle Client with .NET Provider
Before connecting to an Oracle database from DBMoto, make sure the Oracle .NET provider is installed and accessible from the system where DBMoto is running.
If you install DBMoto on the system where the Oracle server is installed, the Oracle .NET Data Provider should already be installed as part of the Oracle installation and DBMoto should be able to find the provider automatically.
If you install DBMoto on a different system, to connect to the Oracle server you need to install the Oracle client on the same system as DBMoto.
1. Download the Oracle appropriate client from the Oracle Web site. The links below are for the Oracle Database 11g Release 2 (11.2.0.1.0)
Windows 32-bit:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html Windows 64-bit:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html 2. Run the setup.exe to install the client.
3. For Oracle version 10 clients, you need to configure the Net Service Name for the client to access the Oracle server.
Install Oracle JDBC Driver & JRE
DBMoto offers a Log Server option for mirroring and synchronization. While the DBMoto Log Server still uses Oracle Log Miner to access source table changes, it provides improved performance for large datasets. If you are planning to use the Log Server option for mirroring and synchronization, install the Oracle JDBC driver and JRE.
The DBMoto Log Server for Oracle is a Java application that reads log changes from the database and writes them to files in a local folder read by the Data Replicator.
A Java Runtime Environment or JRE version 7 or later and an Oracle JDBC Driver version 11 or later are required to run the DBMoto Log Server application. Recommended versions are JRE version 7 or 8, and JDBC driver version 12..
1. Download a JRE if you do not have a recommended version installed on your system.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html 2. Download and copy the following driver files to a folder: ojdbc7.jar and orai18n.jar.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html These jar files need to be included in the Java classpath to run the application. For the sake for clarity, these files should be placed in the folder that contains the DBMoto Log Server jar file: ServerFiles/OracleLogServer/ under the DBMoto installation folder. The remaining steps in this guide assume the driver jar files are placed in this location.
Oracle User Permissions
When setting up replications that use Oracle as either a source or target database, you need to be sure that the user ID used for making connections to the database has sufficient privileges to complete all the operations required for DBMoto to perform a replication.
This section is organized by the type of replication you want to perform. It describes in detail all the user authorities that will be required during the setup and execution of replications.
Refresh with Oracle as Either Source or Target Database
1. AUTHORITY TO CONNECT TO DATABASETo open a connection to an Oracle database, you need specific authority for a user ID using either of the following two syntaxes:
grant create session to <uid>; OR
grant connect to <uid>; Example where dbmoto is the user ID:
grant create session to dbmoto; OR
grant connect to dbmoto;
2. AUTHORITY TO SELECT CATALOG
To display a list of tables and show fields in the table in the Management Center (for selecting a source or target table and for setting which fields to replicate), DBMoto runs a SELECT_CATALOG command. If the user ID has
insufficient privileges, an error is generated on the Oracle server. grant select any table to <uid>;
Example where dbmoto is the user ID:
grant select any table to dbmoto; 3. AUTHORITY TO SELECT TABLES
DBMoto runs a SELECT statement to identify records to replicate. Therefore, the user ID used to make a connection must have adequate authority to run a SELECT statement for tables involved in replication.
If you used grant select any table above, you do not need to grant select authority to specific tables involved in replications because the above command covers all tables. However, in case you need it, the command to grant access to a specific table is:
To verify existing authorities for a user ID, you can run the EDTOBJAUT command, where <TYPE> can be *LIB, *FILE, etc.
grant select on <table> to <UID>;
Example where dbmoto is the user ID:
grant select on SAMPLE.EMPLOYEES to dbmoto; 4. AUTHORITY TO UPDATE TABLES, CREATE TABLES (Optional)
To create a target table in the Management Center (as part of the Create Table Wizard), DBMoto uses the following commands.
grant unlimited tablespace to <uid>;
You first need to grant a quota on the tablespace in which you want to create a table or index. Then you can grant create permissions and update permissions (insert, update and delete.)
grant create any table to <uid>;
grant insert any table, update any table, delete any table to <uid>; Example where dbmoto is the user ID:
grant unlimited tablespace to dbmoto; grant create any table to dbmoto;
grant insert any table, update any table, delete any table to dbmoto; The insert, update and delete commands are broader than needed. They can also be granted to specific tables.
5. AUTHORITY TO DROP TABLES, ALTER TABLES (Optional)
The use of these commands from within DBMoto is entirely optional (i.e. not necessary for running a refresh replication.) They are used if you choose to remove a table from Oracle or change the table via the Management Center SQL Query tab. The following commands are broader than needed. Alter and drop can also be granted to specific tables.
grant alter any table to <uid>; grant drop any table to <uid>;
Transactional Replications/Initial Refresh with Oracle as Either Source or Target
Database
This section includes information for mirroring where Oracle is the data source, and synchronization where Oracle can be either the “source” or “target” data source.
1. AUTHORITY TO CONNECT TO DATABASE
To open a connection to an Oracle database, you need specific authority for a user ID using either of the following two syntaxes:
grant create session to <uid>; OR
grant connect to <uid>;
grant create session to dbmoto; OR
grant connect to dbmoto; 2. AUTHORITY TO SELECT A CATALOG
To display a list of tables and show fields in the table in the Management Center (for selecting a source or target table and for setting which fields to replicate), DBMoto runs a SELECT_CATALOG command. If the user ID has
insufficient privileges, an error is generated on the Oracle server. grant select any table to <uid>;
Example where dbmoto is the user ID:
grant select any table to dbmoto; 3. AUTHORITY TO SELECT TABLES
DBMoto runs a SELECT statement to identify records to replicate. Therefore, the user ID used to make a connection must have adequate authority to run a SELECT statement for tables involved in replication.
If you used grant select any table above, you do not need to grant select authority to specific tables involved in replications because the above command covers all tables. However, in case you need it, the command to grant access to a specific table is:
To verify existing authorities for a user ID, you can run the EDTOBJAUT command, where <TYPE> can be *LIB, *FILE, etc.
grant select on <table> to <UID>; Example where dbmoto is the user ID:
grant select on SAMPLE.EMPLOYEES to dbmoto;
4. AUTHORITY TO UPDATE TABLES, CREATE TABLES (Optional)
To create a target table in the Management Center (as part of the Create Table Wizard), DBMoto uses the following commands.
grant unlimited tablespace to <uid>;
You first need to grant a quota on the tablespace in which you want to create a table or index. Then you can grant create permissions and update permissions (insert, update and delete.)
grant create any table to <uid>;
grant insert any table, update any table, delete any table to <uid>; Example where dbmoto is the user ID:
grant unlimited tablespace to dbmoto; grant create any table to dbmoto;
grant insert any table, update any table, delete any table to dbmoto;
The insert, update and delete commands are broader than needed. They can also be granted to specific tables.
5. AUTHORITY TO DROP TABLES, ALTER TABLES (Optional)
The use of these commands from within DBMoto is entirely optional (i.e. not necessary for running a refresh replication.) They are used if you choose to remove a table from Oracle or change the table via the Management Center SQL Query tab. The following commands are broader than needed. Alter and drop can also be granted to specific tables.
grant alter any table to <uid>; grant drop any table to <uid>;
6. AUTHORITY TO SET UP A CONNECTION FOR TRANSACTIONAL REPLICATIONS (logminer) (Note: Not needed when Oracle is a target in mirroring replications)
When setting up the connection for transactional replications (where Oracle is serving as the source of data, either for mirroring or for synchronization), you may need additional privileges that are not required for replication operations. You need to alter the table or database to enable SUPPLEMENTAL LOG.
In DBMoto versions 7.0.4 and above, the supplemental log is enabled at the table level by default rather than for the entire database. In the default case, you only need an ALTER TABLE privilege when setting up a replication. If the table already has a supplemental log set, then DBMoto does not need this privilege. You can control whether supplemental logging is enabled at the table or database level using the Oracle Setup Info dialog available from the Connection Properties dialog (under Transaction Log Type.)
To set up supplemental logging independent of DBMoto (i.e. not through the Oracle Setup Info dialog), use the following commands:
For minimal supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
For table-level supplemental logging
ALTER TABLE MYSCHEMA.MYTABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS
If using a dictionary file (required for Oracle 8.1, optional for later versions) you need execute permission to build the dictionary file.
grant alter table to <uid>; grant alter database to <uid>;
grant execute on sys.dbms_logmnr_d to <uid>;
Example:
grant alter table to dbmoto grant alter database to dbmoto
grant execute on sys.dbms_logmnr_d to dbmoto;
7. AUTHORITY TO SET UP TRANSACTIONAL REPLICATIONS and ACCESS REDO/ARCHIVED LOGS (logminer) (Note: Not needed when Oracle is a target in mirroring replications)
When setting up the connection for transactional replications (where Oracle is serving as the source of data, either for mirroring or for synchronization), the following permissions are also required: SELECT_TABLE permissions on the source table (see above); execute permission to run the logminer; and access all system tables to retrieve log
information and the last transaction ID.
grant execute on sys.dbms_logmnr to <uid>; grant select on sys.v_$parameter to <uid>; grant select on sys.v_$log to <uid>;
grant select on sys.v_$logfile to <uid>;
grant select on sys.V_$logmnr_contents to <uid>; grant select on sys.V_$thread to <uid>;
grant select on sys.V_$archive_dest to <uid>;
For Oracle 12c and above, also define:
grant select on cdb_pdbs to <uid>;
To use archived logs to identify database changes for mirroring, you must execute the following: grant select on sys.v_$archived_log to <uid>;
For Oracle versions 10g and above, execute the following: grant select any transaction to <uid>;
For Oracle versions 12c and above, execute the following: grant logmining to <uid>;
Example:
grant select any transaction to dbmoto; grant logmining to dbmoto;
grant select on sys.v_$archived_log to dbmoto; grant execute on sys.dbms_logmnr to dbmoto;
grant select on sys.v_$parameter to dbmoto; grant select on sys.v_$log to dbmoto;
grant select on sys.v_$logfile to dbmoto;
grant select on sys.V_$logmnr_contents to dbmoto; grant select on sys.V_$thread to dbmoto;
grant select on sys.V_$archive_dest to dbmoto;
Download and Install DBMoto
1. On the system where you plan to install DBMoto, make sure that you are running Microsoft .NET Framework 4.0 or 4.5.
2. Download DBMoto from HiT Software’s web site at:
http://www.hitsw.com/products_services/register/register_dbmoto.html.
3. In the registration form, be sure to put an email address where you can easily retrieve the registration key. 4. Unzip the downloaded file.
5. Once you have received the registration key via email, install the product using setup.exe. 6. Enter the license key information during installation.
Set Up the DBMoto Environment
1.
Set Up Database Connections from DBMoto
1. Make sure you have database connections via a .NET data provider to your Oracle source database and to your target database (the database to which you are replicating.) For each database you are planning to use in your replication project:
• Install and configure one of the supported data access products. See
http://www.hitsw.com/support/kbase/DBMoto/Providers_DBMoto.htm for a current list of supported providers. • From the data access product, test the connection to the database.
• Create a connection string for the data access product/database you are using. Check the documentation for the data access product for information on how to do this.
• Check that the Oracle user ID you are planning to use has sufficient permissions to complete all operations in DBMoto. The user ID should have permissions to connect, select tables, insert/update/delete records, alter the database to enable SUPPLEMENTAL LOG, run the logminer. For complete details, see the section Oracle User Permissions.
2. Start Management Center.
DBMoto provides a default database (Microsoft SQL Server CE) for your DBMoto metadata, all the information that DBMoto needs to store about your replication setup.
3. In the Metadata Explorer, expand the metadata node to view the Sources and Targets nodes.
Source Connection
1. Select the Sources node.
2. From the right mouse button menu, choose Add New Connection.
3. In the Source Connection Wizard, follow steps to add a connection string and test the connection to the database.
4. In the Select Provider screen, fill out the following fields: Source Name
Type a name to identify the source connection. This name appears in the Metadata Explorer as a way to group connections for a specific replication.
Database
Select Oracle from the drop-down list. Provider
The value Oracle .NET Driver is provided automatically. You do not need to change this value. Assembly
The value for Oracle should be the pathname to the .NET Assembly Oracle.DataAccess (file name:
Oracle.DataAccess.dll.) For later versions of Oracle, you can leave the Assembly field blank because the dll path should be available to DBMoto. (The dll is registered during installation of the Oracle .NET Data Provider.) If the value is not available, DBMoto displays a message when you continue in the Source Connection wizard, allowing you to go back and type in the path. Find out the location of the assembly in your environment by searching for the file name Oracle.DataAccess.dll, then enter the path and the assembly file name as in the example below.
Example:
C:\oracle10_2\client\odp.net\bin\2.x\Oracle.DataAccess.dll. 5. In the Set Connection String screen, fill out the following fields:
Connection Properties
Edit at least the Required connection properties by clicking in the property value field and typing a new value. Note: If using an Oracle 11 client, in the Data Source field, be sure to add the Oracle Service Name after the IP address.
For Synchronization Replications:
The login/user ID that you provide must be unique to DBMoto. It should not be used for any transactions occurring
in either database involved in the synchronization. DBMoto does not replicate transactions by the user you specify in this connection. This user ID is used by DBMoto during synchronization to read the database logs and perform the synchronization operations. Therefore, any transactions found in the logs with this user ID are not replicated as part of the synchronization data.
For Oracle Replications:
If you are replicating from Oracle using mirroring or synchronization, enter a user ID which will be exclusively used by DBMoto and has the authority to read the database transaction log (redo log.) See a detailed list of authorities needed.
The list of Optional properties for .NET and OLE DB providers contains the most commonly used properties for the providers. Edit these as needed. Note that some properties are displayed with default values (no bold text.) Any values that you add or edit are displayed in bold text. Check the documentation for your provider for a complete list of properties. You can set the value of the ExtendedProperties property to define additional property-value pairs. The syntax for defining property-value pairs is: prop1=val1;prop2=val2;....
Edit
Click Edit to open a text entry window where you can paste or type a connection string for your provider. This is offered as an alternative to the Connection Properties grid, but should be used with great care because an error in the connection string can cause a connection to fail or to have unexpected properties. This window displays any
connection information that you have already entered in connection string format. Note that default values are not displayed as part of the connection string.
Test
Click Test to make sure that the connection correctly opens a database connection. 6. In the Setup Info screen, check the Use Transactional Replication option.
8. If you select LogServer, go to step 12. If you select Oracle, continue here.
The Log Settings field displays the Oracle data source where the log is stored. This field is read-only and by default matches the data source specified in the source connection. If you are using the multitenant database architecture introduced in Oracle 12, and connecting to a pluggable database, you may need to modify the log location to match the Oracle root, or container, database. To modify the log location, and access additional log settings, click Verify to enable the [...] button.
9. Click Verify to determine which type of logging is currently set. If you want to make changes to the current setting:
Choose
Minimal Level
to establish minimal supplemental logging at the database level, then, during
replication setup, add logging for the specific table(s) involved in the replication.
Choose
Database Level
to establish supplemental logging for the entire database, including primary key
and index information for all tables. If you have a large number of tables in your database, and they are not
involved in DBMoto replications, you should be aware that supplemental logging can add unnecessary
processing time/log information.
Click
Install
to make changes to the supplemental log settings.
Note that versions of DBMoto prior to 7.0.4 used database-level supplemental logging only.
10. Optionally, click [...] to open the Change Log Settings dialog to:
• Change the Oracle server where the log is stored (for Oracle 12 multitenant database installations).
• Set a path to an Oracle dictionary file (primarily for Oracle versions up to 9) • Change the archived log settings
Server
Specifies the Oracle data source where the log is stored. If you are using the multitenant database architecture introduced in Oracle 12, and connecting to a pluggable database, you need to modify the log location to match the Oracle root, or container, database.
User
User ID for the Oracle data source where the log is stored.
Password
Password for the Oracle data source where the log is stored.
Dictionary File
If the log miner dictionary is defined as a flat file (typically only for Oracle versions up to 9), type the path and file name for an Oracle dictionary file. The Oracle dictionary file should already be defined in your Oracle environment. See Setup for Different Oracle Versions for more information.
Read Archived Logs
When checked, DBMoto accesses Oracle archived redo logs for transactions in addition to the online redo logs. Check with your Oracle system administrator to see if your database is configured to use ARCHIVELOG mode. If it is, you can check the Read Archived Logs option to ensure that all transactions between mirroring intervals are identified by DBMoto. For example, if your mirroring interval is set for 90 seconds, but within that time the online logs fill with transactions, the online logs are archived as needed to accommodate additional transactions. If you check the Read Archived Logs option, DBMoto will be able to locate the last transaction ID from the archived log. Note that the Read Archived Logs option is not useful when using the default setting on Oracle databases, NOARCHIVELOG mode.
Use CONTINUOUS_MINE Option
When ReadArchivedLogs is checked, the Use CONTINUOUS_MINE Option is checked by default. The
CONTINUOUS_MINE option instructs the Oracle log reader to load archived log files continuously as they get created, instead of loading a batch of files statically when the log reader is initially instantiated. DBMoto can therefore replicate transactions as they are backed up. However, Oracle recommends that the CONTINUOUS_MINE option should not be used in a RAC (Real Application Cluster) environment where RAC nodes are continuously enabled/disabled.
Destination ID
This option allows you to set the location from which DBMoto will pick up Oracle archived logs for replication. When the value is set to the default, 0, DBMoto searches for Oracle archived logs in the standard destination, ('FLASH
RECOVERY AREA (FRA)' ). A value between 1 and 31 specifies an alternative location to retrieve archived logs. Oracle users can retrieve a list of all archive locations by running the query: SELECT * FROM V$ARCHIVE_DEST. This feature is useful when your environment includes a customized archive setup and you want to instruct DBMoto use a specific location for archived logs.
11. Optionally make changes in the Log Settings dialog and click OK. 12. Skip Step 12 (for the Log Server option only) and go to Step 13.
13. If you selected LogServer as the Use Transactional Replication option, complete the fields in the Setup Info screen as follows:
Log Files Folder
An existing folder where binary log files will be written by the Log Server.
Prefix
A prefix for all files created in the folder. This will allow easy identification and management of files associated with your connection.
Log Server Folder
An existing folder where connection configuration files are kept for the data source. This folder name is used as a parameter when running the Log Server application. If you configure different data sources for different Oracle databases, you need to specify different folder names.
The Log Server runs independently from the Data Replicator and configuration files that are shared by the two applications are stored in this folder. There are two separate folders so that the Log Server can also run on a different machine to the one running the Data Replicator.
If your data source is an Oracle version 12 pluggable non-root database, you also need to complete the root database connection information in the Log Container Server, User and Password fields:
Log Container Server
The connection string for the root database. This is required because Oracle 12 does not keep redo log records for the entire database instance separately.
The user and password for the Log Container Server connection.
Extra Options
Specify extra optional settings here. The option SERVER_OUTPUT_LOG_FOLDER can be used to tell the Log Server application to write the log files into this folder name instead of the one specified by the field Log Files Folder. This is useful if the Log Server application is run on a different computer on the same network and the directory/folder name structure is mapped differently from where DBMOTO is run. However, please note that while the two folder names are different on the two systems, they point to the same physical folder.
Usage example: SERVER_OUTPUT_LOG_FOLDER=k:/ora11/logfiles
Log File Size
The size of each binary log file in megabytes.
Keep Max Files
The maximum number of binary log files to keep. Combined with the log file size, this number needs to be large enough to make sure the files are read by DBMoto before they get deleted.
Trace
Unchecked by default. When checked, enables tracing for diagnostics and problem reporting. Trace files are logged in the Log Server folder.
Verify, Install and Remove
Click Verify to check that your environment including database logging is set up correctly.
Click Install to finalize the setup. Configuration files will be written. Once installed, the folder names and prefix
defined above cannot be modified.
Click Remove to disable and remove transactional replication for the connection. Note that you will not be able to
click Remove if there are replications defined that use the connection.
14. Click Next in the wizard Setup Info screen.
15. In the Select Tables screen, choose one or more objects from the treeview to associate with the source connection. Objects include tables, views and aliases.
When creating a replication, you will be able to select an object for replication from those that you have chosen in this wizard. If you create multiple replications, you can select an object for each replication that you are defining. Use the Select All Tables and Deselect All Tables buttons to work with multiple tables. Use the Hide System Tables checkbox to limit the number of tables displayed.
Select a database owner/schema, then click this button to check all tables under the owner/schema.
Select a database owner/schema, then click this button to uncheck all tables under the owner/schema.
16. Review the Summary screen.
17. If you want to create a target connection right away, check the option to Proceed with the definition of a target connection.
18. Click Finish to complete the wizard and create your connection.
Target Connection
1. To create a target connection, either the Target Connection Wizard opens automatically because you selected that option in the last screen of the Source Connection wizard, or you can select the Targets node in the Metadata Explorer.
Note that you must install, configure and test the connection to the database to which you are replicating before attempting to create a target connection to the database from DBMoto. For example, if you are replicating from Oracle to MySQL, you need .NET data providers installed and tested for both Oracle and MySQL. See
http://www.hitsw.com/support/kbase/DBMoto/Providers_DBMoto.htm for a current list of supported providers. 2. From the right mouse button menu, choose Add New Connection.
3. In the Target Connection Wizard, select the database to which you are replicating then follow steps to add a connection string and test the connection to the database.
If you are creating a connection to an Oracle database, refer to the steps above (Source Connection) for configuring the connection. Note that you do not need to configure the Setup Info screen unless you are planning to perform a synchronization replication using Oracle.
For all other databases, check the HiT Software knowledge base article on data providers before entering a value in the Assembly field.
4. Choose the tables to which you plan to replicate.
If a table does not exist, continue to the next screen without selecting a table. You can use the Create Target Table wizard once you have created the connection.
5. Complete the wizard.
2. Create a Target Table
If you are replicating to a database where no target table exists, you need to create the target table before defining the replication. If the target table already exists in the database to which you are replicating, go to Defining the Replication. The Target Table wizard allows you to create tables from DBMoto but the target schema/owner etc. (depending on your database type) must have been created prior to running the wizard.
1. Expand the DBMoto tree to display the table that contains the data you want to replicate.
3. In the Select Source Connection screen, choose the source name from the drop-down list that includes all the source connections you have created in DBMoto.
4. Choose the table that you want to replicate from the drop-down list.
5. If you want more information about the table before proceeding, click Open Table.... 6. Click Next to go to the Select Target Connection screen.
Choose a target source name from the drop-down list that includes all the target connections you have created in DBMoto.
7. Type a name for the table you wish to create in the target database. This table will contain the replicated data. 8. Click Next to go to the Define Columns screen.
9. Review the columns that will be created in the target table. You can add or remove columns as well as designate one or more columns as a primary key.
10. When you have finished editing the target table columns, click Next to go to the SQL Script screen.
11. If necessary, edit the SQL script that generates the table.
12. Click Next to review the wizard settings in the Summary screen. 13. To create additional tables, check the option Create another table.
This opens another Create Target Table wizard when this wizard is complete.
14. To go directly to creating a replication once this wizard is complete, check the option Proceed with the Definition of a Replication.
This opens the Replication wizard when the Create Target Table wizard is complete. 15. Click Finish to create the target table.
16. Note that the new table is not automatically displayed in the Metadata Explorer. Click Yes to add the newly created table to the list of target tables.
3. Define the Replication
This section explains how to create a single table-to-table replication. However, most real applications consist of many such replications and, if the replications use the same source and target databases, it may make sense to create a
replication group to optimize database connection and log access times. Use the Help menu in the Management Center to find out more information about grouping replications.
1. Expand the Metadata Explorer tree to display the table that contains the data you want to replicate. 2. Select the table.
4. In the Define Replication Type screen, type a name to identify the replication. 5. Optionally provide a description of the replication.
6. In the Replication Mode area, choose Continuous Mirroring.
7. Click Next to go to the Select Source Connection screen.
8. Choose the source name from the drop-down list that includes all the source connections you have created in DBMoto.
9. Choose the table that you want to replicate from the drop-down list.
10. If you want more information about the table before proceeding, click Open Table.... 11. Click Next to go to the Source Log Info screen.
Service Name
This is a unique identifier for your Oracle Server. The value is obtained automatically from your Oracle database server and cannot be modified.
Dictionary File
If using Oracle 9 or later, you can leave this field blank to use the online dictionary. If you prefer to supply a dictionary file, provide a path and dictionary file name on your Oracle database server. The online dictionary/dictionary file contains table information that is used in replication.
Transaction ID
The ID for the transaction at which you want to start replication. If you want to change the transaction ID, click Read to open the Read Point dialog. In this dialog, you can either retrieve the current transaction or the transaction for a specified date and time. If you enter a date and time, DBMoto retrieves the first transaction after the time
entered. This information is available and can be changed in the Replication Properties dialog after the wizard is completed.
Transaction Timestamp
The timestamp for the transaction above. Read Interval (sec)
The frequency (in seconds) with which you want to check the log during replication. For example, if the setting is 90 seconds, DBMoto will check the log every 90 seconds to see if any transactions have occurred that need to be
replicated to the target table. This information is available and can be changed in the Replication Properties dialog after the wizard is completed.
12. In the Transaction ID field, click Read to open the Read Point dialog.
13. Choose either the current transaction or a transaction ID based on a time and date. 14. Click OK to add the value to the Source Log Info screen.
15. Set the value of the Read Interval field to the frequency with which you want DBMoto to check the transaction log for new events to mirror.
16. Click Next to go to the Select Target Connection screen.
17. Choose a target source name from the drop-down list that includes all the target connections you have created in DBMoto.
18. Choose the table to which you want to replicate from the drop-down list.
If no tables are listed, you need to exit the wizard and add or create a target table. 19. If you want more information about the table before proceeding, click Open Table.... 20. Click Next to go to the Set Mapping Info screen.
Source and target columns with the same name are automatically mapped. You can also map columns by dragging the target column and dropping it on the source column, or you can map a column to an expression. For more
information about mapping, check the Replication Wizard help topic. An alternative is to write a script to set mappings at runtime.
21. Click Next to go to the Scheduling screen.
22. Make sure the Enable Replication option is checked. This is required for the replication to run.
23. Set a start time for the replication. The Start Time field indicates the time at which the Data Replicator will begin considering the replication for execution.
24. Check the option to Execute Initial Refresh if needed.
If you check this option, a full replication will be performed from the source to the target table, prior to starting the mirroring process where only incremental changes will be replicated.
25. Go to the Mirroring Schedule tab.
26. Select how you want to run the replication:
• Run Continuously: the transaction log will be checked for changes to the table using the frequency that you specified on the Set Log Info tab. Any changes will be replicated to the target table.
• Schedule Interruptions: the replication process will run as above, except for interruptions specified when you click the Schedule button in the Scheduler dialog.
27. Click Next to go to the Summary screen. 28. Click Finish to complete the wizard.
4. Run the Log Server Application
If you selected LogServer as the Use Transactional Replication option, you need to run the Log Server application along with the Data Replicator.
Java applications are provided to run, stop and reset the Log Server. Each of the applications requires and accepts the Server Log Folder as parameter. Three .cmd files have been created so you do not have to type in the command each time. Before using the .cmd files, they should be edited to set the correct JRE path and Java classpath.
Assuming that you have installed a supported JRE version, and copied the required Oracle JDBC jar files into the folder ServerFiles/OracleLogServer
• Open a Microsoft Windows Command/DOS window.
• Change the current directory to ServerFiles/OracleLogServer • To start/restart the server, run StartServer.cmd
Example: StartServer.cmd F:\OLS\Ora11
The first time a server starts, it reads records from the most recent transaction in the database. For any subsequent re-start, the server continues reading where it was last stopped so that no transaction is missed.
• To stop the server properly, open a new Command/DOS window and run StopServer.cmd. Example: StopServer.cmd F:\OLS\Ora11
• If you decide to reset/refresh all replications and have the server start and behave as it does the very first time, stop the Data Replicator, then run the command ResetServer.cmd
Example: ResetServer.cmd F:\OLS\Ora11
• After running ResetServer – before restarting the Data Replicator, make sure all replications are set to refresh or reset all TID to current.
5. Run the Replication
If you installed DBReplicator as a service during DBMoto setup, you just need to start the service using the
DBMServiceMonitor program (located in the DBMoto install folder or on the Windows Start > Programs > Startup menu.
• The replication that you have scheduled should start at the specified time.
• Use the Replication Monitor tab in the Management Center to track the progress of the replication. If you would like to set up the DBMoto Replicator as a service:
• From the Windows Desktop Start menu, choose Programs, then HiT Software DBMoto, then Service Installer. • Manage the service from DBMoto Service Monitor program (located in the Windows Notification Area , or in the
DBMoto install folder or on the Windows Start > Programs > Startup menu).
• Use the Replication Monitor tab in the Management Center to track the progress of the replication. To run the DBMoto Data Replicator interactively:
• In the Windows Notification Area, select the DBMoto Service Monitor icon.
• From the right mouse button menu, choose DBMoto Data Replicator, then Start then Application. The replication that you have scheduled should start at the specified time.
• Use the Replication Monitor tab in the Management Center to track the progress of the replication.
Database Access Security Topics
Using an Oracle database as a source for mirroring or synchronization in DBMoto requires a user ID with a high level of privileges because DBMoto accesses the Oracle log. Consequently, a user running the DBMoto Management Center with an established connection to the database can freely run SQL commands against the database using the Execute SQL Query option (available in the Metadata Explorer from the right mouse button menu for the connection.)
The Execute SQL Query option opens a SQL Query tab in the Management Center, and provides access to a full range of SQL commands, including those that may modify the database (e.g. UPDATE, INSERT, DELETE).
To control unlimited access to the database, set up user profiles and login IDs within DBMoto. While there must be at least one DBMoto login ID with a full range of permissions, all remaining users can be assigned login IDs with restricted permissions. To prevent write access to the database, login IDs should have the Browse Connection permission set to False. Login IDs with the user roles Auditor, Operator and Public have the Browse Connection permission set to False by default. When the permission is set to False and a user opens the SQL Query tab, a database login dialog is displayed. A brief overview of the steps involved in setting up DBMoto user IDs is presented below. For complete details, see the DBMoto User Guide available from the DBMoto Management Center Help menu.
To set up DBMoto user IDs in the DBMoto Management Center:
1. In the Metadata Explorer, select the server for which you want to set up IDs (typically “local”). 2. From the right mouse button menu, choose Manage Users.
3. In the User Settings dialog, set the authentication to DBMoto Authentication.
4. In the DBMoto Users section, click Add.
5. In the Create DBMoto Login dialog, create at least one DBMoto login with an Administrator role, followed by any number of other logins with restricted permissions.
Configure the Oracle Client
Oracle 11 and 12 Client installation does not require extra steps to set up the Net Service Name for Oracle. For this reason, HiT Software recommends using the Oracle 11 or 12 Client. However, for those who are required to use Oracle 10, this section provides information on configuring the Oracle 10 Client and the Net Service Name for the Oracle client. 1. Run the installation process for the Oracle client that contains the Oracle ,NET Provider.
The Oracle Net Configuration Assistant starts up automatically after you install the Oracle client. You can also start it manually from the Windows Start menu: All Programs -> Oracle – OraClient10g_homes1 -> Configuration and Migration Tools -> Net Configuration Assistant.
2. Enter the service name and click Next.
3. For the ACCESS_LOG network protocol, select TCP.
5. Select the option Yes, perform a test and click Next.
6. The test may fail because you do not have the correct login and password information.
7. Click Change Login.
8. Enter the user ID and password of your Oracle system, then click OK.
9. When the test is successful, click Next.
11. Follow the on-screen instructions to complete the setup.
Last Updated on 3/17/15
