Oracle Autonomous Data Warehouse: Add Database Connection

Connection requirements for Autonomous Data Warehouses (ADW) differ slightly from other Oracle formats. In addition to installing a specific version of FME, you must also provision the autonomous database, and install and configure the Oracle client.

Install FME 2020.1 or higher

Install FME 2020.1 or higher. This format does not work with FME versions below 2020.1.

Provision Autonomous Database, Install and Configure Oracle Client

Provision an Oracle Autonomous Database – either Autonomous Database Warehouse (ADW) or Autonomous Transaction Processing (ATP) – and download the corresponding credentials.zip file to the system with the FME installation.
- See the Oracle documentation to provision ADW.
- See the Oracle documentation to provision ATP. Also see Downloading Client Credentials (Wallets).

Download the Oracle Instant Client and SQL*Plus Packages to the same system that FME is installed. The next steps will validate that the Oracle Database Client can communicate with your Autonomous Database. Installing it on the same system as FME ensures that FME is also configured correctly.
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="/home/adwc_credentials")))

SSL_SERVER_DN_MATCH=yes

Create an environment variable named TNS_ADMIN. Set it to the location of the secure folder containing the credentials file you saved in step 3.
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 connect username/password@connectString

For example:

sqlplus connect my_username/my_password@my_high

If the connection is successful, you are ready to move to the next step.

Defining the Connection

You can define a connection from several places in the FME Workbench interface:

From the FME Workbench canvas by selecting Tools > FME Options > Database Connections.
From the Add Reader or Add Writer dialogs after selecting an Oracle Autonomous format.
From the Add Reader or Add Writer dialogs after selecting an Oracle Autonomous format and clicking the Parameters button.

Database Connection

Oracle

Name

Enter a name for the new connection. For example, My Oracle Autonomous ADW

Connection Parameters

Connection Mode	Description	Authentication
Service Name (default)	Select this option to connect using Service Name or Easy Connect.	In the Service Name or Easy Connect parameter, enter the same connection parameters you used to connect with SQL*Plus (see instructions above): username/password@connectString where connectString is one of the database service names listed in your tnsnames.ora file.
Cloud Wallet	Connect using a wallet file.	Retrieve client credentials from Oracle as a zip file. The zip file contains network configuration files, wallet-specific files, keystore and trustore files, JDBC connection property files, and wallet expiration information. See Oracle's instructions: Download Client Credentials (Wallets). After you obtain the client credentials: In the Cloud Wallet parameter, browse to the zip file. Retrieve feature types by clicking the browse button in the Wallet Service parameter.

Connection Mode

Description

Authentication

Service Name (default)

Select this option to connect using Service Name or Easy Connect.

In the Service Name or Easy Connect parameter, enter the same connection parameters you used to connect with SQL*Plus (see instructions above):

username/password@connectString

where connectString is one of the database service names listed in your tnsnames.ora file.

Cloud Wallet

Connect using a wallet file.

Retrieve client credentials from Oracle as a zip file. The zip file contains network configuration files, wallet-specific files, keystore and trustore files, JDBC connection property files, and wallet expiration information. See Oracle's instructions: Download Client Credentials (Wallets).

After you obtain the client credentials:

In the Cloud Wallet parameter, browse to the zip file.
Retrieve feature types by clicking the browse button in the Wallet Service parameter.

Username and Password

The username and password to access the Oracle Autonomous database.

Click the Test... button. After a successful connection test, you should be able to connect to your Oracle Autonomous database in FME in the Oracle Autonomous Spatial Object and Oracle Autonomous Non-Spatial formats.