ora_migrator is a PostgreSQL extension that uses oracle_fdw to migrate an Oracle database to PostgreSQL.
Only sequences and normal tables with their constraints and indexes will be migrated, all objects containing PL/SQL code (triggers, functions, procedures and packages) will have to be migrated by hand.
In addition to that, the extension can be used to create foreign tables and views that allow convenient access to Oracle metadata from PostgreSQL.
A superuser sets the stage:
CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
GRANT USAGE ON FOREIGN SERVER oracle TO migrator;
CREATE USER MAPPING FOR migrator SERVER oracle
OPTIONS (user 'orauser', password 'orapwd');
PostgreSQL user migrator
has the privilege to create PostgreSQL schemas
and Oracle user orauser
has the SELECT ANY DICTIONARY
privilege.
Now we connect as migrator
and perform the migration so that all objects
will belong to this user:
CREATE EXTENSION ora_migrator;
SELECT oracle_migrate(server => 'oracle', only_schemas => '{LAURENZ,SOCIAL}');
NOTICE: Creating staging schemas "ora_stage" and "pgsql_stage" ...
NOTICE: Creating Oracle metadata views in schema "ora_stage" ...
NOTICE: Copy definitions to PostgreSQL staging schema "pgsql_stage" ...
NOTICE: Creating schemas ...
NOTICE: Creating sequences ...
NOTICE: Creating foreign tables ...
NOTICE: Migrating table laurenz.log ...
NOTICE: Migrating table laurenz.ft_speed_sa ...
NOTICE: Migrating table laurenz.badstring ...
WARNING: Error loading table data for laurenz.badstring
DETAIL: invalid byte sequence for encoding "UTF8": 0x80:
NOTICE: Migrating table laurenz.datetest ...
NOTICE: Migrating table laurenz.department ...
NOTICE: Migrating table laurenz.hasnul ...
WARNING: Error loading table data for laurenz.hasnul
DETAIL: invalid byte sequence for encoding "UTF8": 0x00:
NOTICE: Migrating table social.blog ...
NOTICE: Migrating table laurenz.employee ...
NOTICE: Migrating table laurenz.identity ...
NOTICE: Migrating table laurenz.req_lot ...
NOTICE: Migrating table social.email ...
NOTICE: Migrating table laurenz.numbers ...
NOTICE: Creating UNIQUE and PRIMARY KEY constraints ...
WARNING: Error creating primary key or unique constraint on table laurenz.badstring
DETAIL: relation "laurenz.badstring" does not exist:
WARNING: Error creating primary key or unique constraint on table laurenz.hasnul
DETAIL: relation "laurenz.hasnul" does not exist:
NOTICE: Creating FOREIGN KEY constraints ...
NOTICE: Creating CHECK constraints ...
NOTICE: Creating indexes ...
NOTICE: Setting column default values ...
NOTICE: Dropping staging schemas ...
NOTICE: Migration completed with 4 errors.
oracle_migrate
----------------
4
(1 row)
DROP EXTENSION ora_migrator;
Even though the migration of two of the tables failed because of bad data in the Oracle database, the rest of the data were migrated successfully.
-
You need PostgreSQL 9.5 or later.
-
The oracle_fdw extension must be installed.
-
A foreign server must be defined for the Oracle database you want to access.
-
The user who calls the
create_oraviews
function to create the foreign tables must have theUSAGE
privilege on the foreign server. -
A user mapping must exist for the user who calls the
create_oraviews
function. -
The Oracle user used in the user mapping must have privileges to read the following Oracle dictionary views:
DBA_CONS_COLUMNS
DBA_CONSTRAINTS
DBA_IND_COLUMNS
DBA_IND_EXPRESSIONS
DBA_INDEXES
DBA_MVIEWS
DBA_MVIEW_LOGS
DBA_SEQUENCES
DBA_TAB_COLUMNS
DBA_TABLES
DBA_USERS
DBA_VIEWS
The above privileges are required for database migration.
Additionally,
SELECT
privileges on the following dictionary views are required by some oth the views created bycreate_oraviews
:DBA_PROCEDURES
DBA_SOURCE
DBA_TAB_PRIVS
DBA_COL_PRIVS
You can choose to grant the user the
SELECT ANY DICTIONARY
system privilege instead, which includes all of the above.
The extension files must be placed in the extension
subdirectory of
the PostgreSQL shared files directory, which can be found with
pg_config --sharedir
If the extension building infrastructure PGXS is installed, you can do that simply with
make install
The extension is installed with the SQL command
CREATE EXTENSION ora_migrator;
This statement can be executed by any user with the right to create
functions in the public
schema (or the schema you specified in the
optional SCHEMA
clause of CREATE EXTENSION
).
-
Function
oracle_migrate
:Performs a migration from an Oracle foreign server to PostgreSQL.
Only tables and sequences are migrated, triggers, functions, procedures and packages will have to migrated by hand.The parameters are:
-
server
: The name of the Oracle foreign server which will be migrated to PostgreSQL.
You must have theUSAGE
privilege on that server. -
staging_schema
(defaultora_stage
): The name of a schema that will be created for objects used during the migration (specifically, the objects created bycreate_oraviews
). This schema will be dropped after the migration is completed. -
pgstage_schema
(defaultpgsql_stage
): The name of a schema that will be created for tables containing PostgreSQL metadata. This schema will be dropped after the migration is completed. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case. -
max_long
(default 32767): The maximal length ofLONG
andLONG RAW
values in the Oracle database. Apart from user data, this also applies to view definitions, column default expressions and index expressions. -
with_data
(defaultTRUE
): specifies if the data should be migrated as well. IfFALSE
, only the table definitions are migrated. This can speed up testing while developing a migration.
Calling this function is equivalent to calling the following functions in this order in a single transaction:
oracle_migrate_prepare
oracle_migrate_mkforeign
oracle_migrate_tables
oracle_migrate_functions
oracle_migrate_triggers
oracle_migrate_views
oracle_migrate_constraints
oracle_migrate_finish
You need permissions to create schemas in the PostgreSQL database to use this function.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_prepare
:Performs the first step of
oracle_migrate
.The parameters are the same as for
oracle_migrate
.Steps performed:
-
Create staging schemas for Oracle and PostgreSQL data.
-
Call
create_oraviews
to create the metadata in the Oracle stage. -
Create tables in the PostgreSQL stage and fill them with values from the views in the Oracle stage. Names and data types will be translated wherever possible.
-
Create the view
oracle_migration_cost_estimate
in the PostgreSQL stage that can be used to estimate the total migration costs.
You need permissions to create schemas in the PostgreSQL database to use this function.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_refresh
:Updates the tables in the PostgreSQL stage with the current values from the Oracle database.
This will only work if you didn't change the names of tables, columns and other objects in the PostgreSQL stage.The parameters are the same as for
oracle_migrate
.The return value is the number of captured errors that have been turned into warnings.
-
Function
oracle_migrate_mkforeign
:Performs the second step of
oracle_migrate
.The parameters are the same as for
oracle_migrate
.Steps performed:
-
Create the destination schemas in the PostgreSQL database.
-
Create sequences in the destination schemas.
-
Create foreign tables in the destination schemas.
You need permissions to create schemas in the PostgreSQL database to use this function.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_materialize
:Replaces a foreign table with a real table and migrates the contents.
This function is used internally byoracle_migrate_tables
, but can be useful to parallelize migration (see the Usage section).The parameters are:
-
s
: The name of the PostgreSQL schema containing the foreign table. -
t
: The name of the PostgreSQL foreign table. -
with_data
(defaultTRUE
): should the table data be migrated too? -
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL stage created byoracle_migrate_prepare
.
The return value is TRUE if the operation succeeded, otherwise FALSE.
-
-
Function
oracle_migrate_tables
:Calls
oracle_materialize
for all foreign tables in the migrated schemas to replace them with real tables.The parameters are:
-
staging_schema
(defaultora_stage
): The name of the Oracle stage created byoracle_migrate_prepare
. -
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL stage created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case. -
with_data
(defaultTRUE
): specifies if the data should be migrated as well. IfFALSE
, only the table definitions are migrated. This can speed up testing while developing a migration.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_functions
:Migrates functions for which
migrate
has been set toTRUE
.The parameters are:
-
pgstage_schema
(defaultpgsql_stage
): The name of the staging schema created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_triggers
:Migrates triggers for which
migrate
has been set toTRUE
. For each trigger, a trigger function with the same name is created.The parameters are:
-
pgstage_schema
(defaultpgsql_stage
): The name of the staging schema created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_views
:Migrates views for which
migrate
has been set toTRUE
.The parameters are:
-
pgstage_schema
(defaultpgsql_stage
): The name of the staging schema created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_constraints
:Creates constraints and indexes on all tables migrated from Oracle with
oracle_migrate_tables
.The parameters are:
-
pgstage_schema
(defaultpgsql_stage
): The name of the staging schema created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_migrate_finish
:Drops the staging schemas.
The parameters are:
-
staging_schema
(defaultora_stage
): The name of the Oracle staging schema created byoracle_migrate_prepare
. -
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL staging schema created byoracle_migrate_pgschema
.
The return value is the number of captured errors that have been turned into warnings.
-
-
Function
oracle_code_count
:This table function provides statistics on the number and size of functions, triggers, packages and view definitions in the Oracle database.
This can be used to assess the complexity of a database migration, since migrating these objects usually requires manual intervention.
There is one optional parameter:
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL staging schema created byoracle_migrate_pgschema
.
The function returns one line per object type (
function
,trigger
,package
andview
) that contains the number of objects in the Oracle database, the total lines of code and the total number of bytes in the object definitions. -
Table function
oracle_test_table
:This function tests an Oracle table for potential migration problems. You have to run it after
oracle_migrate_prepare
.The parameters are:
-
server
: the name of the Oracle foreign server -
schema
: the schema name -
table_name
: the table name -
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL stage created byoracle_migrate_prepare
.
schema
andtable_name
must be values from the columns of the same name of thetables
table in the PostgreSQL stage.This is a table function and returns the Oracle ROWID of the problematic rows as well as a message describing the problem.
Currently there are tests for two problems:
-
zero bytes
chr(0)
in string columns -
values in string columns that are not in the database encoding
-
-
Function
oracle_migrate_test_data
:This function calls
oracle_test_table
for all tables in the PostgreSQL staging schema and records the results in the tabletest_error
in the PostgreSQL stage (after emptying the table).In addition, an error summary is added to the table
test_error_stats
in the PostgreSQL stage. This is useful for measuring the progress of cleaning up bad data in Oracle over time.The function returns the total number of errors encountered.
The function parameters are:
-
server
: the name of the Oracle foreign server -
pgstage_schema
(defaultpgsql_stage
): The name of the PostgreSQL stage created byoracle_migrate_prepare
. -
only_schemas
(default NULL): An array of Oracle schema names that should be migrated to PostgreSQL. If NULL, all schemas except Oracle system schemas are processed.
The names must be as they appear in Oracle, which is usually in upper case.
-
-
Function
create_oraviews
:This function creates a number of foreign tables and views for Oracle metadata. It is used internally by
oracle_migrate_prepare
, but can be used independently.It takes the following parameters:
-
server
: The name of the Oracle foreign server for which the foreign tables will be created.
You must have theUSAGE
privilege on that server. -
schema
(defaultpublic
): The name of the schema where the foreign tables and views will be created.
The schema must exist, and you must have theCREATE
privilege on it. -
max_long
(default 32767): The maximal length of view definitions,DEFAULT
and index expressions in Oracle.
Calling the function will create the following foreign tables and views:
schemas
: Oracle schemaschecks
: Oracle check constraintscolumn_privs
: Privileges on Oracle table columnscolumns
: columns of Oracle tables and viewsforeign_keys
: columns of Oracle foreign key constraintsfunctions
: source code of Oracle functions and procedures (but not package or object definitions)keys
: columns of Oracle primary and foreign keyspackages
: source code of Oracle packages and package bodiestable_privs
: Privileges on Oracle tablestables
: Oracle tablestriggers
: Oracle triggersviews
: definition of Oracle viewssequences
: Oracle sequencesindex_columns
: columns of Oracle indexes that do not belong to a constraintschemas
: Oracle schemastriggers
: Triggers on Oracle tablespackages
: definition and bodies of PL/SQL packagestable_privs
: privileges of users on tablescolumn_privs
: privileges of users on table columnssegments
: size of Oracle objects
Objects in Oracle system schemas will not be shown.
-
The main use of this extension is to migrate Oracle databases to PostgreSQL.
You can either perform the migration by calling oracle_migrate
, or you do
it step by step:
-
Call
oracle_migrate_prepare
to create the Oracle staging schema with the Oracle metadata views and the PostgreSQL staging schema with metadata copied and translated from the Oracle stage. -
After this step, you can modify the data in the PostgreSQL stage, from which the PostgreSQL tables are created. This is useful if you want to modify data types, indexes or constraints.
Be aware that you cannot rename the schemas.
Also, if you want to rename tables, make sure that the new name is used in all tables consistently.The tables
tables
andfunctions
in the PostgreSQL staging schema have a boolean attributemigrate
that should be set toTRUE
to include the object in the migration. Since functions will always require editing, the flag is initially set toFALSE
for functions.If the Oracle definitions change while you are working, you can refresh the tables in the PostgreSQL stage with
oracle_migrate_refresh
. -
Call
oracle_migrate_mkforeign
to create the PostgreSQL schemas and sequences and foreign tables. -
Call
oracle_migrate_tables
to replace the foreign tables with real tables and migrate the data from Oracle.Alternatively, you can use
oracle_materialize
to do this step for Each table individually. This has the advantage that you can migrate several tables in parallel in multiple database sessions, which may speed up the migration process. -
Call
oracle_migrate_functions
to migrate functions. -
Call
oracle_migrate_triggers
to migrate triggers. -
Call
oracle_migrate_views
to migrate views. -
Call
oracle_migrate_constraints
to migrate constraints and indexes from Oracle. -
Call
oracle_migrate_finish
to remove the staging schemas and complete the migration.
Any errors (except Oracle connection errors) that happen during the
migration will not terminate processing. Rather, they will be reported
as warnings. Additionally, such errors are logged in the table migrate_log
in the PostgreSQL staging schema.
Later errors can be consequences of earlier errors: for example, any failure to migrate an Oracle table will also make all views and constraints that depend on that table fail.
Hint: When developing a migration, it will be useful to set the
parameter with_data
to false, so that only the object definitions are
migrated, but not the data. This will make the migration much faster and
is useful for debugging problems that are not connected with the table data.
Apart from migration, you can use the function create_oraviews
to create
foreign tables and views that allow convenient access to Oracle metadata
from PostgreSQL.
This is used by oracle_migrate_prepare
to populate the staging schema,
but it may also be useful for other tools.
These foreign tables can be used in arbitrary queries, e.g.
SELECT table_name,
constraint_name,
column_name,
remote_table,
remote_column
FROM foreign_keys
WHERE schema = 'LAURENZ'
AND remote_schema = 'LAURENZ'
ORDER BY table_name, position;
The additional conditions will be pushed down to Oracle whenever that is possible for oracle_fdw, so the queries should be efficient.
All Oracle object names will appear like they are in Oracle, which is usually in upper case.
Create an issue on Github: https://github.com/cybertec-postgresql/ora_migrator/issues or contact Cybertec: https://www.cybertec-postgresql.com