cancel
Showing results for 
Search instead for 
Did you mean: 

Catch 22 with EnterpriseDB Postgres Replication Server

SOLVED
Highlighted
EDB Team Member

Catch 22 with EnterpriseDB Postgres Replication Server

Dear all,

 

Issue: xDB setup, SMR from Oracle. Set up Oracle DB as publisher. De-installed xDB from the server and re-installed it afterwards. Somehow it remembered that this Oracle database was a publisher before, so now I cannot add the Oracle db to xDB as it already is (so xDB reports me) and I cannot obviously remove it, as it is not there anymore as entity.

Derived issue: xDB uninstall could be seen as an incomplete uninstall, as it does not remove these links between xDB and its publisher database?

What have I done already: Removed the xDB tables and triggers from the system schema (which I used to register the publisher), without much success.

What I can still try (come to think of it): use another user (not system) to create a publisher database on the same database and see what is created. Perhaps I forgot to remove some stuff...

Help...

Size matters not... Look at me. Judge me by size, do you?
1 ACCEPTED SOLUTION

Accepted Solutions
Adventurer

Re: Catch 22 with EnterpriseDB Postgres Replication Server

Before uninstalling xDB, you need to make sure you've removed any SMR or MMR system using the xDB Replication Console, or Rep CLI:

The following EDB Postgres Replication Server Guide section emphasizes this: Section 2.4.2 Design Considerations

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.11.h...

 

It states in the next to last bullet point:

In general, the order of removal of a single-master replication system is as follows: 1) Remove the replication system logical components using the xDB Replication Console or xDB Replication Server CLI starting with the subscriptions (Subscription nodes) and then their parent components (Subscription Database nodes). 2) Unregister the subscription server if you no longer have any need for it. 3) Repeat the same process for the publications. 4) After all replication system logical components have been removed (except for possibly the publication server and subscription server) you can drop any of the physical database objects in Oracle, SQL Server, or Postgres. Do not drop the control schema objects manually, for example by using an SQL command line utility. Doing so may cause the xDB Replication Console and xDB Replication Server CLI to become inoperable. (See Section 10.3.4.3 if this problem occurs.) Deleting the replication system logical components using the xDB Replication Console or xDB Replication Server CLI automatically drops the control schema objects from the physical database.

 

Also, when creating an Oracle publication, you should not use any existing Oracle user (like SYS, SYSTEM or XDB) as the xDB publication database user. Create a brand, new Oracle database user for this purpose.

 

See the steps in: 5.1.4.1 Oracle Publication Database of the Guide:

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.24.h...

 

Particularly, Step 1:

Step 1: Create a database user name for the publication database user. The publication database user name must have a password, and it must have the ability to create a database session. The publication database user becomes the owner of the control schema objects that will be created in the publication database to track, control, and record the replication process and history.

 

That way, if there is any corruption problem that is not solvable by the xDB Replication Console or Rep CLI, you can just delete that new, Oracle database user with the CASCADE option and all of the xDB publication data objects will be deleted with it.

 

See Step 7 in 10.3.4.3 Deleting the Control Schema and Control Schema Objects:

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.66.h...

 

Step 7: 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 control schema 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.4 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;

If you still have to manually drop each xDB database object from the Oracle publication, the following section lists all the individual objects:

 

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.25.h...

 

3 REPLIES 3
Community Manager

Re: Catch 22 with EnterpriseDB Postgres Replication Server

The uninstaller some times doesn't deleat the repliaction schemas which it creates.

Theres objects are mostly in the schema '_rrep'. You have to drop these schemas after unintallation manually from the DB servers

EDB Team Member

Re: Catch 22 with EnterpriseDB Postgres Replication Server

Unfortunately, in this case, there are no _rrep schema's or objects in the Oracle database.

I am currently trying the equision between haveing a freshly installed Publisher repository and the older one to deduce how I can remove it.

Basically I am looking for / trying to find a step-by-step approach of removing forgotten repositories in Oracle databases.

Size matters not... Look at me. Judge me by size, do you?
Adventurer

Re: Catch 22 with EnterpriseDB Postgres Replication Server

Before uninstalling xDB, you need to make sure you've removed any SMR or MMR system using the xDB Replication Console, or Rep CLI:

The following EDB Postgres Replication Server Guide section emphasizes this: Section 2.4.2 Design Considerations

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.11.h...

 

It states in the next to last bullet point:

In general, the order of removal of a single-master replication system is as follows: 1) Remove the replication system logical components using the xDB Replication Console or xDB Replication Server CLI starting with the subscriptions (Subscription nodes) and then their parent components (Subscription Database nodes). 2) Unregister the subscription server if you no longer have any need for it. 3) Repeat the same process for the publications. 4) After all replication system logical components have been removed (except for possibly the publication server and subscription server) you can drop any of the physical database objects in Oracle, SQL Server, or Postgres. Do not drop the control schema objects manually, for example by using an SQL command line utility. Doing so may cause the xDB Replication Console and xDB Replication Server CLI to become inoperable. (See Section 10.3.4.3 if this problem occurs.) Deleting the replication system logical components using the xDB Replication Console or xDB Replication Server CLI automatically drops the control schema objects from the physical database.

 

Also, when creating an Oracle publication, you should not use any existing Oracle user (like SYS, SYSTEM or XDB) as the xDB publication database user. Create a brand, new Oracle database user for this purpose.

 

See the steps in: 5.1.4.1 Oracle Publication Database of the Guide:

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.24.h...

 

Particularly, Step 1:

Step 1: Create a database user name for the publication database user. The publication database user name must have a password, and it must have the ability to create a database session. The publication database user becomes the owner of the control schema objects that will be created in the publication database to track, control, and record the replication process and history.

 

That way, if there is any corruption problem that is not solvable by the xDB Replication Console or Rep CLI, you can just delete that new, Oracle database user with the CASCADE option and all of the xDB publication data objects will be deleted with it.

 

See Step 7 in 10.3.4.3 Deleting the Control Schema and Control Schema Objects:

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.66.h...

 

Step 7: 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 control schema 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.4 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;

If you still have to manually drop each xDB database object from the Oracle publication, the following section lists all the individual objects:

 

https://www.enterprisedb.com/docs/en/6.1/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.25.h...