Create Database Links from Autonomous Database to Publicly Accessible Oracle Databases

Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to an Oracle database that is publicly accessible.

To create database links to a target on a private endpoint, see Create Database Links from Autonomous Database to Oracle Databases on a Private Endpoint.

To use database links with Autonomous Database the target database must be configured to use TCP/IP with SSL (TCPS) authentication. Autonomous 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 Database. Other Oracle databases must be configured to use TCP/IP with SSL (TCPS) authentication. See Configuring Secure Sockets Layer Authentication for more information.

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.

To create database links to a public target, the target database must be accessible. Some databases, including Autonomous 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 Database and allow that IP address to connect to your target database. For example, if the target database is another Autonomous Database, you can add the outbound IP address of the source database to the ACL of the target database. See Obtain Tenancy Details for information on finding the outbound IP address.

To create database links to a target Oracle database do the following:

  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.

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

    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',
              ssl_server_cert_dn => 'CN=adb.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
              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.

  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 Database for more information.

For additional information, see: