Getting started with Oracle Autonomous Transaction Processing

Getting started with Oracle Autonomous Transaction Processing is actually much easier than you might think. In fact, with Oracle’s $300 in free cloud credits you can probably get your first 30 days on the service for free. Please note, you will require an active email address and credit card in order to sign up for a trial account. Of course, if you have existing cloud credits you can skip this step.

Once you sign up for trail account you’ll get an email with your tenancy, username and password. Armed with this information, head on over to https://cloud.oracle.com to sign in. The video below explains in detailed the simple steps needed to provision a new Autonomous Transaction Processing database. I’ve also listed these steps below the video, for  easy reference.

Steps required for provisioning an Autonomous Transaction Processing Database:

1. Login to your Oracle Cloud account at https://cloud.oracle.com using your tenant, username and password.
2. Once logged in, you will find yourself in the “My Services” area. To create an Autonomous Transaction Processing database, you will need to click on the hamburger menu in the top left-hand corner and select the Autonomous Database.
3. You will be automatically redirected to the Oracle Cloud Infrastructure page and from there you will need to click on the hamburger menu in the top left-hand corner and select Autonomous Transaction Processing.
4. On the left-hand side of the screen select the compartment you wish to provision the Autonomous Transaction Processing database in or create one.
5. Click on the Create Autonomous Transaction Processing button.
6. Supply the name, database name, number of CPUs, database size and the admin password. Select if you are bringing existing on-premises Oracle Database licenses or if you want your cloud service instance should include database license and click the create button.

How do I connect to an Autonomous Transaction Processing Database?

Once you have provisioned your Autonomous Transaction Processing database, you’re going to want to connect to it. All connections to an Autonomous Database are encrypted, which means you will need to download a wallet file with your database credentials before you can connect.

The wallet includes a sqlnet.ora and tnsnames.ora files, a Java keystore and other relevant files to make a secure TLS 1.2 connection to your database. The video below shows you how to download your database credentials via the service console and I’ve provided a summary of the steps below the video.

Steps required to download your  Database Credentials:

1. Click on the name of the Autonomous Transaction Processing instance you just created.
2. From the instance details screen you can start, stop, or scale your instance, as well as managing it. Click on the service console button.
3. Login to the service console using the Admin user and the password you specified when you create the Autonomous Transaction Processing instance.
4. From the menu on the left-hand side click on the Admin tab.
5. On the admin panel, select ‘Download Client Credentials’
6. Provide a keystore password and save the wallet file to your local machine. You will need this wallet file and keystore password to connect to the database later.

There’s no need to unzip your wallet file if you plan to connect to your Autonomous Transaction Processing instance from SQL Developer. But before I show you how to do that I wanted to explain the four predefined database services that are created for every Autonomous Transaction Processing database.

The predefined database services allow you to control the priority of the sessions connecting to the Autonomous Transaction Processing database.

TPURGENT: The highest priority application connection service for time critical transaction processing operations. This connection service supports manual parallelism.

TP: A typical application connection service for transaction processing operations. This connection service does not run with parallelism.

HIGH: A high priority application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.

MEDIUM: A typical application connection service for reporting and batch operations. All operations run in parallel and are subject to queuing.

LOW: A lowest priority application connection service for reporting or batch processing operations. This connection service does not run with parallelism.

PLEASE NOTE: the wallet file you download will have the predefined services for every database defined in your tenancy included in it.

The video below shows you how easy it is to connect with SQL Developer and below the video are the actual steps.

1. Launch SQL Developer and select add connection from the top left-hand side.
2. Enter a unique connection name.
3. The username should be ADMIN and the password should be the one you specified when you provisioned the Autonomous Transaction Processing database.
4. The connection type should be set to ‘Cloud PDB’.
5. The configuration file needs to point to the wallet file you downloaded from the console.
6. Finally, select service name from drop down. Service name is database name followed by suffixes low, medium, high or parallel.
7. Test your connection and save.

You now have a secure connection to your cloud database as simple as that!