Create Database Links with Customer-Managed Heterogeneous Connectivity to Non-Oracle Databases on a Private Endpoint

You can create database links from an Autonomous AI Database on Dedicated Exadata Infrastructure to an Oracle Database Gateway to access Non-Oracle databases that are on a private endpoint.

An Oracle Database Gateway is a gateway that is designed for accessing a specific non-Oracle system. Using an Oracle Database Gateway, you can access data anywhere in a distributed database system without knowing either the location of the data or how it is stored. Using database links on Autonomous AI Database with Oracle Database Gateway supports heterogeneous environments and eliminates the need to customize your applications to access data from non-Oracle systems.

Note: Creating database links with customer-managed heterogeneous connectivity to non-Oracle databases on a private endpoint is supported only with versions 19.25 and above for 19c and 23.6 and above for 23ai.

Prerequisites

To use database links from an Autonomous AI Database instance on a public endpoint, do the following:

Configure the Oracle Database Gateway to access a non-Oracle database. See Oracle Database Gateways in Oracle Database 19c Database Heterogeneous Connectivity User’s Guide or Oracle Database 26ai Database Heterogeneous Connectivity User’s Guide for more details.

Depending on the database you want to connect to, you may refer to the corresponding Installation and Configuration Guide and the Gateway User's Guide.

For example, for Oracle Database Gateway for SQL Server see:
- Installing and Configuring Oracle Database Gateway for SQL Server in Oracle Database 19c Installation and Configuration Guide for Microsoft Windows or Oracle Database 26ai Installation and Configuration Guide for Microsoft Windows
- Introduction to the Oracle Database Gateway for SQL Server in Oracle Database 19c Gateway for SQL Server User’s Guide. or Oracle Database 26ai Gateway for SQL Server User’s Guide.
- Configure Oracle Net for the Gateway in Oracle Database 19c Installation and Configuration Guide for Microsoft Windows or Oracle Database 26ai Installation and Configuration Guide for Microsoft Windows.
Configure Oracle Net Listener to handle incoming requests on the Oracle Database Gateway.
Create a self signed wallet on the Oracle Database Gateway.

Additionally:

The target database must be accessible from the source database's Oracle Cloud Infrastructure VCN. For example, you can connect to the target database when:
- The target database is on a private endpoint.
- Both the source database and the target database are in the same Oracle Cloud Infrastructure VCN.
- The source database and the target database are in different Oracle Cloud Infrastructure VCNs that are paired.
- For a target on a private endpoint, DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK supports specifying a single hostname with the hostname parameter. On a private endpoint, using an IP address, SCAN IP, or a SCAN hostname is not supported (when the target is on a public endpoint, CREATE_DATABASE_LINK supports using an IP address, a SCAN IP, or a SCAN hostname).
The following ingress and egress rules must be defined for the private endpoint:
- Define an egress rule in the source database's subnet security list or network security group such that the traffic over TCP is allowed to the target database's IP address and port number.
- Define an ingress rule in the target database's subnet security list or network security group such that the traffic over TCP is allowed from the source database IP address to the destination port.

Customer-Managed Heterogeneous Connectivity to Non-Oracle Databases on a Private Endpoint (without a wallet)

You can create database links from an Autonomous AI Database to an Oracle Database Gateway to access Non-Oracle databases that are on a private endpoint either with a wallet (TCPS), or without a wallet (TLS). This section describes creating a database link without a wallet.

To create database links from an Autonomous AI Database instance on a private endpoint to a target gateway, using database links and a TLS connection, do the following:

On Autonomous AI Database, create credentials to access the target database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database used within the database link (where the target database is accessed through the Oracle Database Gateway).

For example:
```
 BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
         credential_name => 'DB_LINK_CRED',
         username => 'NICK',
         password => 'password'
     );
 END;/
```
Supplying the credential_name parameter is mandatory.

The characters in the username parameter must be all uppercase letters.

This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name.
Create the database link to the target gateway using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

For example:
```
 BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
         db_link_name =>       'SALESLINK',
         hostname =>           'example.com',
         port =>               '1522',
         service_name =>       'example_service_name',
         ssl_server_cert_dn => 'ssl_server_cert_dn',
         credential_name =>    'DB_LINK_CRED',
         directory_name =>      NULL,
         gateway_link =>        TRUE,
         private_target =>      TRUE,
         gateway_params =>      NULL
     );
 END;/
```
The ssl_server_cert_dn parameter is optional if the connection is created as a TCP based database link (without a wallet).

When the directory_name is NULL, the connection is created as a TCP based database link (without a wallet).

The private_target parameter must be set to TRUE when the target non-Oracle Database is on a private endpoint (that is, the database link accesses a hostname that needs to be resolved in a VCN DNS server). When private_target is TRUE, the hostname parameter must be a single hostname (on a private endpoint, using an IP address, a SCAN IP, or a SCAN hostname is not supported).

When gateway_link is TRUE and gateway_params is NULL, this specifies that the database link is to a customer-managed Oracle gateway.

Users other than ADMIN require privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

See CREATE_DATABASE_LINK Procedure for more details.
Use the database link you created to access data on the target gateway.

For example:
```
 SELECT * FROM employees@SALESLINK;
```

For the credentials you create in Step 1, the Oracle Database Gateway credentials, if the password of the target user changes you can update the credential that contains the target user’s credentials as follows:

BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL (
    credential_name =>'DB_LINK_CRED',
    attribute =>'PASSWORD',
    value=>'password'
  );
END;
/

Where password is the new password.

After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.

See UPDATE_CREDENTIAL Procedure for more details.

Customer-Managed Heterogeneous Connectivity to Non-Oracle Databases on a Private Endpoint (with a wallet)

To create database links from an Autonomous AI Database instance on a private endpoint to a target gateway, connecting with a wallet, do the following:

Copy the target gateway self signed wallet, for example, cwallet.sso, containing the certificates for the Oracle Database Gateway to Object Store.

The wallet file, along with the database user ID and password provide access to data available through the target gateway. Store wallet files in a secure location. Share wallet files only with authorized users.
On Autonomous AI Database, create credentials to access the Object Store where you store the cwallet.sso.

See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.
Create a directory on Autonomous AI Database for the wallet file cwallet.sso.

For example:
```
 CREATE DIRECTORY dblink_wallet_dir AS 'directory_path_of_your_choice';
```
See Create directory in Autonomous AI Database for information on creating directories.
Use DBMS_CLOUD.GET_OBJECT to upload the target gateway self signed wallet to the directory you created in the previous step, DBLINK_WALLET_DIR.

For example:
```
 BEGIN
     DBMS_CLOUD.GET_OBJECT(
         credential_name =>'DEF_CRED_NAME',
         object_uri =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
         directory_name =>'DBLINK_WALLET_DIR'
     );
 END;
 /
```
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

Note: The credential_name you use in this step is the credentials for the Object Store. In the next step you create the credentials to access the target gateway.
On Autonomous AI Database, create credentials to access the target database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database used within the database link (where the target database is accessed through the Oracle Database Gateway).

For example:
```
 BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
         credential_name =>'DB_LINK_CRED',
         username =>'NICK',
         password =>'password'
     );
 END;
 /
```
Supplying the credential_name parameter is mandatory.

The characters in the username parameter must be all uppercase letters.

This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name.
Create the database link to the target gateway using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

For example:
```
 BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
         db_link_name =>'SALESLINK',
         hostname =>'example.com',
         port =>'1522',
         service_name =>'example_service_name',
         ssl_server_cert_dn =>'ssl_server_cert_dn',
         credential_name =>'DB_LINK_CRED',
         directory_name =>'DBLINK_WALLET_DIR',
         gateway_link =>TRUE,
         private_target =>TRUE,
         gateway_params =>NULL
     );
 END;
 /
```
If directory_name is not NULL, a TCPS-based database link is created.

The private_target parameter must be set to TRUE when the target non-Oracle Database is on a private endpoint (that is, the database link accesses a hostname that needs to be resolved in a VCN DNS server). When private_target is TRUE, the hostname parameter must be a single hostname (on a private endpoint, using an IP address, a SCAN IP, or a SCAN hostname is not supported).

When gateway_link is TRUE and gateway_params is NULL, this specifies that the database link is to a customer-managed Oracle gateway.

Users other than ADMIN require privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

See CREATE_DATABASE_LINK Procedure for more details.
Use the database link you created to access data on the target gateway.

For example:
```
 SELECT * FROM employees@SALESLINK;
```

For the credentials you create in Step 5, the Oracle Database Gateway credentials, if the password of the target user changes you can update the credential that contains the target user’s credentials as follows:

BEGIN
    DBMS_CLOUD.UPDATE_CREDENTIAL (
        credential_name =>'DB_LINK_CRED',
        attribute =>'PASSWORD',
        value=>'password'
    );
END;
/