ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
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
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
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:
Show quoted text
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
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
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:
Show quoted text
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
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
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:
Show quoted text
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
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?
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:
Show quoted text
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?
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
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:
Show quoted text
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 notwork 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