1.1 What’s New
2 Overview
2.1.4 Migrating Data
2.1.5 Write Availability
2.1.6 Write Scalability
2.1.7 Localized Data Access
2.2.4 Multi-Master Replication
2.2.5 Asynchronous
2.2.8 Snapshot Replication
2.2.12 Table Filters
2.2.10.1 Requirements and Restrictions
2.2.10.2 Logical Replication Slots
2.2.10.4 Replication Origin
2.2.10.5 In-Memory Caching and Persistence
2.2.12.1 Implementing Table Filters
2.2.12.2 Effects of Table Filtering
2.2.12.4 Roadmap for Further Instructions
2.3.1 Physical Components
2.3.2 Logical Components
2.3.1.1 Publication Server
2.3.1.2 Subscription Server
2.3.1.4 xDB Startup Configuration File
2.3.1.5 xDB Replication Console
2.3.1.7 Publication Database
2.3.1.8 Subscription Database
2.3.1.9 Master Node
2.3.1.10 Master Definition Node
2.3.1.12 Controller Database
2.3.2.1 Publication Server
2.3.2.3 Publication Database Definition
2.3.2.4 Publication
2.3.2.5 Subscription Server
2.3.2.6 Subscription Database Definition
2.3.2.7 Subscription
2.4.1 General Steps
2.4.2 Design Considerations
2.4.5 Distributed Replication
2.4.5.1 Single Host
4.1.1 Refresh
4.1.2 Create Publication
4.1.3 Publication Management
4.1.4 Create Subscription
4.1.5 Subscription Management
4.2.1 Server Login File
5.1.3.1 Enabling Access to Oracle
5.1.3.2 Enabling Access to SQL Server
5.1.4.1 Oracle Publication Database
5.1.4.2 SQL Server Publication Database
5.1.4.3 Postgres Publication Database
5.1.5.1 Postgres Subscription Database
5.1.5.2 Oracle Subscription Database
5.1.5.3 SQL Server Subscription Database
5.1.6.1 Firewalls and Access to Ports
5.1.6.2 Network IP Addresses
5.1.6.3 Postgres Server Authentication
5.2.3 Adding a Publication
5.2.4.1 Oracle Control Schema Objects
5.2.4.3 Postgres Control Schema Objects
5.3.3 Adding a Subscription
5.5.3 Updating a Subscription
5.5.5 Removing a Subscription
5.8.2.1 Using Prepared SQL Statements
5.8.2.2 Parallel Synchronization
6.2.3 Adding a Publication
6.6.2 Conflict Types
6.6.3 Conflict Detection
6.6.8 Custom Conflict Handling
6.6.6.1 Creating an MMR-Ready Sequence
6.6.6.2 MMR-Ready Sequence Example
6.6.9.1 Finding Conflicts
6.6.9.2 Conflict Resolution Preparation
6.6.10.1 Finding Conflicts
6.6.10.3 Overview of Correction Strategies
6.6.10.4 Manual Publication Table Correction
6.6.10.5 Correction Using New Transactions
7.3.1 Updating a Schedule
7.3.2 Removing a Schedule
7.4.1 All Replication History
7.4.3 Shadow Table History
7.5 Managing History
7.6.3 Updating a Publication
7.6.5 Validating a Publication
7.6.6 Removing a Publication
7.6.1.1 Publication Server Login File
7.6.3.1 Adding Tables to a Publication
7.6.5.1 Validating a Single Publication
7.6.5.2 Validating All Publications
8.2 General Usage
8.2.2 Getting Help
8.3.1 Getting Help (help)
10 Appendix
10.3 Resolving Problems
10.3.1 Error Messages
10.3.2 Where to Look for Errors
10.3.3 Common Problem Checklist
10.3.4 Troubleshooting Areas
10.3.2.1 General Replication Status
10.3.2.2 Snapshot Replication Failures
10.3.2.3 Synchronization Replication Failures
10.3.2.5 Database Server Errors
10.3.2.6 Oracle Errors
10.3.4.1 Java Runtime Errors
10.4.3 Writing a Cron Expression
10.4.1.2 Replacing Null Characters
10.4.1.3 Schema Migration Options
10.4.1.6 Snapshot Replication Options
10.4.1.8 Using pgAgent Job Scheduling
10.4.1.10 Setting Event History Cleanup Threshold
10.4.1.11 DDL Change Replication Table Locking
This document describes the installation, configuration, architecture, and operation of the EDB xDB Replication Server. EDB xDB (cross database) Replication Server (referred to hereafter as xDB Replication Server) is an asynchronous replication system available for PostgreSQL® and for EDB Postgres™ Advanced Server. The latter will be referred to simply as Advanced Server.xDB Replication Server can be used to implement replication systems based on either of two different replication models – single-master (master-to-slave) replication or multi-master replication.Note: Oracle Real Application Clusters (RAC) and Oracle Exadata are not supported by xDB Replication Server. These Oracle products have not been evaluated nor certified with xDB Replication Server. See Section 10.1 for the certified and supported database server products that may be used with xDB Replication Server.
1.1 What’s New
In the following descriptions a term refers to any word or group of words that are language keywords, user-supplied values, literals, etc. A term’s exact meaning depends upon the context in which it is used.
2 Overview
2.1.4 Migrating Data2.1.5 Write Availability2.1.6 Write Scalability2.1.7 Localized Data Access
xDB Replication Server is a software product that enables the implementation of a replication system. A replication system is software and hardware whose purpose is to make a copy of data from one location to another and to ensure the copied data is the same as the original over time.
This section describes the components and architecture of xDB Replication Server. Section 2.3.1 describes the executable programs, files, and databases that comprise xDB Replication Server. Section 2.3.2 defines the logical components of a replication system and how they correspond to the programs and databases. Section 2.3.3 illustrates some examples of replication systems.2.3.1 Physical Components
2.4.1 General StepsStep 1: Determine if xDB Replication Server is the right solution for your requirements and you have chosen the best solution for your particular needs. xDB Replication Server can be used to implement single-master or multi-master replication systems. For single-master replication systems, the distinguishing characteristic of xDB Replication Server is its ability to replicate from an Oracle database to a PostgreSQL or Advanced Server database, from a SQL Server database to a PostgreSQL or Advanced Server database, from an Advanced Server database to an Oracle database, or from a PostgreSQL or Advanced Server database to a SQL Server database.Step 2: Plan the general strategy of how you will use xDB Replication Server. Will the single-master or multi-master model best suit your needs? (See Section 2.1 for use case examples of single-master and multi-master replication systems.) Will you be replicating from Oracle to Postgres, from SQL Server to Postgres, from Advanced Server to Oracle, or from Postgres to SQL Server? Will you be replicating between PostgreSQL and/or Advanced Server databases? How often will you need to replicate the data? Will replication be done on an ad hoc basis or does it need to occur regularly according to a schedule?Step 3: Plan the logistics of your replication system. How many tables do you expect to replicate and what are their sizes in total number of bytes and number of rows? What percentage of rows do you expect to have been changed on each table between each replication? Are your database servers required to run on dedicated machines?Step 4: Design your replication system. Determine whether your replication system will be distributed or will run on a single host. Determine the publications and subscriptions you will need and their tables and views. Make sure your publication tables meet the requirements for an xDB Replication Server publication. See sections 2.4.2 and 2.4.3 for details.Step 5: Implement and test your replication system in a test environment. Try out your replication system on a subset of your publication data to ensure the replication process works as expected. Make sure the resulting replicated tables can be used as expected in your application. Establish preliminary metrics on how long the replication process will be expected to take in your full production environment.Step 6: Implement and test your replication system in your production environment.2.4.2 Design Considerations
Stack Builder and StackBuilder Plus are programs used to download and install add-on products and updates to PostgreSQL and Advanced Server. Stack Builder is used for PostgreSQL. StackBuilder Plus is used for Advanced Server.Step 1: You must have Java Runtime Environment (JRE) version 1.7 or later installed on the hosts where you intend to install any xDB Replication Server component (xDB Replication Console, publication server, or subscription server). Any Java product such as Oracle Java or OpenJDK may be used.For Windows only: Be sure the system environment variable, JAVA_HOME, is set to the JRE installation directory of the JRE version and bitness (32-bit or 64-bit) you wish to use with the xDB Replication Server. The xDB Replication Server installer for a Windows platform contains both the 32-bit and 64-bit versions. The JAVA_HOME setting determines whether the 32-bit or the 64-bit version of xDB Replication Server is installed. (If JAVA_HOME is not set, then the first JRE version encountered in the Path system environment variable determines the xDB Replication Server version to be installed.)Note: For Advanced Server versions prior to 9.3, a Java runtime is supplied and installed as part of the Advanced Server installation process, however, you must still have pre-installed a separate Java runtime system on your host. The xDB Replication Server installation process does not utilize the Java runtime supplied with Advanced Server.Note: After installation of xDB Replication Server has completed, the path to your Java runtime program is stored in the xDB Startup Configuration file used by xDB Replication Server. Verify that the path to your Java runtime program set in the xDB Startup Configuration file is correct. See Section 3.5 for the location of this file.Step 2: From the host’s application menu, open the Postgres menu and choose Stack Builder or StackBuilder Plus.Step 3 (For Linux only): Depending upon your Linux host, a dialog box or a prompt appears requesting the root account’s password. Enter the root password and click the OK button.Step 4: The StackBuilder Plus welcome screen appears. Select your Postgres installation from the drop-down list and click the Next button.Step 5 (For Advanced Server): Expand the EnterpriseDB Tools node and check the box for Replication Server. Click the Next button.Note: Though the following images show Replication Server v6.0, use the same process for Replication Server v6.2.Step 5 (For PostgreSQL): Expand the Registration-Required and Trial Products node, and then expand the EnterpriseDB Tools node. Check the box for Replication Server under the EnterpriseDB Tools list and click the Next button.Step 6 (For Advanced Server only): In the Account Registration screen, either enter your email address and password for your EnterpriseDB user account if you have one, or click the link in which case you will be directed to the registration page of the EnterpriseDB website where you can create an account. Click the Next button.Note (For PostgreSQL only): Proceed to Step 7. If you are using PostgreSQL, account registration occurs later in the process.Step 7: Verify that Replication Server appears in the list of selected packages. Click the Next button.Step 8: When downloading of the Replication Server package completes, the following screen appears that starts the installation of xDB Replication Server. Click the Next button.Note: You can check the Skip Installation box if you wish to install xDB Replication Server some other time.Step 9: Select the installation language and click the OK button.Step 10: In the Setup xDB Replication Server screen, click the Next button.Step 11: Read the license agreement. If you accept the agreement, select the accept radio button and click the Next button.Step 12: Browse to a directory where you want the xDB Replication Server components installed, or allow it to install the components in the default location shown. Click the Next button.Step 13: If you do not want a particular xDB Replication Server component installed on this particular host, uncheck the box next to the component name. Click the Next button.Step 14: In the Account Registration screen select the radio button that applies to you. Click the Next button.Step 15: Enter information for the xDB administrator.
For information about using the EDB Yum Repository see Chapter 3 of the EDB Postgres Advanced Server Installation Guide available from the EnterpriseDB website located at:Note: Although the following primarily describes the installation of xDB Replication Server version 6.2, access to the RPM packages for prior xDB Replication Server versions are also described in order to differentiate the installation of these different versions.Each xDB Replication Server component is available as an individual RPM package. Thus, you can install all xDB Replication Server components with a single yum install command, or you may choose to install selected, individual components by installing only those particular RPM packages.
Each command creates a repository configuration file in the /etc/zypp/repos.d directory. The files are named:After creating the repository configuration files, use the zypper refresh command to refresh the metadata on your SLES host to include the EnterpriseDB repositories:When prompted for a User Name and Password, provide your connection credentials for the EnterpriseDB repository. If you need credentials, visit the following website:zypper addrepo "http://download.opensuse.org/repositories/Java:/Factory/SLE_12_SP2/Java:Factory.repo"
zypper addrepo "http://download.opensuse.org/repositories/server:/Kolab:/3.3/SLE_12/server:Kolab:3.3.repo"Note: Before starting the publication server and subscription server, the /etc/hosts file must contain an entry for the host name that associates it to the host IP address as shown by the following example where 192.168.187.133 is the IP address and linux-dm8s is the host name:
On Linux hosts where you installed xDB Replication Server with the graphical user interface or from the command line, you should now have a publication server daemon and a subscription server daemon running on your computer assuming you chose to install the publication server and subscription server components. If you installed the xDB RPM package, you must start the publication server and the subscription server based upon the instructions in Section 5.2.1 for the publication server and Section 5.3.1 for the subscription server. On Windows systems, the publication server and subscription server run as services named Publication Service and Subscription Service.Note: On some Linux systems, you may have to restart the server before you can see the xDB Replication Console choice in the application menu. If the xDB Replication Console choice is still unavailable in the application menu, it can be started by invoking the script XDB_HOME/bin/runRepConsole.sh.Note: For xDB Replication Server installed from an xDB RPM package, the xDB Replication Console is started by invoking the script XDB_HOME/bin/runRepConsole.sh.
If you installed xDB Replication Server using the xDB Replication Server installer program invoked from Stack Builder or StackBuilder Plus as described in Section 3.1 or you invoked the xDB Replication Server installer program from the command line as described in Section 3.2, uninstall xDB Replication Server by invoking the uninstall-xdbreplicationserver script as described in this section.If you installed xDB Replication Server from the RPM package, uninstall it using the Yum package manager. See Section 3.7 for information.For Linux only: The following steps are for uninstalling xDB Replication Server from a Linux host.Step 1: As the root account, run the XDB_HOME/uninstall-xdbreplicationserver script from the directory where you installed xDB Replication Server.Step 2: Click the Yes button to confirm uninstallation of xDB Replication Server.Step 3: The Uninstallation Completed dialog box appears when the process has completed. Click the OK button.For Windows only: The following steps are for uninstalling xDB Replication Server from a Windows host.Step 1: From the Windows Control Panel, select Uninstall a Program.Step 2: Select the xDB Replication Server product in the list of programs to uninstall or change. Click the Uninstall/Change button.Step 3: Click the Yes button to confirm uninstallation of xDB Replication Server.Step 4: The Uninstallation Completed dialog box appears when the process has completed. Click the OK button.
If you installed xDB Replication Server from the RPM package, you can uninstall any xDB component by invoking the yum remove package_name command as the root account where package_name is any xDB Replication Server component RPM package as listed in the table in Section 3.3.
The xDB Replication Console is the graphical user interface that you use to configure and manage the replication system. The equivalent functionality can also be done using the xDB Replication Server CLI utility. See Chapter 8 for information on the xDB Replication Server CLI.
This section describes when the various tool bar icons are activated. The operations associated with the tool bar are described in sections 5.2 and 5.3 for single-master replication. For multi-master replication see Section 6.2.Note: The publication server must be running in order to use tools relevant to publications. Similarly, the subscription server must be running in order to use tools relevant to subscriptions.4.1.1 Refresh4.1.2 Create Publication4.1.3 Publication Management4.1.4 Create Subscription4.1.5 Subscription Management
4.2.1 Server Login FileIf you choose to save the login information, the server’s network location (IP address and port number), admin user name, and password are stored in a server login file in a hidden location under the home directory of the operating system account with which you have opened the xDB Replication Console. See Section 3.5 for the location of this file.The following shows the Register Publication Server dialog box where the option to save login information is presented as a check box. In this example 192.168.2.22 entered in the Host field, 9051 entered in the Port field, admin entered in the User Name field, and an encrypted form of the password entered in the Password field are saved in the server login file for this publication server if the admin user name and password validation are successful.The values for User Name and Password that you enter are validated against the admin user name and password in the xDB Replication Configuration file residing on host 192.168.2.22, in this case. The admin user name and password must successfully authenticate before registration of the publication server and saving of the publication server’s login information in the server login file occur. See Section 2.3.1.3 for information on the xDB Replication Configuration file.See Section 5.2.1 for more information on the purpose of these fields and the process of registering a publication server.The following shows the Register Subscription Server dialog box. In this example 192.168.2.22 entered in the Host field, 9052 entered in the Port field, admin entered in the User Name field, and an encrypted form of the password entered in the Password field are saved in the server login file for this subscription server if the admin user name and password validation are successful.See Section 5.3.1 for more information on the purpose of these fields and the process of registering a subscription server.Note: Each operating system account on a given host has its own server login file. Thus, the servers that are saved and appear in the xDB Replication Console when opened is independently determined for each operating system account.Note: The publication database and subscription database cannot be deleted, but unauthorized replications could be forced to occur.
For configuration and management of your replication system, the xDB Replication Console graphical user interface is used to illustrate the steps and examples in this chapter. The same steps can be performed from the operating system command line using the xDB Replication Server Command Line Interface (CLI). The commands of the xDB Replication Server CLI utility are described in Chapter 8.
The publication server and the subscription server are configured to run with a default set of heap size parameters. Either the default settings for 32-bit platforms or the default settings for 64-bit platforms are set by parameter JAVA_HEAP_SIZE when xDB Replication Server is installed.This parameter is configured in the xDB Startup Configuration file. See Section 2.3.1.4 for information on the xDB Startup Configuration file.On a 32-bit system, the initial heap size is set to 128 megabytes (-Xms128m) and the maximum limit is set to 512 megabytes (-Xmx512m). On a 64-bit system the initial heap size is 256 megabytes (-Xms256m) and the maximum limit is 1536 megabytes (-Xmx1536m).The default values can be modified by changing the JAVA_HEAP_SIZE parameter setting in the xDB Startup Configuration file. Be sure to restart the publication server and the subscription server (see sections 5.2.1 and 5.3.1) after making such changes.
Once the publication database has been added, as many publications can be created as there are available tables and views that are readable by the publication database user and that meet the criteria outlined in sections 2.4.2 and 2.4.3.Step 1: Start the publication server if it is not already running.Note: If you are using Oracle publication or subscription databases, and the publication server has not been restarted since copying the Oracle JDBC driver to the lib/jdbc subdirectory of your xDB Replication Server installation, you must restart the publication server.For Linux only: You can verify the publication server is running by using the systemctl command for CentOS 7 or RHEL 7, and the service command for previous Linux versions.If the publication server is not running, use the start option.Similarly, use the stop option to stop the publication server.For Windows only: Open Control Panel, System and Security, Administrative Tools, and then Services. The publication server runs as a service named Publication Service for xDB Replication Server.If the publication server fails to start, see Section 10.3.2.4 for information.Step 2: Register the publication server. Open the xDB Replication Console from the system’s application menu. For xDB Replication Server installed from an xDB RPM package, the xDB Replication Console is started by invoking the script XDB_HOME/bin/runRepConsole.sh.Step 3: Select the top level Replication Servers node. From the File menu, choose Publication Server, and then choose Register Server. Alternatively, click the secondary mouse button on the Replication Servers node and choose Register Publication Server. The Register Publication Server dialog box appears.
Step 1: Start the subscription server if it is not already running. Repeat the same process as in Step 1 of Section 5.2.1.Note: If you are using Oracle publication or subscription databases, and the subscription server has not been restarted since copying the Oracle JDBC driver to the lib/jdbc subdirectory of your xDB Replication Server installation, you must restart the subscription server.For Linux only: Use the systemctl command for CentOS 7 or RHEL 7, and the service command for previous Linux versions to start, stop, or restart edb-xdbsubserver for the subscription server. See Section 5.2.1 for information on how these commands are used.For Windows only: Open Control Panel, System and Security, Administrative Tools, and then Services. Use the Start or Restart link for the service named Subscription Service for xDB Replication Server.If the subscription server fails to start, see Section 10.3.2.4 for information.Step 2: Register the subscription server. Open the xDB Replication Console from the system’s application menu. For xDB Replication Server installed from an xDB RPM package, the xDB Replication Console is started by invoking the script XDB_HOME/bin/runRepConsole.sh.Step 3: Select the top level Replication Servers node. From the File menu, choose Subscription Server, and then choose Register Server. Alternatively, click the secondary mouse button on the Replication Servers node and choose Register Subscription Server. The Register Subscription Server dialog box appears.
This section discusses the procedure for initiating a replication on demand. Section 7.2 discusses how to create a schedule.Step 1: Select the Subscription node of the subscription for which you wish to perform snapshot replication.Step 2: Open the Snapshot dialog box in any of the following ways:Step 3: Select the Verbose Output check box only if you want to display the output from the snapshot in the dialog box. This option should be left unchecked in a network address translation (NAT) environment as a large amount of output from the snapshot may delay the response from the Snapshot dialog box. Click the Snapshot button to start snapshot replication.Step 4: Snapshot Taken Successfully appears if the snapshot was successful. Click the OK button. If the snapshot was not successful, scroll through the messages in the Snapshot dialog box window if Verbose Output was selected or check the log files.The status messages of each snapshot are saved in the Migration Toolkit log files named mtk.log[.n] (where [.n] is an optional history file count if log file rotation is enabled) in the following directories:POSTGRES_HOME is the home directory of the Windows postgres account (enterprisedb account for Advanced Server installed in Oracle compatible configuration mode). The specific location of POSTGRES_HOME is dependent upon your version of Windows. The xDB Replication Server version number is represented by x.x.The publication has now been replicated to the subscription database. A record of the snapshot is maintained in the replication history. See Section 7.4 for information on how to view replication history.Step 1: When the trigger-based method of synchronization replication is in use, select the Subscription node of the subscription for which you wish to perform synchronization replication.Step 2: Open the Synchronize dialog box in any of the following ways:Step 3: Click the Synchronize button to start synchronization replication.Step 4: Subscription Synchronized Successfully appears if the synchronization was successful. Click the OK button. If the synchronization was not successful, scroll through the messages in the Synchronize dialog box window.The operations that were applied to the subscription tables can be seen in the replication history. See Section 7.4 for information on how to view replication history.
Note: This section discusses various aspects of managing a subscription of a replication system. For a similar discussion on managing a publication of a replication system, see Section 7.6.When you register a subscription server in the xDB Replication Console, you may choose to save the subscription server’s network location (IP address and port number), admin user name, and encrypted password in a server login file on the computer on which you are running the xDB Replication Console. See Section 4.2 for information on saving the login information.Step 1: The subscription server whose login information you want to save, change, or delete in the server login file must be running before you can make any changes to the file. See Step 1 of Section 5.3.1 for directions on starting the subscription server.Step 2: Click the secondary mouse button on the Subscription Server node and choose Update. The Update Subscription Server dialog box appears.Step 3: Complete the fields in the dialog box according to your purpose for updating the server login file:Step 4: Click the Update button. If the dialog box closes, then the update to the server login file was successful. Click the Refresh icon in the xDB Replication Console tool bar to show the updated Subscription Server node.When you create a subscription database definition, you save the subscription database server’s network location (IP address and port number), the database identifier, a database login user name, and the user’s password in the control schema accessed by the subscription server. This login information is used whenever a session needs to be established with the subscription database. See Section 5.3.2 for information on creating a subscription database definition.Note: Depending upon the database type (Oracle, SQL Server, or Postgres), certain attributes must not be changed. If you have already added subscriptions, you must not change any attribute that alters access to the schema where the subscription tables were created.Step 1: Make sure the database server that you ultimately wish to save as the subscription database definition is running and accepting client connections.Step 2: Make sure the subscription server whose node is the parent of the subscription database definition you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 3: Select the Subscription Database node corresponding to the subscription database definition that you wish to update.Step 4: From the Subscription menu, choose Subscription Database, and then choose Update Database. Alternatively, click the secondary mouse button on the Subscription Database node and choose Update Database. The Update Database Source dialog box appears.Step 5: Enter the desired changes. See Step 3 of Section 5.3.2 for the precise meanings of the fields.Step 6: Click the Test button. If Test Result: Success appears, click the OK button, then click the Save button.Step 7: Click the Refresh icon in the xDB Replication Console tool bar to show the updated Subscription Database node and any of its subscriptions.5.5.3 Updating a SubscriptionStep 1: Make sure the subscription server whose node is the parent of the subscription you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 2: Select the Subscription node whose attributes you wish to update.Step 3: From the Subscription menu, choose Update Subscription. Alternatively, click the secondary mouse button on the Subscription node and choose Update Subscription. The Update Subscription dialog box appears.Step 4: If the publication server now runs on a host with a different IP address or port number than what is shown in the dialog box, enter the correct information. You must also enter the admin user name and password saved in the xDB Replication Configuration file that resides on the host on which the publication server is running. Click the Update button.Step 5: If Subscription Updated Successfully appears, click the OK button, otherwise investigate the error and make the necessary corrections.Step 6: If the publication server with the new network location manages publications subscribed to by other subscriptions, repeat steps 1 through 5 for these other subscriptions.Table filters must first be defined in a set of available table filters in the publication before they can be enabled on a subscription. See Section 5.2.3 for information on defining table filters in a single-master replication system.Step 1: Make sure the publication server whose node is the parent of the publication associated with the subscription you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server. Make sure the subscription server whose node is the parent of the subscription you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 2: Select the Subscription node of the subscription on which you wish to enable or disable individual filter rules.Step 3: Open the Filter Rules tab in any of the following ways:Step 4: In the Filter Rules tab check or uncheck the boxes to specify the filter rules to enable or disable on the subscription. At most one filter rule may be enabled any given subscription table. Click the Update button.Step 5: A confirmation box appears presenting a warning message and a recommendation to perform a snapshot replication to any subscription on which you changed the filtering criteria.Step 6: If you clicked the Ok button in the preceding step, the Filter Rules updated successfully confirmation message appears if the update was successful.Step 7: It is strongly recommended that a snapshot replication be performed to the subscription that contains tables on which the filtering criteria has changed.A snapshot ensures that the content of the subscription tables is consistent with the updated filtering criteria. See Section 5.4.1 for information on performing a snapshot replication.5.5.5 Removing a SubscriptionStep 1: Make sure the subscription server whose node is the parent of the subscription you wish to remove is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 2: Select the Subscription node of the subscription that you wish to remove.Step 3: Remove the subscription in any of the following ways:Step 4: In the Remove Subscription confirmation box, click the Yes button.Deleting a subscription database definition from xDB Replication Server is equivalent to removing its Subscription Database node. Before a Subscription Database node can be removed, all subscriptions under that Subscription Database node must be removed. See Section 5.5.5 for removing a subscription.Step 1: Make sure the subscription server whose node is the parent of the subscription database definition you wish to remove is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 2: Select the Subscription Database node that you wish to remove.Step 3: From the Subscription menu, choose Subscription Database, then Remove Database. Alternatively, click the secondary mouse button on the Subscription Database node and choose Remove Subscription. The Remove Subscription Database confirmation box appears.Step 4: In the Remove Subscription Database confirmation box, click the Yes button.
Controlled switchover is the exchanging of roles between a publication database and a subscription database. That is, the tables that were formerly publications become the subscription tables. The former subscription tables now become the publications.Note: This discussion assumes that the trigger-based method of synchronization replication is used by the publication database. If the publication database employs the log-based method, then it must be determined if the current subscription database meets the criteria for using the log-based method if that is so desired when it is switched to the role of the publication database. If the subscription database does not meet the criteria, then the trigger-based method must be implemented and used. See Section 2.2.10 for information on the log-based method and the necessary configuration steps that must be performed if the log-based method is to be used.
Failover is the replacement of the publication database by the subscription database should a failure occur on the publication database or its host. Failover is considered an irreversible action so the subscription database permanently takes over the role of the publication database.Generally, the same steps must be followed to perform a failover as was discussed for a controlled switchover in Section 5.6, however, the following points must also be taken into consideration:
The publication server and subscription server configuration options are set in the publication server and subscription server configuration files, respectively. See Section 10.4.1 for a detailed explanation of how to set the configuration options in these files.Note: Most of these configuration options are applicable to multi-master replication systems as well. Options applicable to multi-master replication systems are those that apply to the publication server and are not specific to a database product other than Postgres (such as an Oracle feature).Note: The options described in this section apply to the publication server only and are set in the publication server configuration file unless otherwise specified.When the copyViaDBLinkOra option is set to true, the Oracle database link API, dblink_ora, is used instead of JDBC COPY to populate Advanced Server subscription tables from an Oracle publication during snapshot replication.Note: The Oracle database link API feature is not available with PostgreSQL, therefore the copyViaDBLinkOra option is not applicable to PostgreSQL subscription tables.Note: Prior to using dblink_ora with xDB Replication Server, there are a number of required configuration steps that must be performed in Advanced Server. For Advanced Server versions 9.3 or earlier, see the readme text file, README-dblink_ora_setup.txt located in the POSTGRES_INSTALL_HOME/doc/contrib directory for directions. For Advanced Server versions 9.4 or later, see Chapter dblink_ora in the Database Compatibility for Oracle Developer’s Guide for directions.The default value is false.Set the useFastCopy option to true to skip Write-Ahead Log (WAL) logging during COPY operations in order to optimize data transfer speed.The archive_mode configuration parameter in the postgresql.conf file of the target Postgres database server must be off (thereby disabling archiving of WAL data) in order to use the useFastCopy option.The default value is false.Use the cpBatchSize option to set the batch size (in Megabytes) that is used in the JDBC COPY operation during a snapshot. Increase the value of this option for large publication tables.This option is influential when Postgres is the subscription database since the JDBC COPY operation is used to load Postgres subscription tables.This option has no effect when Oracle or SQL Server is the subscription database. To tune loading of Oracle or SQL Server tables alter the batchSize option.This option is particularly significant when Oracle or SQL Server is the subscription database since tables of these database types are loaded using JDBC batches of INSERT statements.For a Postgres subscription database, tables are loaded using JDBC COPY, however, if the COPY operation fails for some reason, then table loading is retried using JDBC batches of INSERT statements as in the case of Oracle and SQL Server.Set the skipAnalyze option to true if you want to skip execution of the ANALYZE command after loading Postgres subscription tables. The ANALYZE command gathers statistical information on the table contents. These statistics are used by the query planner.The default value is false.Note: To apply this option to a single-master replication system, it must be set for the subscription server within the subscription server configuration file. To apply this option to a multi-master replication system, it must be set for the publication server within the publication server configuration file.The snapshotParallelLoadCount option controls the number of threads used to perform snapshot data replication in parallel mode. The default behavior is to use a single thread. However, if the target system architecture contains multi-CPUs/cores you can specify a value greater than 1, normally equal to the CPU/core count, to fully utilize the system resources.If a table contains a column with a data type typically used for large objects such as BYTEA, BLOB, or CLOB, there is a greater possibility that a heap space error may occur because of a potentially large amount of data (hundreds of megabytes) brought into memory. In order to minimize the possibility of this error, a snapshot replication loads tables containing a large object data type, one row at a time using a single INSERT statement per batch.If however, the large object data type column is known to contain relatively small amounts of data, you can increase the speed of a snapshot replication by increasing the value of the lobBatchSize option to allow a greater number of rows (specified by n) in each batch.In addition, for configuration options specifically applicable to publication databases configured with the log-based method of synchronization replication, see Section 10.4.1.15.Note: The options described in this section apply to the publication server only and are set in the publication server configuration file.5.8.2.1 Using Prepared SQL StatementsWhen synchronization replication occurs, the changes recorded in the shadow tables are applied to the subscription tables in JDBC batch updates. Within each batch, changes may be applied using either an individual SQL statement for each change; or a set of changes may be applied using a single, prepared SQL statement. A prepared SQL statement is parsed and compiled only once, but it can be executed multiple times using different values for certain components of the SQL statement in each execution. A SQL statement that is not prepared is parsed, compiled, and executed only once.Prepared statements are useful only if the same type of SQL statement (INSERT, UPDATE or DELETE) is executed repeatedly and consecutively with the same target table, but with different values. If there is a sequence of consecutive changes that occur to the same table using the same operation such as inserting a set of rows into the same table populating the same columns, the publication server may apply these changes using a prepared statement. Otherwise, each change is applied with its own individual SQL statement.The defaultBatchUpdateMode option controls whether the default mode is to use individual SQL statements in the JDBC batch update (this mode of operation is referred to as BUS) or to use prepared SQL statements in the JDBC batch update (this mode of operation is referred to as BUP).The default value is BUS.The switchBatchUpdateMode option controls whether or not the publication server dynamically switches between BUS mode and BUP mode during the replication process depending upon the type and sequence of updates it encounters in the shadow tables for the trigger-based method or the changeset stream for the log-based method.The default value is true.This means using the default settings of defaultBatchUpdateMode=BUS and switchBatchUpdateMode=true, the publication server starts out by applying updates with individual SQL statements. When it encounters a stream of consecutive changes that can all be processed in a single prepared statement, it will switch to using prepared SQL statements.Note: If you want a certain batch update mode used throughout all synchronization replications applied by a given publication server without switching update modes, set the defaultBatchUpdateMode option to the desired mode in combination with switchBatchUpdateMode=false. For example, if you only want prepared statements used, set the following options:Note: When Oracle is the subscription database, synchronization replication always occurs in BUP mode as if the preceding two options were always set. The reason for this is so large columns of TEXT data type from Postgres publications can successfully replicate to Oracle CLOB columns. In BUS mode an individual Oracle SQL statement has a string literal maximum length of 4000 characters. This limitation does not occur for prepared SQL statements that are used in BUP mode.The busBatchThresholdCount option sets the number of consecutive updates of the same type that must be encountered in the shadow tables for the trigger-based method or the changeset stream for the log-based method before the publication server switches from BUS mode to BUP mode if dynamic switching is permitted (that is switchBatchUpdateMode=true).The number of consecutive changes using the same table and SQL statement type must exceed the specified value n before a prepared statement is used.If changes to the publication were made using many SQL statements where each statement affected more than one row, then it may be beneficial to lower busBatchThresholdCount to encourage the use of prepared statements on the multiple shadow table rows resulting from each individual change on the publication.The bupBatchThresholdCount option is used in combination with the bupBatchThresholdRepeatLimit option to control the frequency of mode switches based on the volatility of expected update types to the publication.Each time the same prepared SQL statement is consecutively executed, an internal “batch” counter is incremented. If this batch count falls below bupBatchThresholdCount for the number of executions of a given prepared statement, then a second internal “repeat” counter is incremented by one. If the repeat counter eventually reaches bupBatchThresholdRepeatLimit, the update mode is switched from BUP to BUS.Thus, if there are frequent, consecutive changes of prepared SQL statements (as measured against bupBatchThresholdRepeatLimit), each of which is executed a small number of times (as measured against bupBatchThresholdCount), then the mode of execution changes back to individual SQL statements instead of prepared statements.Note: The publication server changes back to prepared statements when the threshold set by busBatchThresholdCount is met.The following example illustrates the processing of updates when bupBatchThresholdCount is set to 3 and bupBatchThresholdRepeatLimit is set to 4. A change to the “query domain” referred to in this example means a different statement type (INSERT, UPDATE, or DELETE) or a different target table are encountered in the next update, thus requiring the use of a different prepared SQL statement.At this point the query domain is changed after the first two updates (change from table emp to dept) and the number of executions of the prior prepared statement (2) is less than bupBatchThresholdCount, so the repeat counter is set to 1.The query domain is changed again (change from table dept to emp), but this time the number of executions (4) for the same query domain (updates 3 thru 6) exceeds bupBatchThresholdCount so the repeat counter is reset to 0.
For configuration and management of your replication system, the xDB Replication Console graphical user interface is used to illustrate the steps and examples in this chapter. The same steps can be performed from the operating system command line using the xDB Replication Server Command Line Interface (CLI). The commands of the xDB Replication Server CLI utility are described in Chapter 8.
Replication speed and efficiency can be affected by the heap memory size set for the publication server. The xDB Startup Configuration file sets a parameter controlling the minimum and maximum heap size allocated for the publication server. See Section 5.1.1 for guidelines and information on setting this parameter.If you plan to use the log-based method of synchronization replication with any master node running under a given Postgres database server, the following configuration parameter settings are required in the configuration file, postgresql.conf, of each such Postgres database server:
Registering a publication server is done in a manner identical to single-master replication. See Section 5.2.1 for directions on registering a publication server.Step 1: Make sure the database server for the master definition node is running and accepting client connections.Step 2: Select the MMR type node under the Publication Server node. From the Publication menu, choose Publication Database, and then choose Add Database. Alternatively, click the secondary mouse button on the MMR type node and choose Add Database. The Publication Service – Add Database dialog box appears.Step 3: Fill in the following fields:
Step 1: Make sure the database server for the master definition node is running and accepting client connections.Step 2: Select the MMR type node under the same Publication Server node that contains the master definition node. From the Publication menu, choose Publication Database, and then choose Add Database. Alternatively, click the secondary mouse button on the MMR type node and choose Add Database. The Publication Service – Add Database dialog box appears.Step 3: Fill in the following fields:
See Section 5.2.4 for the control schema objects created in each master node.
This section discusses the procedure for initiating a replication on demand. Section 7.2 discusses how to create a schedule.When you create a master node for the first time, you have the option of performing an initial snapshot (see Step 3 of Section 6.3).Step 1: Select the Publication node under the master node for which you wish to perform snapshot replication.Step 2: Open the Snapshot dialog box in any of the following ways:Step 3: Select the Verbose Output check box only if you want to display the output from the snapshot in the dialog box. This option should be left unchecked in a network address translation (NAT) environment as a large amount of output from the snapshot may delay the response from the Snapshot dialog box. Click the Snapshot button to start snapshot replication.Step 4: Snapshot Taken Successfully appears if the snapshot was successful. Click the OK button. If the snapshot was not successful, scroll through the messages in the Snapshot dialog box window if Verbose Output was selected or check the log files.The status messages of each snapshot are saved in the Migration Toolkit log files named mtk.log[.n] (where [.n] is an optional history file count if log file rotation is enabled) in the following directories:POSTGRES_HOME is the home directory of the Windows postgres account (enterprisedb account for Advanced Server installed in Oracle compatible configuration mode). The specific location of POSTGRES_HOME is dependent upon your version of Windows. The xDB Replication Server version number is represented by x.x.The publication has now been replicated from the master definition node to the selected master node. A record of the snapshot is maintained in the replication history. See Section 7.4 for information on how to view replication history.Note: Be sure an initial snapshot replication has been performed from the master definition node to every other master node in the multi-master replication system. If a newly added master node did not undergo an initial snapshot, any subsequent synchronization replication may fail to apply the transactions to that master node. The initial snapshot could be taken when the master node is first added (see Section 6.3) or by performing an on demand snapshot (see Section 6.5.1).There may be circumstances where changes made on different nodes result in conflicts. Section 6.6 discusses the types of conflicts that may occur and how they can be resolved.Step 1: Select the Publication node under any master node. Regardless of the master node chosen, synchronization is applied to every master node pair in the replication system.Step 2: Open the Synchronize dialog box in any of the following ways:Step 3: Click the Synchronize button to start synchronization replication.Step 4: Publication Synchronized Successfully appears if the synchronization was successful. Click the OK button. If the synchronization was not successful, an error message is displayed.The operations that were applied to the publication tables can be seen in the replication history. See Section 7.4 for information on how to view replication history.Conflicting changes that were encountered can be seen in the conflict history. See Section 6.7 for information on how to view conflict history.
Conflict resolution deals with the topic of the types of conflicts that might occur, the strategies for dealing with conflicts, and the options available for automatically resolving such conflicts.
See Section 6.6 for more information on conflict resolution.Note: The conflict history can be viewed from the Publication node under any master node in the multi-master replication system. The history shows conflicts on all publication tables of all master nodes that occurred during synchronization, and hence, the history appears the same regardless of the master node under which it is viewed.Note: For uniqueness (insert/insert) conflicts the number of entries appearing under the Conflict History tab differs when the trigger-based method of synchronization replication is used as compared to the log-based method. If the trigger-based method is used, a single insert/insert conflict appears as two entries in the conflict history. Each entry differs in that the source and target database fields for the two conflicting master nodes are interchanged. If the same conflict occurs when the log-based method is used, only one entry appears in the conflict history.Step 1: Select any Publication node under a Database node representing a master node. Tabs labeled General, Realtime Monitor, Replication History, and Conflict History appear.Step 2: Click the Conflict History tab to show conflict history. Click the Refresh button to ensure all conflicts are listed.Step 3: Use the Conflict Display Criteria drop-down list to display only conflicts of the chosen status.Step 4: Click the View Data link to show the details of a particular conflict.Note: The View Data link and Conflict Details window are available only for multi-master replication systems configured with the trigger-based method of synchronization replication. There is no View Data link or Conflict Details window for multi-master replication systems configured with the log-based method of synchronization replication.
A current conflict resolution option on a publication table can be changed. See Section 6.6 for information on conflict resolution.Step 1: Make sure the publication server whose node is the parent of the publication you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2: Select the Publication node under the Publication Database node representing the master definition node.Step 3: Open the Conflict Resolution Options dialog box in any of the following ways:Step 4: For each table, you can select the primary conflict resolution strategy and a standby strategy by clicking the primary mouse button over the appropriate box to expose a drop-down list of choices.Step 5: Click the Update button, and then click OK in response to Conflict Resolution Options Updated Successfully.
Table filters must first be defined in a set of available table filters in the publication before they can be enabled on a master node. See Section 6.2.3 for information on defining table filters in a multi-master replication system.Note: See Section 2.2.12.3 for table setup requirements for a log-based replication system as well as general restrictions on the use of table filters.Step 1: Make sure the publication server whose node is the parent of the master nodes of the replication system is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2: Select the Publication Database node corresponding to the master node on which you wish to enable or disable individual filter rules.Step 3: Click the secondary mouse button on the Publication Database node and choose Update Filter Rule.Note: If you wish to enable or disable filter rules on the current master definition node, you must first switch the role of the master definition node to another master node in order to expose the Update Filter Rule option in the master node context menu. See Section 6.10 for directions on switching the master definition node.Step 4: In the Filter Rules tab check or uncheck the boxes to specify the filter rules to enable or disable on the master node. At most one filter rule may be enabled on any given table. Click the Save button.Step 5: A confirmation box appears presenting a warning message and a recommendation to perform a snapshot replication to any master node on which you changed the filtering criteria.Step 6: If you clicked the Ok button in the preceding step, the Filter Rules updated successfully confirmation message appears if the update was successful.Step 7: It is strongly recommended that a snapshot replication be performed to the master node that contains tables on which the filtering criteria has changed.A snapshot ensures that the content of the master node tables is consistent with the updated filtering criteria. See Section 6.5.1 for information on performing a snapshot replication.Note: The master definition node, which provides the source of the table content for a snapshot, should contain a superset of all the data contained in the other master nodes of the multi-master replication system. This ensures that the target of the snapshot receives all of the data that satisfies the updated filtering criteria.
Step 1: Make sure the publication server whose node is the parent of the master nodes of the replication system is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2: Select the Publication Database node corresponding to the master node that you wish to set as the master definition node.Step 3: Click the secondary mouse button on the Publication Database node and choose Set as MDN.Step 4: In the Set as MDN confirmation box, click the Yes button.Step 5: The selected master node is now the master definition node.Step 6: The value Yes in the MDN field of the Property window indicates this database is the master definition node.Note: The new master definition node is moved to the top of the replication tree in the xDB Replication Console.
Almost all publication server performance related configuration options for single-master replication systems are equally applicable to multi-master replication systems (except when they are database produce specific, such as for Oracle). See Section 5.8 for a discussion of these options.The publication server configuration options are set in the publication server configuration file. See Section 10.4.1 for a detailed explanation of how to set the configuration options in this file.In addition, for configuration options specifically applicable to publication databases configured with the log-based method of synchronization replication, see Section 10.4.1.15.The uniquenessConflictDetection option determines if uniqueness conflict needs to be detected at data load time or should be deferred to when data is applied against a target master node. Possible values are EAGER and LAZY. Set it to EAGER if there is a high probability of duplicate inserts across master nodes.When the number of master nodes is greater than two, then the conflict detection is always performed in EAGER mode. (A LAZY mode setting is ignored.) This is primarily required to avoid removing the already replicated conflicted changes from a target node, which otherwise is an expensive option.The default value is LAZY when the number of master nodes is two.The skipConflictDetection option controls whether or not to skip conflict detection during synchronization replication. The default is false and should be changed only when the probability of data conflict across master nodes is zero. For example if each master node operates on an independent set of data then turning on this option improves the replication time.The default value is false.In a multi-master replication system, if a deadlock is detected on a target master node, the deadlockRetryCount option controls the number of times the publication server attempts to retry application of the changes in the current replication cycle after waiting for the number of milliseconds specified by deadlockWaitTime. Set deadlockRetryCount to 0 to turn off this option in which case the failed changes are attempted in the next replication cycle.The deadlockWaitTime option is used with the deadlockRetryCount option to set the wait time in milliseconds before the publication server attempts to retry application of the changes on the target master node.
For configuration and management of your replication system, the xDB Replication Console graphical user interface is used to illustrate the steps and examples in this chapter. The same steps can be performed from the operating system command line using the xDB Replication Server Command Line Interface (CLI). The commands of the xDB Replication Server CLI utility are described in Chapter 8.Note: Though most steps described in this chapter apply to both single-master and multi-master replication systems, those steps that apply only to single-master replication systems are noted with For SMR only. Those steps that apply only to multi-master replication systems are noted with For MMR only.
When selecting tables for creating a publication for a single-master replication system (see Section 5.2.3) or a multi-master replication system (see Section 6.2.3), there may be cases where the number of available tables for selection is so large that simply choosing them from a checklist becomes a difficult and time-consuming process.This difficulty can also be encountered when adding tables to an existing publication (see Section 7.6.3.1) or deleting tables from an existing publication (see Section 7.6.3.2).Pattern matching as performed by the wildcard selector is the process in which the eligible tables for an operation are returned in a filtered list if their schema and table name combination match a character string called a pattern.Matching a pattern means that the schema and table name combined in a string formatted as schema_name.table_name matches the pattern, character by character, according to the rules designated for the characters appearing in the pattern.If the schema_name.table_name string matches the pattern, then the schema and table are displayed in the filtered list for that pattern, which is the Available Tables field of the Wildcard Selector dialog box. You can then selectively choose the tables from the filtered list to be added to a local list, which contains the potential, candidate tables for the operation for which you are using the wildcard selector.With the exception of characters called wildcards, characters appearing in a pattern require that the character in the corresponding position in the schema_name.table_name string must match the pattern character in a case insensitive manner (that is, the letters A or a, match both A and a).The pattern characters called wildcard characters or simply wildcards are interpreted in a special manner when compared to the corresponding character position of the schema_name.table_name character string.
A schedule establishes recurring points in time when replication is to occur.Note (For MMR only): Be sure an initial snapshot replication has been performed from the master definition node to every other master node in the multi-master replication system. If a newly added master node did not undergo an initial snapshot, any subsequent synchronization replication initiated by a schedule may fail to apply the transactions to that master node. The initial snapshot could be taken when the master node is first added (see Section 6.3) or by performing an on demand snapshot (see Section 6.5.1).See Section 7.3 for changing or removing a schedule.Step 1 (For SMR only): Select the Subscription node of the subscription for which you wish to create a schedule.Step 1 (For MMR only): Select the Publication Database node designated as the controller database. (The Controller database field in the Property window is set to Yes for the controller database.)Step 2 (For SMR only): Open the Scheduled Task Wizard dialog box in any of the following ways:Step 2 (For MMR only): Open the Scheduled Task Wizard dialog box in any of the following ways:Step 3: In the Scheduled Task Wizard dialog box, select the radio button for either synchronization replication or snapshot replication.Note: If the publication associated with this subscription is a snapshot-only publication, then only Snapshot may be chosen.Note: In a multi-master replication system, only Synchronize may be chosen.Step 4: Select the radio button for the scheduled replication frequency, or select Cron Expression to write your own cron expression. The frequency choices have the following meanings:
7.3.1 Updating a ScheduleStep 1 (For SMR only): Make sure the subscription server whose node is the parent of the subscription you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 1 (For MMR only): Make sure the publication server whose node is the parent of the controller database you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2 (For SMR only): Select the Subscription node of the subscription for which you wish to update the schedule.Step 2 (For MMR only): Select the Publication Database node designated as the controller database for which you wish to update the schedule.Step 3 (For SMR only): Open the Scheduled Task Wizard dialog box in any of the following ways:Step 3 (For MMR only): Open the Scheduled Task Wizard dialog box in any of the following ways:Step 4: The Configure Scheduler confirmation box appears. Click the Yes button.Step 5: In the Scheduled Task Wizard dialog box, create the new schedule. See Step 3 of Section 7.2 for details on how to create a new schedule.7.3.2 Removing a ScheduleStep 1 (For SMR only): Make sure the subscription server whose node is the parent of the subscription you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.3.1 for directions on starting and registering a subscription server.Step 1 (For MMR only): Make sure the publication server whose node is the parent of the controller database you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2 (For SMR only): Select the Subscription node of the subscription for which you wish to remove the schedule.Step 2 (For MMR only): Select the Publication Database node designated as the controller database for which you wish to remove the schedule.Step 3 (For SMR only): Remove the schedule in any of the following ways:Step 3 (For MMR only): Remove the schedule in any of the following ways:Step 4: In the Removing Schedule confirmation box, click the Yes button.
A summary of replications performed on each subscription or master node can be viewed in the xDB Replication Console. A detailed replication history showing each insert, update, and deletion made against each target table can be viewed as well. See Section 2.2.9 for a discussion on how changes are applied to target tables for the target-based method of synchronization replication. See Section 2.2.10 for information on the log-based method of synchronization replication.Note (For SMR Only): The replication history can be viewed from the Publication node as well as from the Subscription node. The history shown for a Publication node is actually the exact same set of inserts, updates, and deletions made on the subscription tables by the publication server during synchronization. The history shown for a Publication node does not show the actual SQL statements processed on the publication tables that originated from user applications.Note (For MMR only): The replication history can be viewed from the Publication node under any master node in the multi-master replication system. The history shown includes inserts, updates, and deletions made on all publication tables of all master nodes by the publication server during synchronization, and hence, the history appears the same regardless of the master node under which the history is viewed.7.4.1 All Replication HistoryStep 1 (For SMR only): Select the node beneath the Subscription node. Tabs labeled General, Realtime Monitor, and Replication History appear.Step 1 (For MMR only): Select any Publication node under a Database node representing a master node. Tabs labeled General, Realtime Monitor, Replication History, and Conflict History appear.Step 2: Click the Replication History tab to show a history of replications.Note: Every snapshot replication and each synchronization replication with at least one update produces a history record that is maintained in replication history tables in the control schema. Over time the size of the replication history tables will grow. Replication history records can be periodically deleted. See Section 7.5.3 for information on cleaning up replication history.Step 1: Check the Show History With Transactions Count > 0 check box located at the bottom of the Replication History tab.Step 2: The next time the Replication History tab refreshes, only the replications with non-zero transaction counts appear in the Replication History.Note: Zero transaction count replication records are maintained in the publication server memory. By default, they are not permanently stored on disk. Therefore when the publication server is shut down, the in-memory zero transaction count replication records are no longer available.If you wish to permanently store zero transaction count replication records to disk, set the publication server configuration option persistZeroTxRepEvent to true. See Section 10.4.1.12 for further information.7.4.3 Shadow Table HistoryStep 1: Select a table to reveal tabs that contain general information about the table and the replication history of the table. Expand a Table node to reveal the columns in the table.Step 2: Click the Replication History tab to show a history of replications for this table.Step 3: Click the View Data link to show a list of each change made to the table during the synchronization replication. The Synchronize History window shows two update operations followed by one insert operation against the emp target table that correspond to the following set of SQL statements executed on the emp source table:Note: Since all insert, update, and delete operations on all source tables are recorded in shadow tables, the size of the shadow tables may grow considerably over time for volatile source tables. The rows shown in the Synchronize History window are obtained from these shadow tables. Rows in the shadow tables can be periodically deleted. See Section 7.5.2 for information on cleaning up the shadow tables.
7.5 Managing History
The first type of update is discussed in the following section. The second type of update is discussed in Section 7.6.1.2.7.6.1.1 Publication Server Login FileWhen you register a publication server in the xDB Replication Console, you may choose to save the publication server’s network location, admin user name, and encrypted password in a server login file on the computer on which you are running the xDB Replication Console. See Section 4.2 for information on saving the login information.Step 1: The publication server whose login information you want to save, change, or delete in the server login file must be running before you can make any changes to the file. See Step 1 of Section 5.2.1 for directions on starting the publication server.Step 2: Click the secondary mouse button on the Publication Server node and choose Update. The Update Publication Server dialog box appears.Step 3: Complete the fields in the dialog box according to your purpose for updating the server login file:Step 4: Click the Update button. If the dialog box closes, then the update to the server login file was successful. Click the Refresh icon in the xDB Replication Console tool bar to show the updated Publication Server node.Note: This section applies only to single-master replication systems.This network information enables the publication server to communicate with the subscription server.Step 1: The publication server whose metadata you want to change must be running. See Step 1 of Section 5.2.1 for directions on starting the publication server.Step 2: Click the secondary mouse button on the Publication Server node and choose Update Subscription Servers. The Update Subscription Servers dialog box appears.Note: If the error message box reappears, click the OK button and repeat Step 2.Step 3: Enter the new network location for each subscription server in the list whose network location has changed.Step 4: Click the Update button. If the dialog box closes, then the update to the publication server’s metadata was successful.Step 5: If the subscription server with the new network location manages subscriptions associated with publications in other publication servers, repeat steps 1 through 4 for these other publication servers.When you create a publication database definition, you save the publication database server’s network location (IP address and port number), the database identifier, a database login user name, and the user’s password in the control schema accessed by the publication server. This login information is used whenever a session needs to be established with the publication database. See Section 5.2.2 for information on creating a publication database definition for a single-master replication system. See sections 6.2.2 and 6.3 for a multi-master replication system.Note: Depending upon the database type (Oracle, SQL Server, or Postgres), certain attributes must not be changed. You must not change any attribute that alters access to the schema where the control schema objects were created when you originally added this publication database definition. See Section 5.2.4 for the location of the control schema objects.Step 1: Make sure the database server that you ultimately wish to save as the publication database definition is running and accepting client connections.Step 2: Make sure the publication server whose node is the parent of the publication database definition you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 3: Select the Publication Database node corresponding to the publication database definition that you wish to update.Step 4: From the Publication menu, choose Publication Database, and then choose Update Database. Alternatively, click the secondary mouse button on the Publication Database node and choose Update Database. The Update Database Source dialog box appears.Step 5: Enter the desired changes. See Step 3 of Section 5.2.2 for the precise meanings of the fields for a single-master replication system. See sections 6.2.2 and 6.3 for a multi-master replication system.Step 6: Click the Test button. If Test Result: Success appears, click the OK button, then click the Save button.Step 7: Restart the publication server. See Section 5.2.1 for directions on restarting the publication server.Step 8: Click the Refresh icon in the xDB Replication Console tool bar to show the updated Publication Database node and any of its publications.7.6.3 Updating a Publication7.6.3.1 Adding Tables to a PublicationStep 1: Make sure the publication server whose node is the parent of the publication you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2 (For SMR only): Select the Publication node of the publication to which you wish to add tables.Step 2 (For MMR only): Select the Publication node under the Publication Database node representing the master definition node.Step 3: Open the Add Tables dialog box in any of the following ways:Step 4: Fill in the following fields in the Add Tables tab of the Add Tables dialog box:
The controller database is designated in the xDB Replication Configuration file and determines the publication database to which the publication server and subscription server initially connect upon startup. See Section 2.3.1.12 for information on the controller database. See Section 2.3.1.3 for information on the xDB Replication Configuration file.Note: If the controller database is an Oracle or a SQL Server publication database, then a second Oracle or SQL Server publication database cannot be added to create a second single-master replication system. In order for xDB Replication Server to run more than one single-master replication systems consisting of Oracle or SQL Server publication databases, a Postgres publication database must be designated as the controller database.Upon switching the controller database, the publication server updates the xDB Replication Configuration file so the parameters user, password, host, port, database, and type are set to the connection and authentication settings for the selected publication database.Step 1: Make sure the publication server whose node is the parent of the publication databases is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.Step 2: Select the Publication Database node corresponding to the publication database that you wish to set as the controller database.Step 3: Click the secondary mouse button on the Publication Database node and choose Set as Controller database.Step 4: In the Set as Controller database confirmation box, click the Yes button.Step 5: The selected publication database has now been set as the controller database.Step 6: The value Yes in the Controller database field of the Property window indicates this database is the controller database.
Table definition changes are generally implemented using the SQL ALTER TABLE statement, which is issued in an SQL command line utility program such as PSQL.The DDL change replication feature accepts one or more ALTER TABLE statements. The statements may be provided by means of a text file or by entering them directly into the Alter Publication Table dialog box. The latter can be done by copying and pasting the statements into the dialog box, or by directly typing in the statements. The DDL change replication feature then performs the following actions:
https://www.postgresql.org/docs/current/static/sql-createtable.htmlIn the DROP COLUMN clause, do not drop the column if there are objects dependent upon it. This is the default. Note: You cannot specify the CASCADE option as it is not supported by the DDL change replication feature.Collation assigned to the column. If omitted, the column data type’s default collation is used. Examples of collation are default, C, POSIX, en_US, en_GB, or de_DE.The following are examples of ALTER TABLE statements that can be used by the DDL change replication feature.The following set of ALTER TABLE statements, either specified by a text file or entered directly into the dialog box, adds columns to the edb.emp table.The following ALTER TABLE statement changes the data type length of the title column and sets its values with the USING data_type_expr clause.The following query shows the values assigned to the title column after the DDL change replication feature applies the preceding ALTER TABLE statement to the edb.emp table. This change to the title column and assignment of values occurs in all the subscription databases of a single-master replication system or in all the master nodes of a multi-master replication system.The following set of ALTER TABLE statements drops the columns that were added in the first example.The DDL change replication feature can be invoked from either the xDB Replication Console (see Section 7.8.2) or the xDB Replication Server CLI (see Section 8.3.30).The DDL statement is executed in a controlled manner such that the target table is exclusively locked (by the default setting of configuration option ddlChangeTableLock) during the course of the operation. This is done to avoid loss of any transactions while the replication triggers and shadow table are modified by the DDL change replication process. Only one target table is locked at a time while DDL change replication takes place on that table, its triggers, and shadow table.Note: Exclusive acquisition of each target table during the DDL change replication process can be turned off by setting ddlChangeTableLock to false. However, this should be done only when there are no write transactions taking place against the target table, otherwise transactions may not be recorded by the replication system. See Section 10.4.1.11 for additional information on the ddlChangeTableLock configuration option.
If one of the updates had been an insertion of a new row, and this new row is already in the target table loaded from the offline snapshot, a duplicate key error results when the publication server attempts to apply the batch containing the INSERT statement for this row. The duplicate key error forces the rollback of the entire batch. This causes the exclusion of updates in the batch that may not yet have been carried over to the target tables. The source tables and target tables are now inconsistent since there were updates to the source tables that have not been applied to the target tables.Note: The effects of applying UPDATE and DELETE statements in the batch to a target table that already has been changed by these updates does not cause the same problem as repeated application of INSERT statements. The UPDATE statement would just change the row to the same values a second time. When a DELETE statement affects no rows, this is not considered an error by the database server, and therefore, no rollback of the batch occurs.The batchInitialSync configuration option controls whether the first synchronization replication occurs in batch or non-batch mode.If you are using an offline snapshot in an active replication system where updates are occurring to the source tables and transactions are thus accumulating in the shadow tables for the trigger-based method, it is advisable to set batchInitialSync to false to perform the first synchronization replication in non-batch mode.Note: An offline snapshot cannot be used to add a subscription or a master node to an active replication system that uses the log-based method. For the log-based method, offline snapshots can only be used to initially configure the system, and not to update it with additional nodes after the publication database or master node is actively receiving transactions.If you are using offline snapshots to initially create the entire replication system that has yet to be activated, and the content of the offline snapshots are all assumed to be consistent for the source and target tables, then batchInitialSync can be left with its default setting of true since it is assumed that the first synchronization replication will not apply any duplicate updates.Note: These options apply to the publication server only.The offlineSnapshot option must be set to true before creating the subscription for a single-master replication system, or before adding the master node for a multi-master replication system.The default value is false.When set to true, the offlineSnapshot option prevents the usual creation of the subscription schema and table definitions when the subscription is defined in a single-master replication system since it is assumed that you are creating the subscription table definitions and loading them from an external source other than the publication.When adding the master node in a multi-master replication system, leave the Replicate Publication Schema and Perform Initial Snapshot boxes unchecked (see Section 6.3).When offlineSnapshot is set to true, this has the direct effect within the control schema by setting column has_initial_snapshot to a value of O indicating an offline snapshot is used for the target subscription or master node represented by the row. Column has_initial_snapshot is set in table xdb_publication_subscriptions for a single-master replication system and in table xdb_mmr_pub_group for a multi-master replication system.The setting of has_initial_snapshot influences the behavior of the batchInitialSync option as explained in the following section.After the first replication completes to the target subscription or master node, has_initial_snapshot is changed to Y by xDB Replication Server.The batchInitialSync option is used to control whether the first synchronization after loading the target tables from an offline snapshot is done in batch mode (the default) or non-batch mode.The offlineSnapshot configuration option must have first been set to true prior to creating the subscription or adding the additional master node. A non-batch mode synchronization occurs only if batchInitialSync is false and the has_initial_snapshot column in the control schema is set to a value of O as described for the offlineSnapshot option.The default value is true.An offline snapshot can be used to initially load the subscription tables of a single-master replication system. For a publication that is intended to have multiple subscriptions, it is possible to create some of the subscriptions using the default xDB Replication Server snapshot replication process as described in Section 5.4.1, while other subscriptions can be created from an offline snapshot.Step 1: Register the publication server, add the publication database definition, and create the publication as described in Section 5.2.Step 2: Register the subscription server and add the subscription database definition as described in sections 5.3.1 and 5.3.2, respectively.Note: Steps 3 and 4 must be performed before creating the subscription. Steps 3 through 9 can be repeated each time you wish to create an additional subscription from an offline snapshot.Step 3: Modify the publication server configuration file if these options are not already set as described by the following:
If you are using Advanced Server, partitioned tables can be created using the CREATE TABLE statement with partitioning syntax compatible with Oracle databases. For information on partitioning compatible with Oracle databases, see Chapter 10 “Table Partitioning” in the EDB Postgres Advanced Server 10.0 Database Compatibility for Oracle Developers Guide available from the EnterpriseDB website located at:If you are using version 10 or later of PostgreSQL or Advanced Server, declarative partitioning can be used to create partitioned tables. The CREATE TABLE syntax for creating a declarative partitioned table is similar to the partitioning compatible with Oracle databases, but the individual partitions of the declarative partitioned table must be separately created with their own CREATE TABLE statements.If you are using native PostgreSQL version 9.6 or earlier, you must use a technique called table inheritance where you first create a parent table from which you then create one or more child tables that inherit the columns of the parent. Each child is an independent table in its own right except that it includes the column definitions of its parent. You then define a trigger on the parent table to direct which child table an inserted row is to be stored. Table inheritance can be used on Advanced Server as well.All three partitioning techniques are illustrated on the emp table used as an example throughout this document. The partitioned table is then used in a publication of a multi-master replication system in the following sections:Note: When creating a declarative partitioned table that is to be replicated using xDB Replication Server, the PRIMARY KEY constraint must be included in the CREATE TABLE statements of the individual partitions, not in the CREATE TABLE statement of the parent table to be partitioned.Querying the parent table, emp, with the asterisk appended to the table name in the SELECT statement, shows the rows in the parent and child tables. This is the default behavior if the asterisk is omitted.The following queries show how the rows are physically divided amongst the child tables. The use of the ONLY keyword results in rows only in the specified table of the SELECT statement, and not from any of its children.Section 7.10.2 shows creation of the publication when using partitioning compatible with Oracle databases or declarative partitioning on a Postgres 10 or later database server.Follow the directions in Section 6.2 to create a master definition node along with a publication containing the partitioned table. (For a single-master replication system, create the publication database along with the publication according to the directions in Section 5.2.)Create additional master nodes as described in Section 6.3. (For a single-master replication system, create the subscription database and subscription according to the directions in Section 5.3.)Note: If you are using table inheritance, you must still use the process described in Section 7.10.1 even when creating the publication on a Postgres 10 or later database server.Follow the directions in Section 6.2 to create a master definition node along with a publication containing the partitioned table. (For a single-master replication system, create the publication database along with the publication according to the directions in Section 5.2.)Create additional master nodes as described in Section 6.3. (For a single-master replication system, create the subscription database and subscription according to the directions in Section 5.3.)
Note: SSL connections are not used from the xDB Replication Console or the xDB Replication Server Command Line Interface. The xDB user interfaces communicate with the publication server and subscription server, which in turn connect to the publication/subscription databases or master nodes.Note: The Migration Toolkit connection using SSL occurs within the context of the publication server and subscription server SSL connections. Therefore, there are no separate steps that you need to perform for the Migration Toolkit SSL connection.The Java truststore is the file containing the Certificate Authority (CA) certificates with which the Java client (the publication server and subscription server) uses to verify the authenticity of the server to which it is initiating an SSL connection.The Java keystore is the file containing private and public keys and their corresponding certificates. The keystore is required for client authentication to the server, which is used for xDB Replication Server SSL connections.
https://www.postgresql.org/docs/current/static/ssl-tcp.html
This chapter discusses the syntax and usage of the xDB Replication Server Command Line Interface (CLI). This utility program is a command line driven alternative to the xDB Replication Console.The steps for creating a replication system using the xDB Replication Server CLI are no different than those required when using the xDB Replication Console. The logical components of the replication system must be created in the same order, with the same sets of attributes as when creating the replication system with the xDB Replication Console.You should understand the concepts and steps presented in chapters 2, and 5 (for single-master replication) or 6 (for multi-master replication) before building a replication system using the xDB Replication Server CLI.There are no restrictions on using both the xDB Replication Console and the xDB Replication Server CLI to build and manage the same replication system.In Section 8.3, the syntax and examples are given for each xDB Replication Server CLI command run individually. Where applicable, the discussion of a command contains a reference back to its xDB Replication Console counterpart where a detailed description of the affected component and its attributes can be found.
This section describes the installation and setup required prior to using the xDB Replication Server CLI.The xDB Replication Server CLI is included if the xDB Replication Console component is chosen when installing xDB Replication Server. The xDB Replication Server CLI is a Java application found in directory XDB_HOME/bin.Step 2: Follow the prerequisite steps given in Section 5.1 for single-master replication systems or Section 6.1 for multi-master replication systems.Step 3: Set the Java Runtime Environment as described by the following discussion.On the host from which you intend to run the xDB Replication Server CLI, the Java Runtime Environment (JRE) must be present and the Java runtime bin directory must be included in the path of the operating system user name that will be used to run xDB Replication Server CLI.The xDB Startup Configuration file, xdbReplicationServer-xx.config, contains the path of the JRE runtime program that was detected during the installation of xDB Replication Server. The following is an example of the xDB Startup Configuration file (see Section 3.5 for the location of this file.)On Windows systems, open the Properties dialog box of My Computer, choose Advanced System Settings, and then click on Environment Variables. Edit the Path system environment variable to include the Java Runtime Environment bin directory. Alternatively, you can set the path for just the current session when you open the Command Prompt window as in the following example:
8.2 General UsageThis section explains the general usage rules for the xDB Replication Server CLI.You can run the xDB Replication Server CLI from any host on which you can run the xDB Replication Console. The xDB Replication Server CLI is run by executing the java runtime program and specifying the following arguments to the java program:
This section provides a description, syntax diagram, and examples of each xDB Replication Server CLI command.Note: Though most commands described in this section apply to both single-master and multi-master replication systems, those commands that apply only to single-master replication systems are noted with For SMR only. Those commands that apply only to multi-master replication systems are noted with For MMR only. The same notation is used for command parameters that may apply only to single-master replication systems or multi-master replication systems.For the examples used in this section, it is assumed that the xDB Replication Server CLI commands are executed after you have made XDB_HOME/bin your current working directory, thereby eliminating the need to specify the full path of XDB_HOME/bin for each execution of the edb-repcli.jar file. For example, assuming xDB Replication Server is installed in the default installation directory you have issued the following command in Linux:Whenever the repsvrfile parameter appears in the examples, file ~/pubsvrfile contains the publication server login information and is located in the user’s home directory while ~/subsvrfile contains the subscription server login information. For Windows, the equivalent usage is %HOMEPATH%\pubsvrfile and %HOMEPATH%\subsvrfile.The examples in this section were run on Linux so you will see use of the Linux continuation character, which is a backslash (\), to show how an xDB Replication Server CLI command can be continued onto the next line if you do not want to wrap the text in your terminal window. For Windows, use the Windows continuation character, which is a caret (^).8.3.1 Getting Help (help)-repversion -repsvrfile pubsvrfileThe file containing the publication server login information.The encrypt command encrypts the text supplied in an input file and writes the encrypted result to a specified output file. Use the encrypt command to generate an encrypted password that can be copied into a text file that will be referenced by an xDB Replication Server CLI command that requires a user name and the user’s password.The text in infile is processed using the MD5 encryption algorithm, and the encrypted text is written to file pwdfile. Make sure that infile contains only the text that you want to encrypt and that there are no extraneous characters or empty lines before the text or after the text that you want to encrypt.The file infile contains the word “password”.The content of file pwdfile contains the encrypted form of “password”.The uptime command prints the time interval since the publication server has been up and running.-uptime -repsvrfile pubsvrfileThe file containing the publication server login information.The addpubdb command adds a publication database definition.-repsvrfile pubsvrfile-dbhost host-dbport port-dbuser user-database dbname[ -urloptions jdbc_url_parameters ][ -nodepriority priority_level ]The addpubdb command creates a new publication database definition. The addpubdb command displays a unique publication database ID that is assigned to the newly created publication database definition. The publication database ID is used to identify the publication database definition on which to operate when running other xDB Replication Server CLI commands.See Section 5.2.2 for details on the database connection information that must be supplied when adding a publication database definition for a single-master replication system. See sections 6.2.2 and 6.3 for a multi-master replication system.The file containing the publication server login information.Specify oracle if the database is an Oracle database. Specify enterprisedb if the database is an Advanced Server database in Oracle compatible configuration mode. Specify postgresql if the database is a PostgreSQL database or an Advanced Server database in PostgreSQL compatible configuration mode. Specify sqlserver if the database is a Microsoft SQL Server database.The port number on which the database server is listening for connections.The encrypted password of the publication database user. See Section 8.3.4 for directions on using the encrypt command to generate an encrypted password.Specify sid if the Oracle system ID (SID) is used to identify the publication database in the database parameter. Specify servicename if the Oracle service name is used to identify the publication database in the database parameter. Note: For Oracle 12c, use the service name.Extended usage of JDBC URL parameters such as for support of SSL connectivity. (See Section 7.11 for information on SSL connectivity to the publication database.)For MMR only: Applies to non-MDN nodes. Comma-separated list of filter IDs identifying the filter rules from the set of available table filters to enable on the corresponding tables in the new master node. Use the printpubfilterslist command to obtain the filter IDs for the available filter rules in the publication (see Section 8.3.17). Note: There must be no white space between the comma and filter IDs.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system. If omitted, the default is s.For MMR only: Applies to non-MDN nodes. Set this option to true if you want the publication table definitions replicated from the master definition node when creating a new master node. Set this option to false if you have already created the table definitions in the new master node. If omitted, the default is true. Do not specify this parameter when creating the master definition node.For MMR only: Applies to non-MDN nodes. Specify this option if you want an initial snapshot replication to be performed when creating the master node. Omit this option if you do not want an initial snapshot replication to be performed when creating the master node.Note (For MMR only): Unless you intend to use the offline snapshot technique (see Section 7.9), it is suggested that you specify this option. An initial snapshot replication must be performed from the master definition node to every other master node before performing synchronization replications on demand (see Section 8.3.42) or by a schedule (see Section 8.3.44). If a newly added master node did not undergo an initial snapshot, any subsequent synchronization replication may fail to apply the transactions to that master node. The initial snapshot can also be taken by performing an on demand snapshot (see Section 8.3.41).Set this option to true if you want the output from the snapshot to be displayed. Set this option to false if you do not want the snapshot output displayed. If omitted, the default is true.Note: This option may be given only directly following the specification of the -initialsnapshot option.For MMR only: Integer value from 1 through 10 assigning the priority level to a master node with 1 having the highest priority and 10 having the lowest priority.Specify T to use the trigger-based method of synchronization replication for this publication database. Specify W to use the log-based (WAL) method of synchronization replication for this publication database. If omitted, the default is T.The following example adds a publication database definition for an Oracle database. The encrypted password is given on the command line with the dbpassword parameter. A publication database ID of 1 is assigned to the database by the publication service.The following example adds a publication database definition for an Advanced Server database. The encrypted password is read from a file named pwdfile with the dbpassfile parameter. A publication database ID of 2 is assigned to the database by the publication service.The following example adds a publication database definition for a master node (other than the master definition node) in a multi-master replication system where an initial snapshot is not invoked (the initialsnapshot parameter is omitted). Filter rules with filter IDs 8 and 16 are applied to this master node. A node priority level of 3 is assigned to the master node.Note: A publication must be created in the master definition node before creating additional master nodes. See Section 8.3.14 for the command to create a publication.The printpubdbids command prints the publication database IDs of the publication database definitions.-printpubdbids -repsvrfile pubsvrfileThe file containing the publication server login information.The printpubdbidsdetails command prints the connection information for each publication database definition.-printpubdbidsdetails –repsvrfile pubsvrfileNote: The database user’s password is not displayed.The file containing the publication server login information.The port number on which the database server is listening for connections.There are four publication database definitions subordinate to the publication server identified by the content of file pubsvrfile.prop.The printcontrollerdbid command prints the publication database ID of the controller database.-printcontrollerdbid -repsvrfile pubsvrfileThe file containing the publication server login information.For MMR only: The printmdndbid command prints the publication database ID of the master definition node.-printmdndbid -repsvrfile pubsvrfileThe file containing the publication server login information.The updatepubdb command provides the ability to change the connection information for an existing publication database definition identified by its publication database ID.-repsvrfile pubsvrfile-pubdbid dbid-dbhost host-dbport port-dbuser user-database dbname[ -urloptions jdbc_url_parameters ][ -nodepriority priority_level ]The publication database definition to be updated is identified by the pubdbid parameter.See Section 5.2.2 for details on the database connection information that must be supplied for a publication database definition for a single-master replication system. See sections 6.2.2 and 6.3 for a multi-master replication system.The file containing the publication server login information.The port number on which the database server is listening for connections.The password of the database user in encrypted form. See Section 8.3.4 for directions on using the encrypt command to generate an encrypted password.Specify sid if the Oracle system ID (SID) is used to identify the publication database in the database parameter. Specify servicename if the Oracle service name is used to identify the publication database in the database parameter. Note: For Oracle 12c, use the service name.Extended usage of JDBC URL parameters such as for support of SSL connectivity. (See Section 7.11 for information on SSL connectivity to the publication database.) Specification of the urloptions parameter completely replaces any existing JDBC URL parameters that may have previously been specified with this database. Omission of the urloptions parameter deletes any existing JDBC URL parameters that may have previously been specified with this database.For MMR only: Integer value from 1 through 10 assigning the priority level to a master node with 1 having the highest priority and 10 having the lowest priority.In the following example, an existing publication database definition with publication database ID 1 is updated.The removepubdb command removes a publication database definition.–repsvrfile pubsvrfile–pubdbid dbidThe publication database definition to be removed is identified by the pubdbid parameter.See Section 7.6.7 for additional information on removing a publication database.The file containing the publication server login information.The gettablesfornewpub command lists the tables and views that are available for inclusion in a new publication from a given publication database definition.The file containing the publication server login information.For the publication database definition identified by publication database ID 1, the tables available for inclusion in a publication are EDB.DEPT, EDB.EMP, and EDB.JOBHIST. The view available for inclusion in a publication is EDB.SALESEMP.The createpub command creates a new publication.-createpub pubname-repsvrfile pubsvrfile-pubdbid dbidThe createpub command adds a new publication subordinate to the publication database definition with the publication database ID given by parameter pubdbid. If the publication is designated as snapshot-only by setting parameter reptype to s, then any views listed after the views parameter are ignored.See Section 5.2.3 for additional information on creating a publication for a single-master replication system. See Section 6.2.3 for a multi-master replication system.Note: The schema names, table names, and view names that you supply as values for the tables and views parameters are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.Specify s if the publication is to be a snapshot-only publication. Specify t if the publication is to allow synchronization replications.The name of the schema containing the nth table of the tables parameter list. This value is case-sensitive.For SMR only: The name of the schema containing the nth view of the views parameter list. This value is case-sensitive.The ordinal number (that is, the position in the list counting from left to right starting with 1) of a table in the tables parameter list to which an attribute is to be applied.The filter clause to be applied to the table in the tables parameter list at the position indicated by ordinal_tn.For SMR only: The ordinal number (that is, the position in the list counting from left to right starting with 1) of a view in the views parameter list to which an attribute is to be applied.For SMR only: The filter clause to be applied to the view in the views parameter list at the position indicated by ordinal_vn.For MMR only: For the conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling. The specified conflict resolution applies to the table in the position given by ordinal_tn counting from left to right in the tables parameter list. If omitted the default is E.For MMR only: For the standby conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling. The specified conflict resolution applies to the table in the position given by ordinal_tn counting from left to right in the tables parameter list. If omitted the default is M.For MMR only: For the conflict resolution option or the standby conflict resolution option, specify customhandler_tn as the function name with an optional schema prefix (that is, formatted as schema.function_name) as given in the CREATE FUNCTION command for the custom conflict handling function created for the table in the tables parameter list at the position indicated by ordinal_tn. The custom conflict handling function must be added to the master definition node. See Section 6.6.8.2 for an example of adding the custom conflict handling function using PSQL. The custom handler name option must be specified if and only if the conflict resolution option or the standby conflict resolution option is set for custom conflict handling with the C value.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system. If omitted, the default is s.In the following example, a publication named dept_emp is created that contains the EDB.DEPT and EDB.EMP tables of an Oracle database. The replication method is synchronization.In the following example, a publication named salesemp is created that contains the EDB.SALESEMP view of an Oracle database. The replication method is snapshot-only.In the following example, a publication named analysts_managers is created that contains the edb.dept table and employees from the edb.emp table who are analysts or managers. The tables are in an Advanced Server database. The replication method is snapshot-only.The following example creates a publication for a multi-master replication system. One table filter is defined on table edb.dept and three table filters are defined on table edb.emp. Table edb.dept is assigned node priority conflict resolution and latest timestamp as the standby conflict resolution strategy. Table edb.emp is assigned earliest timestamp conflict resolution and manual resolution (the default) as its standby strategy.The printpublist command prints a list of publication names.-printpublist -repsvrfile pubsvrfile[ -pubdbid dbid ]The file containing the publication server login information.If the pubdbid parameter is specified, only the publication names subordinate to the publication database definition specified by dbid are printed. If the pubdbid parameter is omitted, all publication names subordinate to the publication server are printed.The printpublishedtables command prints a list of tables and views that belong to the given publication.The file containing the publication server login information.The tables belonging to publication dept_emp are printed.The printpubfilterslist command prints a list of table filters that are defined in the given publication.The file containing the publication server login information.The table filters in publication analysts_managers are printed.The table filters defined in publication emp_pub are printed.The addtablesintopub command adds tables or views into an existing publication.-addtablesintopub pubname-repsvrfile pubsvrfileThe addtablesintopub command updates an existing publication identified by pubname. If the publication is snapshot-only, then any views listed after the views parameter are ignored.See Section 7.6.3.1 for additional information on adding tables to a publication.Note: The schema names, table names, and view names that you supply as values for the tables and views parameters are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.The name of the schema containing the nth table of the tables parameter list. This value is case-sensitive.For SMR only: The name of the schema containing the nth view of the views parameter list. This value is case-sensitive.The ordinal number (that is, the position in the list counting from left to right starting with 1) of a table in the tables parameter list to which an attribute is to be applied.The filter clause to be applied to the table in the tables parameter list at the position indicated by ordinal_tn.For SMR only: The ordinal number (that is, the position in the list counting from left to right starting with 1) of a view in the views parameter list to which an attribute is to be applied.For SMR only: The filter clause to be applied to the view in the views parameter list at the position indicated by ordinal_vn.For MMR only: For the conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling. The specified conflict resolution applies to the table in the position given by ordinal_tn counting from left to right in the tables parameter list. If omitted the default is E.For MMR only: For the standby conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling. The specified conflict resolution applies to the table in the position given by ordinal_tn counting from left to right in the tables parameter list. If omitted the default is M.For MMR only: For the conflict resolution option or the standby conflict resolution option, specify customhandler_tn as the function name with an optional schema prefix (that is, formatted as schema.function_name) as given in the CREATE FUNCTION command for the custom conflict handling function created for the table in the tables parameter list at the position indicated by ordinal_tn. The custom conflict handling function must be added to the master definition node. See Section 6.6.8.2 for an example of adding the custom conflict handling function using PSQL. The custom handler name option must be specified if and only if the conflict resolution option or the standby conflict resolution option is set for custom conflict handling with the C value.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system. Note: This parameter is not required and may be completely omitted. It is present to provide support for its usage in previous xDB Replication Server CLI versions.In the following example, table edb.jobhist and view edb.salesemp are added to an existing publication named analysts_managers.The removetablesfrompub command removes tables from a publication.-removetablesfrompub pubname-repsvrfile pubsvrfileSee Section 7.6.3.2 for additional information on removing tables from a publication.Note: The schema names, table names, and view names that you supply as values for the tables and views parameters are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.The name of the schema containing the nth table of the tables parameter list. This value is case-sensitive.The name of the schema containing the nth view of the views parameter list. This value is case-sensitive.In the following example, table edb.jobhist and view edb.salesemp are removed from the analysts_managers publication.The addfilter command adds the definition of table filter rules to the specified publication.See Section 8.3.38 for information on enabling table filter rules.-addfilter pubname–repsvrfile pubsvrfileSee Section 2.2.12 for additional information on table filters.Note: The schema names and table or view names that you supply as values for the tables or views parameters are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.The name of the schema containing the nth table of the tables parameter list. This value is case-sensitive.For SMR only: The name of the schema containing the nth view of the views parameter list. This value is case-sensitive.The ordinal number (that is, the position in the list counting from left to right starting with 1) of a table in the tables parameter list to which an attribute is to be applied.The filter clause to be applied to the table in the tables parameter list at the position indicated by ordinal_tn.For SMR only: The ordinal number (that is, the position in the list counting from left to right starting with 1) of a view in the views parameter list to which an attribute is to be applied.For SMR only: The filter clause to be applied to the view in the views parameter list at the position indicated by ordinal_vn.In the following example, a table filter is added to table edb.emp in publication analysts_managers.The updatefilter command changes the filter clauses of the specified tables or views.-updatefilter pubname–repsvrfile pubsvrfileSee Section 2.2.12 for additional information on table filters.The file containing the publication server login information.Filter ID identifying the filter rule for which the filter clause is to be changed. Use the printpubfilterslist command to obtain the filter IDs for the available filter rules in the publication (see Section 8.3.17).The removefilter command deletes the table filter from the specified publication.-removefilter pubname–repsvrfile pubsvrfile-filterid filteridSee Section 2.2.12 for additional information on table filters.The file containing the publication server login information.Filter ID identifying the filter rule to be deleted. Use the printpubfilterslist command to obtain the filter IDs for the filter rules in the publication (see Section 8.3.17).In the following example, the filter rule with filter ID 26 is removed from publication analysts_managers.For MMR only: The printconfresolutionstrategy command prints the conflict resolution strategy and the standby conflict resolution strategy of the specified table.-printconfresolutionstrategy pubname–repsvrfile pubsvrfileSee Section 6.6 for additional information on conflict resolution.Note: The schema name and table or view name that you supply as values for the table parameter are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.The name of the schema containing table_name. This value is case-sensitive.In the following example, the conflict resolution strategy on Advanced Server table edb.emp in publication emp_pub is printed.For MMR only: The updateconfresolutionstrategy command changes the conflict resolution strategy or standby conflict resolution strategy of the specified table.-updateconfresolutionstrategy pubname–repsvrfile pubsvrfile[ -customhandlername customhandler ]See Section 6.8 for additional information on updating the conflict resolution strategy.Note: The schema name and table or view name that you supply as values for the table parameter are case-sensitive. Unless quoted identifiers were used to build the database objects, Oracle names must be entered using uppercase letters (for example, EDB.DEPT), and Advanced Server names must be entered in lowercase letters (for example edb.dept). See Section 10.4.5 for additional information on quoted identifiers and case translation.The file containing the publication server login information.The name of the schema containing table_name. This value is case-sensitive.For the conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling.For the standby conflict resolution option, specify E for earliest timestamp conflict resolution, L for latest timestamp conflict resolution, N for node priority conflict resolution, M for manual conflict resolution, or C for custom conflict handling.For the custom handler name option, specify customhandler as the function name with an optional schema prefix (that is, formatted as schema.function_name) as given in the CREATE FUNCTION command for the custom conflict handling function. The custom conflict handling function must be added to the master definition node. See Section 6.6.8.2 for an example of adding the custom conflict handling function using PSQL. The custom handler name option must be specified if and only if the conflict resolution option or the standby conflict resolution option is set for custom conflict handling with the C value.The conflict resolution strategy on Advanced Server table edb.emp in publication emp_pub is modified to use latest timestamp conflict resolution with a standby strategy of node priority conflict resolution.Custom conflict handling is set for the edb.dept table along with the custom conflict handling function edb.custom_conflict_dept.For MMR only: The setasmdn command sets a master node to the role of master definition node.-setasmdn pubdbid–repsvrfile pubsvrfileSee Section 6.10 for additional information on setting the master definition node.The file containing the publication server login information.The following example sets the master node with publication database ID 9 as the master definition node.The setascontroller command sets a publication database to be designated as the controller database. The publication database may be the master database of a single-master replication system or a master node of a multi-master replication system.-setascontroller pubdbid–repsvrfile pubsvrfileSee Section 7.7 for additional information on setting the controller database.The publication database ID of the publication database to be designated as the controller database.The file containing the publication server login information.The following example sets the publication database with publication database ID 4 as the controller database.The validatepub command checks if any of the definitions of the tables in the given publication have changed since the publication was created.-validatepub pubname–repsvrfile pubsvrfileSee Section 7.6.5 for additional information on validating publications.The file containing the publication server login information.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system.In the following example, publication dept_emp is validated.The validatepubs command checks if any of the definitions of the tables subordinate to the given publication database definition have changed since the publication was created.–repsvrfile pubsvrfile-pubdbid dbidSee Section 7.6.5 for additional information on validating publications.The file containing the publication server login information.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system.In the following example, the Oracle publication database definition identified by publication database ID 1 is validated.The removepub command removes one or more publications.–repsvrfile pubsvrfileSee Section 7.6.6 for additional information on removing a publication.The file containing the publication server login information.Specify s if this command applies to a single-master replication system. Specify m if this command applies to a multi-master replication system.A publication named dept_emp is removed from a single-master replication system.The replicateddl command applies an ALTER TABLE statement to a publication table in all databases of a replication system as well as updates the xDB Replication Server insert/update/delete triggers and shadow table associated with that publication table.-replicateddl pubname–repsvrfile pubsvrfile-ddlscriptfile script_fileSee Section 7.8 for additional information on DDL change replication.The name of the publication containing the table to which the ALTER TABLE statement is to be applied.The file containing the publication server login information.The name of the schema containing table_name. This value is case-sensitive.The name of the table in the ALTER TABLE statement whose definition is to be modified. This value is case-sensitive.Path to the file containing the ALTER TABLE statements.The following example shows the addition of a column named title to table edb.emp. The ALTER TABLE statement is in the text file addcolumn.sql as shown by the following:The replicateddl command is executed using the addcolumn.sql file to update the triggers and shadow tables on the master nodes:For SMR only: The addsubdb command adds a subscription database definition.-repsvrfile subsvrfile-dbhost host-dbport port-dbuser user-database dbname[ -urloptions jdbc_url_parameters ]The addsubdb command creates a new subscription database definition. The addsubdb command displays a unique subscription database ID that is assigned to the newly created subscription database definition. The subscription database ID is used to identify the subscription database definition on which to operate when running other xDB Replication Server CLI commands.See Section 5.3.2 for details on the database connection information that must be supplied when adding a subscription database definition.The file containing the subscription server login information.Specify oracle if the database is an Oracle database. Specify enterprisedb if the database is an Advanced Server database in Oracle compatible configuration mode. Specify postgresql if the database is a PostgreSQL database or an Advanced Server database in PostgreSQL compatible configuration mode. Specify sqlserver if the database is a Microsoft SQL Server database.The port number on which the database server is listening for connections.The encrypted password of the subscription database user. See Section 8.3.4 for directions on using the encrypt command to generate an encrypted password.Specify sid if the Oracle system ID (SID) is used to identify the subscription database in the database parameter. Specify servicename if the Oracle service name is used to identify the subscription database in the database parameter. Note: For Oracle 12c, use the service name.Extended usage of JDBC URL parameters such as for support of SSL connectivity. (See Section 7.11 for information on SSL connectivity to the subscription database.)The following example adds a subscription database definition for an Oracle database. The encrypted password is given on the command line with the dbpassword parameter. A subscription database ID of 1 is assigned to the database by the subscription server.The following example adds a subscription database definition for an Advanced Server database. The encrypted password is read from a file named pwdfile with the dbpassfile parameter. A subscription database ID of 2 is assigned to the database by the subscription server.For SMR only: The printsubdbids command prints the subscription database IDs of the subscription database definitions.-printsubdbids -repsvrfile subsvrfileThe file containing the subscription server login information.For SMR only: The printsubdbidsdetails command prints the connection information for each subscription database definition.-printsubdbidsdetails –repsvrfile subsvrfileNote: The database user’s password is not displayed.The file containing the subscription server login information.The port number on which the database server is listening for connections.The following are the subscription database definitions subordinate to the subscription server identified by the content of file subsvrfile.prop.For SMR only: The updatesubdb command provides the ability to change the connection information for an existing subscription database definition identified by its subscription database ID.-repsvrfile subsvrfile-subdbid dbid-dbhost host-dbport port-dbuser user-database dbname[ -urloptions jdbc_url_parameters ]The subscription database definition to be updated is identified by the subdbid parameter.See Section 5.3.2 for details on the database connection information that must be supplied for a subscription database definition.The file containing the subscription server login information.The port number on which the database server is listening for connections.The password of the database user in encrypted form. See Section 8.3.4 for directions on using the encrypt command to generate an encrypted password.Specify sid if the Oracle system ID (SID) is used to identify the subscription database in the database parameter. Specify servicename if the Oracle service name is used to identify the subscription database in the database parameter. Note: For Oracle 12c, use the service name.Extended usage of JDBC URL parameters such as for support of SSL connectivity. (See Section 7.11 for information on SSL connectivity to the subscription database.) Specification of the urloptions parameter completely replaces any existing JDBC URL parameters that may have previously been specified with this database. Omission of the urloptions parameter deletes any existing JDBC URL parameters that may have previously been specified with this database.In the following example, an existing subscription database definition with subscription database ID 2 is updated.For SMR only: The removesubdb command removes a subscription database definition.The subscription database definition to be removed is identified by the subdbid parameter.See Section 5.5.6 for additional information on removing a subscription database.The file containing the subscription server login information.For SMR only: The createsub command creates a new subscription.-createsub subname-subsvrfile subsvrfile-subdbid dbid-pubsvrfile pubsvrfile-pubname pubnameThe createsub command adds a new subscription subordinate to the subscription database definition with the subscription database ID given by parameter subdbid.See Section 5.3.3 for additional information on creating a subscription.The file containing the subscription server login information of the subscription server under which the new subscription is subordinate.The file containing the publication server login information of the publication server under which the publication is subordinate to which the new subscription is to be associated.Comma-separated list of filter IDs identifying the filter rules from the set of available table filters to enable on the corresponding tables in the new subscription. Use the printpubfilterslist command to obtain the filter IDs for the available filter rules in the publication (see Section 8.3.17). Note: There must be no white space between the comma and filter IDs.In the following example, a subscription named dept_emp_sub is created in the Advanced Server subscription database identified by subscription database ID 2. The subscription is associated with a publication named dept_emp.For SMR only: The printsublist command prints a list of subscription names.The file containing the subscription server login information.The subscription names subordinate to the subscription database definition specified by dbid are printed.The enablefilter command enables one or more filter rules on a single-master replication system subscription or on a multi-master replication system master node other than the master definition node.The enablefilter command is used when it is desired to apply a filter rule to a subscription or a non-MDN node, but the filter rule did not yet exist or it was neglected to be included with the subscription or the non-MDN node when these components were initially created.-repsvrfile pubsvrfileSee Section 2.2.12 for additional information on table filters.
The two databases being compared are referred to as the source database and the target database. The source database can be of type Oracle, EnterpriseDB, SQL Server, Sybase®, or MySQL®. The target database must be either Oracle or EnterpriseDB.Note: The Data Validator does not validate columns having the following data types. Tables containing one or more columns of these types will only be partially validated.
Step 1: When you install the xDB Replication Server product, the components for the Data Validator are installed as well. See Chapter 3 for information on installing the xDB Replication Server product.
The current working directory from which you invoke the Data Validator script runValidation.sh (runValidation.bat for Windows) must be the bin subdirectory containing the script (that is, XDB_HOME/bin)../runValidation.sh { –ss | --source-schema } schema_name[ option ] ...schema_name is the name of the schema in the source database containing the tables to be validated. The choices for option are listed later in this section within the Options subsection.runValidation { –ss | --source-schema } schema_name[ option ] ..../runValidation.sh –ss schema[ -ts schema ][ -ld log_directory_path ][ -sdbms database_type ][ -sh host ][ -sp port ][ -sdb dbname ][ -su user ][ -spw password ][ -tdbms database_type ][ -th host ][ -tp port ][ -tdb dbname ][ -tu user ][ -tpw password ][ -bs row_count ][ -fs row_count ]For clarity, the preceding syntax diagram shows only the single-character form of the option. The Options subsection lists both the single-character and multi-character forms of the options.Specification of any database connection option (-sdbms through -tpw listed in the preceding syntax diagram) overrides the corresponding parameter in the datavalidator.properties file. See Section 9.1 for information on the datavalidator.properties file.The tables within the source schema that are to be included for comparison. If omitted, all tables within the source schema are compared against tables in the target schema with the exception of those tables excluded from comparison using the -et option. Note: There must be no white space between the comma and table names.The tables within the source schema that are to be excluded from comparison. If omitted, only those tables specified with the -it option are included for comparison. If both the -it and -et options are omitted, all source schema tables are included for comparison. Note: There must be no white space between the comma and table names.-srs, --skip-rowsonlyin-source { true | false }When true is specified, the logging of differences for rows that exist only in the source database table are skipped. The default is false.-srt, --skip-rowsonlyin-target { true | false }When true is specified, the logging of differences for rows that exist only in the target database table are skipped. The default is false.-srb, --skip-rowsin-both { true | false }When true is specified, the logging of differences for rows that exist both in the source and target database tables with the same primary key, but with different non-primary key values are skipped. The default is false.Directory path to where the Data Validator log and diff files are to be created and stored. If log_directory_path does not exist, Data Validator attempts to create it. If a full directory path is not specified log_directory_path is created or assumed to be located relative to the XDB_HOME/bin subdirectory where the runValidation.sh script is invoked. (That is, the logs directory is XDB_HOME/bin/log_directory_path.) Be sure the operating system account used to invoke the runValidation.sh script has the privileges to create the directory if it does not already exist, or to create files in the specified directory if it does already exist. If omitted, the default is the XDB_HOME/bin/logs directory.-ds, --display-summary { true | false }Specify true to display only the Data Validator summary. This omits the source and target database connection information as well as the detailed breakdown of the results by source database table. Specify false to display all of the Data Validator results. The type and amount of information that is displayed at the command line console when the Data Validator is invoked is the same information that is also stored in the log file for that run. If omitted, the default is false (that is, all of the Data Validator results is displayed).The type of the source database server. Supported types are oracle, enterprisedb, sqlserver, sybase, and mysql.The port number on which the source database server is listening for connections.The port number on which the target database server is listening for connections.The -bs option specifies the number of rows to group in a batch to be used for comparison across the source and target database tables. For example, if a table contains 1000 rows, then a -bs setting of 100 requires 10 batch iterations to complete the comparison across the source and target databases. The Data Validator reads 100 rows, both from the source and target tables, and adds them in source and target buffers. The validation thread then reads the 100 rows from the source and target buffers and performs the comparison. It will then move to read and prepare the next 100 rows for comparison and so on. Note that the actual database round trips required to bring in 100 rows from the database depends on the -fs option for the fetch size. For example, an -fs setting of 100 needs just one round trip whereas an -fs setting of 10 requires 10 database round trips.Performing data validation for tables that are quite large in size may cause the Data Validator to terminate with an out of heap space error when using the default fetch size of 5000 rows. Use the -fs option to specify a smaller fetch size to help avoid the out of heap space issue. The result set iteration will bring in as many rows as represented by the row_count value in a single database round trip.The following examples use an Oracle source database and an Advanced Server target database to compare the tables in schema EDB on Oracle against the tables in schema public in Advanced Server.The following lists the tables in schema EDB along with the content of tables DEPT and EMP in the Oracle source database:The following lists the tables in schema public along with the content of tables dept and emp in the Advanced Server edb database:
10 Appendix
If you have xDB Replication Server 5.1.x, you must first upgrade this version to xDB Replication Server 6.0. See Section 10.2 “Upgrading to xDB Replication Server 6.0” in the EDB Replication Server 6.0 User’s Guide located at:Step 1: Any pending backlog of transactions on the publication tables must be replicated before starting the upgrade process.Step 2: After all pending transactions have been replicated to their target databases, stop the xDB Replication Server 6.1.x publication server and subscription server (see sections 5.2.1 and 5.3.1).Step 3: Install xDB Replication Server 6.2. See Chapter 3 for instructions on installing xDB Replication Server, but note the differences described in the following steps.Step 4: Following the acceptance of the license agreement in Step 11 of Section 3.1, the Select Components screen appears, but with the entries grayed out. The old xDB Replication Server components are replaced by the new ones in the old xDB Replication Server’s directory location. Click the Next button.Step 5: The Existing Installation screen confirms that an existing xDB Replication Server installation was found. Click the Next button to proceed with the upgrade.Step 6: On the Ready to Install screen, click the Next button.Step 7: The remaining screens that appear confirm completion of the installation process and allow you to exit from Stack Builder or StackBuilder Plus.Step 8: After installation completes, the publication server of the new xDB Replication Server product should be running, connected to the controller database used by xDB Replication Server 6.1. The subscription server may or may not be running at this point, however, that is an expected outcome of this process.Step 9: Complete the publication server and subscription server configuration file setup.In the XDB_HOME/etc directory, a new set of configuration files for xDB Replication Server version 6.2 are created. These files are named xdb_pubserver.conf.new and xdb_subserver.conf.new. The new configuration files contain any new configuration options added for xDB Replication Server 6.2.The old configuration files used by xDB Replication Server version 6.1.x remain unchanged as xdb_pubserver.conf and xdb_subserver.conf.The final set of active configuration files must be named xdb_pubserver.conf and xdb_subserver.conf.In the XDB_HOME/etc/sysconfig directory, make sure the xDB Startup Configuration file xdbReplicationServer-62.config contains the parameter settings you wish to use with xDB Replication Server 6.2. See Section 2.3.1.4 for information on the xDB Startup Configuration file.Step 11: Check the publication server and subscription server log files to verify that no errors have occurred (see Section 10.3.2.4).Step 12: Adjust the publication server and subscription server port numbers if necessary.The xDB Replication Server 6.2 publication and subscription servers are installed to use the default port numbers 9051 and 9052, respectively. If the xDB Replication Server 6.1.x replication systems used port numbers other than 9051 and 9052, then perform the modifications to correct this inconsistency as described in Section 10.2.3.If no such adjustment to the port numbers is needed, register the publication server and subscription server with the xDB Replication Console as described in sections 5.2.1 and 5.3.1. The existing replication systems should appear in the replication tree of the xDB Replication Console.Step 13: You are now ready to use xDB Replication Server 6.2 to create new replication systems and manage existing ones.Note: Be sure the repository configuration file edb.repo for xDB Replication Server 6.2 is set up in the /etc/yum.repos.d directory. See Section 3.3 for information.Step 1: Any pending backlog of transactions on the publication tables must be replicated before starting the upgrade process.Step 2: After all pending transactions have been replicated to their target databases, stop the xDB Replication Server 6.1.x publication server and subscription server (see sections 5.2.1 and 5.3.1).Step 3: Save a copy of the following configuration files:Step 4: If any Oracle publication or subscription databases are used in existing single-master replication systems, make sure a copy of the Oracle JDBC driver, version ojdbc5 or later, will be accessible by the publication server and subscription server where xDB Replication Server 6.2 will be installed. See Section 5.1.3.1 for information.Note: There are two options available: Option 1) Copy the Oracle JDBC driver to the jre/lib/ext subdirectory of your Java runtime environment. Option 2) Copy the Oracle JDBC driver to the lib/jdbc subdirectory of the xDB Replication Server installation directory.It is suggested that you perform option 1 (copy the Oracle JDBC driver to the jre/lib/ext subdirectory of your Java runtime environment).If on the other hand you perform option 2, you must copy the Oracle JDBC driver to the /usr/ppas-xdb-6.2/lib/jdbc directory after you have installed xDB Replication Server 6.2.Step 5: It is best to ensure that the controller database is up and running. The other publication and subscription databases of existing SMR and MMR systems do not need to be up and running.Step 6: As the root account invoke the yum update command to begin the upgrade from xDB Replication Server 6.1.x to xDB Replication Server 6.2 as shown by the following:Be sure to include the asterisk character (*) following ppas-xdb in order to update all xDB Replication Server components.
10.3 Resolving Problems10.3.1 Error MessagesWhen an error message is displayed by the xDB Replication Console, it may be followed by a specific reason as denoted by Reason: reason_for_failure as in the following example:
