Обсуждение: [GENERAL] Oracle to PostgreSQL Migration.

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.

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.

[========================>] 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.

On Mon, May 29, 2017 at 6:51 PM, Chris Mair <chris@1006.org> 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.

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
adrian.klaver@aklaver.com