Oracle to EDB migration using XDB

[sushmitha]

Hi,

I am configurating migration from opacle to postgres using xdb.I have created publication and subscription service which is running fine.But facing error while creating subscription (connecting tables).Kindly help.

Please find below details.

Oracle:

SQL> desc test.persons;
Name Null? Type
----------------------------------------- -------- ----------------------------
PERSON_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(50)
LAST_NAME NOT NULL VARCHAR2(50)

EDB:

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -printpublist -repsvrfile pubsvr.prop
Printing publications ...
puborcl

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createsub suborcl -subsvrfile subsvr.prop -subdbid 1001 -pubsvrfile pubsvr.prop -pubname puborcl
Creating subscription...
Error: Unable to create subscription schema tables.
MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42501: com.edb.util.PSQLException: ERROR: permission denied for schema test

Error logs:

2019-02-25 06:19:10 MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42P01: com.edb.util.PSQLException: ERROR: relation "test.iseq$$_20339" does not exist
2019-02-25 06:19:10 Stack Trace:
com.edb.MTKException: MTK-15016: The table PERSONS could not be created in EDB Postgres database.
at com.edb.MigrationToolkit.copyTables(MigrationToolkit.java:4575)
at com.edb.MigrationToolkit.copyTables(MigrationToolkit.java:4477)
at com.edb.MigrationToolkit.importSchema(MigrationToolkit.java:4146)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:2031)
Caused by: com.edb.MTKException:
at com.edb.common.MTKMetaData.executeScript(MTKMetaData.java:1542)
at com.edb.MigrationToolkit.copyTables(MigrationToolkit.java:4540)
... 3 more
Caused by: com.edb.util.PSQLException: ERROR: relation "test.iseq$$_20339" does not exist
at com.edb.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2669)
at com.edb.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at com.edb.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
at com.edb.jdbc.PgStatement.executeSimpleQuery(PgStatement.java:1232)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.edb.common.MTKMetaData.executeScript(MTKMetaData.java:1432)

Thanks and regards,

Sushmitha M

[Ranjan]

Hi sushmitha,

We are looking into the issue you have raised and will update you soon.

Meanwhile could you please give us the steps you followed for doing below things :

1. Register Publication Database

2. Register Subscription Database

3. Create publication

4. Create Subscription (Error you are receiving)

Also, provide your pubsvr.prop and subsvr.prop

[sushmitha]

Hi Ranjan,

Please find the steps below:

Details about machines:

Oracle :172.31.25.207

EDB Postgres :172.31.1.226

#more pubsvr.prop

user=admin
password=IrXVPu86W4rbfGK+biM8qA\=\=
port=9051
host=172.31.1.226

#more subsvr.prop

user=admin
password=IrXVPu86W4rbfGK+biM8qA\=\=
port=9052
host=localhost

1. Register Publication Database

# more addpubsvr
-addpubdb -repsvrfile pubsvr.prop -dbtype oracle -dbhost 172.31.25.207 -dbport 1521 -dbuser pubuser -dbpassword deIuKoLKPi4= -oraconnectiontype si
d -database ORCL

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -paramfile addpubsvr

2. Register Subscription Database

#more addsubsvr
-addsubdb -repsvrfile subsvr.prop -dbtype enterprisedb -dbhost localhost -dbport 5446 -dbuser enterprisedb -dbpassword QF0Hj/4sulNpuqnwX4DBVw== -d
atabase oracle(I have also tried with new db user "subuser")

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -paramfile addsubsvr

3. Create publication

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createpub puborcl -repsvrfile pubsvr.prop -pubdbid 1 -reptype t -tables ORACLE.PERSONS;

4. Create Subscription (Error you are receiving)

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createsub suborcl -subsvrfile subsvr.prop -subdbid 9009 -pubsvrfile pubsvr.prop -pubname puborcl
Creating subscription...
Error: Unable to create subscription schema tables.
MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42P01: com.edb.util.PSQLException: ERROR: relation "oracle.iseq$$_22119" does not exist

[Ranjan]

Hi sushmitha,
 
Thanks for providing details.
 
Could you please help us in understanding your current xDB environment setup. 
 
Publication database - Oracle :172.31.25.207
Subscription database - EDB Postgres :172.31.1.226
xDB server - ?
publication server - ?
subscription server - ?
 
Is your xDB server, publication server and the subscription server is on same host?
 
Also please check is there any dependency between tables 'ORACLE.PERSONS' and 'oracle.iseq$$_22119' and is this a table (oracle.iseq$$_22119) present at Oracle end, looks like the partition table.

[sushmitha]

Hi Ranjan,

Yes.xDB server, publication server and the subscription server is on same host.(EDB Postgres :172.31.1.226).

I have no idea from where "oracle.iseq$$_22119" has come.I have only created the Oracle.persons table in oracle database and oracle schema in edb database.

[Ranjan]

Hi sushmitha,

Please let us know the xDB version you are using.

[sushmitha]

Hi Ranjan,

Xdb version is "xdbreplicationserver v6.2".

[Ranjan]

Hi,

Initially, you were facing below error saying 'ERROR: permission denied for schema test', which is related to test schema

---------------------------

Error: Unable to create subscription schema tables.
MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42501: com.edb.util.PSQLException: ERROR: permission denied for schema test

Error logs:

2019-02-25 06:19:10 MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42P01: com.edb.util.PSQLException: ERROR: relation "test.iseq$$_20339" does not exist

-------------------------

In subsequent emails, we are seeing the error saying 'ERROR: relation "oracle.iseq$$_22119" does not exist' which is different than previous.

Is there any changes you are doing at oracle end?

Also replace 'host=172.31.1.226' in pubsvr.prop file with localhost or 127.0.0.1 as xDB server, pub and sub server, subscription database all are on single host. 

[sushmitha]

Hi Ranjan,

I have not changed anything at oracle end.

Earlier i got error like below

2019-02-25 06:19:10 MTK-15016: The table PERSONS could not be created in EDB Postgres database.
DB-42P01: com.edb.util.PSQLException: ERROR: relation "test.iseq$$_20339" does not exist

So i re-tested it by changing the schema name from "test" to "oracle".Then also i got the same error

DB-42P01: com.edb.util.PSQLException: ERROR: relation "oracle.iseq$$_20339" does not exist.

Here test and oracle is just different schema names.

So the error is

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createpub puborcl -repsvrfile pubsvr.prop -pubdbid 1 -reptype t -tables <schema name>.<table name>;

#java -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createsub suborcl -subsvrfile subsvr.prop -subdbid 9009 -pubsvrfile pubsvr.prop -pubname puborcl

Error:
The table <table name> could not be created in EDB Postgres database.
DB-42P01: com.edb.util.PSQLException: ERROR: relation "<schema name>.iseq$$_20339" does not exist.

And i have tried replacing with "localhost" in pubsvr.prop file .But still the error is same.

[slonkar]

Hi @sushmitha,

From the error it looks like, the subscription DB user do not have permission to create the new objects inside the database  or the database itself is not present in the EPAS cluster.

Could you please verify that you have a database named oracle (as per commands shared by you) already created in the EPAS cluster with owner as enterprisedb user.

If it is not present, please create it and try again.

Also, verify that you do not have schema and table/views already present in the subscription database with the same name as in the publication database.

Please get back to us in case of any issues/queries.

Regards,

Sudhir

[justicehlaps]

Hi

Just to add on this,

the iseq$$  is a sequence which gets automatically  created in the background when a table is created with an identity column feature of oracle.

e.g

CREATE TABLE identity_demo (

    id NUMBER GENERATED ALWAYS AS IDENTITY,

    description VARCHAR2(100) NOT NULL

);

from the above table creation a sequence will be created automatically in the background for id column and automatically inserts the the id whenever a description is  inserted into the table.

We normaly use identity column feature for columns such as id for generating unique values for primary keys.

I am having an issue  with regard to this type of a sequence during the xdb subscription creation.

[justicehlaps]

I have attached the screenshots with the sequence issue below

[Rohit]

Hi,

Sequences (database objects created by the CREATE SEQUENCE statement) are not replicated from the publication database to the subscription databases in a single-master replication system.

You need to manually create the sequence in the subscription database and then perform a synchronize.

[justicehlaps]

Hi Robit,

Thanks for the response.Yes I have noticed the sequence do not get created but if you are having sequences which get created through  oracle identity columns you are going to run into problems.

I just have this 3 questions,

1. for sequences, if the sequece has been created manually on the subscriber,does it stay in sync with the sequence on the publisher during synchronization? ie if the  sequence's next value increment on the publisher will  the sequence on the subscriber next value also change on the subscriber after synchronization?  Do i have to create the sequence using the same name as 

2. I have noticed triggers to do not get copied across too,do  i need to create the triggers manually as well.what happens to the triggers during synchronization? will the fire? my understanding is that they should not fire.

3. Foreign keys do not get copied accross too? what must happen to the foreign keys ? When do i create them on the subscriber?

I will be happy to get clarity on this

Regards,

Justice

[Rohit]

Hi Justice,

Please refer below in-line comments for your raised concerns.

1. for sequences, if the sequece has been created manually on the subscriber,does it stay in sync with the sequence on the publisher during synchronization? ie if the sequence's next value increment on the publisher will the sequence on the subscriber next value also change on the subscriber after synchronization? Do i have to create the sequence using the same name as
>> Sequences (database objects created by the CREATE SEQUENCE statement) are not replicated from the publication database to the subscription databases in a single-master replication system.

>> Sequence's next value increment on the publisher will not change on subscriber after synchronization.

2. I have noticed triggers to do not get copied across too, do I need to create the triggers manually as well.what happens to the triggers during synchronization? will the fire? my understanding is that they should not fire.
>> Any change you want to see on the subscriber needs to be done on the publisher. All the changes made by triggers on the publisher will get replicated over to the subscriber.
>> If triggers are created on subscriber and changes are made due to them, they are not propagated back to the publication. If changes are made to the subscription table rows, it is fairly likely that the rows will no longer match their publication counterparts.

3. Foreign keys do not get copied accross too? what must happen to the foreign keys ? When do i create them on the subscriber?
>> Foreign key constraints are not replicated by the publication or subscription server in a single-master replication system.

Go through  'Restrictions on Replicated Database Objects'  for more details.