Connect to an Oracle Service Name (not Oracle SID) in FME

Introduction

This article discusses how to connect to an Oracle Database via the Service Name instead of direct connection using the SID value of the Oracle Database.

Oracle Easy Connect

The Easy Connect connection string is the easiest way of connecting to your Oracle database. Use this type of connection string in the Database Connections dialog boxes

user/password@//hostname:port/servicename
user/password@//hostname:port:sid

For example

  scott/tiger@//amidala:1521/orcl
  scott/tiger@//amidala:1521:orcl

Using this string it is not necessary to enter a username or password in the dialog - as these are included in the Easy Connect string.  However, using them in the connect string does make them visible in the FME logfile.

As of FME 2015, these connections are now maintained in the Database Connections file, which can be accessed via Tools > FME Options > Database Connections.

This example uses an Oracle SID (configured in the tnsnames.ora alias for aporacle12)

This example uses an Oracle Easy Connect string with a service name (notice the forward slash after the port)

This example uses an Oracle Easy Connect string with a SID (notice the colon after port)


 

Oracle RAC for Server Oracle Repositories

This also looks at configuring FME Server to use an Oracle Repository that is accessed with the Service Name instead of the SID - for example, if you are using Oracle RAC.

If you intend on using Oracle RAC for your FME Server Database you will need to make a change to the FME Server configuration files. If you are using FME Server 2014 SP2 or newer, the file will be fmeCommonConfig.txt. For FME Server 2014 SP1 or older, the file will be fmeServerConfig.txt.  If using FME Server 2020 or new you must also edit the fmeServerWebApplicationConfig.txt.

In the admin guide, we suggest a couple of URL's: (doc link )
The most common is using this form and usually involves the Service Name:
JDBC_URL=jdbc:oracle:thin:@<host>:1521/<service name>
where <host> is the name of your database server and <service name> is the service name of the database.
Another form is when using the SID of the database.
JDBC_URL=jdbc:oracle:thin:@<host>:1521:<sid>
Where <sid> is the value that the Oracle Listener on the Oracle Server system has been configured for.

If you are connecting to a Service for Oracle RAC its been found this syntax can be used. JDBC_URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)
where <host> is the name of the Oracle RAC system.