Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 7:17 PM, Adrian Klaver 
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
> adrian.kla...@aklaver.com
>


Thanks all,

now it's working After DATA_LIMIT15000 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=;sid=;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>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Adrian Klaver]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Neil Anderson]

>>>
>>>
>> 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
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:54 PM, PAWAN SHARMA 
wrote:

>
>
> On Mon, May 29, 2017 at 6:51 PM, Chris Mair  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=))(CONNECT_DATA=(SID=)))
>>> 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.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:51 PM, Chris Mair  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=))(CONNECT_DATA=(SID=)))
>> 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.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris 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=))(CONNECT_DATA=(SID=))) 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:28 PM, Chris Mair  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_USERtest
> #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.


*TYPETABLE*


*output after this*
-
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database:
dbi:Oracle:host=;sid=;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=;sid=;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=;sid=;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=))(CONNECT_DATA=(SID=))) 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.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris 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_USERtest
#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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 2:49 PM, Chris Mair  wrote:

> I am facing below issue while running below command.
>>
>> *C:\ora2pg>ora2pg -c ora2pg.conf*
>> DBI connect('host=;sid=;port=',',...)
>> 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  and 
>>
>
> 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=;sid=;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>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...) 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  and 


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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[William Dunn]

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
 wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).


I would suggest refrain from dismissing EnterpriseDB's PostgreSQL Advanced
Server like that. It is not free like the community version of Postgres but
the cost pays for developer time spent adding the additional features which
make it capable of being a drop-in replacement of Oracle. For an
organization migrating off of Oracle paying for that would make a lot of
sense because of the developer time (and cost) saved by porting to that
rather than the additional effort of migration to community Postgres. It
also has the additional benefit of providing all the programming features
that their app developers have become used to when working with Oracle.

The EnterpriseDB developers are some of the most active contributors to the
community version of Postgres and the advancements made in the community
version are included in EnterpriseDB Advanced Server as well.

*Will J. Dunn*
*willjdunn.com http://willjdunn.com*

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).

 Sincerely,

 JD


 --
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Centered full stack support, consulting and development.
 Announcing I'm offended is basically telling the world you can't
 control your own emotions, so everyone else should do it for you.



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Julien Rouhaud]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Le 08/07/2015 22:25, CS DBA a écrit :
 
 
 On 07/08/2015 02:20 PM, John R Pierce wrote:
 On 7/8/2015 1:16 PM, dinesh kumar wrote:
 We recently done the similar migration for one of our customer.
 We used all opensource tools to achieve this migration
 process.
 
 We used Pentaho Data Integration tool for doing Online DB
 migration, which took minimal downtime with CDC{Change Data
 Capture} approach. Also, we used Ora2Pg tool to migrate the DB
 objects with some manual syntax modifications.
 
 
 thats the easy part.
 
 now what about the massive code base of pl/sql and triggers he 
 mentioned ?
 
 
 
 Have you considered using ora2pg? http://ora2pg.darold.net/
 
 We've done several client migrations with it, quite successfully
 
 
 

ora2pg is definitely the tool you need.  It can give you a total
migration estimated time (in man-day unit), with a call like

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --estimate_cost

You can check a sample report here: http://ora2pg.darold.net/report.html

- -- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (GNU/Linux)

iQEcBAEBAgAGBQJVnimjAAoJELGaJ8vfEpOqTKAH/0+uGEXUmGm6tfagpJqU7kB+
2c+jooW/MKokDcgifvmUTy+fKb8iDoF8CUffActFyX5YyrCFfb4Bjw9P6wuJfF6S
WXhzWXQ//AFiApqNPknfHWnYeqe4jJlLq2fHN7qCQvItEWuKFiHpWcEi1zVBPnMm
e6NLxePm0WzjpigbwhT2X0Ziena8CxxdencPQvO81clsR8Fgtq4B//6KQ3GEsIL2
aUtj4k+wBCmRywiMgiSYiQzLUXUp2HWOp6qLLYpwifr4BgY2X+CQNSHlAK2KXecf
fQ+rm3tyo5QEtMxHQPO/NUsl+zSIllZjPYG1Wa81RwsQpWKhNNB/+reDTSgC5ws=
=9pb/
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

Hi,

We recently done the similar migration for one of our customer. We used all
opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which
took minimal downtime with CDC{Change Data Capture} approach. Also, we used
Ora2Pg tool to migrate the DB objects with some manual syntax modifications.

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Jul 8, 2015 at 12:24 PM, Tim Clotworthy 
tclotwor...@bluestonelogic.com wrote:

 Hello,

 I have a customer that is about to undertake a migration of an Oracle 11g
 database to PostgreSQL 9.x (exact version to be determined). I am talking
 not only of the migration of schemas and data, but also of a substantial
 codebase of Pl/SQL stored procedures, as well as many triggers.

 I don't think they know yet what they are up against. Everything I have
 read is that this is a very substantial effort. At this stage, they would
 be particularly interested in realistic and practical information on how
 to
 estimate the effort required as well as any best-practices or guidance on
 transition strategies.

 I have found official documentation on the PostgreSQL site for porting
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
 there success will require that they are well prepared realistically
 understanding the scope of the effor they are asbout to undertake.

 Thanks for any response!

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John R Pierce]

On 7/8/2015 1:16 PM, dinesh kumar wrote:
We recently done the similar migration for one of our customer. We 
used all opensource tools to achieve this migration process.


We used Pentaho Data Integration tool for doing Online DB migration, 
which took minimal downtime with CDC{Change Data Capture} approach. 
Also, we used Ora2Pg tool to migrate the DB objects with some manual 
syntax modifications.




thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Joshua D. Drake]

On 07/08/2015 12:47 PM, John McKown wrote:



​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a drop in replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology


Because EDB is expensive. Why go from one closed source solution to 
another when you can go to the best Open Source database and forgo all 
of that?


Yes, EDB has some nice tools (no denying that) but porting from Oracle 
to PostgreSQL proper is not difficult in the least (although time 
consuming).


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing I'm offended is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[CS DBA]

On 07/08/2015 02:20 PM, John R Pierce wrote:
 On 7/8/2015 1:16 PM, dinesh kumar wrote:
 We recently done the similar migration for one of our customer. We
 used all opensource tools to achieve this migration process.

 We used Pentaho Data Integration tool for doing Online DB migration,
 which took minimal downtime with CDC{Change Data Capture} approach.
 Also, we used Ora2Pg tool to migrate the DB objects with some manual
 syntax modifications.


 thats the easy part.

 now what about the massive code base of pl/sql and triggers he
 mentioned ?



Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clarke]

This is almost exactly what we did around 8 years ago; obviously the
version numbers have changed. The reason we chose Postgres was the
enormous similarity between the two languages plus the overwhelming ROI
on the migration; my CEO had a spontaneous nosebleed when the Oracle
licensing costs were revealed one year.

Migration requires some thought, investigation, a clear plan and testing
- but I'm probably preaching to the converted.

We did it, we've never looked back and have had at least 8 long very
happy, stable and productive years and are looking forward to many more
(raises a glass to the developers and maintainers).

Tim Clarke

On 08/07/15 20:24, Tim Clotworthy wrote:
 Hello, 
  
 I have a customer that is about to undertake a migration of an Oracle 11g 
 database to PostgreSQL 9.x (exact version to be determined). I am talking 
 not only of the migration of schemas and data, but also of a substantial 
 codebase of Pl/SQL stored procedures, as well as many triggers. 
  
 I don't think they know yet what they are up against. Everything I have 
 read is that this is a very substantial effort. At this stage, they would 
 be particularly interested in realistic and practical information on
 how to 
 estimate the effort required as well as any best-practices or guidance on 
 transition strategies. 
  
 I have found official documentation on the PostgreSQL site for porting 
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
 there success will require that they are well prepared realistically 
 understanding the scope of the effor they are asbout to undertake. 
  
 Thanks for any response! 

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce pie...@hogranch.com wrote:

 On 7/8/2015 1:16 PM, dinesh kumar wrote:

 We recently done the similar migration for one of our customer. We used
 all opensource tools to achieve this migration process.

 We used Pentaho Data Integration tool for doing Online DB migration,
 which took minimal downtime with CDC{Change Data Capture} approach. Also,
 we used Ora2Pg tool to migrate the DB objects with some manual syntax
 modifications.


 thats the easy part.

 now what about the massive code base of pl/sql and triggers he mentioned ?


Yeah, we need to rewrite the business logic if there are any un-supported
features like autonomous transactions, packages, nested procedures, e.t.c.

Regards,
Dinesh
manojadinesh.blogspot.com



 --
 john r pierce, recycling bits in santa cruz



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John McKown]

On Wed, Jul 8, 2015 at 2:24 PM, Tim Clotworthy 
tclotwor...@bluestonelogic.com wrote:

 Hello,

 I have a customer that is about to undertake a migration of an Oracle 11g
 database to PostgreSQL 9.x (exact version to be determined). I am talking
 not only of the migration of schemas and data, but also of a substantial
 codebase of Pl/SQL stored procedures, as well as many triggers.

 I don't think they know yet what they are up against. Everything I have
 read is that this is a very substantial effort. At this stage, they would
 be particularly interested in realistic and practical information on how
 to
 estimate the effort required as well as any best-practices or guidance on
 transition strategies.

 I have found official documentation on the PostgreSQL site for porting
 Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
 there success will require that they are well prepared realistically
 understanding the scope of the effor they are asbout to undertake.

 Thanks for any response!


​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has extensions
to make it a drop in replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
​


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! 
John McKown

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Melvin Davidson]

I would start by looking at how many databases, schemas, tables and views
are involved. Then look at how many individual Oracle functions need to be
converted to plpgsql. You also need to investigate if there are any custom
data types. I do not have the formula, but I am sure there is a general
time factor involved in converting x databases, y schemas and z tables. You
did not mention what hardware is involved, but I am also sure there is a
time factor involved in copying / converting data from Oracle to
PostgreSQL, and you need to consider how much data you need to move and the
order, as there are probably foreign keys involved also.

You might want to do a small test to see how long it takes to dump 10k rows
of data from 1 tOracle table and load to PostgreSQL. That will at least
give you a general idea of how long it will take to move all data.

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake j...@commandprompt.com
wrote:


 On 07/08/2015 12:47 PM, John McKown wrote:


 ​Why are they converting?

 Would EnterpriseDB (a commercial version of PostgreSQL which has
 extensions to make it a drop in replacement for Oracle) be a
 possibility?
 http://www.enterprisedb.com/solutions/oracle-compatibility-technology


 Because EDB is expensive. Why go from one closed source solution to
 another when you can go to the best Open Source database and forgo all of
 that?

 Yes, EDB has some nice tools (no denying that) but porting from Oracle to
 PostgreSQL proper is not difficult in the least (although time consuming).

 Sincerely,

 JD


 --
 Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
 PostgreSQL Centered full stack support, consulting and development.
 Announcing I'm offended is basically telling the world you can't
 control your own emotions, so everyone else should do it for you.


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Oracle to PostgreSQL migration

[Uwe C. Schroeder]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


I think that's been part of pgadmin 2

On Monday 08 December 2003 10:49 pm, Randy Harris wrote:
 I've been scouring the PostgreSQL web site and have found several
 references to a Database Migration Wizard.  I was unable, however, to find
 any details. Would someone be kind enough to explain what and where it is? 
 I need to migrate an Oracle db to PostgreSQL.





 ---(end of broadcast)---
 TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

- -- 
UC

- --
Open Source Solutions 4U, LLC   2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/1nP4jqGXBvRToM4RAtNDAJwOOpIgk1da1DMmC2DvcNONZHk77ACgu2b0
QBvZnGi+n3yvBFPDnu542Zk=
=2xqU
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org