Database Configuration - Oracle

It is assumed that an Oracle database server is installed.

<FMEServerDir> is the installation directory of FME Server. In Windows, this is typically C:\Program Files\FMEServer.

If you are upgrading, you should back up any job history you want to keep.

Note: If your version of Oracle requires a service name, rather than session ID (SID), for database connections, you must update the DB_JDBC_URL parameter in configuration file fmeCommonConfig.txt accordingly. For more information, see this FME Knowledge Center article.

ClosedWindows

This section describes how to configure an Oracle database. You can configure the Oracle database from other Oracle database tools depending on your personal preference.

For an Oracle database configuration, various SQL scripts are provided to help.

These SQL scripts are located in the <FMEServerDir>\Server\database\oracle directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

  • oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.

    • Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

    CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

    Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

    ALTER SESSION SET CONTAINER=fmeserver;

    CREATE USER fmeserver IDENTIFIED BY fmeserver;

  • oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
  • oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server database user.
Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

  1. Install Oracle SQL*Plus, if you haven’t already.
  2. Open Oracle SQL*Plus and log on as a user with adequate privileges, such as SYSDBA.

    3. In this example, the following parameters were used and entered:

    • User: SYS AS SYSDBA
    • Password: fmeserver
    • String: XX-ORACLE11G:1521/ORCL AS SYSDBA

  3. Create an FME Server database user as follows:
    • From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

      • @”<FMEServerDir>\Server\database\oracle\oracle_createUser.sql”

      Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

      CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

      Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

      ALTER SESSION SET CONTAINER=fmeserver;

      CREATE USER fmeserver IDENTIFIED BY fmeserver;

      By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. The default TABLESPACE is the Oracle DB configured default. If the created user doesn’t have read/write permissions on the default TABLESPACE, change the default TABLESPACE or create an appropriate TABLESPACE.

      This command creates a TABLESPACE on the Oracle DB server called FMESERVER shown in the following example:

      CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE 'E:\ORADATA\FMESERVER01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

      By default this command uses the TABLESPACE “FMESERVER” as shown in the following example:

      CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";

  4. When an FME Server database user is created, you can connect to it by logging on again to SQL*Plus.

    5. In this example, the following parameters were used and entered on the user interface:

    • User: fmeserver
    • Password: fmeserver

    Note: The preceding example assumes your user name and password are fmeserver.

  5. When connected to the FME Server database, run the oracle_createDB.sql script by entering the following command:

    6. @”<FMEServerDir>\Server\database\oracle\oracle_createDB.sql”

Note: If you drop the FME Server database and login user, run the oracle_dropUser.sql script.

ClosedLinux

Oracle SQL*Plus is a freeware client for connecting to an Oracle database. You can download it from the Internet using the following address:

http://www.oracle.com/technology/software/tech/sql_plus/index.html

For more information, see the Oracle web site at:

http://www.oracle.com/technology/sample_code/tech/sql_plus/htdocs/demobld.html

The instructions that follow describe how to configure Oracle for use with FME Server. These instructions describe using SQL*Plus for the database setup. Depending on your personal preference, you can also configure the Oracle database using other Oracle tools.

For Oracle configurations, various SQL scripts are provided to help with Oracle database configuration. These SQL scripts are located in the <FMEServerDir>/Server/database oracle/ directory of the FME Server Core machine.

Note: This directory refers to the machine on which the FME Server Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

  • oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.

    • Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

    CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

    Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

    ALTER SESSION SET CONTAINER=fmeserver;

    CREATE USER fmeserver IDENTIFIED BY fmeserver;

  • oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
  • oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server database user.
Using the Command Prompt

To use the command prompt to configure the database, follow these instructions:

  1. Install the Oracle Client Tools, which includes SQL*Plus, if you haven’t already.

  2. Run sqlplus and log on as a user with adequate privileges, such as SYSDBA.

    For example:

    sqlplus SYS/<password>@<Host>:<Port>/<service> AS SYSDBA

  3. Create an FME Server database user. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

    @”<FMEServerDir>/Server/database/oracle/oracle_createUser.sql”

    Note: If oracle_createUser.sql returns an error on Oracle Database 12c, modify the script to allow a common user to be created under the root container, as follows:

    CREATE USER C##fmeserver IDENTIFIED BY fmeserver;

    Alternatively, modify the script to create a local user in a pluggable database (PDB) container. For example:

    ALTER SESSION SET CONTAINER=fmeserver;

    CREATE USER fmeserver IDENTIFIED BY fmeserver;

    By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. The default TABLESPACE is the Oracle DB configured default.

    If the created user doesn’t have read/write permissions on the default TABLESPACE, you need to change the default TABLESPACE or create an appropriate TABLESPACE.

    This command creates a TABLESPACE on the Oracle DB server called FMESERVER shown in the following example:

    CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE '/ORADATA/FMESERVER01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

    By default this command uses the TABLESPACE “FMESERVER” shown in this example:

    CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";

  4. When an FME Server database user has been created, you can connect to it by logging on again to SQL*Plus or by entering the following command:

    CONNECT fmeserver/fmeserver@<Host>:<Port>/<service>;

  5. When connected to the FME Server database, run the oracle_createDB.sql script by entering the following command:

    @”<FMEServerDir>/Server/database/oracle/oracle_createDB.sql”