Oracle Autonomous Database: Connecting to Autonomous Database

This post is part of a series of blog posts on the Oracle Autonomous Database.

Connecting to the Oracle Autonomous Database relies upon a wallet to help with an encrypted SSL connection over the public internet. Keep in mind that the wallet is mandatory when connecting to Autonomous Database Shared. However, with Autonomous Database Dedicated, you can choose to connect directly without using a wallet. So most of this post is 100% required when connecting to the Autonomous Database Shared. It’s optional when connecting to Dedicated.

So with that being said, all apps will use a secure connection to connect to the Autonomous Database, and the Autonomous Database uses certificate authentication and SSL. This ensures that no unauthorized access is available to the ADB, and it also ensures that communications between the app and the server are fully encrypted and cannot be intercepted or altered. This information that’s required to authenticate to the database is stored in a wallet that’s available on both the client (where your application is running) and the server (where your database is running), and is automatically generated for you when provisioning the Autonomous Database.

When you actually download this wallet from your Autonomous Database, you’ll see there’s a collection of files that are included with it– like a couple of key files– in addition to a TNS entry, a tnsnames names file, and a sqlnet file. These files have existing service handles and connect strings to connect to your Autonomous Database. And you will also see there’s a readme file. The readme file contains information about when the wallet is expected to expire. And just so you know, the expiration date of a wallet is around about 2 and 1/2 year mark. So you’re looking at a long period of time before these wallets do expire. And you will get warnings before that’s required to replace it.

There are two types of wallets:

For ADB-Shared:

  • Instance Wallet file
    Instance wallet contains only credentials and keys for a single Autonomous Database
  • Regional Wallet file
    Regional wallet contains credentials and keys for all Autonomous DBs in a specified region.
    Regional wallets should only be used by DBAs.

For ADB-Dedicated, the wallet file contains only credentials and keys for a single autonomous database. There is no regional file.

The ADB only accepts secure connections to the database, and you need to download that wallet first. You can download it both from the DB Connection button on your main instance details page, as well as through the Service Console. You can also use API calls to download the wallet as well. It’s definitely a good practice to store the credential file in a secured location and only share the file contents with people that have authorized access. However, with that file alone, you cannot connect to the database. You still need to know the database password. It provides a secure way to connect to your database to route you to it, but not to actually log into the database.

You also have the option to rotate either the instance wallet or the region wallet. If you rotate the instance wallet, all the keys for that associated instance are discarded, and it generates a completely new wallet. With a regional wallet, if I were to replace or rotate my instance, the regional wallet will continue to work. However, all existing connections will continue to work, but any new connections will be terminated and will need to be re-established using the new wallet. So ADB gracefully allows the controlled operation of rotating your wallet.

Predefined Database Service Names

If you look inside your wallet file, you will notice that there are several connections that are predefined for you– connection services– depending what service you’re running.

  • tpurgent (ATP only):
    The highest priority application connection service for time critical OLTP operations.
    This connection supports manual parallelism
    Share of resources: 12
  • tp (ATP only):
    A typical application connection service for OLTP operations.
    This connection service does not run with parallelism.
    Share of resources: 8
  • high:
    A high priority application connection service for reporting and batch operations.
    All operations run in parallel and are subject to queuing.
    Share of resources: 4
  • medium:
    A typical application connection service for reporting and batch operations.
    All operations run in parallel and are subject to queuing.
    Using this service, the degree of parallelism is limited to 1.25 × OCPUs
    Share of resources: 2
  • low:
    A lowest priority application connection service for reporting and batch processing operations.
    This connection service does not run with parallelism.
    Share of resources: 1

Please note: After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, ADB performs more actions to configure the connection than just setting its consumer group.

If we were running an Autonomous Data Warehouse (ADW), we would only have high, medium, and low. In the case of Autonomous Transaction Processing (ATP), we have both tpurgent and tp in addition to high, medium, and low. When you go to download or to connect to the database, the autonomous database will automatically create the service handles for you. But they will be prefixed with your database name. e.g. dbname_high, dbname_medium, and dbname_low.

The tp and tpurgent are focused on OLTP workloads, they’re intended to support a very high concurrency. As you would typically see for a transactional workload, you’ll see that ADB supports up to 100 times the number of CPUs before it starts queuing, versus the high, medium, and low data warehouse ones have a limitation on the number of concurrent sessions that you would see connected to the database.

With that being said, the parallelism for the high, medium, and low is different. For high, ADB allows you to run parallelism– so operating parallel operations that might run with parallel query can run across all CPUs. Medium would run with a default of 4, and low would run with a default of 1. And it’s not possible to change that for low.

With OLTP, ADB explicitly sets tp as only ever using one parallel query slave. And that’s because this is a transactional workload. It’s not typical to run things with parallel query enabled. If, however, you have some batch process or some operation that requires some level of parallelism, if you’re using tpurgent as your service to connect to, you can explicitly define your parallel degree on the operation you’re performing.

Now, in addition to parallelism, ADB also controls the amount of resource shares. So you’ll notice OLTP tpurgent has a high number of shares of resources that it gets slices to, if you wish. tp doesn’t have as high a number of resource shares, but it is up there, because we’re intending to run operations concurrently and simultaneously. And in transactional workloads, typically you’re going to see these will be short, bursty style operations.

With warehouse, batch, and reporting, on the other hand, the number of resource shares you get are diminished. And that’s because the idea is this is more of an ad-hoc, asynchronous style of performing work, where the units of work could be varied in complexity and size. So we typically do not share as many of these resources, bearing in mind that these resources could also be consuming a lot of memory or running for an inordinate amount of time when compared to an OLTP workload.

So connecting to the Autonomous Database– I will point out that the Autonomous Database licensed on port 1522. And that’s the standard on ADB Shared. And it’s the default for ADB Dedicated. All connections, as I pointed out earlier, are using SSL for encryption. And then, unlike other cloud services, you do not have a Unix command line interface on the system you’re connecting to. Whether you’re running in Autonomous Database Shared or Dedicated, you do not have access to the underlying operating system.

Having said that, all Autonomous Databases can be connected through the typical client utilities, such as SQL*Net, JDBC thick or thin, OCI, etc. Remember, the clients will require the security credentials wallet to connect when using those tools. And there’s a few connectivity options. The first one is through the public internet. And when connecting through the public internet, you could do public endpoints or private endpoints. So there’s two parts to that.

And then the additional approach is using the FastConnect model with public peering, in which you could connect through either a private connection from an on-premise network or from another cloud vendor. And obviously, through public internet, you could set up private endpoints, or you could set up a FastConnect private connectivity to other clouds. FastConnect is useful for extremely high bandwidth. Perhaps you have an application tier that does require the benefit of being on a dedicated pipe to connect to.

There is no SYS or SYSTEM. Admin is the highest user you will have in an Autonomous Database.

Transparent Application Continuity

Preconfigured with Autonomous Dedicated and can be enabled in Autonomous-Shared with DBMS_CLOUD_ADMIN.ENABLE_APP_CONT

Transparent application continuity is there to help with failover and masking transferring of instances from one machine to another. So remember, Autonomous Shared provides application continuity versus Dedicated provides the full on transparent application continuity. Both have a full view of session state, but application continuity allows customers the flexibility to keep their side effects and restore more complex states, such as session during lobs at failover for example. And you can use the ORAchk protection report to identify the protection level, to see where things are at.

Oracle Call Interface (OCI), ODBC, JDBC, OCI

  • Wallet File is required
  • Set WALLET_LOCATION in sqlnet.ora
  • Set TNS_ADMIN environment variable to credentials file
  • Oracle Client 11.2.0.4 or higher
  • HTTP Proxy in tnsnames.ora

So to connect through OCI, the traditional TNS model, you still require your wallet. But you would set your wallet location in the sqlnet.ora file, and you would point to wherever it is you’ve downloaded that wallet. Once you’ve done that, SQLNet, if you’re running a client 11.2.0.4 or higher, is capable of knowing and reading that wallet to find what it needs. And you would also set your TNS_ADMIN environment variable to point to that location of that wallet, or where you’ve put your tnsnames.ora file if you’ve unzipped your wallet.

Connecting with .NET applications, you would download and install ODT and ODP.NET. And then you can go ahead and run Visual Studio, and use the NuGet package manager, and then you can download ODAC, which is the Oracle Data Access Components for Windows. Once you’ve got all that configured and setup, you can then set your wallet location in sqlnet.ora, and you would ensure that you’ve set TLS 1.2 to be enabled. And then your .NET applications will connect no problem.

JDBC Thin or Universal Connection Pool (UCP)

A couple of requirements– you need to install JDK8u161 or higher. Or just use JDK9, and you’re good to go– or higher. And you would download either the JDBC Thin driver, ojdbc8.jar. Or you could use the 12.1 version, and you would patch it up to use the upc.jar for the universal connection pool.

Once you’ve set that up or you’ve got the correct driver working, you would want to go ahead and copy the ojdbc.properties file, which is inside your wallet. You could unzip your wallet, copy those contents to your TNS_ADMIN directory, set the properties to be your net wallet location. And then the JDCB URL is going to use oracel:thin@service_name– so the service name that’s inside your tns entry is what you would point to.

Now, if you’re not using a TNS_ADMIN environment variable, if you’ve not set that, you would then point to the TNS_ADMIN = wallet directory as part of your connect string. If you’re using a HTTP proxy– perhaps you have a proxy server to block your requests– that can easily be configured. You would add the HTTP proxy hostname to tnsnames.ora very easily. And that’s documented for you as well.

Using SQLNet connections

Changes required to sqlnet.ora

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=”?/network/admin”)))
SSL_SERVER_DN_MATCH=yes

ADB also supports connecting through Python, Node,js, and other scripting languages. You install the Instant Client, install the language driver, unzip the credentials wallet, and use the TNS entry file with the various contents. In some cases, like Python, you can simply run a yum install command to get that.

Perl: DBD-Oracle
Python: cx_Oracle
Node.js: Node-oracledb
PHP: PHP OCI8 or PDO_OCI
Ruby: Ruby-oci8

ADB also supports connecting through Active Directory. So if you are running Active Directory, traditionally, in the past, you would have been expected to authenticate through your LDAP repository, Oracle LDAP. Now, since Oracle 18c, we are able to directly connect to Active Directory and authorize and authenticate your users and manage them through Active Directory and have them routed to your Autonomous Database. So if you have a Windows or a Microsoft-based application that uses Active Directory to connect to your workloads, that is supported to work natively with the Autonomous Database. Just keep in mind that when using Autonomous Database with Active Directory, both users and roles are mapped in Active Directory and not in the Autonomous Database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s