DB Links and ADB - Dos and Don'ts

DB Link simplifies data sharing across oracle databases. And DB link is created using the CREATE DATABASE LINK SQL command, a very simple and straight forward method. DB link creation has a different procedure on an Autonomous database in contract to typical Oracle databases. In this post, we will highlight the rules, limitations and the use of new DBMS_CLOUD_ADMIN package. Let's get into the action.

Rules and Limitations

Note some of below rules and limitations of creating DB link from ADB:

  • The target databases (non-ADB) should be configured to use TCP/IP with SSL (TCPS) authentication
  • The allowed DB port range is 1521-1525 to ensure the security. You can define the port while creating the DB link
  • ADB DB link support databases that are only accessible through a public IP or public hostname
  • One directory restricted to one wallet file. Create multiple directories to keep multiple wallet files of multiple databases
  • Supported target database versions are,,, 18c and 19c.
Creating DB link from Autonomous Database

Now let's walk through DB link creation procedure from an Autonomous database. As mentioned, you should use DBMS_CLOUD_ADMIN.CREATE_ DATABASE_LINK procedure on ADB. Below steps describe the required procedure:
  • Copy your target database wallet file (cwallet.sso) which contains target database certification to an object store
  • Using DBMS_CLOUD.CREATE_CREDENTIAL procedure, create access credentials to the object store where the target wallet file is stored.
  • Upload the target database wallet file from Object store to database directory using the DBMS_CLOUD.GET_OBJECT procedure. Refer example below:
        credential_name => 'DEFF_CRED_CRDB',
        object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
        directory_name => 'DATA_PUMP_DIR'); -- DATA_PUMP_DIR is the predefined/default directory. It can be another DB directory.
  • Create credentials on ADB, refer below example:

    credential_name => 'DBL_CRDB',
    username => 'SCOTT', -- username must be specified in CAPITAL
    password => 'welcome1'
Details are stored in the database in encrypted format.
You can use the query below to list the stored credentials:

SELECT credential_name, username, comments FROM all_credentials;
  • You can use below command to drop credentials from the database:

    • Now we are ready to create database link from Autonomous database. Use the example below to create:
              db_link_name => 'ADB_TO_CRDB', 
              hostname => 'adb.eu-frankfurt-1.oraclecloud.com', 
              port => '1525',
              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 => 'DBL_CRDB',
              directory_name => 'DATA_PUMP_DIR');
    If you are not an admin privileged user, get the permission to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK procedure.

    Now, you can use the DB link from ADB to the target database.

    To drop DB link from ADB, you can use the example below:

              db_link_name => 'ADB_TO_CRDB' );

    Creating DB link to ADB

    We have learned so far how to create a DB link from ADB to target database. This section will zip through DB link creation to ADB.
    As I have mentioned my previous blog entry that ADB only accepts secured client connections. To which, you will have to download client connect credentials. You can refer my previous blog post for the procedure.

    Before creating database link, ensure the following is met:
    • Download the client connect credentials
    • Unzip the downloaded file to a secure location your computer
    • Using below example, create a db LINK ADB:
              BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))';

    At this point in time the DB link is ready.

    In nutshell, this blog post explained the do's and don'ts of creating DB link from/to ADB.

    Connecting to an Autonomous Database using JDBC Thin client

    I have discussed in my earlier posts about establishing SQL * Plus and using the SQLcl tool details. Today's blog post will take you through JDBC Thin client configuration details to be able to successfully connect with an autonomous database service. Will primarily focus on mandatory JDK, JDBC versions and client connect credentials requirement details. Let's get started.

    To begin with, any Java application that uses JDBC Thin driver requires an Oracle wallet or JavaKeyStore(JKS). Luckily, these files are readily available on OCI console for download. Refer my earlier blog post to learn about downloading client connect credentials files and JavakeyStore files.

    Prerequisites for JDBC Thin Driver

    Below is the list of perquisites for JDBC Thin client connection:

    • Download Connect Credentials as explained in the previous post
    • Validate JDK version for security, if you are on less than JDK8u162
    • Check JDBC driver version
    • JDBC driver 18.3 or higher simplifies security credentials wallet connection configuration.
    • Specify the wallet directory in the JDBC connect string.
    • Include the wallet directory location in the JDBC url if no TNS_ADMIN environmental variable is set.
    Connection string url

    Part of client connect credentials downloads, you will have tnsnames.ora files which contains tns service names and connection strings. Below is an example of high level connect string entry:

    dbname_high= (description=
          US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
    JDBC 18.3 Thin Driver connection string example, for Linux platform:


    Using JavaKeyStore (JKS)

    Below example shows the JDBC Thin driver connectivity using JKS to an ADB:

    Subsequently, add the JKS related connection properties to ojdbc.properties file. To use SSL connectivity over Oracle Wallet, below properties should be set to ojdbc.properties files:

    # Properties for using Java KeyStore (JKS)

    Once the properties are set, compile and run using the below example:

    java –classpath ./lib/ojdbc8.jar:./lib/ucp.jar UCPSample
    To go through your firewall with HTTP proxy requirement to connect to an internet, use JDBC Thin client 18.1 or higher. So, add the following to your tnsnames.ora file to go through HTTP proxy:

    ADWC1_high =
                 (security=(ssl_server_cert_dn="adw.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")

    In nutshell, we have walk through over JDBC thin driver client connection to an ADB requirements and prerequisites.


    Autonomous Database (ADB) connectivity with Oracle SQLcl

    This blog post takes you through Oracle SQL Developer command line interface connectivity to an autonomous database (ADB). SQLcl is a free command line interface for Oracle database. It will allow interactive or batch execution of SQL and PL/SQL commands with in-line editing, statement completion, and command recall for a feature-rich experience capabilities. Download the tool from the below url:

    With SQLcl 4.2 or higher you can also connect  to ADB database, either using Oracle Call Interface or JDBC thin connection. Whatever connection mode, OCI or JDBC, it has to be setup before you can use the SQLcl.

    Use the below example to connect to a ADB using SQLcl oci connection:

    sql -oci
    SQLcl: Release 18.4 Production on Wed Apr 03 15:28:40 2019
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    Username? (''?) sales_dwh@adwc1_low
    Password? (**********?) **************
    Last Successful login time: Wed Apr 03 2019 15:29:19 -07:00
    Connected to:
    Oracle Database 18c Enterprise Edition Release - Production
    Use the below example to use SQLcl with JDBC thin connection:

    • Start with sql /nolog 
    • Configure the session to use existing wallet connection: 
    • SQL> set cloudconfig /home/adwc/wallet_ADWC1.zip
      Wallet Password:  **********
    • Connect to ADB database , as shown below:
    • SQL> connect admin@adwc1_medium

    In my next post, will discuss JDBC connectivity details.

    SQL * Plus connectivity to Autonomous Database (ADB) procedure

    Autonomous Database (ADB) learning keeping myself pretty occupied these days. All credit should go to Oracle for their free offering of Oracle cloud technology university courses and a chance to certify. So planned to write a few important topics along the way while learning about ADB. This blog post will primarly focus on autonomous database (ADB) service available connectivity options, downloading database credentials and a procedure to connect to an ADB database service using the SQL * Plus tool.

    An Autonomous Database (ADB) service can be connected through SQL * Net, JDBC thick/thin, ODBC processes etc. ADB connection uses certificate authentication and SSL, where  certificate authentication uses encrypted key stored in a wallet files on both client and server. All client connections to the database must use SSL for encryption and under any circumstances it doesn't accept any unsecured connections. 

    To begin with, every ADB database connection must be made over a public internet and application should use a secure connection (SSL).  Before establishing any sort of client connectivity to the ADB service, service client connect credentials must be downloaded from the OCI page for the respective database (instance) or region. Below procedure summarizes how to download database connect (wallet) credentials and use of SQL * Plus to connect to  ADB.

    Downloading database connection credentials

    As discussed earlier, ADB only accepts secure connections to an Autonomous Database. Therefore, Oracle client credential (wallet) files which contains your connection credentials should be downloaded first. You need to download this files only once. The wallet can be downloaded either from Administration link from the ADB service console page, or from the DB Connection button on instance page of OCI page or through API calls. Use the following method to download the connection credentials from OCI console:

    • Navigate to ADB details page  and click on DB Connection button
    • Based on the connectivity requirement, choose either Instance or Regional wallet type and click on the Download button 
    • The credentials file is protected with a password. So, you will have to input a password before downloading the file. The password must be 8 characters long, with at least 1 letter and either 1 numeric character or 1 special character
    • Once the password is entered and confirmed, click the download button
    • Default name for the file is Wallet_databaename.zip, and the zip files contains following files:
      • tnsnames.ora & sqlnet.ora
      • cwallet.sso & ewallet.p12
      • keystore.jks & tuststore.jks
      • ojdbc.properties & 
    • Unzip the file to a secure location
    Predefined DB Service Names

    The tnsnames.ora files contains the following predefined 5 database services names for Autonomous Transaction Processing (ATP) and 3 (high, medium, low) for Autonomous Data warehouse (ADW) databases:
    • high A high priority app connection for reporting and batch operations which runs in parallel with little concurrency capacity, subject to queuing.
    • medium A typical connection service for reporting and batch operations with limited (4) parallel degree.
    • low A low priority connection service for reporting and batch operations with no parallelism options.
    • tp (for ATP only), typical application connection service for transaction processing operations with no parallelism.
    • tpurgent (ATP only) the highest priority application connection for time critical transaction processing operations. Can specify manual parallelism. 
    By default, sessions on ADB that are idle for over 5 minutes will be automatically disconnected from the database to make room for more active sessions.

    SQL * Plus connection to ADB

    For any sort of ADB connection, database credentials must be dowloaded by an individual user first. In this segment, we will discuss how to use an SQL * Plus tool to connect to ADB to execute regular SQL commands. Unlike other Oracle cloud options, ADB doesn't provide compute node access. So, you must have SQL * Plus installed on your computer. So, you have to install Oracle client software or higher version on your system. Once the client software is installed, download connection credentials using the procedure described above.

    Unzip/uncompress the zip file downloaded into a secure folder on your computer.

    Edit the sqlnet.ora file and replace ?/network/admin text with name of the folder containing the credentials, as shown in the below example on a unix/linux client:

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/adw_credentials")))

     It is highly recommended to set TNS_ADMIN environmental variable and set the location of the credentials files.

    As previously stated, the connection must be through an internet. So the companies that doesn't provide direct internet connection, they will allow the access through HTTP proxy settings. In this case, you must update the sqlnet.ora and tnsnames.ora files as per below example if you have a firewall connection and requires HTTP proxy to connect internet (this scenario only support clients with or higher version):

    • sqlnet.ora should have this entry: SQLNET.USE_HTTPS_PROXY=on
    • And the tnsnames.ora entry should contains the following:
    ADWC1_high =
                 (security=(ssl_server_cert_dn="adwc.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
    Once you have the client software and connect credentials ready, now you can use the sql * plus tool to connect to a ADB. Refer below syntax using low credentials service name here:

    sqlplus sales_dwh@adwc1_low  
    SQL*Plus: Release - Production on Wed Apr 3 15:09:53 2019
    Copyright (c) 1982, 2018, Oracle.  All rights reserved.
    Enter password:
    Last Successful login time: Wed Apr 03 2019 14:50:39 -07:00
    Connected to:
    Oracle Database 18c Enterprise Edition Release - Production
     You can also see the last successful login time.

    In nutshell, this post explained how to download ADB database connect credentials from various methods, how to connect to ADB using SQL * Plus tool. Will be discussing about JDBC thin client connectivity procedure in the next post.