Oracle to PostgreSQL Migration.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle to PostgreSQL Migration.

Pawan Sharma
Hi All,

I am going to migrate Oracle database into PostgreSQL using ora2pg tools.

I have installed Strawberry Perl.

C:\ora2pg>perl -v
This is perl 5, version 24, subversion 1 (v5.24.1) built for MSWin32-x64-multi-thread
Copyright 1987-2017, Larry Wall


I am facing below issue while running below command.

C:\ora2pg>ora2pg -c ora2pg.conf
DBI connect('host=<servername>;sid=<SID>;port=<port>',<username>',...) failed: ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same <username> and <password>

entry in sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Please suggest.!!!!!!!!!!!!!!

-Pawan



Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Christian Mair
> I am facing below issue while running below command.
>
> *C:\ora2pg>ora2pg -c ora2pg.conf*
> DBI connect('host=<servername>;sid=<SID>;port=<port>',<username>',...) failed: ORA-01017: invalid username/p
> assword; logon denied (DBD ERROR: OCISessionBegin) at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
> FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)
> Aborting export...
>
> even I am able to login in Oracle with same <username> and <password>

If you can login from the same host using sqlplus like this:

sqlplus myuser/mypass@myhost:1521/mysid

then you likely can have ora2pg connect too. Just make sure that in ora2pg.conf you put the lines:

ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
ORACLE_USER myuser
ORACLE_PWD  mypass

Bye,
Chris.




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Pawan Sharma


On Mon, May 29, 2017 at 2:49 PM, Chris Mair <[hidden email]> wrote:
I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=<servername>;sid=<SID>;port=<port>',<username>',...) failed: ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same <username> and <password>

If you can login from the same host using sqlplus like this:

sqlplus myuser/mypass@myhost:1521/mysid

then you likely can have ora2pg connect too. Just make sure that in ora2pg.conf you put the lines:

ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
ORACLE_USER myuser
ORACLE_PWD  mypass

Bye,
Chris.


 
Hi Chris,

C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[>                        ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<server_name>;sid=<mysid>;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
        id bigint,
        name varchar(30)
) ;

CREATE TABLE mytab (
        id bigint,
        name varchar(30),
        dt timestamp
) ;
DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
                    ^ at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 11574.
FATAL: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
                    ^
Aborting export...

C:\ora2pg>
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Christian Mair
> C:\ora2pg>ora2pg -c ora2pg.conf
> [========================>] 2/2 tables (100.0%) end of scanning.
> [>                        ] 0/2 tables (0.0%) end of scanning.
> [========================>] 2/2 tables (100.0%) end of table export.

Looks good so far.
This means you could connect to Oracle DB now.


> DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist

This is coming from the Postgres side.

In ora2pg.conf go to the section

OUTPUT SECTION (Control output to file or PostgreSQL database)

I suggest you comment out (prefix with #) the part

#PG_DSN         dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USER        test
#PG_PWD         test

and just have ora2pg write its ouput to a file by setting OUTPUT like this:

OUTPUT          output.sql

This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).

You can then see at what point you get an error (and hopefully understand
what's happening).

Bye,
Chris.




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Pawan Sharma


On Mon, May 29, 2017 at 6:28 PM, Chris Mair <[hidden email]> wrote:
C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[>                        ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.

Looks good so far.
This means you could connect to Oracle DB now.


DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist

This is coming from the Postgres side.

In ora2pg.conf go to the section

OUTPUT SECTION (Control output to file or PostgreSQL database)

I suggest you comment out (prefix with #) the part

#PG_DSN         dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USER        test
#PG_PWD         test

and just have ora2pg write its ouput to a file by setting OUTPUT like this:

OUTPUT          output.sql

This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).

You can then see at what point you get an error (and hopefully understand
what's happening).

Bye,
Chris.



Hi Chris,

Thanks for suggestion.!!!

here in ora2pg.conf, I have used below type in ora2pg.conf and create the table manually on PostgreSQL server.

TYPE                TABLE


output after this
-------------------------------------
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Retrieving table information...
[1] Scanning table MYTAB (1 rows)...
[2] Scanning table TEST (1 rows)...
Dumping table TEST...
Dumping table MYTAB...
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
        id bigint,
        name varchar(30)
) ;

CREATE TABLE mytab (
        id bigint,
        name varchar(30),
        dt timestamp
) ;


but, when i am trying to insert data using

TYPE                       TABLE, INSERT


C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[>                        ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
        id bigint,
        name varchar(30),
        dt timestamp
) ;

CREATE TABLE test (
        id bigint,
        name varchar(30)
) ;
[========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[============>            ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!            ] 1/2 rows (50.0%) on total estimated data (4 sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=<servername>))(CONNECT_DATA=(SID=<mysid>))) at C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I checked on PostgreSQL server their data only in one table.

test=# select * from mytab ;
 id | name |         dt
----+------+---------------------
  1 | aa   | 2017-05-29 06:05:46
(1 row)

test=# select * from test;
 id | name
----+------
(0 rows)




So, I am stuck here..!!! Please suggest.




Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Christian Mair
> [========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
> [============>            ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
> Out of memory!            ] 1/2 rows (50.0%) on total estimated data (4 sec., avg: 0 recs/sec)
> Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
> RT=1521)(PROTOCOL=tcp)(HOST=<servername>))(CONNECT_DATA=(SID=<mysid>))) at C:/Strawberry/perl/vendor/
> lib/DBD/Oracle.pm line 348.
>
>
> So, It will show data enter in mytab 100% but in test it is 50%. but when I checked on PostgreSQL server their data only in one table.

Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.








--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Pawan Sharma


On Mon, May 29, 2017 at 6:51 PM, Chris Mair <[hidden email]> wrote:
[========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[============>            ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!            ] 1/2 rows (50.0%) on total estimated data (4 sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=<servername>))(CONNECT_DATA=(SID=<mysid>))) at C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I checked on PostgreSQL server their data only in one table.

Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.





ya, but how can fix it.??

because memory is not an issue on both the server.

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Pawan Sharma


On Mon, May 29, 2017 at 6:54 PM, PAWAN SHARMA <[hidden email]> wrote:


On Mon, May 29, 2017 at 6:51 PM, Chris Mair <[hidden email]> wrote:
[========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[============>            ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!            ] 1/2 rows (50.0%) on total estimated data (4 sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=<servername>))(CONNECT_DATA=(SID=<mysid>))) at C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I checked on PostgreSQL server their data only in one table.

Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.





ya, but how can fix it.??

because memory is not an issue on both the server.


Hi Chris,

I am able to get the output script in output.sql but now data migration is the big problem.
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Neil Anderson-2
>>>
>>>
>> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>

I've never used ora2pg but there is some documentation saying that
often an out of memory issue can be fixed by adjusting your DATA_LIMIT
setting. https://github.com/darold/ora2pg/blob/master/README#L307

> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration is
> the big problem.

--
Neil Anderson
[hidden email]
https://www.postgrescompare.com



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Adrian Klaver-4
In reply to this post by Pawan Sharma
On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:
>

>     ya, but how can fix it.??
>
>     because memory is not an issue on both the server.
>
>
> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration
> is the big problem.

http://ora2pg.darold.net/documentation.html#postgresql_import

--
Adrian Klaver
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL Migration.

Pawan Sharma

On Mon, May 29, 2017 at 7:17 PM, Adrian Klaver <[hidden email]> wrote:
On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:


    ya, but how can fix it.??

    because memory is not an issue on both the server.


Hi Chris,

I am able to get the output script in output.sql but now data migration is the big problem.

http://ora2pg.darold.net/documentation.html#postgresql_import

--
Adrian Klaver
[hidden email]


Thanks all,

now it's working After DATA_LIMIT    15000 in ora2pg.conf.

test migration is successful. 


C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[>                        ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
        id bigint,
        name varchar(30),
        dt timestamp
) ;

CREATE TABLE test (
        id bigint,
        name varchar(30)
) ;
[========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[============>            ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
[========================>] 1/1 rows (100.0%) Table TEST (0 recs/sec)
[========================>] 2/2 total rows (100.0%) - (261 sec., avg: 0 recs/sec).
[========================>] 2/2 rows (100.0%) on total estimated data (262 sec., avg: 0 recs/sec)
C:\ora2pg>