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

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

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

   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.

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

   For example:

   SELECT * FROM employees@PRIVATE_ENDPOINT_LINK;
                 

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

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

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

   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;
                 

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

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

Notes for database links to other Oracle databases:

• Only one wallet file is valid per directory for use with database links. You can only upload one cwallet.sso at a time to the directory you choose for wallet files (for example DBLINK_WALLET_DIR). This means with a cwallet.sso in DBLINK_WALLET_DIR you can only create database links to the databases for which the wallet in that directory is valid. To use multiple cwallet.sso files with database links you need to create additional directories and put each cwallet.sso in a different directory. When you create database links with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK, specify the directory that contains the wallet with the directory_name parameter.

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

• Supported target Oracle database versions for database links to another Oracle Database are: 19c, 12.2.0, and 12.1.0.

  Note
  
  For complete information on supported versions, see Client Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

• Autonomous Database sets the SEC_CASE_SENSITIVE_LOGON parameter to true and this value cannot be changed. If your target database is not an Autonomous Database, then you must set SEC_CASE_SENSITIVE_LOGON parameter to true on the target database. If SEC_CASE_SENSITIVE_LOGON is set to false on the target database, then error ORA-28040: No matching authentication protocol is raised.

• To list the database links, use the ALL_DB_LINKS view. See ALL_DB_LINKS for more information.

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

• When the Autonomous Database instance is on a private endpoint, there are two options to specify the target database: use either the hostname parameter or the rac_hostnames parameter:

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

    When you specify a list of host names in the rac_hostnames parameter, CREATE_DATABASE_LINK uses all of the specified host names as addresses in the connect string. If one of the specified hosts is not available on the target Oracle RAC database, Autonomous Database automatically attempts to connect using another host name from the list.

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