This article discusses tools for migrating schema online from Oracle to PostgreSQL and EDB Postgres Advanced Server.
- EDB Postgres Advanced Server and PostgreSQL
- Challenges of converting database schemas online
- Why migrate to PostgreSQL from Oracle?
- Why migrate to EDB Postgres Advanced Server from Oracle?
- Online tools to convert Oracle databases
- How to convert database schemas using Ora2Pg
- How to convert schemas using Migration Portal and EDB Postgres Advanced Server
- How to migrate from Oracle to PostgreSQL using ora_migrator
This article covers online schema migration from Oracle to PostgreSQL and EDB Postgres Advanced Server. We’ll take a look at the challenges confronted during online schema migration, different migration tools like EDB Migration Portal, Ora2Pg, and ora_migrator, and examples of online migration from Oracle to PostgreSQL and EDB Postgres Advanced Server using EDB Migration Portal, Ora2Pg, and ora_migrator.
EDB Postgres Advanced Server and PostgreSQL
PostgreSQL is an open-source object-relational database management system (ORDBMS) that supports both SQL and JSON querying. EDB Postgres Advanced Server is built on top of PostgreSQL to provide enterprise-level features and tools and also has native PL/SQL compatibility, making it the ideal choice for Oracle migration.
Challenges of converting database schemas online
Converting database schemas online does pose some challenges that deserve a closer look:
- Data loss is always a possibility during database schema conversion, and that risk of data loss increases with the size of the data and when data is migrated from different systems.
- Compatibility issues between applications can occur when converting data and applications from one environment to another—e.g., access control on one application may not work on the other.
- Data may be corrupted while converting database schemas. There can be different reasons for data corruption, including hardware failures, database failures, and human error.
- Source data complexity is another major challenge that can lead to various issues while converting the data.
Why migrate to PostgreSQL from Oracle?
Migrating from Oracle to PostgreSQL provides many benefits businesses:
- PostgreSQL offers significant cost saving, as PostgreSQL has no licensing costs, whereas Oracle requires significant license costs.
- PostgreSQL is open source, meaning it provides a larger toolset and does not require reliance on one single vendor for support, and thus no vendor lock-in.
- PostgreSQL supports multiple platforms, including every major cloud provider.
- PostgreSQL has no licence audits.
- PostgreSQL has strong community support.
- PostgreSQL is easy to use and has a rich feature set.
Why Migrate to EDB Postgres Advanced Server from Oracle?
What is EDB Postgres Advanced Server?
EDB Postgres Advanced Server is based on PostgreSQL and provides features and tools for enterprise-level deployments with significant control and security.
Features of EDB Postgres Advanced Server
EDB Postgres Advanced Server provides following features:
- Increased security
- Mission-critical tools
- Oracle compatibility features
- Modern deployment options
- Enhanced integration
- Enhanced DBA and developer productivity
- PostgreSQL community leadership
- 24x7 “Follow-the-Sun” support
Other EDB tools to leverage with EDB Postgres Advanced Server
EDB also provides the following tools to better use the Advanced Server:
- EDB Backup and Recovery Tool (BART)
- xDB Replication Server for Replication
- EDB Foreign Data Wrappers for PostgreSQL, Hadoop, MongoDB, and MySQL
- EDB Migration Portal
- EDB Failover Manager
- EDB Connectors
- EDB Postgres Enterprise Manager
- EDB Migration Toolkit
Online tools to convert Oracle databases
Ora2Pg
Ora2Pg is a free tool that provides features for migrating Oracle schemas to PostgreSQL schemas. It connects to Oracle, analyzes the database objects, and generates an SQL script to be loaded in PostgreSQL. Ora2Pg provides many features, including:
- Export full schemas with constraints—e.g., primary, unique, and foreign keys
- Export specific tables based on certain criteria
- Export functions, procedures, triggers, and packages
- Export materialized views
- Export Oracle user-defined types
- Available on multiple platforms
On the other hand, many users find Ora2Pg cumbersome to configure and use and not very efficient when compared to some other tools.
EDB Postgres Advanced server and EDB Migration Portal
- EDB Migration Portal is a web-based service for migrating Oracle schemas to EDB Postgres Advanced server.
- Migration Portal analyzes Oracle schemas and converts the database objects to DDLs that are compatible with EDB Postgres Advanced Server.
- Migration Portal’s web interface is easy to use and helps simplify the migration process.
Ora_migrator
- Ora_migrator is a free tool that uses the oracle_fdw foreign data wrapper to migrate an Oracle database to PostgreSQL.
- Ora_migrator is a lightweight tool that is easy to use. It is more efficient and faster than Ora2pg. It uses parallelism for more efficient and faster migration.
- On the other hand, Ora_migrator does not support migration of PL/SQL code and partitioning, and the migration is slow when the table contains LOBs.
How to convert database schemas using Ora2Pg
Ora2Pg performs migration by generating PostgreSQL-compatible SQL files, which can then be loaded into PostgreSQL. To use Ora2Pg, pull its source code from its Github page and install it following instructions mentioned there.
Configuration
Once Ora2pg is installed, you’ll need to configure it through its configuration file, ora2pg.conf.
Open ora2pg.conf and set the following configurations:
Note: The following configurations are related to Oracle. You may need to update based on the specifics of your Oracle installation.
# Set the Oracle home directory
ORACLE_HOME /u01/app/oracle/product/11.2.0/xe
# Set the Oracle database connection (data source, user, password)
ORACLE_DSN dbi:Oracle:host=localhost;sid=XE;port=1521
ORACLE_USER migrator
ORACLE_PWD test
# Set the Oracle schema/owner to use
SCHEMA MIGRATOR
Set the target PostgreSQL version in the following configuration:
# Set the PostgreSQL major version number of the target database. Ex: 9.6 or 10
# Default is the current major version released. This replaces the old PG_SUPPORTS_*
# configuration directives.
PG_VERSION 12
bEnable following configuration to migrate Oracle schema to PostgreSQL schema
# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA 1
Once configuration is done, test Ora2Pg to verify it’s working:
Estimation Report
Ora2Pg provides an estimation report feature, which provides an estimate of the cost of migration from Oracle to PostgreSQL. It must be executed before performing the actual migration.
This feature inspects all the Oracle database objects to verify if there are any database objects or PL/SQL code that cannot be converted to PostgreSQL-compatible scripts automatically. The report lists all relevant objects and PL/SQL code so that a user can change these manually.
Use the following command to generate an estimation report:
[edb@localhost scripts]$
[edb@localhost scripts]$ ./ora2pg -t SHOW_REPORT --estimate_cost -c /etc/ora2pg/ora2pg.conf
[========================>] 10/10 tables (100.0%) end of scanning.
[========================>] 10/10 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 11g Express Edition Release 11.2.0.2.0
Schema
Size 2.19 MB
-------------------------------------------------------------------------------
Object Number Invalid Estimated cost Comments Details
-------------------------------------------------------------------------------
DATABASE LINK 0 0 0.00 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE 0 0 0.00 Global temporary tables are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX 26 0 4.10 15 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain indexes are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. 15 b-tree index(es).
JOB 0 0 0.00 Job is not exported. You may set an external cron job with them.
PROCEDURE 2 0 8.00 Total size of procedure code: 772 bytes. hr.secure_dml: 3. hr.add_job_history: 3.
SEQUENCE 5 0 1.00 Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 0 0 0.00 SYNONYMs will be exported as views. SYNONYMs do not exist with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access objects outside the current schema.
TABLE 10 0 3.10 21 check constraint(s). Total number of rows: 215. Top 10 of tables sorted by number of rows:. hr.employees has 107 rows. hr.departments has 27 rows. hr.countries has 25 rows. hr.locations has 23 rows. hr.jobs has 19 rows. hr.job_history has 10 rows. hr.regions has 4 rows. migrator.employees has 0 rows. migrator.departments has 0 rows. migrator.test has 0 rows. Top 10 of largest tables:.
TRIGGER 2 0 5.00 Total size of trigger code: 123 bytes. update_job_history: 3.
VIEW 1 0 1.00 Views are fully supported but can use specific functions.
-------------------------------------------------------------------------------
Total 46 0 22.20 22.20 cost migration units means approximately 1 man-day(s). The migration unit was set to 5 minute(s)
-------------------------------------------------------------------------------
Migration level : A-3
-------------------------------------------------------------------------------
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 5 days
C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------
Details of cost assessment per function
Function hr.secure_dml total estimated cost: 3
TEST => 2
SIZE => 1
Function hr.add_job_history total estimated cost: 3
TEST => 2
SIZE => 1
-------------------------------------------------------------------------------
Details of cost assessment per trigger
Trigger update_job_history total estimated cost: 3
TEST => 2
SIZE => 1
-------------------------------------------------------------------------------
[edb@localhost scripts]$
Migration
To perform the migration, use the following command. Ora2Pg will connect with Oracle and convert the database objects to PostgreSQL-compatible objects and generate the SQL file.
An output.sql file is generated once the migration is successfully completed.
Connect to PostgreSQL and execute the output.sql:
Once the file is loaded, the migrated schemas are created in PostgreSQL.
How to convert schemas using Migration Portal and EDB Postgres Advanced Server
Here are the detailed steps for converting Oracle schemas to EDB Postgres Advanced Server using EDB Migration Portal.
Note : Create a user account and log into the migration portal for the below steps.
- Before extracting a schema, the latest EDB DDL Extractor script must first be downloaded from the Migration Portal projects page or from the link provided in the wiki portal of DDL Extractor guide. The script can be executed in the SQL Developer or SQL*Plus. It uses Oracle’s DBMS_METADATA built-in package to extract DDLs for the different objects under schemas. The EDB DDL Extractor creates the DDL file that will be uploaded to the portal and analyzed for EDB PostgreSQL compatibility.
To run on SQL*Plus use the following syntax:
SQL>@edb_ddl_extractor.sqlProvide the schema name and the path or directory where the Extractor will store the extracted DDL. When extracting multiple schemas, use a comma (‘,’) as a delimiter.
- To assess Oracle database schema for compatibility with EDB Postgres Advanced Server, create a new project by clicking on CREATE PROJECT icon.
Enter the following info on the page:- Name of the Project
- Select the Application Interface
- Select the Source DB and its version number.
- Select the Target DB and its version number.
- Click Choose file to upload the SQL file generated by the latest EDB DDL Extractor for Oracle Database. Click Create & assess to create a new project and to assess the schemas.
- The analysis tool will review every construct, execute repair actions to improve compatibility with EDB Postgres Advanced Server, and flag any remaining errors that require manual intervention.
- If there are any errors in the DDL due to compatibility issues with EDB Postgres Advanced Server, the following report will be produced.
- View the details of all the errors that occurred. At the bottom of the middle panel, you can view repair action details.
- On the Knowledge Base tab, search the error. The object detail panel displays the workaround or resolution for the failed object. You can manually make the changes on the Assessment tab for that object and click Reassess.
- After all the errors are resolved, you can choose one of the following options for migrating the schema:
- Existing on-premise EDB Postgres Advanced Server
- New on-premise EDB Postgres Advanced Server
- EDB Postgres Advanced Server on Cloud
- Here, we will choose the Existing on-premise EDB Postgres Advanced Server option. On the Select Schema screen, select the schema you need to migrate and click on the Next button.
- On the Dowload Schema Screen, click on the Download SQL File button to download the SQL File of the assessed schema. Then, click on the Next button.
- On the Import Screen, select the OS where the target EDB Postgres Advanced Server is running. In our case we will click on Linux. Use the listed steps to load the accessed schema file on the target EDB Postgres Advanced Server. Click on the Next button.
- On the Finish Screen, click on the Done button to complete the migration.
- After performing the schema migration, use the Migration Toolkit to migrate the data.
For more information on the EDB Migration Portal, visit the Migration Portal documentation.
How to migrate from Oracle to PostgreSQL using ora_migrator
To use ora_migrator, build the extensions ora_migrator and db_migrator from code downloaded from the ora_migrator Github page.
The oracle_fdw and db_migrator extensions are prerequisites, as ora_migrator is based on oracle_fdw.
Create a foreign server and user mapping for Oracle:
Grant the following permissions:
Call the function db_migrate to perform the migration from Oracle to PostgreSQL. The function takes the following parameters:
plugin: ora_migrator is provided to this parameter
server: the server created for Oracle
only_schemas: the schemas to migrate; if there are multiple schemas, then separate them with commas.
If the migration is successful then the status 0 is returned. After the successful migration, the schema and database objects are created in PostgreSQL.
If the migration fails, then the number of failures will be displayed with the corresponding migration errors.
