Using Database Links with Autonomous Data Warehouse

You can create database links to Oracle databases that are accessible from an Autonomous Data Warehouse database.

Create Database Links from Autonomous Database to Other Databases

Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to another database.

To use database links with Autonomous Data Warehouse 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 for an Autonomous Transaction Processing or Autonomous Data Warehouse database. Other 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. You specify the target database port when you create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

To use database links the target database must be accessible. Some databases, including Autonomous Databases may limit access, for example using Access Control Lists. If access to the target database is limited and does not include the database where you are creating the database link, this would prevent using database links with those target databases. See Use an Access Control List with Autonomous Database for more information.

To create database links to a target 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.
  3. Upload the target database wallet to the data_pump_dir directory, or to another directory where you have write privileges, using DBMS_CLOUD.GET_OBJECT.

    For example:

    BEGIN 
         DBMS_CLOUD.GET_OBJECT(
            credential_name => 'DEF_CRED_NAME',
            object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adwc/b/adwc_user/o/cwallet.sso',
            directory_name => 'DATA_PUMP_DIR'); 
    END;
    /

    Note:

    The credential_name you use in this step is the credentials for the Object Store. In the next step create the credentials to access the target database.
  4. On Autonomous Data Warehouse 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 optional if you use the same credentials on the target database. If credential_name is not supplied in the following step, or is supplied with a NULL value, the database link is created using the connected user credentials (the link is created and uses the credentials, username and password, of the user who is accessing the link).

    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.

  5. 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.adwc.example.oraclecloud.com',
              ssl_server_cert_dn => 'CN=adwc.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
              credential_name => 'DB_LINK_CRED',
              directory_name => 'DATA_PUMP_DIR');
    END;
    /

    Users other than ADMIN require privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK. See CREATE_DATABASE_LINK Procedure for more information.

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

    For example:

    SELECT * FROM employees@SALESLINK;
    

Notes for database links:

  • 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 DATA_PUMP_DIR directory. This means with a cwallet.sso in DATA_PUMP_DIR you can only create database links to the databases for which the wallet 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.
  • The DATA_PUMP_DIR is the only available predefined directory for uploading the target database wallet with DBMS_CLOUD.GET_OBJECT. You can specify a different directory as the directory argument if you previously created the directory and you have write privileges on the directory. See Create Directory in Autonomous Database for information on creating directories.
  • Supported target database versions are: 19c, 18c, 12.2.0, 12.1.0, and 11.2.0.
  • To list the database links accessible to the connected user, use the ALL_DB_LINKS view. See ALL_DB_LINKS for more information.
  • For the credentials you create in Step 4, 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.

For additional information, see:

Create Database Links from Other Databases to Autonomous Database

You can create database links from another Oracle Database to an Autonomous Database.

To create database links to an Autonomous Data Warehouse database do the following:

  1. Download your Autonomous Database wallet. See Download Client Credentials (Wallets) for more information.
  2. Upload the wallet to the database instance where you want to create the link to the Autonomous Data Warehouse database.
  3. Unzip the Autonomous Database wallet:

    Note:

    The wallet file, along with the Database user ID and password provide access to data in your Autonomous Database. Store wallet files in a secure location. Share wallet files only with authorized users.
    [oracle@sys1 ~]$ cd/u01/targetwallet
    [oracle@sys1 targetwallet]$ unzip Wallet_name1.zip
            Archive: Wallet_name1.zip  
              inflating: cwallet.sso
              inflating: tnsnames.ora    
              inflating: truststore.jks            
              inflating: ojdbc.properties          
              inflating: sqlnet.ora
              inflating: ewallet.p12
              inflating: keystore.jks
    
  4. Set GLOBAL_NAMES to FALSE.
    SQL> ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
     
    System altered.
    
    SQL> SHOW PARAMETER GLOBAL_NAMES
    NAME                     TYPE        VALUE
    ----------------------   ----------- -----------
    global_names             boolean     FALSE
    

    Set GLOBAL_NAMES to FALSE to use a database link name without checking that the name is different than the remote database name.When GLOBAL_NAMES, is set to TRUE, the database requires the database link to have the same name as the database to which it connects. See GLOBAL_NAMES for more information.

  5. Create the database link to the target Autonomous Data Warehouse database. Note that the security path includes my_wallet_directory; the path where you unzip the Autonomous Database wallet.
    CREATE DATABASE LINK ADBLINK 
       CONNECT TO NAME1 IDENTIFIED BY ************ 
       USING
    '(description=(retry_count=20)(retry_delay=3)
         (address=(protocol=tcps)(port=1522)(host=example1.oraclecloud.com))     
         (connect_data=(service_name=example2_high.adwc.oraclecloud.com))     
         (security=(my_wallet_directory=/u01/targetwallet)
            (ssl_server_dn_match=true)
            (ssl_server_cert_dn="CN=example2.oraclecloud.com,OU=Oracle
              BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))';
    
    Database link created.
  6. Use the database link you created to access data on the target database (your Autonomous Database instance in this case):

    For example:

    SELECT * FROM employees@ADBLINK;
    

To list the database links accessible to the connected user, use the ALL_DB_LINKS view. See ALL_DB_LINKS for more information.

For additional information, see:

Drop Database Links

After you create a database link you can drop the database link.

  1. Drop a database link to a target database using DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK.

    For example:

    BEGIN
         DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
              db_link_name => 'SALESLINK' );
    END;
    /

See DROP_DATABASE_LINK Procedure for detailed information about the procedure.