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