Migrating Schema from Oracle to YugabyteDB using HexaRocket

PostgreSQL is a very popular open-source relational database system used widely across industries. Over time, it has inspired several forks and distributed databases that either extend PostgreSQL or maintain full compatibility with it. One of the most notable examples is YugabyteDB, a distributed SQL database designed to deliver high availability, horizontal scalability, and strong consistency - ideal for both OLTP and OLAP workloads. With PostgreSQL and YugabyteDB being a popular migration target for Oracle databases, we were curios to see if our database migration tool: HexaRocket works seamlessly. We choose to migrate an Oracle schema to YugabyteDB, and our aim was to migrate as if we are migrating to PostgreSQL. You can see our observations in this article.

Are Enterprises moving from Oracle to Open-Source ?

Oracle is a popular database for massive critical workloads and supports OLTP and OLAP workloads efficiently. While it is a commercial database, users may find it challenging to scale if it also scales up the cost of Infrastructure due to enterprise licensing. This is when users look for migrating to open-source databases like PostgreSQL or Postgres compatible databases like YugabyteDB.

Please visit HexaRocket at www.hexarocket.com

What is YugabyteDB?

YugabyteDB is an open-source, distributed SQL database built for global, internet-scale applications. It combines the best of both worlds - the flexibility and familiarity of PostgreSQL with the scalability and fault-tolerance of a distributed NoSQL system.

At its core, YugabyteDB is designed for cloud-native architectures, supporting:

• Automatic sharding and rebalancing

• Strong consistency and fault tolerance

• Multi-region and multi-cloud deployments

• ACID transactions across distributed clusters

Yugabyte’s storage engine, DocDB, ensures transactional consistency, while its YSQL API provides full PostgreSQL compatibility - making it a perfect candidate for Oracle to Yugabyte migration.

For a detailed overview, visit YugabyteDB Official Documentation.

PostgreSQL Compatibility

One of YugabyteDB’s biggest advantages is its deep PostgreSQL compatibility. Its YSQL layer is wire-compatible and syntax-compatible with PostgreSQL, which means:

• Most PostgreSQL drivers, tools, and ORM frameworks (like Hibernate, SQLAlchemy, and Django ORM) work seamlessly with YugabyteDB.

• SQL functions, DDL statements, and PL/pgSQL procedures can often be used without modification.

• Applications that connect to PostgreSQL can connect to YugabyteDB using the same JDBC or libpq interfaces.

For detailed PostgreSQL compatibility notes, visit Yugabyte YSQL Compatibility Reference.

This makes migration from Oracle to YugabyteDB easier using tools already optimized for PostgreSQL compatibility - such as HexaRocket and YugabyteDB Voyager.

Migrating from Oracle to YugabyteDB

Oracle Database has long been the go-to choice for enterprise-scale transactional and analytical workloads. However, its licensing costs, scaling limitations, and vendor lock-in have led many organizations to explore open-source alternatives like PostgreSQL and PostgreSQL-compatible distributed databases such as YugabyteDB.

When choosing a migration approach, automation and accuracy are key. This is where HexaRocket plays an important role.

Tools Available to Simplify Migrations to YugabyteDB

We will limit this article to the 2 popular tools supporting end-to-end database migrations from Oracle to YugaByteDB.

1. Yugabyte Voyager

As proud creators and contributors of Ora2Pg, we are happy that Yugabyte Voyager is created on top of Ora2Pg. At the same time, Yugabyte team has put in a lot of efforts to provide the Voyager with several additional capabilities to simplify Online migrations and migrations from PostgreSQL to Yugabyte and logical replication from Yugabyte to other data sources.

2. HexaRocket

HexaRocket is a unified database migration and replication engine developed by HexaCluster to support multiple migration paths including -

• Oracle → PostgreSQL

• SQL Server → PostgreSQL

• MySQL → PostgreSQL

• MariaDB → PostgreSQL

• SQL Server → BigQuery

Additionally, HexaRocket supports CDC (real-time replication) across -

• Oracle → PostgreSQL

• PostgreSQL → Oracle

• Oracle → Oracle

• MySQL → PostgreSQL

• SQL Server → PostgreSQL

While HexaRocket was not built on top of Ora2Pg, we have incorporated a lot of lessons from Ora2Pg. Especially for code conversions from Oracle PL/SQL to PostgreSQL PL/pgSQL, we have designed highly effective code parsers and workflows with validations to support near to 100% automatic schema conversions.

Since Yugabyte is PostgreSQL-compatible, HexaRocket connects to it just like a standard PostgreSQL database. Our tests confirmed a 100% success rate using HexaRocket for schema migration from Oracle to Yugabyte in the first attempt - without any code modifications or manual adjustments.

What did we consider for our Testing?

Our aim was to test schema migration involving Tables of almost all data types supported by Oracle, and some of the code objects like Stored Procedures, Functions and also Views.

Overall we have included 40 objects that cover a good amount of complexity.

• Tables: 14
• Constraints and Indexes: 12
• Sequences: 2
• User Defined Types: 3
• Views: 6
• Stored Procedures: 5
• Functions: 10

Results

The result was expected to 100% from our confidence that YugaByteDB is 100% PostgreSQL comaptible. As expected, we see all objects migrated and also deployed to YugabyteDB without any adjustments.

You can find the Schema Migration Summary of HexaRocket below.

 class="language-sql">
[yugabyte@yugabytedb$]bin/ysqlsh -h yugabyte_server -p 5439 -U yugabyte -d yugabyte
ysqlsh (15.12-YB-2025.1.0.1-b0)
Type "help" for help.
yugabyte=# \dn genericmini_cdc
      List of schemas
      Name       |  Owner
-----------------+----------
genericmini_cdc | yugabyte
(1 row)

yugabyte=# \dt genericmini_cdc.*
                         List of relations
     Schema      |             Name              | Type  |  Owner
-----------------+-------------------------------+-------+----------
genericmini_cdc | blob_data                     | table | yugabyte
genericmini_cdc | blob_data_clone               | table | yugabyte
genericmini_cdc | blob_data_oracle              | table | yugabyte
genericmini_cdc | boolean_non_boolean           | table | yugabyte
genericmini_cdc | boolean_non_boolean_no_pk     | table | yugabyte
genericmini_cdc | bytea_raw_with_pk             | table | yugabyte
genericmini_cdc | bytea_raw_with_pk_not_null    | table | yugabyte
genericmini_cdc | bytea_raw_without_pk          | table | yugabyte
genericmini_cdc | bytea_raw_without_pk_not_null | table | yugabyte
genericmini_cdc | character_data                | table | yugabyte
genericmini_cdc | character_data_07             | table | yugabyte
genericmini_cdc | clob_data                     | table | yugabyte
genericmini_cdc | datetime_and_intervals        | table | yugabyte
genericmini_cdc | number_combinations           | table | yugabyte
(14 rows)

yugabyte=# \dv genericmini_cdc.*
                         List of relations
     Schema      |              Name              | Type |  Owner
-----------------+--------------------------------+------+----------
genericmini_cdc | blob_data_hexa                 | view | yugabyte
genericmini_cdc | boolean_non_boolean_no_pk_hexa | view | yugabyte
genericmini_cdc | character_data_hexa            | view | yugabyte
genericmini_cdc | clob_data_hexa                 | view | yugabyte
genericmini_cdc | datetime_and_intervals_hexa    | view | yugabyte
genericmini_cdc | number_combinations_hexa       | view | yugabyte
(6 rows)

yugabyte=# select pp.proname from pg_proc pp
join pg_namespace pn on pn.oid=pp.pronamespace where pn.nspname = 'genericmini_cdc';
           proname
-----------------------------
add_semesters_to_date
binary_float_subtype
bitwise_and_operation
boolean_to_string
calculate_bonus_percentage
calculate_discounted_price
check_employee_status
compare_binary_float_values
create_invoice_line
fetch_limit_rows
generate_random_integers
get_membership_level
initialize_test_scores
is_binary_float
last_day_leap_year
(15 rows)

Want to try HexaRocket? Contact Us today.

Datatype Mapping Between Oracle and Yugabyte

Now, let us take a deeper look at the datatype mapping between Oracle and Yugabyte. Please note that the Yugabyte official documentation states that some of the datatypes are still being adopted. In case of any typos made or mistakes done during this draft, please notify us and we could correct the same.

Reference: Data type mapping from Oracle to YugabyteDB

Conclusion

HexaRocket not only simplifies database migrations to PostgreSQL, but also to PostgreSQL Compatible databases. Be it PostgreSQL or YugabyteDB, you can simply migrate your Oracle, and other databases to YugabyteDB seamlessly.

In our next article, we will talk about our observations on data migration and CDC from Oracle to Yugabyte. And then, we shall present you with some interesting facts about SQL Server, MySQL and MariaDB to YugabyteDB. This is because, HexaRocket supports end-to-end database migrations from SQL Server, MySQL and MariaDB to PostgreSQL.

Explore our services at Database migrations to PostgreSQL.

The authors of this article are also the creators of HexaRocket schema migration engine that simplifies schema migration from any database to any database. Your inputs and feedback is greatly valuable and it directly reaches the authors.

Subscribe to our Newsletters and Stay tuned for more interesting topics.