Обсуждение: 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
- connection to both database is correct.
I have tried to fix it, but it didn't work, here are what i did:
- try to install ora2pg again -> not work
- try to transfer others tables -> have the same issue
- try to install ora2pg on other computer -> everything works fine
- 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...
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
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 preventedthis transfer's process. I don't think so. Does the -d flag helps in finding out the problem? Luca
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
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
> 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.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?
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 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
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