Thread: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:

Hello Friends,
i have an very strange issue that i have tried to fix in hours but couldn't solve it. Could you please help me this case:

  • I have installed ora2pg to migrate oracle table to postgres, everything was working fine for several projects before
  • recently i started a new project to mingrate tables from oracle to postgres again in the same computer, and i did the same process as the previous projects.
    But when i copy the data from oracle table to postgres tabe by  following command, it doesn't work properly:
    ora2pg -c ora2pg.conf -t COPY -a tablename
    no data have been transfered to postgres. it shows: 0/0 tables(100%) end of scanning.
    • connection to both database is correct.
    • The tables are existing on source and on target( structure table )...
    • i can create table structure from this config file

I have tried to fix it, but it didn't work, here are what i did:

  1. try to install ora2pg again -> not work
  2. try to transfer others tables -> have the same issue
  3. try to install ora2pg on other computer -> everything works fine
  4. Only one case when i transfered data to this table, it works but very only some rows not all, not exactly what i want to transfer. Maybe this data is stored on the Cachmemory not really from the source table...
Here ist my config file:

ORACLE_HOME          C:\Oracle\product\12.2.0\client_1

ORACLE_DSN  dbi:Oracle:host=xxx;sid=xx;port=1521

ORACLE_USER            system

ORACLE_PWD  xxx

SCHEMA                      Schemaname

ALLOW   TZV_DATA

TYPE                TABLE

PG_DSN                      dbi:Pg:dbname=xxxx;host=xxx;port=5432

PG_USER                     schemaname

PG_PWD                      xxx

LOG_ON_ERROR                    1

STOP_ON_ERROR                   0 



could you please recommend me the possible causes of this? it is possible, that perl does't work properly or in this computer has some other software running, which prevent this transfer....?. or something else that I really dont understand what happended

Thank you for you help in advance

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
Luca Ferrari
Date:
On Thu, Aug 24, 2023 at 1:51 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> no data have been transfered to postgres. it shows: 0/0 tables(100%) end of scanning.
>

Even if asking only for the table structure?
Can you perform an offline migration (i.e., saving to file)?

I would suggest to ask on ora2pg support.

Luca



Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:

Further updated to the below Post. Shortly question:

  • Saving data to file using the following Command is fine:
    ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/
  • Direct copy data from Oracle to Postgres doesn't work(command: ora2pg -c ora2pg.conf -t COPY -a tablename). Everything Else works fine. Connection to Oracle and Postgres using DBD::Pg, DBD::Oracle are set correctly.

do i have to completely delete the Ora2pg and install it again!? or some other softaware running on the computer have prevented this transfer's process.

or Do you know the root cause of that?
Thanks
Hans


Vào Th 5, 24 thg 8, 2023 vào lúc 19:01 Luca Ferrari <fluca1978@gmail.com> đã viết:
On Thu, Aug 24, 2023 at 1:51 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> no data have been transfered to postgres. it shows: 0/0 tables(100%) end of scanning.
>

Even if asking only for the table structure?
Can you perform an offline migration (i.e., saving to file)?

I would suggest to ask on ora2pg support.

Luca

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
Luca Ferrari
Date:
On Thu, Aug 24, 2023 at 3:49 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> do i have to completely delete the Ora2pg and install it again!? or some other softaware running on the computer have
preventedthis transfer's process. 

I don't think so.
Does the -d flag helps in finding out the problem?

Luca



Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:
Thanks for you idea, here is the log file when i used -d flag:


D:\ora2pg\hans>ora2pg -c ora2pg.conf -t COPY -a codes1 -d

WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11

[2023-08-24 18:07:13] Ora2Pg version: 24.0

[2023-08-24 18:07:13] Export type: COPY

[2023-08-24 18:07:13] Geometry export type: INTERNAL

[2023-08-24 18:07:13] ORACLE_HOME = C:\Oracle\product\12.2.0\client_1

[2023-08-24 18:07:13] NLS_LANG = AMERICAN_AMERICA.AL32UTF8

[2023-08-24 18:07:13] NLS_NCHAR = AL32UTF8

[2023-08-24 18:07:13] Trying to connect to database: dbi:Oracle:host=xx.xx ;sid=xx.xx;port=1521

[2023-08-24 18:07:14] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

[2023-08-24 18:07:14] Force Oracle to compile schema HANS before code extraction

[2023-08-24 18:07:14] Retrieving table information...

[2023-08-24 18:07:14] Collecting 0 tables in DBA_OBJECTS took:  0 wallclock secs ( 0.00 usr +  0.02 sys =  0.02 CPU)

[2023-08-24 18:07:14] Collecting 0 tables information in DBA_TABLES took:  0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)

[2023-08-24 18:07:14] ORACLE_HOME = C:\Oracle\product\12.2.0\client_1

[2023-08-24 18:07:14] NLS_LANG = AMERICAN_AMERICA.AL32UTF8

[2023-08-24 18:07:14] NLS_NCHAR = AL32UTF8

[2023-08-24 18:07:14] Trying to connect to database: dbi:Oracle:host=xx.xx;sid=xx.xx;port=1521

[2023-08-24 18:07:14] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

[2023-08-24 18:07:14] Retrieving partitions information...

[2023-08-24 18:07:14] Collecting 0 indexes in DBA_INDEXES took:  0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)

[2023-08-24 18:07:15] Total time to export data from 0 tables (0 partitions, 0 sub-partitions) and 0 total rows:  1 wallclock secs ( 0.00 usr +  0.02 sys =  0.02 CPU)

[2023-08-24 18:07:15] Fixing function calls in output files...


it was collecting 0 tables,  i don't see the connection part to Posgrest database

maybe do you see some thing strange here?


the output without -d flag:

D:\ora2pg\hans>ora2pg -c ora2pg.conf -t COPY -a codes1

WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11

[========================>] 0/0 tables (100.0%) end of scanning.

 

Fixing function calls in output files...


Thanks

Hans

 


Vào Th 5, 24 thg 8, 2023 vào lúc 22:55 Luca Ferrari <fluca1978@gmail.com> đã viết:
On Thu, Aug 24, 2023 at 3:49 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> do i have to completely delete the Ora2pg and install it again!? or some other softaware running on the computer have prevented this transfer's process.

I don't think so.
Does the -d flag helps in finding out the problem?

Luca

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
Erik Wienhold
Date:
> On 24/08/2023 15:49 CEST duc hiep ha <haduchiep@googlemail.com> wrote:
>
> Further updated to the below Post. Shortly question:
>   * Saving data to file using the following Command is fine:
>     ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/
>   * Direct copy data from Oracle to Postgres doesn't work
>     (command: ora2pg -c ora2pg.conf -t COPY -a tablename).
>     Everything Else works fine. Connection to Oracle and Postgres using
>     DBD::Pg, DBD::Oracle are set correctly.

Can you access table "tablename" when connecting with Postgres via psql and
the connection settings from ora2pg.conf?  Maybe privileges are missing or
the table is not on the search path.

--
Erik



Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:
Yes i can access the target table on Postgres, because i have to create it as strukture on the posgres before transfer data. Actually in the past i have done it successfully many time. The Problem here is some thing happened in between in my computer, or there is a bug on Ora2pg.

Vào Th 5, 24 thg 8, 2023 vào lúc 23:34 Erik Wienhold <ewie@ewie.name> đã viết:
> On 24/08/2023 15:49 CEST duc hiep ha <haduchiep@googlemail.com> wrote:
>
> Further updated to the below Post. Shortly question:
>   * Saving data to file using the following Command is fine:
>     ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/
>   * Direct copy data from Oracle to Postgres doesn't work
>     (command: ora2pg -c ora2pg.conf -t COPY -a tablename).
>     Everything Else works fine. Connection to Oracle and Postgres using
>     DBD::Pg, DBD::Oracle are set correctly.

Can you access table "tablename" when connecting with Postgres via psql and
the connection settings from ora2pg.conf?  Maybe privileges are missing or
the table is not on the search path.

--
Erik

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
Luca Ferrari
Date:
On Thu, Aug 24, 2023 at 6:19 PM duc hiep ha <haduchiep@googlemail.com> wrote:
> [2023-08-24 18:07:14] Collecting 0 tables information in DBA_TABLES took:  0 wallclock secs ( 0.00 usr +  0.00 sys =
0.00CPU) 

I suspect there is a permission problem here.
Are you really sure this same configuration works in offline mode?
What have you changed since the last time it worked?



Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:
Hi, 

I've just found the root cause! In this command:
ora2pg -c ora2pg.conf -t COPY -a tablename
I have created and changed the name of table in postgres. It will not work like that if i don't put replace_table option in config file.

Thanks all, my case can be closed now

Best
Hans

Vào Th 6, 25 thg 8, 2023 lúc 21:53 Luca Ferrari <fluca1978@gmail.com> đã viết:
On Thu, Aug 24, 2023 at 6:19 PM duc hiep ha <haduchiep@googlemail.com> wrote:
> [2023-08-24 18:07:14] Collecting 0 tables information in DBA_TABLES took:  0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)

I suspect there is a permission problem here.
Are you really sure this same configuration works in offline mode?
What have you changed since the last time it worked?

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
Luca Ferrari
Date:
On Fri, Aug 25, 2023 at 5:49 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> Hi,
>
> I've just found the root cause! In this command:
> ora2pg -c ora2pg.conf -t COPY -a tablename
> I have created and changed the name of table in postgres. It will not work like that if i don't put replace_table
optionin config file. 

Out of curiosity: you renamed a table in PostgreSQL after the schema
was migrated, therefore you need something like
REPLACE_TABLES old_name:new_name
in ora2pg, right?
That explains also why offline mode seemed to work: ora2pg was dumping
with the original name without asking itself if the table was there in
PostgreSQL.

Luca



Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

From
duc hiep ha
Date:
Yes, you are right.
Thank you
Brs
Hans
Vào Th 2, 28 thg 8, 2023 lúc 14:43 Luca Ferrari <fluca1978@gmail.com> đã viết:
On Fri, Aug 25, 2023 at 5:49 PM duc hiep ha <haduchiep@googlemail.com> wrote:
>
> Hi,
>
> I've just found the root cause! In this command:
> ora2pg -c ora2pg.conf -t COPY -a tablename
> I have created and changed the name of table in postgres. It will not work like that if i don't put replace_table option in config file.

Out of curiosity: you renamed a table in PostgreSQL after the schema
was migrated, therefore you need something like
REPLACE_TABLES old_name:new_name
in ora2pg, right?
That explains also why offline mode seemed to work: ora2pg was dumping
with the original name without asking itself if the table was there in
PostgreSQL.

Luca