AWS Database Blog
Heterogeneous data sources: Access your data in PostgreSQL from Amazon RDS for Oracle using Oracle Database Gateway
Amazon Relational Database Service (Amazon RDS) for Oracle is a comprehensive, fully managed commercial database service. It simplifies the setup, operation, and scalability of Oracle deployments in the cloud. By handling critical database administration tasks such as provisioning, backups, software patching, monitoring, and hardware scaling, Amazon RDS allows you to concentrate on innovation and application development.
In certain customer scenarios, Amazon RDS for Oracle databases need to connect to external data sources, such as RDS for PostgreSQL. For instance, a customer might want to query PostgreSQL data using a database link from Oracle. This requirement often arises when different applications use distinct databases and there is a data dependency between the applications.
PostgreSQL can establish connections to Oracle databases using a foreign data wrapper (FDW). However, when connecting from RDS for Oracle to PostgreSQL, you need an Oracle Database Gateway for ODBC (DG4ODBC) installed on a separate server such as an Amazon Elastic Compute Cloud (Amazon EC2) instance, because you don’t have access to the underlying operating system of RDS for Oracle. According to Oracle documentation, “Oracle Database Gateway for ODBC can be installed and used on a machine different from the machine where the Oracle Database is installed and used. It is not necessary to obtain a separate license for the machine running Oracle Gateway for ODBC.”
In this post, we walk you through setting up an EC2 instance as a database gateway server. You will install and configure Oracle Database Gateway for ODBC (DG4ODBC), ODBC drivers, a PostgreSQL client, and PostgreSQL libraries. With this setup, you can create database links on RDS for Oracle to connect to PostgreSQL through this gateway.
If you’re using RDS Custom for Oracle, see Using an Oracle Database Gateway to connect Amazon RDS Custom for Oracle to PostgreSQL.
Solution overview
The following diagram provides an overview of the solution. In RDS for Oracle, when a user needs to access data from RDS for PostgreSQL, the solution uses a database link pointing to Oracle Database Gateway for ODBC (DG4ODBC), which uses ODBC drivers and PostgreSQL client libraries to connect to RDS for PostgreSQL and retrieves the data.
- When a user connects to RDS for Oracle and queries the RDS for PostgreSQL target database, the query is sent to the heterogeneous services (HS) component of the Oracle database gateway over the database link (created for RDS for PostgreSQL).
- Upon receiving the request, the DG4ODBC parses the /etc/odbc.ini config file (which has the necessary PostgreSQL connection information and libraries) and retrieves the drivers needed for the connection.
- The connection is then established to the PostgreSQL database using the PostgreSQL ODBC drivers and PostgreSQL client libraries.
Prerequisites
For RDS for Oracle databases to connect to external data sources, you need to install Oracle Database Gateway on an EC2 instance along with the required ODBC drivers. The following are the prerequisites for this solution:
- For the sake of simplicity, we are using the same virtual private cloud (VPC) to create the services that follow. Also, we are using the same subnet to minimize unexpected network errors. If you need to run the services in different VPCs or different subnets, you can use these scenarios.
- Create an EC2 instance with Linux x86-64 OS (check the certified OS version for Oracle Database Gateway from My Oracle Support Document 2617521.1 – Certification Matrix for Oracle Database Gateway Products versions 11.2.x and greater), which will be used to install and run Oracle Database Gateway for ODBC. See Tutorial: Get started with Amazon EC2 Linux instances for guidance on how to launch an EC2 instance. In this post, we’re using the latest AMI for RHEL 8 at the time of writing.
- Make sure the EC2 instance meets the minimum hardware and software requirements to run Oracle Database Gateway for ODBC.
- Create an RDS for PostgreSQL instance. In this post, we’re using version 15.4, which was the latest version at the time of writing.
- Create an RDS for Oracle instance. In this post, we’re using version 19c of RDS for Oracle.
- Update the Oracle Database Gateway for ODBC EC2 instance’s security group to allow inbound traffic from the RDS for Oracle instance. In this post, we’re using the default listener on Oracle Database Gateway server, which runs on port 1521. In this case, an inbound rule allowing traffic on port 1521 from RDS for Oracle instance was added.
- Update the RDS for PostgreSQL security group to allow inbound traffic from the Oracle Database Gateway for ODBC EC2 instance. By default, RDS for PostgreSQL accepts connections on port 5432. For this solution, we added an inbound rule allowing traffic on port 5432 from Oracle Database Gateway for ODBC EC2 instance.
Download the Oracle Database Gateway software
Prepare wget.sh script to download the Oracle Database Gateway software.
- Go to the Oracle Software Delivery Cloud and sign in by using Oracle credentials.
- Search for Oracle Database Gateways and select 19.3 or higher release of the product. For this post, we selected 19.3.
- Choose Continue in the top right corner to view the download queue.
- For Platform, select Linux x86-64 and choose Continue.
- Review and accept Oracle Standard Terms and Restrictions and choose Continue.
- On the next screen, choose the link for WGET Options, and then choose Download .sh. This will download the
wget.sh
file to your computer.
Install Oracle Database Gateway on Amazon EC2
- Connect to your Oracle database Gateway Server EC2 instance using AWS Systems Manager Session Manager.
- Connect to ec2-user.
- Create the sh file with the content from
wget.sh
file generated in the previous procedure. - Run
wget.sh
to download the Oracle Database Gateways 19c software. It will downloadV982066-01.zip
. You will need to provide your My Oracle Support credentials. - Configure swap space (required for Oracle Database Gateway installation).
The following example creates swap space of 1GB (count=1024) which is the minimum requirement for Oracle Database Gateway installation. - Install the required packages and unzip the install file.
- Find the default response file named
tg.rsp
in the/home/ec2-user/gateways/response
folder. Crosscheck the presence of the parameters and update the following parameters within the file.UNIX_GROUP_NAME
INVENTORY_LOCATION
ORACLE_HOME
ORACLE_BASE
install.tg.customComponents
- Set the environment variables and crosscheck the values of the environment variables.
- Update
.bash_profile
with the same environment variables to initialize them whenever the ec2-user logs in. - Run the installer.
- If runInstaller fails, clean up the following locations and retry the installation.
- In this example, as indicated by the output, installation of Oracle Database Gateways was successful. Run
orainstRoot.sh
androot.sh
as the root user to complete the post-installation requirements. - It’s recommended to apply latest database release update on your Oracle Database Gateway installation. At the time of writing this post, Database Release Update 19.24.0.0.0 was the latest database release update available.
Install ODBC drivers, PostgreSQL client and libraries
- Install the Unix ODBC Driver Manager.
- Install the latest PostgreSQL repository RPM on the EC2 instance that’s hosting the gateway server.
- Disable the built-in PostgreSQL module.
- Install the latest PostgreSQL client and libraries.
Note: This will also install postgresql15-libs.
- Install the latest PostgreSQL ODBC drivers.
- Define the PostgreSQL ODBC data source by configuring ODBC parameters in
/etc/odbc.ini
.
This example uses RDS for PostgreSQL as a data source. Values for Database, Servername, and Port need to be set to appropriate values to connect to your PostgreSQL instance. The following is an example of/etc/odbc.ini
configured to provide those values. - Use wget to download the RDS certificate bundle to connect to a PostgreSQL instance over SSL.
- Add an ingress rule to the RDS for PostgreSQL security group to allow traffic from the EC2 instance running the Oracle ODBC gateway security group.
- Test the PostgreSQL connection by exporting the following parameter (update
.bash_profile
with the parameter for permanency), connect to the PostgreSQL database using isql and execute the SQL command to query the database name with isql. - A successful connection will look like the following:
Run a test select statement as below:
Possible errors
- Errors like either of the following examples mean that there are issues with either the SSL configuration or the certificate.
Example 1:
Example 2:
- Errors like the following mean that the connection is timing out and the security group isn’t allowing connections from the EC2 instance running the Oracle ODBC gateway server to PostgreSQL.
Example 1:
Example 2:
- Errors like the following mean that the username or password specified in /etc/odbc.ini is incorrect.
Example 1:
Example 2:
Configure Oracle Net for the gateway
- Create an initialization file for your Oracle Database Gateway for ODBC using
<Oracle Database Gateway Home>/hs/admin/init<sid>.ora
. Here<sid>
is the Oracle system identifier used for the gateway. - Configure
<Oracle Gateway Home>/network/admin/listener.ora
with the following configuration including the certificate environmentPGSSLROOTCERT=/home/ec2-user/global-bundle
. - Start the listener.
Create a database link in RDS for Oracle and test the connection to RDS PostgreSQL
- Connect to your RDS for Oracle instance as the master user using tools such as SQL* Plus or SQL Developer.
- Create a database link to connect to the ODBC connection for PostgreSQL in RDS for Oracle. Replace username, password, HOST, PORT and SID to match your environment.
- Run the following SQL query to test the connection.
A successful connection will show the databases present in the RDS for PostgreSQL instance, as shown in the following screenshot of SQL Developer.
Clean up
After testing is complete and if the Oracle Database Gateway EC2 instance is no longer required, you can terminate the instance. Also, if the RDS instances are no longer required, you can delete them.
Conclusion
In this post, we showed you how to configure a secure (SSL/TLS) database connection between an RDS for Oracle instance and an RDS for PostgreSQL instance using an Oracle Database Gateway and ODBC drivers. For more information about Amazon RDS for Oracle, see Working with RDS for Oracle. For more information about RDS for PostgreSQL, see Working with RDS for PostgreSQL.
About the Authors
Jennifer Kwon is a senior Database Product Owner of several of the Oracle Database Gateway services at the Boeing Company. In the past, she managed several database management systems and connectivity between them, including Oracle, SQL Server, Teradata, Hana, DB2 and IMS. In the past few years, as the focus of technology has shifted toward the cloud and open source, she has brought practical experience and expertise to enable successful cloud implementation of modern data stores and legacy databases. Outside of work, she finds joy in developing wildlife artworks with a story in them and exhibiting her work with other artists to share her view of nature with peace and harmony.
Arnab Chowdhury is a Specialist Solutions Architect with Amazon Web Services specializing in databases. Arnab works with customers in the North American automotive and manufacturing industries, guiding them through the process of optimizing and migrating their database workloads to AWS.
Vishal Patil is a Senior Worldwide Specialist Solutions Architect at Amazon Web Services. In his role, he specializes in assisting organizations with the migration and optimization of their Oracle workloads to AWS.