Guide to configuring an Oracle database for a LANSA installation

Date:17 July 2009
Product/Release:Visual LANSA V11 SP5 or later
Abstract:A guide to the configuration of an Oracle database for use by a Visual LANSA installation (either development or production).
Submitted By:LANSA Technical Support
External(s):Oracle (supported versions), Windows, Linux.
Updated:12 November 2011

Update (21/10/2011):

This tip has been updated to include additional privileges required by the LANSA_TABLE_CREATOR role, and also steps to avoid using the USERS or TEMP tablespaces. If you have previously used this guide to configure an Oracle database for use with LANSA, you should make sure the required privileges are added as they may be necessary for future EPC/Service Pack installation.

Description:

The following instructions should be used when installing Visual LANSA (SP5 or later) against an Oracle database (refer to the Supported Platforms guide for details on the current supported Oracle version).

  1. Install/Update Oracle Database Server
    Only required, if not already available. Update to the appropriate level currently supported by Oracle.

    Note that the setting up of an Oracle database system should ideally be carried out by an administrator with a good knowledge of the workings of Oracle. Experienced Oracle administrators may wish to browse through the following steps, but will not need to follow them exactly as they are fairly standard procedures.

    At this stage, it is worth taking at least a cursory look at the online documentation for Installation that comes with Oracle Database. A novice to Oracle will find this information very useful, particularly the information on setup procedures, and it is worth referring to the appropriate sections while performing each of the following steps.
     
  2. Create a database and service name for the LANSA Installation

    Preferably use one of the standard templates available (for example, General Purpose). If the default Character Set is not appropriate, select an appropriate Character Set for your environment. Note: When selecting Database Character Set, do NOT use Unicode.

    Note: LANSA assumes the USERS and TEMP tablespaces exist.If you do not wish to use the USERS or TEMP tablespace, refer to (5) Optional Instructions.

    Also, ensure you create a service name for the database. This service name is required when configuring the ODBC connection.
     
  3. Setup LANSA-specific Roles for different levels of security

    Remember, these are just guidelines. These role names are not required by LANSA.

    The LANSA_USER role is for general application users, including developers who do not need to rebuild tables or execute imports that build tables.

    CREATE ROLE LANSA_USER;
    GRANT CREATE SESSION, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO LANSA_USER;

    The LANSA_TABLE_CREATOR role is for users (including developers who work on files) who need to rebuild tables, or execute imports that build tables. However, if the table owner (schema) does not yet exist, the LANSA_SCHEMA_CREATOR role is required.

    CREATE ROLE LANSA_TABLE_CREATOR;
    GRANT LANSA_USER, CREATE ANY TABLE, DROP ANY TABLE, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY SEQUENCE, DROP ANY SEQUENCE, CREATE ANY TRIGGER, DROP ANY TRIGGER TO LANSA_TABLE_CREATOR;

    * Items in red denote required privileges that have been added in the latest update to this tip

    The LANSA_SCHEMA_CREATOR role is for installation (including of deployment packages that require schemas to be created if they do not already exist). It is also required when building tables in new partitions, or non-standard libraries, as a new schema is required for each library used in LANSA.

    CREATE ROLE LANSA_SCHEMA_CREATOR;
    GRANT LANSA_TABLE_CREATOR, CREATE USER TO LANSA_SCHEMA_CREATOR;
     
  4. Configure LANSA database users

    Note that LANSA requires database login names and passwords of 10 characters or less. This restriction does not apply when used trusted connections (also known as OS authentication).

    • Option A: All users have same database login

      All users use the same database login. You need to create the user (if it doesn't already exist), and assign the role LANSA_SCHEMA_CREATOR to that user. For example,

      CREATE USER DBA IDENTIFIED BY SQL DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;
      GRANT LANSA_SCHEMA_CREATOR TO DBA;

    • Option B: Different database logins

      Create the user as you normally would.
      Grant the appropriate role (created in step 3) to each user.
       
  5. Optional instructions

    When creating tables, LANSA automatically creates a user for the file library; this user is known as the Collection or Schema: By default the following command is used:

    CREATE USER >C< IDENTIFIED BY >C< DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS

    >C< is replaced by the Collection name. If you do not want to use this command as is, there are two steps to take:

    1. Before the first install, create Collections required for the installation, according to your own requirements. These include:

      Collection LX_DTA required for the repository tables. This is always required.

      Collection XDEMOLIB is required if you plan to include the Demo Application during the Install.

      For environments populated by a Deployment Tool package, create the collection required by your own application tables.

    2. After the install, modify the file X_DBMENV.DAT, located in the x_lansa directory under your installation.

      Find the following line (under DATABASE_TYPE=ODBCORACLE), and modify it so that it creates a new collection according to your own requirements as per (5a), then save the file:

      CMD_CREATE_COLLECTION=CREATE USER >C< IDENTIFIED BY >C< DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS

      From now on, this line will be used when you apply EPCs or later versions of LANSA.
       
  6. Windows-specific Notes
    1. Install 32-bit Oracle Client and Oracle ODBC Driver (available from Oracle) at a version supported by LANSA.

      Do this before the install!

    2. Install Visual LANSA - Configure Data Source option.

      The "Server" to connect to is the Service Name you created in step 2 above.

      Use a database login that has LANSA_SCHEMA_CREATOR role assigned, or equivalent privileges for the database connection.
       
  7. Linux-specific Notes on installing LANSA

    When you install LANSA, you will need the Service Name you created in step 2 above.

    Use a database login that has LANSA_SCHEMA_CREATOR role assigned, or equivalent privileges for the database connection.

    LANSA will install Oracle Instant Client and the Oracle ODBC driver at the appropriate version supported by LANSA.