One of the challenges organizations face when looking to modernize their Oracle database estates is the degree of interoperability between databases, which often manifests as large numbers of database links between these databases. The dilemma here is how to unravel this spiderweb of interconnectivity; it’s very rare that you can migrate all the databases at the same time in a big bang approach, which leads to the need for cross-engine database connectivity.
The Oracle Foreign Data Wrapper (oracle_fdw) has been available as a production-ready extension to the community version of PostgreSQL since April 8, 2014. This extension allows a PostgreSQL database to connect directly with an Oracle database—much like database links do in Oracle databases. As of July 9, 2021 this popular extension has been available in Amazon Relational Database Service (Amazon RDS) for PostgreSQL and as of August 20, 2021 this extension has been available in Amazon Aurora PostgreSQL-Compatible Edition. The extension allows you to connect AWS managed PostgreSQL databases to Oracle databases regardless of where they’re running. You can use the extension with Oracle databases running on-premises, in Amazon Elastic Compute Cloud (Amazon EC2), or Amazon Relational Database Service (Amazon RDS) for Oracle,
In this post, I show you how to configure and utilize oracle_fdw in Amazon RDS for PostgreSQL to allow you to progressively migrate your Oracle databases into PostgreSQL on AWS regardless of whether or not they’re using Oracle database links.
Considerations
Before we start, we must take into account a few important considerations when planning a staggered database migration strategy: network latency and network bandwidth.
Network latency
Network latency is the time taken by the data to travel across a network and back to the source. We measure the round trip under the network latency because computers don’t initiate a new TCP connection until the previous one is complete, and so until the requested data arrives, the database waits before creating a new request.
Although the speed of data transmission is high (similar to the speed of light), many hurdles can contribute to the network latency time. The biggest single contributors are distance and bottlenecks, simply put the greater the distance between the source and destination and the more switches, routers and firewalls encountered along the route the greater the network latency will be.
Network bandwidth
Although it may at times appear that there is a proliferation of database links and interconnectivity, they have all generally been created to address a specific business need, and it’s important to understand that business need as part of the migration planning process.
One particular area to consider is the volume of traffic that is transmitted over the link and potential impact on end-user applications if you have to transmit a high volume of data over a slow network connection. For this reason, databases that transmit large volumes of data between themselves over database links should be considered as a single unit and migrated at the same time.
Overview of solution
Now that we have planned our migration and identified an Oracle database that uses database links that we want to migrate to Amazon RDS for PostgreSQL, we must make a few simple modifications to the new RDS for PostgreSQL database to facilitate this requirement.
All of the examples shown in this post are based on the simple solution architecture that is provided in the following AWS CloudFormation template, PG-Oracle-FDW.json.
The bastion host and RDS for PostgreSQL environments are created using the Free Tier configuration, and the Oracle XE host is configured to use the free Oracle XE database. Therefore, we have no database license requirements to run this; however, the minimum requirement of 1 GB RAM precludes the use of the Free Tier, so this template defaults to a small EC2 host.
The following diagram illustrates this architecture.
Build the environments
All examples in this post use an Oracle XE 18c pluggable database and RDS for PostgreSQL 12 database that are created from the CloudFormation template provided. I use Oracle XE on an EC2 host rather than an RDS for Oracle database so that I can take advantage of the free-to-use Oracle XE database, which doesn’t require any Oracle licenses.
The build of this CloudFormation template takes approximately 45 minutes to complete, with the database host taking the bulk of that time. You can check the progress by connecting to your Linux EC2 hosts using SSH, as ec2-user, and monitoring the log file, Install-Log.txt, which is located in the /tmp subdirectory.
Although you’re not required to connect to the database host to complete this brief tutorial, you can connect to the database host from the bastion host by copying your .pem key into the SSH subdirectory on the bastion host. You can do this either at build time by providing an Amazon Simple Storage Service (Amazon S3) location for the .pem key to the CloudFormation template, or copy it to the bastion host from your laptop after the build of the bastion host is complete.
Connect to the bastion host
All the commands shown in this post are issued from the bastion host, and again, there is no requirement to connect to the database host. If you’re using your own Oracle database, you can simply change the connection credentials to that database.
To connect to the bastion host, you first need to obtain its public IP address. This is provided as an output parameter in the CloudFormation template, or you can locate it on the Amazon EC2 console.
When you have the public IP address, connect using a string similar to the one shown in the following code, and make sure you connect as the Oracle user to take advantage of the environment that has been set up:
When you’re connected, make sure that you can log in to both the Oracle and PostgreSQL databases.
The connection details for the Oracle database are set up in the tnsnames.ora file during the build process, and you can obtain the connection details for the PostgreSQL database from the output parameter in the CloudFormation template, or on the Amazon RDS console.
If either of these fails, the build process either wasn’t completed, failed, or the security group information wasn’t correctly provided. You must address this before continuing.
Common Failure Reasons
Invalid Password
Remember that passwords are case sensitive, this is the password that you used when you created the database through the Cloud Formation Template
Invalid Hostname
There are two ways to check that the hostname is correct;
Check the entry in the Managed Relational Database Services section in the AWS Console
Check the outputs for the Cloud Formation Template, the PostgreSQL DNS name is held in the PostgreSQLEndpointAddress key.
Connection Timeout
If you issue the command as shown above and nothing happens initially and the connection eventually times out, then the most likely cause is that the database security group you specified during the Cloud Formation Template creation did not allow connectivity from the Bastian host through to the database host via port 5432.
This is checked by going to the entry in the Managed Relational Database Services section in the AWS Console where the VPC security groups are shown, there should be two, click on each in turn and you will be taken to the security groups section where you can check the inbound rules. There should be a rule in here to allow port 5432 access between you Bastian host and the RDS instance. This may show up as a rule type of PostgreSQL.
Build sample tables
To run this demo, you need to create the following tables in your Oracle and PostgreSQL databases:
oracle_fdw_local_parent
oracle_fdw_local_child
postgres_fdw_local_parent
postgres_fdw_local_child
Set up Oracle
Log in to the Oracle database from your bastion host, using the credentials provided when you built the environment, and create the two tables for testing, oracle_fdw_local_parent and oracle_fdw_local_child:
Then create some test data by running the following script:
Set up PostgreSQL
Log in to the PostgreSQL database from your bastion host using the credentials provided when you built the environment and create the two tables for testing, postgres_fdw_local_parent and postgres_fdw_local_child:
Then create some test data by running the following script:
Enable oracle_fdw
Before you can use oracle_fdw, you have to enable it in the database, which is as straightforward as creating the oracle_fdw extension:
Create a connection from PostgreSQL to Oracle
After you enable the extension, you need to tell PostgreSQL how to connect to the Oracle database. This is similar to creating a database link in Oracle.
You create the connection with two sets of commands.
The first defines the connect string to the Oracle database. For this demo, the Oracle host DNS address is the private IPv4 DNS obtained from the output parameter in the CloudFormation template or from the Amazon EC2 console.
A useful strategy is to initiate an SQL*Plus string to the database from the bastion host to make sure that the connect string is valid. See the following code:
Note: If the DNS Service is not fully configured in your environment you may need to use the IP Address of the Oracle host in place of the DNS Address
The next command defines the connection credentials. Make sure you use the correct password encapsulated with quotes—this has to be an Oracle user with the necessary read, write and delete permissions on the base tables that will be accessed.:
Create remote tables
In Oracle, you use a synonym or a view to access a remote table over the database link. PostgreSQL is slightly different in that we define a foreign table to access the remote data with the following code. We use OPTIONS (KEY ‘true’) in the definition to denote the primary key. This allows updates and deletes to be performed through the foreign table.
Test the links
To test the links, first select Oracle data from the Oracle database:
Then select PostgreSQL data from the PostgreSQL database:
Next, select Oracle data from the PostgreSQL database:
Now update, insert, and delete a row in the Oracle database from PostgreSQL:
Check the results in Oracle from PostgreSQL:
Finally, check the Oracle data from the Oracle database:
Clean up
To avoid incurring future charges, delete the resources used for this solution. You can do this by removing the CloudFormation template from your account.
Conclusion
In this post, we discussed some key considerations regarding the use of inter-database connectivity through the use of Oracle database links and the PostgreSQL Foreign Data Wrapper, oracle_fdw.
We walked you through the process of configuring your RDS for PostgreSQL database to facilitate the use of selects, inserts, updates, and deletes across that Foreign Data Wrapper, which allows you to accelerate your database modernization journey even in scenarios where cross-database communication is required for extended periods of time.
If you have any questions or comments about this post, please use the comments section.
About the Author
Mike Revitt is a Database Migration Specialist working in the Database Freedom team at AWS, with over 30 years of experience in database and application migration, modernization, design, development, and integration.
Read MoreAWS Database Blog
