Creating DB Links in Oracle AWS RDS: A Comprehensive Guide

Creating Database Links in Oracle AWS RDS: A Comprehensive Guide

In today's interconnected data landscape, the ability to seamlessly access data across different databases is crucial. Oracle Database Links provide this capability, allowing you to query and manipulate data residing in remote Oracle databases as if they were local tables. This article will guide you through the process of creating database links in Oracle AWS RDS, focusing on accessibility for both public and current users.

Introduction

As a database professional, I often encounter scenarios where accessing data across different Oracle databases is necessary. Oracle Database Links help me to avoid cumbersome data transfers and maintain data consistency by enabling real-time access to remote data. In this guide, I'll share my knowledge and provide practical examples to help you set up database links in your Oracle AWS RDS environment.

Scenario

Imagine you have an application running on an Oracle Autonomous DB in the cloud. This application needs to access product information stored in a legacy Oracle database hosted on AWS RDS. Instead of migrating the data, you can create a database link in the Autonomous DB to access the product data directly from the RDS instance. This approach minimizes downtime, reduces storage costs, and ensures that your application always has access to the latest product information.

How to Setup a Database Link in Oracle AWS RDS

Below are the steps to setup database link in Oracle AWS RDS.

1. Gather Connection Information: You'll need the following information for the remote database:

   • Hostname or IP address of the RDS instance.
   • Port number (typically 1521).
   • Service name or SID of the database.
   • Username and password for a database user with appropriate privileges.

2. Create the Database Link: Connect to your Oracle database (e.g., using SQL Developer or SQL*Plus) and use the CREATE DATABASE LINK statement. Here's a basic example:

   CREATE DATABASE LINK rds_link
   CONNECT TO remote_user IDENTIFIED BY "password"
   USING '(DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = rds_endpoint)(PORT = 1521))
         (CONNECT_DATA =
         (SERVICE_NAME = orcl)
       )
     )';
   
   

   Replace rds_link, remote_user, password, rds_endpoint, and orcl with your actual values.

3. Test the Database Link: Verify the connection by querying a table in the remote database:

   SELECT * FROM products@rds_link;
   

   If the query executes successfully, your database link is working!

How to Setup db link for Public

To make the database link accessible to all users, you can create a public synonym. This allows any user to access the remote data without needing to know the database link name.

CREATE PUBLIC SYNONYM products_public FOR products@rds_link;

Now, any user can query the remote products table using:

SELECT * FROM products_public;

If you want to grant access to the database link to specific users only, you can grant them the CREATE SESSION privilege on the remote database and then create a private synonym for each user.

1. Grant CREATE SESSION Privilege: Connect to the remote database as an administrator and grant the CREATE SESSION privilege to the desired user:

   GRANT CREATE SESSION TO user1;
   

2. Create Private Synonym: Connect to the user's schema (e.g., as user1) and create a private synonym:

   CREATE SYNONYM products_private FOR products@rds_link;
   

   Now, only user1 can access the remote products table using:

   SELECT * FROM products_private;
   

Grants and Privileges Required to Access DB Link

To ensure secure and controlled access to the database link, the following grants and privileges are essential:

On the Remote Database:

On the Local Database:

No specific grants are required on the local database for the database link to function. However, users who need to access the database link need the privilege to select from specific tables/views

Usage & Examples

Let's illustrate with a practical example. Assume you have a table named Customers in the remote database.

Querying Data:

SELECT * FROM Customers@rds_link WHERE customer_id = 123;

Joining Tables:

SELECT o.order_id, c.customer_name
FROM Orders o
JOIN Customers@rds_link c ON o.customer_id = c.customer_id;

Inserting Data:

INSERT INTO Customers@rds_link (customer_id, customer_name) VALUES (456, 'New Customer');

Conclusion

Creating and managing database links in Oracle AWS RDS is a straightforward process that can significantly improve data accessibility and integration. By understanding the different access options (public vs. private) and the necessary grants and privileges, you can ensure secure and efficient data access across your Oracle environments. This approach avoids cumbersome data transfers and maintains data consistency by enabling real-time access to remote data.