This blog article will walk through creating an Autonomous Transaction Processing (ATP) Database in the Oracle Cloud. It can then be accessed using a WebLogic Server datasource, as described in https://blogs.oracle.com/weblogicserver/atp-database-use-with-weblogic-server-v2 .
For an introduction to Oracle ATP databases, see https://docs.oracle.com/en/cloud/paas/atp-cloud/index.html.
You need to start by getting an account in the Oracle Cloud. You can sign up for a free account. A free account was used to create these screen shots and in some cases that limits the options available. The screen shots were created on 12/07/2020 and may change slightly over time. This is the home page.
Open the menu ("hamburger button") in the upper left corner and select the "Autonomous Transaction Processing" option.
If you have multiple Compartments, select the correct one in the drop down box on the left side (in a free account, there is only one). Leave the workload set to "Transaction Processing" for use with WebLogic Server. Then click on the "Create Autonomous Database" button.
On the next page, enter a display name and a database name for the ATP database. If you are using a free account, you should turn on the "Show Only Always Free configuration options". Enter the administrator password for the database. For a non-free database, you would set the number of CPU's and storage. If you already have an Oracle License, you would need to use "Bring Your Own License".
This brings up the "Autonomous Database Details" page with a "Metrics" view that shows the details and metrics for the newly created database. The Lifecycle State will be "Provisioning" until the the setup is complete and the database is "Available" for processing. From this page, you can choose to scale up/down, stop, and backup/restore the database.
When the database is available, click on the "Service Console" button to bring up the Overview page. Not much interesting yet.
Click on the Activity button to see database activity.
The Administration button takes you to a screen where you can download the client wallet that contains the database credentials or reset the administrator password.
Click on Development to bring up a screen that provides access to development activities including downloading the Instant Client (for SQL*Plus) and using the SQL Developer Web tool.
Click the SQL Developer Web option and enter the database administrator credentials that you specified earlier. This will bring up a web view that is similar to the standalone SQL Developer tool.
Before you leave the Cloud login, make sure to download the database credentials that are stored in a wallet zip file used by the client. As we saw earlier, you can get there via Service Console -> Administration -> Download Client Credentials (Wallet) The other approach is to use "DB Connections" from the "Autonomous Database Details" page. Click on the "Download Client Credentials (Wallet)" button to bring up the following pop-up box. Click on the "Download Wallet" button. You will need to enter a password to protect the wallet and click "Download", then "Close".
The completes the creation of the ATP database but obviously you may want to come back to view database metrics and perform database operations.
The two most popular standalone tools for connecting to a database are SQL Developer and SQL*Plus.
The simpler of the two is SQL Developer because it knows how to deal with the wallet zip file. If you have used it before for non-ATP databases, there are a few differences when using an ATP database (note that you will need to use a relatively recent version of the software). Click on the green "+" to create a new connection. Enter the name of the connection (probably your database name). Enter the Username and Password and set the Role to "default". Very important, set the Connection Type to "Cloud Wallet" and fill in the "Configuration File" with a path name to the wallet zip file. For use with WebLogic Server, the Service should end with "tp" (not the default "_high"). You can now click on "Test" to check that you typed the information in correctly and then "Connect" if you want to work with the database. You probably want to "Save" the information for subsequent use.
You might want to create one or more users at this point and grant privileges.
To work with SQL*Plus, you need to unzip the wallet zip file. Make a directory to hold the wallet files. Download the zip file and unzip it in this directory. Then update sqlnet.ora to point to the wallet directory. Finally, set TNS_ADMIN=directory in the environment.
edit sqlnet.ora with your favorite text editor and add
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/tmp/demoatp")))
export TNS_ADMIN=/tmp/demoatp (or set TNS_ADMIN=/tmp/demoatp)
This setup will allow connecting to the database using sqlplus (or sqlplus.exe) with an entry from the “tnsnames.ora” file in the wallet directory.
SQL*Plus: Release 22.214.171.124.0 - Production on Mon Dec 7 19:57:52 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Mon Dec 07 2020 11:11:33 -05:00
Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 – Production
See https://blogs.oracle.com/weblogicserver/atp-database-use-with-weblogic-server-v2 for a description of connecting to the database using WebLogic Server.