Connecting to Oracle Autonomous Databases

Introduction

In FME 2020.1, we added the ability to connect to Oracle Autonomous Databases. In this article, learn how to set up the database connection on your computer and in FME to be able to connect to Oracle Autonomous Databases. If you encounter any errors, follow the troubleshooting steps at the bottom of this article. Once you have created your connection, learn how to read and write with Oracle Autonomous. To upload credentials to FME Server, see Using Oracle Wallet in FME Server. This tutorial has two methods to connect to Oracle Autonomous; using the wallet credentials or using TNSNames. 

Requirements

• Oracle Client and SQL*Plus Packages 18.5 or higher
• FME Desktop 2020.1 or higher

Configuration Steps

Connecting with an Oracle Wallet Credential Zip File 

This method is only available in FME Desktop 2021.0 or higher. 

1. Provision Oracle Autonomous Database
Provision Oracle Autonomous Database, either Autonomous Database Warehouse (ADW) or Autonomous Transaction Processing (ATP) and download the corresponding credentials.zip file to the same system that has FME installed. For the Oracle documentation to provision ADW click here. For the Oracle documentation to provision ATP, click here. Also, check Downloading Client Credentials (Wallets).

2. Open FME Workbench 2021.0+
In FME Workbench 2021.0+, navigate to Tools > FME Options (Windows/Linux) For Mac navigate to FME Workbench > Preferences. 


3. Create a New Connection
In the FME Options window, switch to Database Connections and then click on the plus sign “+” to create a new connection. 


In the Add Database Connection dialog, change the Database Connection to Oracle then name the connection (eg. Oracle Autonomous).
Then change the Connection Mode to Cloud Wallet. Click on the ellipsis next to Cloud Wallet File and browse to your zipped credentials. After uploading the credentials, click on the ellipsis next to Wallet Service and select the service you wish to use. Depending on your security you may need to enter a username and password. Once the credentials have been created, click OK to save them. 



This connection can now be used in any of the Oracle Autonomous readers and writers. If you would like to upload the credentials to FME Server, please see Using Oracle Wallet in FME Server .

Connecting with TNSNames 

This method can be used for FME 2020.1 or higher. If using FME 2021.0 or higher, connecting with the credentials zip file is the preferred method. 
 

Part 1: Setting up Oracle

1. Provision Oracle Autonomous Database

Provision Oracle Autonomous Database, either Autonomous Database Warehouse (ADW) or Autonomous Transaction Processing (ATP) and download the corresponding credentials.zip file to the same system that has FME installed. For the Oracle documentation to provision ADW click here. For the Oracle documentation to provision ATP, click here. Also, check Downloading Client Credentials (Wallets).

2. Download Oracle Instant Client

Download the Oracle Instant Client and SQL*Plus Packages to the same system that FME is installed. For instructions on how to do this and troubleshooting steps, see the article How to setup an Oracle Instant Client for use with FME.

3. Update Credentials

Uncompress credentials.zip file into a secure folder. In the uncompressed folder, edit the sqlnet.ora file, replacing “?/network/admin” with the name of the folder containing the client credentials. For example:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\instantclient_19_6\credentials")))
SSL_SERVER_DN_MATCH=yes

4. Create Environment Variable

To edit an environment variable, go to your computer’s control panel > System and Security > System > Advanced System Settings. In the System Properties dialog, switch to the Advanced tab then click on Environment Variables.

Create a new environment variable named TNS_ADMIN then set it to the secure folder containing the credentials file that was set up in Step 3.

5. Test the Oracle Client

Test the Oracle Client with Oracle SQL*Plus. The tnsnames.ora file in the uncompressed folder contains either three or five database service names identifiable as either high, medium and low, or high, medium, low, tp and tpurgent (the service names presented depend on whether your database is ADW or ATP). The predefined service names provide different levels of performance and concurrency for your database. Use one of these service names (for example, ‘my_high’) as your ConnectString. For more information on these service names, click here for ADW databases, and here for ATP databases.

The command to test your connection with SQL*Plus will follow this format:

sqlplus username/password@connectString

For example:

sqlplus my_username/my_password@database_low

Connecting on Mac OS

When connecting to Oracle on Mac OS, please review the following articles for further instructions:

• To allow FME to see the Oracle Client:

  • Mac OS Catalina, FME 2020 and Oracle Client

• To allow FME to see the TNS_ADMIN environment variable

  • FME, macOS and the Oracle Client - a tip for setting the TNS_ADMIN Env Variable

Part 2: Configure FME to Connect with Autonomous Databases

1. Open FME Workbench 2020.1+

In FME Workbench 2020.1+, navigate to Tools > FME Options (Windows/Linux). For Mac navigate to FME Workbench > Preferences

2. Create a New Connection

In the FME Options window, switch to Database Connections and then click on the plus sign “+” to create a new connection.

In the Add Database Connection dialog, switch the Database Connection to Oracle then name your connection Oracle Autonomous.

For Service Name or Easy Connect type in your Connection String that you used in the Command Line.

my_username/my_password@database_low

Once your Oracle connection parameters are set, click Test to confirm the connection. If you were successful in Part 1 Step 5, this should work. If not, see the bottom of this article for troubleshooting steps.

After a successful connection test, you should now be able to connect to your Oracle Autonomous database in FME using the Oracle Autonomous Spatial Object and Oracle Autonomous Non-Spatial readers & writers. Please see Reading and Writing Oracle Autonomous Databases for instructions on how to use this connection.

 

Troubleshooting

The first step in troubleshooting is to close and restart both your command line and FME after any changes to files, or environment variables have been made. After a restart, if you are still experiencing an error, follow the steps below to resolve. If you have followed the steps below and are still getting an error, contact Safe Support.

sqlnet.ora set up incorrectly

If the sqlnet.ora file is set up incorrectly or missing, you will get the error ORA-28759. If this occurs, go back to Part 1 Step 3 and repeat the steps.

Missing TNS_ADMIN Environment Variable or Database Name

If the TNS_ADMIN environment variable is missing or set to the incorrect path, you will get the error ORA-12154. If this error occurs, ensure that you created the TNS_ADMIN environment variable according to Part 1 Step 4.

Also, if the database name is incorrect in your connection string, you will see this error. Review your tnsnames.ora file and Part 1 Step 5 to correct this.

Incorrect Username or Password

If your username or password in the connect string is incorrect, you will get the error ORA-03113 or ORA-00600. If this occurs, confirm that you entered the correct username or password. If you have the correct username or password, but you are still seeing these errors, speak to your database administrator.

Instant Client Missing

If you do not have Oracle Instant Client installed or the PATH environment variable isn’t set up, you will get the error Oracle client libraries could not be loaded. To resolve this, follow the instructions in How to setup an Oracle Instant Client for use with FME