Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint

You can create database links from an Autonomous Database to a target Oracle database that is on a private endpoint.

Note:

Database links from an Autonomous Database to a target Oracle database that is on a private endpoint are only supported in commercial regions and US Government regions. This feature is enabled by default in all commercial regions.

If you want to create database links from an Autonomous Database to a target in a US Government region, please file a Service Request at Oracle Cloud Support and request to enable the private endpoint in government regions database linking feature. This includes the following US Government regions:

• Oracle Cloud Infrastructure US Government Cloud with FedRAMP Authorization
• Oracle Cloud Infrastructure US Federal Cloud with DISA Impact Level 5 Authorization

Depending on the type and the configuration of the target Oracle database:

• Autonomous Database or another Oracle Database, such as on-premises or a Database Cloud Service database, on a private endpoint that is configured for SSL (TCPS): In this case you create the database link with a wallet and the database link communicates with TCPS. See Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint with a Wallet for details.

• Oracle Database, such as on-premises or a Database Cloud Service database, on a private endpoint that is configured for TCP: In this case you create the database link without a wallet and the database link communicates with TCP. See Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint without a Wallet for details

Topics

• Prerequisites for Database Links from Autonomous Database to Oracle Databases on a Private Endpoint

• Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint with a Wallet

• Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint without a Wallet

Prerequisites for Database Links from Autonomous Database to Oracle Databases on a Private Endpoint

Lists the prerequisites to create database links from an Autonomous Database to a target Oracle database that is on a private endpoint.

To create a database link to a target Oracle database on a private endpoint:

• 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.

  • The target database is an on-premises database that is connected to the source database's Oracle Cloud Infrastructure VCN using FastConnect or VPN.

• 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.

  See Configuring Network Access with Private Endpoints for information on configuring private endpoints with ingress and egress rules.

• To ensure security, the database link port is restricted to the range 1521-1525, or port 2484. You specify the target database port when you create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

Note:

When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing database links are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint with a Wallet

Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to a target Oracle database that is on a private endpoint.

Note:

This option is for target Oracle databases that have SSL/TCPS configured and that are on a private endpoint. Autonomous Database has TCP/IP with SSL (TCPS) authentication configured by default, so you can use these steps to connect to another Autonomous Database.

If the target Oracle database does not have SSL/TCPS configured, you have two options:

• You can configure the target Oracle database to use TCP/IP with SSL (TCPS) authentication. See Configuring Transport Layer Security Authentication for information on configuring SSL/TCPS.

• You can connect to the target Oracle database with TCP. See Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint without a Wallet for details.

Perform the prerequisite steps, as required. See Prerequisites for Database Links from Autonomous Database to Oracle Databases on a Private Endpoint for details.

To create a database link to a target database on a private endpoint using TCP/IP with SSL (TCPS) authentication:

1. Copy your target database wallet, cwallet.sso, containing the certificates for the target database to Object Store.

   Note:

   The wallet file, along with the Database user ID and password provide access to data in the target Oracle database. Store wallet files in a secure location. Share wallet files only with authorized users.
2. Create credentials to access your 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.
3. Create a directory on Autonomous Database for the wallet file cwallet.sso.

   For example:

   CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
   

   See Create Directory in Autonomous Database for information on creating directories.

4. Use DBMS_CLOUD.GET_OBJECT to upload the target database wallet to the directory you created in the previous step, 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 => '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 database.
5. On Autonomous 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 VCN).

   Note:

   Supplying the credential_name parameter is required.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'DB_LINK_CRED',
       username => 'NICK',
       password => 'password'
     );
   END;
   /
   

   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.

6. Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

   For example:

   BEGIN
        DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
             db_link_name => 'PEDBLINK1', 
             hostname => 'example1.adb.ap-osaka-1.oraclecloud.com',
             port => '1522',
             service_name => 'example_high.adb.oraclecloud.com',
             ssl_server_cert_dn => 'ssl_server_cert_dn',
             credential_name => 'DB_LINK_CRED',
             directory_name => 'WALLET_DIR',
             private_target => TRUE);
   END;
   /

   Note:

   If you set ROUTE_OUTBOUND_CONNECTIONS to PRIVATE_ENDPOINT, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

   DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK only supports a single hostname for the hostname parameter. Using an IP address or SCAN IP is not supported.

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

7. Use the database link you created to access data in the target database.

   For example:

   SELECT * FROM employees@PEDBLINK1;
   

Note:

For the credentials you create in Step 5, the Oracle Database 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 CREATE_DATABASE_LINK Procedure for additional information.

Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint without a Wallet

Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to a target Oracle database that is on a private endpoint and connect without a wallet (TCP).

Note:

This option is for target Oracle databases that are on a private endpoint and do not have SSL/TCPS configured.

Perform the prerequisite steps, as required. See Prerequisites for Database Links from Autonomous Database to Oracle Databases on a Private Endpoint for details.

To create a database link to a target database on a private endpoint using a secure TCP connection without a wallet:

1. On Autonomous 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 VCN).

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'PRIVATE_ENDPOINT_CRED',
       username => 'NICK',
       password => 'password'
     );
   END;
   /
   

   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.

2. Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

   For example:

   BEGIN
        DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
             db_link_name => 'PRIVATE_ENDPOINT_LINK', 
             hostname => 'exampleHostname',
             port => '1522',
             service_name => 'exampleServiceName',
             ssl_server_cert_dn => NULL,
             credential_name => 'PRIVATE_ENDPOINT_CRED',
             directory_name => NULL,
             private_target => TRUE);
   END;
   /

   DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK only supports a single hostname for the hostname parameter. Using an IP address or SCAN IP is not supported.

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

   As shown in the example, to create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to a target database on a private endpoint using a secure TCP connection without a wallet, all of the following are required:

   • The directory_name parameter must be NULL.

   • The ssl_server_cert_dn parameter must be NULL.

   • The private_target parameter must be TRUE.

     Note:

     If you set ROUTE_OUTBOUND_CONNECTIONS to PRIVATE_ENDPOINT, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

   • To ensure security, the database link port you specify in the port parameter is restricted to the range 1521-1525, or port 2484.

3. Use the database link you created to access data in the target database.

   For example:

   SELECT * FROM employees@PRIVATE_ENDPOINT_LINK;
   

Note:

For the credentials you create in Step 1, the Oracle Database 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 CREATE_DATABASE_LINK Procedure for additional information.