How to Migrate Oracle Database to PostgreSQL in 2 Ways?

Oracle database is the most favorite relational database management system globally that has been widely deployed by enterprises with large-scale environments. While PostgreSQL is an open-source object-relation database system and another popular attraction.

Ranked as the #4 attractive RDBMS, PostgreSQL is often considered an alternative to Oracle database for the following reasons:

Cost saving: cost-cutting becomes a crucial factor for small to mid-sized businesses, and PostgreSQL provides free service and relieves financial concerns.

Scalability: the open-source PostgreSQL has better scalability than the Oracle database, which costs high for scaling.

Compatibility: PostgreSQL works with a variety of operating systems that have complicated ecosystems. Because of its high degree of interoperability, developers can utilize it easily and in their own unique ways.

Security: you can get advanced but costly security packages in the Oracle database but PostgreSQL offers free role-based access control and security authentications like Host, LDAP, and PAM.

Technical support: the premier technical support is available 24/7 throughout the year from the Oracle product experts at a lower price than Oracle. It also has a large community.

Update frequency: Oracle releases major updates every 2 to 4 years while PostgreSQL delivers a significant upgrade along with bug fixes every 3 months.

Wide support: PostgreSQL has wide support for SQL conformance, JSON, and procedural languages like PL/pgSQL, PL/Perl, PL/Python, and DBaaS by cloud vendors, and it handles reams of data better.

Cloud: PostgreSQL is compatible with all clouds. However, with Oracle, you must either use on-demand licensing or bring your own license (BYOL).

PostgreSQL is definitely a go-to option for businesses that try to find a substitute for Oracle. But the Oracle to PostgreSQL migration can be hectic, here in this blog, I’ll touch on two free database migration tools and how they are used for database conversion.

Migrate Oracle to PostgreSQL with Ora2Pg

Ora2Pg is a free database migration tool for converting an Oracle database to a PostgreSQL-compatible schema. The tool establishes a connection to the Oracle database, performs an automatic scan to extract any data or structure, and then creates SQL scripts to import into the PostgreSQL database.

Requirements:

• The machine has Oracle Instant Client or a complete installation.

• Install the Perl module > 1.614, DBD::Oracle (needs Oracle client libraries and the defined ORACLE _HOME environment variable), and DBD::Pg (if you want to load export on the fly, otherwise transfer the data to a host with the psql client installed).

• May need the Time::HiRes Perl module on some distributions.

1.     Optionally install Perl modules.

Install Perl modules if the distribution hasn’t any with CPAN:

perl -MCPAN -e 'install DBD::Oracle'

perl -MCPAN -e 'install Time::HiRes'

perl -MCPAN -e 'install DBD::Pg'

perl -MCPAN -e 'install Compress::Zlib'

2.     Install the latest Ora2Pg via the following commands.

tar xjf ora2pg-x.x.tar.bz2

cd ora2pg-x.x/

perl Makefile.PL

make && make install

Before using Ora2Pg, add PERL5LIB to the path of your installation directory.

export PERL5LIB=<your_install_dir>

ora2pg -c config/ora2pg.conf -t TABLE -b outdir/

3.     Configure Ora2Pg.

ora2pg --project_base /usr/local/bin/ora2pg --init_project migv

(this will produce base folders, driver scripts, and a configuration file)

/usr/local/bin/ora2pg/migv

[root@xxxx# ls

config data export_schema.sh import_all.sh reports schema sources

4.     Change conf file.

# Connect Oracle database (data source, user, password)

ORACLE_DSN      dbi:Oracle:host=;sid=;port=

ORACLE_USER    

ORACLE_PWD     

# Oracle schema/owner to use

SCHEMA xxx

# To send exports to PostgreSQL databases directly, define the directive below. When done, file output will be disabled.

PG_DSN         dbi:Pg:dbname=;host=;port=

PG_USER        

PG_PWD       

5.     Verify the configuration.

# ora2pg -t SHOW_VERSION -c config/ora2pg.conf

Oracle Database xx Edition Release xx.x.x.x.x

6.     Produce a migration cost report.

# ora2pg -t show_report  --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/ora2pg.html

7.     Create DDL scripts with Oracle.

./export_schema.sh

8.     Produce DDL objects and load PostgreSQL’s data.

# ./import_all.sh

9.     Confirm the data.

ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt

Note: If EXPORT SCHEMA, SCHEMA, or PG SCHEMA are defined, it should be noted that this functionality respects the schema name restriction. Only EXPORT SCHEMA will be scanned if it is set, which will scan all Oracle and PostgreSQL schemes. 

While SCHEMA and/or PG SCHEMA allow you to filter to a single schema, a list of schema cannot be used as a filtering criterion. You will need to execute Ora2Pg repeatedly, supplying a different schema each time if you want to test a list of schema.

Migrate Oracle to PostgreSQL Manfully

1.     Install the PostgreSQL ODBC driver (in Linux here).

yum install postgresqlxx-odbc

2.     Produce a odbc.ini file in the user folder.

[ODBC Data Sources]

TARGET_PG_LINK = PostgreSQL

[TARGET_PG_LINK]

Debug =

CommLog =

ReadOnly = no

Driver = /usr/pgsql-xx/lib/psqlodbc.so

Servername = <PostgreSQL_SERVER_IP>

FetchBufferSize =

Username =

Password =

Port =

Database =

[Default]

Driver = /usr/lib64/liboplodbcS.so.1

3.     Set up heterogeneous services in Oracle. Go to the /hs/admin in the Oracle installation directory and create a file with the command.

HS_FDS_CONNECT_INFO = TARGET_PG_LINK

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME=/usr/pgsql-xx/lib/psqlodbc.so

set ODBCINI=/PATH_TO_ODBC_FILE/.odbc.ini

4.     Locate the tnsnames.ora file, and then enter information regarding the destination PostgreSQL instance.

TARGET_PG_LINK  =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=<ORACLE_HOSTNAME>)(PORT=<ORACLE_PORT>))

(CONNECT_DATA=(SID=TARGET_PG_LINK))

(HS=OK)

  )

5.     Locate the listener.ora file in the same directory and add the commands.

SID_LIST_<your_LISTENER_NAME>=

  (SID_LIST=

       (SID_DESC=

           (SID_NAME=TARGET_PG_LINK)

           (ORACLE_HOME=<ORACLE_HOME_DIRECTORY>)

           (PROGRAM=dg4odbc)

       )

  )

6.     Restart the Oracle listeners.

lsnrctl reload <your_LISTENER_NAME>

7.     Start the PostgreSQL and connect it.

Create database link TARGET_PG_LINK connect to "pg_user" identified by "pg_pass" using ‘TARGET_PG_LINK’;

8.     Execute the following queries in PostgreSQL.

select * from "target_table_name"@TARGET_PG_LINK;

Backup Oracle and PostgreSQL as a Precaution

Primary data failures can be the consequence of a hardware or software malfunction, data corruption, a malicious attack (virus or malware), data deletion by accident, or some other events. Likewise, the database migration process can also be a reason for data loss.

Therefore, database backup before and after the process is essential and necessary. Vinchin Backup & Recovery protects Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, NAS, Physical Servers, and 10+ virtualizations including VMware, XenServer, XCP-ng, Hyper-V, oVirt, RHV, Oracle OLVM, Sangfor HCI, OpenStack, etc. with ease-to-use and smart backup strategies like automatic backup, email notification, 50% of data reduction, various backup options (full, incremental, differential, log backup that vary with databases), and more.

You can also keep the data per restore point/day for simplified data backup management and restore the backups to the original or a new destination.

Try the 60-day free trial of the Enterprise version now if you’re looking for a backup solution.

Here is an example of database backup with Vinchin Backup & Recovery.

1.     Navigate to Database Backup> Backup, and select the licensed database.

2.     Specify the target node and storage for the database backup.

3.     Configure backup strategies.

General Strategy:

Mode: choose to backup as scheduled or as a once-off job.

Schedule: choose the backup types and runtime.

Speed Controller: add speed limits to the job for less consumption on the network.

Data Storage Policy: deduplicate and compress data to save space.

Retention Policy: reserve the data per restore point/day to auto-manage it.

Transmission Strategy:

Encrypted Transmission: encrypt the path for safe data transfer during backup.

Transfer via: LAN by default.

Advanced Strategy:

change with databases.

4.     Check the settings and hit Summit.

Conclusion:

Oracle database users may turn to PostgreSQL due to cost, compatibility, or other advanced functions. They can use the free data migration tool Ora2Pg to perform a migration assessment and data conversion, or the Orafce to port applications from Oracle to PostgreSQL. There is also a manual way to migrate Oracle to PostgreSQL (see PostgreSQL to Oracle migration).

Keep in mind that database backup is indispensable. Vinchin Backup & Recovery backs up, restores Oracle and PostgreSQL easily and efficiently, and manages their backups in a heterogeneous environment for enterprises.