Create Database Links to an Oracle AI Database that is not an Autonomous AI Database

You can create database links from an Autonomous AI Database to an Oracle AI Database that is on a private endpoint or on a public endpoint (publicly accessible).

Note: See Create Database Links from Autonomous AI Database to Another Autonomous AI Database if the target for your database link is another Autonomous AI Database.

Create Database Links from Autonomous AI Database to a Publicly Accessible Oracle AI Database with a Wallet (mTLS)

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

To use database links with Autonomous AI Database the target database must be configured to use TCP/IP with SSL (TCPS) authentication. Autonomous AI Databases use TCP/IP with SSL (TCPS) authentication by default, so you do not need to do any additional configuration in your target database to link to another Autonomous AI Database. Other Oracle AI Databases must be configured to use TCP/IP with SSL (TCPS) authentication. See Configuring Secure Sockets Layer Authentication for more information.

To create database links to a public target, the target Oracle AI Database must be accessible. Some databases may limit access (for example, using Access Control Lists). Make sure you enable your target database to allow access from your source database for the database link to work. If you limit access with Access Control Lists (ACLs), you can find the outbound IP address of your source Autonomous AI Database and allow that IP address to connect to your target database.

See How to Create a Database Link from Your Autonomous AI Database to a Database Cloud Service Instance for more information.

To create database links to a target Oracle AI Database with a wallet (mTLS):

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

    Note the following for the wallet file:

    • The wallet file, along with the Database user ID and password provide access to data in the target Oracle AI Database. Store wallet files in a secure location. Share wallet files only with authorized users.

    • Do not rename the wallet file. The wallet file in Object Storage must be named cwallet.sso.

  2. Create credentials to access your Object Store where you store the wallet file cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

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

  4. Use DBMS_CLOUD.GET_OBJECT to upload the target database 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 database.

    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

  5. On the Autonomous AI Database instance, 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 that you use to create the database link.

    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.

    Note: You can use a vault secret credential for the target database credential in a database link. See Use Vault Secret Credentials for more information.

    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 => 'SALESLINK',
        hostname => 'adb.eu-frankfurt-1.oraclecloud.com',
        port => '1522',
        service_name => 'example_medium.adb.example.oraclecloud.com',
        credential_name => 'DB_LINK_CRED',
        directory_name => '*DBLINK_WALLET_DIR*');
END;
/

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

    If the wallet file in the directory specified with directory_name is not cwallet.sso, the procedure reports an error such as: ORA-28759: failure to open file.

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

    For example:

    SELECT * FROM employees@SALESLINK;

For the credentials you create in Step 5, the target 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.

Note: You can create links to Big Data Service using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK. See Query Big Data Service Hadoop (HDFS) Data from Autonomous AI Database for more information.

For additional information, see:

Create Database Links from Autonomous AI Database to an Oracle AI Database on a Private Endpoint

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

Depending on the type and the configuration of the target Oracle AI Database:

See How to Create a Database Link from Your Autonomous AI Database to a Database Cloud Service Instance for more information.

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

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

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

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

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

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

Note: This option is for target Oracle AI 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 AI Database to a Target Autonomous AI Database 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 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 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.

    Note: You can use a vault secret credential for the target database credential in a database link. See Use Vault Secret Credentials for more information.

    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;
/

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

    When the target is an Oracle RAC database, use the rac_hostnames parameter to specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK. This allows you to take advantage of the high availability capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a SCAN hostname in the rac_hostnames value is not supported.

    For example, with a target Oracle RAC database use the rac_hostnames parameter:

    BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
         db_link_name => 'PRIVATE_ENDPOINT_LINK',
         rac_hostnames => '["sales1-svr1.example.adb.us-ashburn-1.oraclecloud.com",
                            "sales1-svr2.example.adb.us-ashburn-1.oraclecloud.com",
                            "sales1-svr3.example.adb.us-ashburn-1.oraclecloud.com"]',
         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 does not support a value of localhost for the hostname or in the rac_hostnames parameter.

    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 the ROUTE_OUTBOUND_CONNECTIONS database property, 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.

  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 AI 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 AI Database to Oracle AI Databases on a Private Endpoint with a Wallet (mTLS)

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

Note: This option is for target Oracle AI Databases that have SSL/TCPS configured and that are on a private endpoint.

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

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

To create a database link to a target Oracle AI 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 AI 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 AI Database for the wallet file cwallet.sso.

    For example:

     CREATE DIRECTORY WALLET_DIR AS 'directory_path_of_your_choice';

    See Create Directory in Autonomous AI 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 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 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.

    Note: You can use a vault secret credential for the target database credential in a database link. See Use Vault Secret Credentials for more information.

    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 the ROUTE_OUTBOUND_CONNECTIONS database property, 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.

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

    When the target is an Oracle RAC database, use the rac_hostnames parameter to specify one or more hostnames with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK. This allows you to take advantage of the high availability capabilities of Oracle RAC. Using an IP address, a SCAN IP, or a SCAN hostname in the rac_hostnames value is not supported.

    For example, with a target Oracle RAC database use the rac_hostnames parameter:

    BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'PEDBLINK1',
    rac_hostnames => '["sales1-svr1.example.adb.us-ashburn-1. oraclecloud.com",
                       "sales1-svr2.example.adb.us-ashburn-1.oraclecloud.com",
                       "sales1-svr3.example.adb.us-ashburn-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;
/

    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK does not support a value of localhost for the hostname or in the rac_hostnames parameter.

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

Database Link Notes with a Target Oracle AI Database

Provides notes for creating database links to a target Oracle AI Database (when the target is not an Autonomous AI Database)

Notes for database links to other Oracle AI Databases: