Postgres Plus xDB Replication Server with Multi-Master User's Guide : 9.3 Resolving Problems

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server xDB Replication Server User's Guide

 

9.3 Resolving Problems

This section contains tips for locating and correcting various problems that may occur.

9.3.1 Error Messages

The following table is a list of error messages that can appear from the xDB Replication Console. The messages are listed in alphabetical order based on the first word in the message following “a”, “an”, or “the”.

When 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:

Authentication failed. Reason: Invalid user name/password.

The various specific reasons are not listed for all messages in the table.

Table 9 - xDB Replication Server Error Messages

Error Messages and Resolutions

Authentication failed. Reason: Invalid user name/password.

Resolution: Occurs when registering a publication server or subscription server. Verify the user name and password you enter matches the user name and password in the xDB Replication Configuration file on the host you are running the publication server or subscription server.

Cannot register database because it is already registered with a publication service.

Resolution: Only one publication database definition can be created for any given database. (Oracle is the exception whereby more than one publication database definition can be created for the same Oracle database if different Oracle user names are specified in each publication database definition.)

The connection could not be established with the server. Verify that the server is running and accepting connections.

Resolution: Occurs whenever a Java RMI connection cannot be made to the publication server, the subscription server, or a database server. Can occur when registering a publication or subscription server, adding a publication database or a subscription database, or identifying the publication server for a new subscription. Verify you have entered the correct host IP address and port number of the server. Verify the server is running (see Section 9.3.4.1). If the server is running on Linux, verify that in the /etc/hosts file, the host name is mapped to the correct network IP address, which matches the IP address returned by the Linux /sbin/ifconfig command, and also matches the IP address you entered in the Host field of the dialog box. Alternatively, instead of modifying the /etc/hosts file, set configuration option java.rmi.server.hostname to the IP address of the publication or subscription server (see Section 9.4.1.6). Do not use the loopback address 127.x.x.x for this entry.

Connection rejected: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name", database "db_name", SSL off

Resolution: Occurs when attempting to save a publication database definition. Verify that the database host IP address, port number, database user name, password, and database identifier are correct. Verify that the database server is running. For a Postgres database, verify there is an entry in the pg_hba.conf file permitting access to the database by the given user name originating from the IP address where the publication server is running.

Currently no publication exists on the publication server. Please create at least one publication on the server and then retry.

Resolution: Occurs when attempting to create a subscription. If there are no publications in the specified publication server, then this error messge is displayed.

The database cannot be registered because a partial schema already exists. A manual cleanup is required to proceed.

Resolution: The metadata database objects from a prior publication already exist in the schema under which the publication server is attempting to create new metadata database objects. Perform the operation described in Section 9.3.4.2.

Database cannot be removed. Reason: Publication database connection cannot be removed as one or more publications are defined against it.

Resolution: Make sure all publications subordinate to the publication database definition have been removed. If no publications appear under the Publication Database node in the xDB Replication Console replication tree and the error persists, there may be a problem with the replication system metadata. Perform the operation described in Section 9.3.4.2.

Database cannot be removed. Reason: Publication service failed to clean up replication control schema tables.

Resolution: The metadata database objects under the Oracle publication database user schema or under the Postgres schema _edb_replicator_pub cannot be deleted by the publication server. The metadata database objects or schema may have already been deleted. The publication database definition cannot be removed using the xDB Replication Console. Perform the operation described in Section 9.3.4.2.

Database connection cannot be added. Cannot register database because it is already registered by a publication service.

Resolution: A given Oracle database can be specified in a publication database definition only once with a given network location and user name. An Oracle database can be used multiple times if different Oracle user names are used for each publication database definition. A given Postgres database can be specified in a publication database definition only once with a given network location.

Database connection cannot be added. Connection rejected: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name", database "db_name", SSL off

Resolution: Occurs when attempting to save a subscription database definition. Verify that the database host IP address, port number, database user name, password, and database identifier are correct. Verify that the database server is running. For a Postgres database, verify there is an entry in the pg_hba.conf file permitting access to the database by the given user name originating from the IP address where the subscription server is running.

Database connection cannot be added. Publication database connection registration failed. Reason: IO exception: The Network Adapter could not establish the connection.

Resolution: Verify that the database server is running. For Oracle, verify that the Oracle listener program lsnrctl is running.

Database connection cannot be added. The user has insufficient privileges to manage publications. Grant required privileges as listed below and then proceed with operation.

Resolution: An Oracle publication database user must have CONNECT, RESOURCE, and CREATE ANY TRIGGER privileges.

The database type for the selected database is different than that of the MDN database. Each database should be of the same type in a MMR cluster.

Resolution: All database servers in a multi-master replication system must be of the same type – either all PostgreSQL (or Advanced Server installed in PostgreSQL compatible configuration mode); or all Advanced Server installed in Oracle compatible configuration mode. This error message is displayed when attempting to add a master node and the database server type differs from the database server type of the master definition node. See Section 9.1.3.

An error occurred while removing tables from other Master node(s). Please refer to the user manual for instructions on how to remove shadow tables and triggers from Master node(s).

Resolution: When a master node of a multi-master replication system is deleted using the xDB Replication Console or the xDB Replication Server CLI, the metadata database objects that were created in the master node are also dropped. These include schema _edb_replicator_pub, which contains shadow tables and other database objects, and triggers on the publication tables. If any of these metadata database objects fail to be dropped, this error message is displayed. See Section 9.3.4.2 for directions on how to remove these metadata database objects.

The initial snapshot is not performed for this publication. Please take the snapshot first and then proceed with the synchronize operation.

Resolution: A snapshot replication must be performed before the first synchronization replication. Perform an on demand snapshot replication.

It is recommended to use a network IP address, the loopback address may result in connectivity issues.

Resolution: This warning is given when localhost or 127.0.0.1 is specified as the host address of a replication system component. If is strongly recommended that all replication system components are identified by their specific IP address on the network.

The log triggers creation failed for one or more publication tables. Make sure the database is in valid state and user is granted the required privileges.

Resolution: Either the user does not have the trigger creation privilege or there is a database server problem. The database server message is displayed as part of the error.

The MMR mode is currently not supported for database_type database.

Resolution: A database server of type database_type cannot be used in a multi-master replication system. Only Advanced Server or PostgreSQL database servers may be used as master nodes in a multi-master replication system.

No JDBC Client driver is configured for the Oracle data source.

Resolution: Occurs when creating an Oracle publication or subscription database definition. Copy the Oracle JDBC driver file ojdbc14.jar to subdirectory lib/jdbc of where the publication server or subscription server is installed on the host running the publication server or subscription server. Restart the publication server or subscription server.

None of the target master/subscription databases is accessible, hence the replication process failed to complete.

Resolution: Synchronization replication failed due to the unavailability of a target database. See the publication server log file for details. See Section 9.3.2.

One or more master database node(s) are defined against this publication. Removing the publication will invalidate the MDN.

Resolution: Master nodes are still defined in a multi-master replication system in which an attempt is being made to delete the publication from the master definition node. All master nodes (other than the master definition node) should be deleted first before deleting the publication from the master definition node. Perform this deletion process with the xDB Replication Console or xDB Replication Server CLI.

One or more subscriptions are defined against this publication. Removing the publication will invalidate the subscription.

Resolution: Warning issued when you attempt to remove a publication with subscriptions associated with it. You can remove the publication, but the subscriptions are no longer usable and should be removed as well.

Multiple Publications creation is not supported yet.

Resolution: Only one publication is supported in a multi-master replication system and only one such multi-master replication system can exist for an xDB Replication Server installation.

Only subscription which has subscribed against a publication with transactional replication type, can be synchronized.

Resolution: You cannot perform synchronization replication on a snapshot-only publication. Perform snapshot replication instead.

operation_type operation is not allowed in restricted mode.

Resolution: The specified multi-master operation_type is not permitted in restricted mode. Restricted mode is applicable when the license evaluation period expires. See Section 9.4.7.

Parent table table_name is not selected when its child tables are part of the publication list.

Resolution: Table selected for a publication has a foreign key referencing a parent table that has not been chosen for the publication. This is only a warning that the parent table will not be part of the subscription.

Problem occurred in publish process. Reason: ERROR: permission denied for schema _edb_replicator_pub

Resolution: For a Postgres publication, verify that the publication database user has CREATE ON DATABASE privilege on the publication database, or the database user is a superuser.

Publication cannot be created. One or more tables have no attributes defined and cannot be published. Unselect the specific tables and retry.

Resolution: In Postgres, it is possible to create a table with no columns. A publication is not allowed to include a Postgres table with no columns since the corresponding subscription table cannot be created in Oracle.

Publication cannot be created. Publication publication_name already exists on the publisher server. Please choose a different name and then proceed.

Resolution: Publication names must be unique within a publication server. Enter a different publication name.

Publication cannot be created. Table table_name does not contain a primary key. Transactional replication is not supported for a non-pk table.

Resolution: All tables used for synchronization replication must have primary keys. Create a primary key on the table or add the table to a snapshot-only publication.

Publication cannot be created. The publication creation process timed out as one or more tables may be locked by another session. Please retry later.

Resolution: For a Postgres publication that is not for snapshot-only, the publication database user must be able to create triggers on the publication tables. In order to do this, the publication database user must have the privilege to execute the ALTER TABLE statement on the publication tables and the publication database user must have CREATE and USAGE privileges on the schema containing the publication tables. Verify that one of the following is true: 1) All the tables in the publication are owned by the publication database user and the user has CREATE and USAGE privileges on the publication tables’ schemas, or 2) the publication database user is a superuser.

Publication cannot be updated. Reason: The parent table schema.table_name is selected for removal while it has one or more child tables in the publication list. Make sure that parent-child dependency holds in the publication tables.

Resolution: Choose the child tables for removal as well as the parent table.

Publication defined in MMR cluster cannot be subscribed in SMR cluster.

Resolution: A given publication cannot be used in both a multi-master replication system and a single-master replication system.

Publication does not exist on the publication server. It might have been removed.

Resolution: The publication does not exist for a given subscription. The subscription is no longer usable and must be removed.

Publication having subscription against it, cannot be updated by adding tables into (or removing tables from) it.

Resolution: Remove the subscription, add tables to, or remove tables from the publication, then add the subscription.

Publication Service connection failure.

Resolution: Verify that the publication server is running. See Section 9.3.4.1.

The replication control database cannot be registered as the publication database.

Resolution: Occurs when adding a publication database. Publications cannot be created in the xDB Control database so you cannot register the xDB Control database as a publication database.

The replication process could not be completed due to a database failure. Check the database state and retry.

Resolution: May be caused by characters in the publication data that are illegal for the character set of the subscription database. Check the snapshot replication failure log file or the database server log file. See Section 9.4.1.2.

Replication server does not support Oracle to Oracle replication.

Resolution: See Section 9.1 for supported database server configurations. Use Oracle products for Oracle to Oracle replication.

Subscription subscription_name already exists on the subscriber server. Please choose a different name and then proceed.

Resolution: Subscription names must be unique within a subscription server. Enter a different subscription name.

Subscription subscription_name cannot be removed. Reason: Publication does not exist on the publication server.

Resolution: Warning issued if the subscription you are attempting to remove does not have an associated publication. You can still remove the subscription.

Subscription database connection cannot be removed as one or more subscriptions are defined against it.

Resolution: You cannot remove a subscription database definition if there are subordinate subscriptions. Remove the subscriptions first.

Subscription does not exist on the subscription service. It might have been removed by some other user.

Resolution: The Subscription node you are trying to select no longer represents an existing subscription. The subscription may have been removed by a concurrent xDB Replication Console or xDB Replication Server CLI session. Click the Refresh icon in the xDB Replication Console toolbar to display the current replication tree.

Subscription Service connection failure.

Resolution: Verify that the subscription server is running. See Section 9.3.4.1

'Synchronize Publication' process failed for one or more master nodes. Please see logs for more details.

Resolution: Synchronization replication failed to complete for all target databases in the multi-master replication system due to the unavailability of some target database. See the publication server log file for details. See Section 9.3.2.

A table with large object type PK attribute cannot be published for (synchronize) incremental replication.

Resolution: Oracle doesn’t log changes for a large object column. Such a column cannot be referenced in the triggers that log changes to the shadow tables. Use snapshot-only replication instead.

Test result: Failure

Database connection information test failed. Reason: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Resolution: Occurs when testing the connection of a publication or subscription database definition. The publication or subscription server cannot connect to the database server network location given in the Add Database dialog box. Verify that the correct IP address and port for the database server are given. Verify that the database server is running and is accessible from the host running the publication or subscription server.

Test result: Failure

Database connection information test failed. Reason: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name", database "db_name", SSL off

Resolution: Occurs when testing the connection of a publication or subscription database definition. Verify that the database host IP address, port number, database user name, password, and database identifier are correct. Verify that the database server is running. For a Postgres database, verify there is an entry in the pg_hba.conf file permitting access to the database by the given user name originating from the IP address where the publication server or subscription server is running.

Unable to create Subscription subscription_name. Reason: Connection rejected: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx" user "user_name", database "db_name", SSL off

Resolution: Occurs when creating a subscription. The subscription server running on host xxx.xxx.xx.xxx could not access the xDB Control database. Verify that the pg_hba.conf file on the xDB Control database server permits access from the subscription server host

Unable to create subscription schema tables. Org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx" user "user_name", database "db_name", SSL off

Resolution: Occurs when creating a subscription. The subscription server running on host xxx.xxx.xx.xxx could not access the publication database. Verify that the pg_hba.conf file on the publication database server permits access from the subscription server host.

Unable to create subscription schema tables. The database type is not supported.

Resolution: The subscription database type is not supported for the intended publication database type. See Section 9.1.2 for a list of permitted source and target database server configurations.

Unable to create subscription schema tables. The table "table_name" could not be created in EnterpriseDB database.

Resolution: The subscription server was unable to create a subscription table definition in the intended target schema. The error returned by the database server is displayed. Typically, the reason is that a table with the same name already exists in the target schema of the subscription database.

Unable to perform snapshot for subscription sub_name. Reason: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name", database "db_name", SSL off

Resolution: Occurs when attempting a snapshot replication. The publication server running on host xxx.xxx.xx.xxx could not access the subscription database. Verify that the pg_hba.conf file on the subscription database server permits access from the publication server host.

Unable to synchronize. Reason: FATAL: no pg_hba.conf entry for host "xxx.xxx.xx.xxx", user "user_name", database "db_name", SSL off

Reason: Occurs during an implicit synchronization following snapshot replication. The publication server running on host xxx.xxx.xx.xxx could not access the subscription server’s xDB Control database. Verify that the pg_hba.conf file on the subscription server permits access from the publication server host using network address xxx.xxx.xx.xxx.

Unable to update publication database information. Reason: Publication control schema does not exist on target database.

Resolution: The metadata database objects in the publication database may have been deleted or corrupted. For an Oracle publication database the metadata database objects are located in the publication database user’s schema. For a Postgres publication database the metadata database objects are located in schema _edb_replicator_pub. See Section 9.3.4.2.

A valid product key must be entered in order to use Postgres Plus xDB Replication Server application once the initial trial has ended. Without a valid product key, once the trial has ended you will not be able to use the core features in MMR replication mode. Please contact EnterpriseDB to purchase a subscription for Postgres Plus xDB Replication Server.

Resolution: A new license key is required for multi-master replication operation. See Section 3.3

The xDB (MMR) license has expired. Please contact EnterpriseDB technical support for a new license key.

Resolution: A new license key is required for multi-master replication operation. See Section 3.3.

9.3.2 Where to Look for Errors

There are a number of places to look to find more detailed information about a replication error that may have occurred. This section provides a guide as to where to look for various types of errors.

9.3.2.1 General Replication Status

In the xDB Replication Console, view the replication history. See Section 7.3.

9.3.2.2 Snapshot Replication Failures

View the log file found in the following path:

For Linux:

/var/log/xdb-rep/buildx.x/mtk_yyyymmddhhmiss.log

For Windows:

POSTGRES_HOME\.enterprisedb\edb_replicator\buildx.x\mtk_yyyymmddhhmiss.log

POSTGRES_HOME is the home directory of the Windows postgres account (enterprisedb account for Advanced Server installed in Oracle compatible configuration mode). In the buildx.x subdirectory, x.x is the xDB Replication Server version number. The specific location of POSTGRES_HOME is dependent upon your version of Windows.

9.3.2.3 Synchronization Replication Failures

Check the database server log file.

The typical default location of these files is:

POSTGRES_INSTALL_HOME/data/pg_log

9.3.2.4 Publication and Subscription Server Startup Failures

View the log files pubserver.log and subserver.log in the following directory:

For Linux:

/var/log/xdb-rep/buildx.x

For Windows:

POSTGRES_HOME\.enterprisedb\edb_replicator\buildx.x

POSTGRES_HOME is the home directory of the Windows postgres account (enterprisedb account for Advanced Server installed in Oracle compatible configuration mode). In the buildx.x subdirectory, x.x is the xDB Replication Server version number. The specific location of POSTGRES_HOME is dependent upon your version of Windows.

Note: The severity level of messages logged in these files can be controlled by a configuration option. See Section 9.4.1.1.

Also check the database server log file.

9.3.2.5 Database Server Errors

Check the database server log file.

The typical default location of these files is:

POSTGRES_INSTALL_HOME/data/pg_log

9.3.2.6 Oracle Errors

For problems in Oracle, first find the directory locations of the log files by issuing the following commands in SQL*Plus:

SQL> SHOW PARAMETER USER_DUMP_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /usr/lib/oracle/xe/app/oracle/
                                                 admin/XE/udump

The directory given by parameter USER_DUMP_DEST contains errors given by user processes.

SQL> CONNECT system/password
Connected.
SQL> SHOW PARAMETER BACKGROUND_DUMP_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /usr/lib/oracle/xe/app/oracle/
                                                 admin/XE/bdump

The directory given by parameter BACKGROUND_DUMP_DEST contains errors given by the Oracle background processes.

Find the latest log file in the preceding directories to investigate the problem.

9.3.3 Common Problem Checklist

Use the following checklist to verify that the proper configuration steps have been followed. Omission of one or more of these steps is a common source of errors.

Step 1: Verify that the database server of the publication database, the database server of the subscription database (for single-master replication systems), the database servers of the master nodes (for multi-master replication systems), and the database server of the xDB Control database are all running.

Step 2: When viewing information in the xDB Replication Console, click the Refresh icon in the toolbar to ensure you are viewing the most current information, especially after making a configuration change to your replication system.

Step 3: Verify that the publication server and the subscription server (for single-master replication systems) are running. If they are not running and cannot be started see Section 9.3.4.1.

Step 4: If you are using an Oracle publication or subscription database, verify that the Oracle JDBC driver file, ojdbc14.jar has been copied to the XDB_HOME/lib/jdbc directory. XDB_HOME is the location where you installed xDB Replication Server. This directory is typically the Postgres installation home directory.

See Section 5.1.1.1.

Step 5: Verify that the necessary privileges have been granted to the publication database user.

For an Oracle publication database, verify that the publication database user has CONNECT, RESOURCE, and CREATE ANY TRIGGER privileges.

See Section 5.1.2.1.

For a SQL Server publication database, verify the following:

    ● In the msdb database, verify that the database user mapped to the SQL Server login given in the publication database definition has EXECUTE and SELECT privileges on schema dbo.

    ● In the publication database, verify that the database user mapped to the SQL Server login given in the publication database definition has its default schema set to the schema containing the xDB Replication Server metadata database objects.

    ● For the same database user discussed in the prior paragraph, verify that this database user is either the owner of the schema containing the xDB Replication Server metadata database objects, or has the following privileges on this schema: ALTER, EXECUTE, SELECT, INSERT, UPDATE, and DELETE.

    ● For the same database user discussed in the prior paragraph, verify that this database user has CREATE TABLE and CREATE PROCEDURE privileges.

    ● For the same database user discussed in the prior paragraph, verify that this database user has ALTER privilege on the publication tables.

    ● For any database user that will be updating the publication tables, verify that these database users have EXECUTE, SELECT, and INSERT privileges on the schema containing the xDB Replication Server metadata database objects.

See Section 5.1.2.2.

For a Postgres publication database in a single-master replication system, verify that the publication database user has the CREATE ON DATABASE privilege on the publication database, the CREATE and USAGE privileges on the schema containing the publication tables and views, and is the owner of the publication tables. Alternatively, use a superuser for a Postgres publication database user name.

See Section 5.1.2.3.

For the master definition node in a multi-master replication system, verify that the publication database user is a superuser, has the privilege to modify pg_catalog tables, and is the owner of the publication tables.

See Section 6.1.1.

For a master node other than the master definition node in a multi-master replication system, verify that the master node database user is a superuser and has the privilege to modify pg_catalog tables.

See Section 6.1.2.

Step 6: Verify that the necessary privileges have been granted to the subscription database user.

For an Oracle subscription database, verify that the subscription database user has CONNECT and RESOURCE privileges.

For a Postgres subscription database, verify that the subscription database user is a superuser and has the privilege to modify pg_catalog tables.

See Section 5.1.3.

Step 7 (For Linux only): Verify that the network IP address returned by the /sbin/ifconfig command either matches the IP address associated with the host name in the /etc/hosts file (see Section 5.1.4.2), or matches the IP address specified with the java.rmi.server.hostname configuration option in the publication and subscription server configuration files (see Section 9.4.1.6).

9.3.4 Troubleshooting Areas

The following topics provide information on specific problem areas you may encounter.

9.3.4.1 Starting the Publication Server or Subscription Server

Note: The subscription server only applies to single-master replication systems.

If you cannot start the publication server or the subscription server perform the following steps:

Step 1: Check the pubserver.log and subserver.log files for errors.

Step 2: Check the log file of the Postgres database server running the xDB Control database for errors.

Step 3: Verify that the user name and password in the xDB Replication Configuration file on the hosts running the publication server and subscription server match a superuser name and password in the Postgres database server running the xDB Control database that the publication server and subscription server are attempting to access.

Step 4: Verify that the pg_hba.conf file of the Postgres database server running the xDB Control database has an entry that allows access to the xDB Control database from the loopback address (127.0.0.1) by the user name in the xDB Replication Configuration file.

9.3.4.2 Deleting the xDB Replication Server Metadata

The xDB Replication Server metadata completely describes the replication system. This metadata must be complete and correct in order for replication to occur properly. In addition, the configuration and maintenance operations performed through the xDB Replication Console or the xDB Replication Server CLI cannot be accomplished properly unless the metadata is complete and correct.

There may be occasions where the metadata becomes corrupted. Either one or more tables containing metadata are inadvertently deleted, or the data within the metadata tables becomes corrupted. Typically, corruption occurs in the form of the first case – one or more metadata tables were deleted, or the entire schema and its contents were deleted manually using an SQL utility rather than through the operation of the xDB Replication Console or xDB Replication Server CLI.

In these situations, there is usually no other choice than to remove all of the remaining metadata database objects using the database management system’s deletion functions, which effectively deletes all replication systems that use the publication server and subscription server that access the xDB Control database where the metadata database objects are to be deleted. All replication systems running subordinate to the publication server and subscription server must then be recreated following the directions in sections 5.2 onward.

Warning: Do not attempt this if any replication systems are running in production. All replication systems using the affected publication server and subscription server will become inoperable.

This section describes what to look for in order to tell if the metadata is not complete, and if so, what must be deleted to completely remove the replication system. This section does not discuss the internal contents of the metadata tables. If all of the database objects comprising the metadata are present, then review the checklist in Section 9.3.3 before proceeding with deletion of the metadata as it is fairly unlikely that the content of a metadata table becomes corrupted.

If you decide that you must delete all of the metadata database objects, follow the steps as discussed in the following:

Step 1: Stop the publication server.

Step 2: Stop the subscription server.

Step 3: Look for the metadata database objects contained within a publication database. In the example used in this section, pubuser is the publication database user name. The publication consists of two tables – dept and emp.

For Oracle only: The metadata database objects to manage the publication are created in the publication database user’s schema as shown in the following output:

SQL> CONNECT pubuser/password
Connected.
SQL> SET PAGESIZE 9999
SQL> SELECT table_name FROM user_tables;

TABLE_NAME
------------------------------
RREP_TABLES
RREP_PUBLICATION_TABLES
RREP_TXSET
RREP_MMR_TXSET
RREP_PUBLICATION_SUBSCRIPTIONS
RREP_TXSET_LOG
RREP_LOCK
RREP_MMR_PUB_GROUP
RREP_PROPERTIES
RREP_TXSET_HEALTH
RRST_EDB_DEPT
RRST_EDB_EMP

12 rows selected.

SQL> SELECT sequence_name FROM user_sequences;

SEQUENCE_NAME
------------------------------
RREP_TX_SEQ
RREP_TXSET_SEQ
RREP_COMMON_SEQ

SQL> SELECT DISTINCT name FROM user_source WHERE type = 'PACKAGE';

NAME
------------------------------
RREP_PKG

SQL> SELECT trigger_name FROM user_triggers;

TRIGGER_NAME
------------------------------
RRPI_EDB_DEPT
RRPU_EDB_DEPT
RRPD_EDB_DEPT
RRPI_EDB_EMP
RRPU_EDB_EMP
RRPD_EDB_EMP

6 rows selected.

Note the following in the preceding output.

    ● The tables named according to the convention RRST_schema_table from the SELECT statement on user_tables are found only for synchronization publications. In this example, these tables are RRST_EDB_DEPT and RRST_EDB_EMP.

    ● The triggers named according to the convention RRPD_schema_table, RRPI_schema_table, and RRPU_schema_table from the SELECT statement on user_triggers are found only for synchronization publications. In this example, these triggers are RRPU_EDB_DEPT, RRPI_EDB_DEPT, RRPD_EDB_DEPT, RRPI_EDB_EMP, RRPU_EDB_EMP, and RRPD_EDB_EMP.

The following example shows what the same set of queries would look like if the publication was a snapshot-only publication:

SQL> CONNECT pubuser/password
Connected.
SQL> SET PAGESIZE 9999
SQL> SELECT table_name FROM user_tables;

TABLE_NAME
------------------------------
RREP_TABLES
RREP_PUBLICATION_TABLES
RREP_TXSET
RREP_MMR_TXSET
RREP_PUBLICATION_SUBSCRIPTIONS
RREP_TXSET_LOG
RREP_LOCK
RREP_MMR_PUB_GROUP
RREP_PROPERTIES
RREP_TXSET_HEALTH

10 rows selected.

SQL> SELECT sequence_name FROM user_sequences;

SEQUENCE_NAME
------------------------------
RREP_TX_SEQ
RREP_TXSET_SEQ
RREP_COMMON_SEQ

SQL> SELECT DISTINCT name FROM user_source WHERE type = 'PACKAGE';

NAME
------------------------------
RREP_PKG

SQL> SELECT trigger_name FROM user_triggers;

no rows selected

For SQL Server only: Most of the metadata database objects to manage the publication are created in the schema of your choosing when the publication database is prepared as described in Section 5.1.2.2. The following examples assume the metadata database objects are in schema pubuser. The publication tables are dept and emp located in the edb schema.

The following query lists the metadata database objects located in the pubuser schema:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> SELECT s.name + '.' + o.name "Object Name", o.type_desc "Object Type"
3>   FROM sys.objects o,
4>        sys.schemas s
5>   WHERE s.name = 'pubuser'
6>     AND o.type IN ('U','P')
7>     AND o.schema_id = s.schema_id
8>   ORDER BY 2, 1;
9> GO
Object Name                            Object Type
-------------------------------------- --------------------------------------
pubuser.CleanupShadowTables            SQL_STORED_PROCEDURE
pubuser.ConfigureCleanUpJob            SQL_STORED_PROCEDURE
pubuser.ConfigureCreateTxSetJob        SQL_STORED_PROCEDURE
pubuser.CreateMultiTxSet               SQL_STORED_PROCEDURE
pubuser.CreateTableLogTrigger          SQL_STORED_PROCEDURE
pubuser.CreateTxSet                    SQL_STORED_PROCEDURE
pubuser.CreateUniTxSet                 SQL_STORED_PROCEDURE
pubuser.GetNewTxsCount                 SQL_STORED_PROCEDURE
pubuser.JobCleanup                     SQL_STORED_PROCEDURE
pubuser.JobCreateTxSet                 SQL_STORED_PROCEDURE
pubuser.LoadPubTableList               SQL_STORED_PROCEDURE
pubuser.nextval                        SQL_STORED_PROCEDURE
pubuser.RemoveCleanupJob               SQL_STORED_PROCEDURE
pubuser.RemoveCreateTxSetJob           SQL_STORED_PROCEDURE
pubuser.sp_createsequence              SQL_STORED_PROCEDURE
pubuser.sp_dropsequence                SQL_STORED_PROCEDURE
pubuser.rrep_common_seq                USER_TABLE
pubuser.rrep_lock                      USER_TABLE
pubuser.rrep_mmr_pub_group             USER_TABLE
pubuser.rrep_mmr_txset                 USER_TABLE
pubuser.rrep_properties                USER_TABLE
pubuser.rrep_publication_subscriptions USER_TABLE
pubuser.rrep_publication_tables        USER_TABLE
pubuser.rrep_tables                    USER_TABLE
pubuser.rrep_tx_seq                    USER_TABLE
pubuser.rrep_txset                     USER_TABLE
pubuser.rrep_txset_health              USER_TABLE
pubuser.rrep_txset_log                 USER_TABLE
pubuser.rrep_txset_seq                 USER_TABLE
pubuser.rrst_edb_dept                  USER_TABLE
pubuser.rrst_edb_emp                   USER_TABLE

(31 rows affected)

For non-snapshot only publication tables, triggers are created as well that reside in the schema containing the publication tables as shown by the following query:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> SELECT s.name + '.' + o.name "Trigger Name"
3>   FROM sys.objects o,
4>        sys.schemas s
5>   WHERE s.name = 'edb'
6>     AND o.type = 'TR'
7>     AND o.name LIKE 'rr%'
8>     AND o.schema_id = s.schema_id
9>   ORDER BY 1;
10> GO
Trigger Name
--------------------------------------
edb.rrpd_edb_dept
edb.rrpd_edb_emp
edb.rrpi_edb_dept
edb.rrpi_edb_emp
edb.rrpu_edb_dept
edb.rrpu_edb_emp

(6 rows affected)

Finally, some jobs are created in the msdb database as shown by the following:

1> USE msdb;
2> GO
Changed database context to 'msdb'.
1>
2> SELECT j.name "Job Name"
3>   FROM msdb.dbo.sysjobs j,
4>        master.dbo.syslogins l
5>   WHERE l.name = 'pubuser'
6>     AND j.name LIKE 'rrep%'
7>     AND j.owner_sid = l.sid
8>   ORDER BY 1;
9> GO
Job Name
-----------------------------------
rrep_cleanup_job_edb
rrep_txset_job_edb

(2 rows affected)

Postgres only: The metadata database objects to manage the publication are created in the schema _edb_replicator_pub as shown in the following:

Figure 253 - Metadata database objects in a Postgres publication database

Note: The triggers used for synchronization replication do not appear under the Trigger Functions node in pgAdmin (Postgres Enterprise Manager Client in Advanced Server) because the replication triggers are in Oracle syntax. The pgAdmin Trigger Functions node displays only trigger functions, which are in PostgreSQL syntax. In pgAdmin, Oracle triggers appear under the specific table nodes on which the triggers are called.

Step 4: If the schema that is supposed to contain the metadata database objects (the publication database user name for Oracle, or _edb_replicator_pub for Postgres) is missing, or there are no database objects under the metadata schema, then you must complete the process of removing all remaining metadata database objects. If the metadata database objects look intact, go on to Step 5.

For Oracle only: If the publication user name still exists, then log onto SQL*Plus or any other Oracle database administration utility and drop all objects owned by the publication user. Alternatively, you can drop the publication database user along with its database objects using the cascade option, but the publication database user must be recreated and privileges reassigned if you intend to rebuild your replication systems. See Section 5.1.2 for directions on creating the publication database user. The following example illustrates use of the cascade option:

SQL> CONNECT system/password
Connected.
SQL> DROP USER pubuser CASCADE;

User dropped.

For SQL Server only: If any of the metadata database objects listed in Step 3 still exist, then log onto the SQL Server command line program, sqlcmd, or SQL Server Management Studio and drop these objects. The following example assumes the metadata database objects were created under schema pubuser. The publication tables are dept and emp located in schema edb.

The following example shows how to delete the jobs in the msdb database:

1> USE msdb;
2> GO
Changed database context to 'msdb'.
1> EXEC sp_delete_job @job_name = 'rrep_cleanup_job_edb';
2> GO
1> EXEC sp_delete_job @job_name = 'rrep_txset_job_edb';
2> GO

The next example shows the deletion of the triggers on the non-snapshot only publication tables:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> DROP TRIGGER edb.rrpd_edb_dept;
3> DROP TRIGGER edb.rrpi_edb_dept;
4> DROP TRIGGER edb.rrpu_edb_dept;
5> DROP TRIGGER edb.rrpd_edb_emp;
6> DROP TRIGGER edb.rrpi_edb_emp;
7> DROP TRIGGER edb.rrpu_edb_emp;
8> GO

The metadata database objects under the pubuser schema are dropped as shown by the following:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> DROP PROCEDURE pubuser.CleanupShadowTables;
3> DROP PROCEDURE pubuser.ConfigureCleanUpJob;
4> DROP PROCEDURE pubuser.ConfigureCreateTxSetJob;
5> DROP PROCEDURE pubuser.CreateMultiTxSet;
6> DROP PROCEDURE pubuser.CreateTableLogTrigger;
7> DROP PROCEDURE pubuser.CreateTxSet;
8> DROP PROCEDURE pubuser.CreateUniTxSet;
9> DROP PROCEDURE pubuser.GetNewTxsCount;
10> DROP PROCEDURE pubuser.JobCleanup;
11> DROP PROCEDURE pubuser.JobCreateTxSet;
12> DROP PROCEDURE pubuser.LoadPubTableList;
13> DROP PROCEDURE pubuser.nextval;
14> DROP PROCEDURE pubuser.RemoveCleanupJob;
15> DROP PROCEDURE pubuser.RemoveCreateTxSetJob;
16> DROP PROCEDURE pubuser.sp_createsequence;
17> DROP PROCEDURE pubuser.sp_dropsequence;
18> GO
1>
2> DROP TABLE pubuser.rrep_common_seq;
3> DROP TABLE pubuser.rrep_lock;
4> DROP TABLE pubuser.rrep_mmr_pub_group;
5> DROP TABLE pubuser.rrep_mmr_txset;
6> DROP TABLE pubuser.rrep_properties;
7> DROP TABLE pubuser.rrep_publication_subscriptions;
8> DROP TABLE pubuser.rrep_publication_tables;
9> DROP TABLE pubuser.rrep_tables;
10> DROP TABLE pubuser.rrep_tx_seq;
11> DROP TABLE pubuser.rrep_txset;
12> DROP TABLE pubuser.rrep_txset_health;
13> DROP TABLE pubuser.rrep_txset_log;
14> DROP TABLE pubuser.rrep_txset_seq;
15> DROP TABLE pubuser.RRST_edb_dept;
16> DROP TABLE pubuser.RRST_edb_emp;
17> GO

For Postgres only: If the schema _edb_replicator_pub still exists in the publication database, drop the schema and all of its database objects. The following example shows a connection established in psql to the publication database edb. The DROP SCHEMA CASCADE statement is then used to drop the metadata schema _edb_replicator_pub.

edb=# \c edb enterprisedb
You are now connected to database "edb" as user "enterprisedb".
edb=# DROP SCHEMA _edb_replicator_pub CASCADE;
NOTICE:  drop cascades to 16 other objects
DETAIL:  drop cascades to sequence _edb_replicator_pub.rrep_common_seq
drop cascades to sequence _edb_replicator_pub.rrep_tx_seq
drop cascades to sequence _edb_replicator_pub.rrep_txset_seq
drop cascades to table _edb_replicator_pub.rrep_tables
drop cascades to table _edb_replicator_pub.rrep_publication_tables
drop cascades to table _edb_replicator_pub.rrep_txset
drop cascades to table _edb_replicator_pub.rrep_mmr_txset
drop cascades to table _edb_replicator_pub.rrep_publication_subscriptions
drop cascades to table _edb_replicator_pub.rrep_txset_log
drop cascades to table _edb_replicator_pub.rrep_lock
drop cascades to table _edb_replicator_pub.rrep_mmr_pub_group
drop cascades to table _edb_replicator_pub.rrep_properties
drop cascades to table _edb_replicator_pub.rrep_txset_health
drop cascades to schema rrep_pkg
drop cascades to table _edb_replicator_pub.rrst_edb_dept
drop cascades to table _edb_replicator_pub.rrst_edb_emp
DROP SCHEMA

Step 5: Repeat steps 3 and 4 for each publication database definition subordinate to the publication server. If all of the metadata schemas and their database objects are intact for all publication database definitions, then chances are that the problem lies elsewhere. Recheck the checklist in Section 9.3.3.

Step 6: Locate the metadata of the publication and subscription servers. The database objects for the main metadata are located in the xDB Control database in schemas _edb_replicator_pub and _edb_replicator_sub. These schemas are shown in the following:

Figure 254 - Metadata database objects in the xDB Control database

Note: If the publication server and subscription server are running on separate hosts, each with their own xDB Control database, then the database on the publication server’s host contains only the _edb_replicator_pub schema and the database on the subscription server’s host contains only the _edb_replicator_sub schema.

Note: If all of your replication systems are multi-master, the xDB Control database used by the publication server contains only the _edb_replicator_pub schema.

Step 7: Delete schemas _edb_replicator_pub and _edb_replicator_sub and all their database objects from the xDB Control database. The following psql session shows this process:

edb=# \c xdb enterprisedb
You are now connected to database "xdb" as user "enterprisedb".
xdb=# DROP SCHEMA _edb_replicator_pub CASCADE;
NOTICE:  drop cascades to 14 other objects
DETAIL:  drop cascades to sequence _edb_replicator_pub.rrep_common_seq
drop cascades to table _edb_replicator_pub.erep_pub_database
drop cascades to table _edb_replicator_pub.erep_mmr_pub_group
drop cascades to table _edb_replicator_pub.erep_publications
drop cascades to table _edb_replicator_pub.erep_pubtables_ignoredcols
drop cascades to table _edb_replicator_pub.erep_publication_subscriptions
drop cascades to table _edb_replicator_pub.erep_events
drop cascades to table _edb_replicator_pub.erep_events_status
drop cascades to table _edb_replicator_pub.erep_conflicts
drop cascades to table _edb_replicator_pub.erep_conflicts_options
drop cascades to table _edb_replicator_pub.erep_cleanup_conf
drop cascades to table _edb_replicator_pub.erep_pub_replog
drop cascades to table _edb_replicator_pub.erep_pub_table_replog
drop cascades to table _edb_replicator_pub.erep_sub_servers
DROP SCHEMA
xdb=#
xdb=# DROP SCHEMA _edb_replicator_sub CASCADE;
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to sequence _edb_replicator_sub.rrep_common_seq
drop cascades to table _edb_replicator_sub.erep_sub_database
drop cascades to table _edb_replicator_sub.rrep_tables
drop cascades to table _edb_replicator_sub.rrep_subscriptions
drop cascades to table _edb_replicator_sub.rrep_subscription_tables
drop cascades to table _edb_replicator_sub.rrep_txset
drop cascades to table _edb_replicator_sub.erep_publication_subscriptions
DROP SCHEMA

Note: If the publication server and the subscription server are running on separate hosts with their own xDB Control database, the first DROP SCHEMA statement of Step 7 is performed on the xDB Control database of the publication server. The second DROP SCHEMA statement of Step 7 is performed on the xDB Control database of the subscription server.

Step 8: Start the publication server.

Step 9: Start the subscription server.

Step 10: In the replication tree you should see the following:

Figure 255 - Replication tree after removal of all metadata database objects

All the nodes under the SMR and MMR type nodes beneath the Publication Server node, and under the Subscription Server node no longer appear as the metadata has been deleted.

Step 11: You will need to recreate the replication system as described in sections 5.2 onward for a single-master replication system. See sections 6.2 onward for a multi-master replication system.

Previous PageTable Of ContentsNext Page