Forum OpenACS Q&A: Postgres 7.2.3 Data to Oracle 8.1.7

Posted by Heiko Kern on 09/23/03 04:21 PM

I would like to migrate a Postgresql 7.2.3 backed
openacs 4.6/dotlrn 1.0 on Linux to Oracle on Solaris.

Is there a way to do this?

What character set should i use when creating the oracle database (postgres was configured with --enable-locale --enable-multibyte)?

Thanks for any comments!

Heiko

2: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 1)

Posted by Andrei Popov on 09/23/03 06:47 PM

Wouldn't a result of pg_dump (with --inserts --no-owner and --no-priveleges) almost work? It should be just an SQL script with all the CREATE statements and INSERTs for data.

Granted, CREATEs are not going to be perfect -- some tweaking may be required.

3: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 1)

Posted by Andrew Piskorski on 09/24/03 10:46 AM

A more interesting question is, why do you want to do that?

I can't imagine any reason to move an OpenACS site from Linux to Solaris. Usually people go the other direction.

I can imagine reasons to switch from PostgreSQL to Oracle (a few features Postgres lacks), but I can imagine reasons to go the other way too (including a few features Oracle lacks). And I don't know of anyone who's made a Postgres to Oracle migration, everyone using PostgreSQL with OpenACS seems happy with it.

4: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 1)

Posted by Andrew Piskorski on 09/24/03 10:52 AM

Heiko, for Oracle, you want UTF8 character set encoding, not ASCII7 or anything else. Oracle asks you for "Character Set" and "National Character Set". I don't really know that that means, so I just pick UTF8 for both, and that seems to work ok.

Andrei, I would be very surprised if you'd be able to load a PostgreSQL dump into Oracle without major changes. Why else would OpenACS use completely separate Oracle and PostgreSQL versions of the *.sql package data model install files?

5: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 1)

Posted by Michael Hinds on 09/24/03 12:49 PM

Andrew, Oracle allows you to store data in one char set other than the default one in columns of type NCHAR, NVARCHAR2 and NCLOB. The Character Set is for normal CHAR, VARCHAR2 etc. The National Character Set is for NCHAR, etc.

6: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 3)

Posted by Patrick Giagnocavo on 09/24/03 02:43 PM

Andrew asks, why Solaris?

Actually there are a number of reasons that someone might consider using Solaris over Linux.

One might be the "lights out management" feature of some SPARC machines, where you can completely control the system remotely, even booting it.

Further, Oracle on Solaris is usually considered the very most stable and reliable way to run Oracle. This may change in the future of course as Oracle puts more emphasis on Linux.

7: Re: Postgres 7.2.3 Data to Oracle 8.1.7 (response to 3)

Posted by Carl Robert Blesius on 09/25/03 08:24 AM

Some other reasons:

In Haus Solaris Guru (with beard)

In Haus Oracle Guru (with mustache)

That are there to help. It is a question of long term maintenance. These guys have been playing with Solaris and Oracle for years. Experienced firemen.