Le 08/04/2019 à 14:24, Rick Otten a
écrit :
On Mon, Apr 8, 2019 at
1:49 PM Daulat Ram <Daulat.Ram@xxxxxxxxxxxxxxx>
wrote:
>
> Please confirm ! Can we migrate Oracle 12c database
(12.1.0.1.0) running on Solaris to PostgreSQL 11.2 on Linux
(Ubuntu). Also, please suggest the tools and pre-requisites.
A database migration is likely feasible, but might require
quite a lot
of work depending on what features you're using, and the
amount of PL
code. Also, obviously migrating the database is only a part
of the
overall migration process, as you'll also need to take care
of the
application(s), the backup/restore, monitoring and all other
tools you
need.
Concerning the database migration, the best tool is probably
Gilles
Darold's ora2pg. The tool also provides a migration cost
assessment
report, to evaluate the difficulty of the database
migration. More
information on http://ora2pg.darold.net/
The last big Oracle to PG migration that I did was
several years ago. We stood up the PostgreSQL instance(s)
and then used SymmetricDS to synchronize the Oracle and PG
databases. After tuning and testing the postgresql side,
we cut over the applications live - with minimal downtime -
by releasing the updated application code and
configuration. If we needed to fail back, it was also
pretty easy to undo the release and configuration changes.
Another approach you can play with is to leverage Foreign
Data Wrappers. In that scenario, you can run queries on
your Oracle database from within PostgreSQL. You can use
those queries to copy data directly into new tables without
any interim files, or as a hybrid transition while you get
the new database set up.
At the time I was working on that migration, we had too
many data-edge-cases for ora2pg to be very useful. It has
come a long ways since then. I'm not sure it can do a live
cutover, so you may need to plan a bit of downtime if you
have a lot of data to move into the new database.
Note that you will also almost certainly want to use a
connection pooler like PGBouncer and/or PGPool II (or both
at the same time), so be sure to include that in your plans
from the beginning.
That said, none of this is on topic for the performance
mailing list. Please try to direct your questions to the
right group next time.
Just a few additional pieces of information.
1) migration from one DBMS to another must always be lead as a
project (because your data are always important ;-)
2) a migration project always has the following main tasks:
- setting a proper postgres platform (with all softwares, procedures
and documentation needed to provide a good PostgreSQL service to
your applications/clients) (you may already have such a platform).
- migrating the data. This concerns both the structure (DDL) and the
data content.
- migration the stored procedures, if any. In Oracle migrations,
this is often a big workload in the project.
- adapting the client application. The needed effort here can be
huge or ... null, depending on the used languages, whether the data
access API are compatible or whether an ORM is used.
- when all this has been prepared, a test phase can start. This is
very often the most costly part of the project, in particular for
mission critical databases.
- then, you are ready to switch to Postgres.
3) do not hesitate to invest in education and external professional
support.
4) before launching such a project, it is highly recommended to
perform a preliminary study. For this purpose, as Julien said,
ora2pg brings a big help in analysing the Oracle database content.
The cost estimates are pretty well computed, which gives you very
quickly an idea of the global cost of the database migration. For
the application side, you may also have a look at code2pg.
KR. Philippe.
|
