LIDS Application Server Database Installation Guide - Oracle

This guide details the steps required to install the Oracle database backend for the LIDS Application Server. It covers prerequisites, file organization, configuration, and execution of the installation scripts.

---

1. Prerequisites

Before proceeding, ensure the following requirements are met:

• Oracle Database A running Oracle instance (e.g., ORCL).

• Privileged Access Access to the SYS user (SYSDBA) is required for tablespace and user creation.

• SQL Tool A command-line tool to execute the scripts.

---

2. Directory Structure

Currecntly, the installation scripts for each database are stored inside the .ward archive of lids-as. So please extract the oracle folder from the lids-as.war/sql/oracle path and place it in a convenient location on your database server. Ensure your installation files are organized as follows to maintain script path integrity:

oracle/
├── dbclear.sql                 # Database cleanup script
├── dbinstall.sql               # System tables installation
└── create-lids-user/
    ├── create-lids-user.sql    # The main entry point script
    └── scripts/
        ├── create-license-tables.sql
        ├── create-license-user.sql
        ├── create-lids-tablespace.sql
        ├── create-lids-user.sql
        ├── create-security-tables.sql
        ├── create-temporary-tablespace.sql
        ├── drop-license-tables.sql
        └── drop-security-tables.sql

---

3. Installation scripts overview

The installation scripts for creating LIDS database contains several parts:

Creating a temporary tablespace

It is a good idea to create separate temporary tablespace for a LIDS project. Oracle Spatial uses a lot of structures in the temporary tablespace for many spatial operations. Creating the temporary tablespace is not mandatory, you can use a default temporary tablespace (e.g. TEMP).

Parameters:

• Temporary tablespace name
• Path to a DBF file
• Initial size of a tablespace
• Size of next extent
• Log directory for logging script creation

Creating a tablespace for LIDS data user

Creates tablespace with specified datafile and size. Always a separate tablespace should be created for a single data user. If you share a tablespace with more projects, you may experience troubles when exporting/importing data.

Parameters:

• Tablespace name
• Path to a DBF file
• Initial size of a tablespace
• Size of next extent
• Log directory for logging script creation

tip

In case of project with large amount of data, it is recommended to create 4 separate tablespaces for Oracle Database located on 4 separate drives:

• For redologs
• For UNDO and TEMP tablespaces
• For data tablespace
• For index tablespace

Create LIDS user

Creates LIDS data user and mandatory structures in the data user (LIDS_DB_INFO view).

Parameters:

• LIDS data user name
• LIDS data user password
• Data tablespace size
• Temporary tablespace size
• Log directory for logging script creation

caution

It is recommended to change login and password.

Install security tables

Creates several security tables and default user accounts (user administrator, group LIDS 7 Users, group Administrators).

Parameters:

• LIDS data user name
• LIDS data user password
• Oracle alias (for Oracle Client)
• Log directory for logging script creation

Install tables for license server

Creates two tables for persisting information about user sessions.

Tables for license server can be installed into the LIDS data user in case that only single LIDS project will be connected to the license server. If multiple LIDS projects share a common license server, it is recommended to install license tables into a separate db user. In such case you can use script Create license user (see below).

Parameters:

• License server database user name
• License server database user password
• Oracle alias (for Oracle Client)
• Log directory for logging script creation

Create license database user (optional)

Creates a database user for license server. Use only when multiple LIDS projects share a single license server.

Parameters:

• License server database user name
• License server database user password
• Data tablespace size
• Temporary tablespace size
• Log directory for logging script creation

---

4. Configuration (Critical Step)

Before running the installation, you must open create-lids-user.sql in a text editor and update the parameters to match your specific environment.

Edit create-lids-user.sql:

Find the lines starting with @scripts/ and modify the following values:

1. Tablespace Paths: Change c:/oracle/oradata/orcl/test1.dbf to your actual Oracle data directory.
2. Usernames & Passwords: Change TEST1 to your desired schema name and password
3. Database Alias: Change ORCL to your actual Oracle Service Name/SID.

Section	Parameter to Change	Purpose	Example Value
Temp Tablespace	TEMP_TEST1	The name of the temporary workspace.	TEMP_LIDS
Data Tablespace	TEST1	The name of the LIDS data storage.	LIDS_DATA
LIDS User	TEST1 TEST1	The Username and Password for the app.	LIDS_DEV LIDS_DEV
Security	ORCL	The TNS Alias/Service name for connection.	LIDS_DEV

---

5. Installation Instructions

The installation is performed in two primary phases.

Phase 1: Environment & Identity Setup (SYS User)

Log in to Oracle database using the SQL Tool as SYS AS SYSDBA.

# Example using SQL*Plus, the command may vary based on your SQL tool
sqlplus sys/your_sys_password AS SYSDBA 

Run the master configuration script from the root oracle/ directory:

@create-lids-user/create-lids-user.sql

This script automates the following:

1. Tablespace Creation: Creates a temporary tablespace and a permanent LIDS tablespace.
2. User Provisioning: Creates the LIDS database user and grants necessary roles (CONNECT, CREATE TABLE, etc.).
3. Security Framework: Connects as the new user to install identity management tables (Users, Groups, Permissions).
4. License Server: Installs tables required for the License Server.

Phase 2: Core System Installation (LIDS User)

Once the user is created, log in as the newly created LIDS User to install the application core:

@dbinstall.sql

This script performs the following:

• Defines custom spatial object types
• Initializes over 20 system sequences for unique IDs.
• Creates core application tables (LIDS_SETTINGS, LIDS_AUDIT, LIDS_DOCUMENT).
• Sets up the lids_db_info view for version tracking.

---

6. Verification

After installation, verify the database state by running these queries as the LIDS user:

1. Check System Version:

SELECT * FROM lids_db_info;

2. Verify security tables:

SELECT table_name FROM user_tables WHERE table_name LIKE 'SEC_%';
-- Should return tables like SEC_USER, SEC_GROUP, SEC_PERMISSION.

3. TODO: Add more verification queries as needed.

---

7. Default Credentials

The installation script creates a default administrator account:

• ID: 0
• Username: ADMINISTRATOR
• Initial Role: LIDS 7 Administrators
• Security Status: Enabled

The account name and password can be changed to any value after installation.

warning

The user with ID=0 is required for certain internal system operations and must never be deleted.

caution

Changing the default password is strongly recommended. Do so immediately via the Security Manager after the first login.

---

8. Troubleshooting

• Tablespace Errors: Ensure the directory path provided for the .dbf file exists on the server's physical disk before running the script.
• SPOOL Errors: If logs are not generating, verify that the ./log folder exists in your execution directory.
• Privilege Errors: If user creation fails, ensure you are logged in with the SYSDBA role, as standard users cannot create tablespaces or users.

9. Frequently Asked Questions (FAQs)

Which character set of Oracle database should be used?

LIDS is able to work with a database in any character set that is supported by Oracle JDBC drivers. Make sure that you have installed correct Oracle JDBC drivers that support your character set. We recommend to use UTF-8 encoding (Unicode AL32), you will avoid possible problems with special characters.

Do I need UTL_FILE_DIR during installation? (not sure if this is needed ???)

No, LIDS installation scripts do not require UTL_FILE_DIR.

Should I create a temporary tablespace for LIDS user?

Yes, create a separate temporary tablespace for LIDS user. Temporary tablespace is heavily used by Oracle spatial functionality

What is the recommendation for spatial indexes?

There are following tips how to tune spatial indexes:

• Use a separate tablespace for indexes (stored on a different HDD than a data tablespace)
• Define correct range of data into the USER_SDO_GEOM_METADATA view
• Set correct precision for an index in the USER_SDO_GEOM_METADATA view
• Rebuild (spatial) indexes. You can do it in LIDS AS console.

Are there any differences between Oracle Data Pump and classical Oracle Imp/Exp utility?

Yes, Oracle Data Pump have more possibilities than the classical Imp/Exp utility (e.g. renaming tablespace, regenerating OID, …). Oracle Data Pump is faster. You must perform export/import using data pump on the server machine. You can export data with data pump from a remote database over database link. You cannot use classical imp utility to import dump file exported using data pump and vice versa.

I'm trying to import data from a database with a single byte code page (e.g. WE8ISO8859P15) to a database with Unicode (e.g. AL32UTF8)?

The problem is that a text in UTF-8 encoding can be longer than in single byte encoding. You can specify CHAR or BYTE parameter when creating a database column. If nothing is specified a database parameter NLS_LENGTH_SEMANTICS is used. By default it is set to BYTE. When you are importing data some texts will not fit the VARCHAR2 column specified by BYTE. You can get these errors:

• ORA-19011: Character string buffer too small
• ORA-01401 inserted value too large for column

The solution is to create a SQL script that will contain a create command for destination tables. Add CHAR parameter for problematic columns. Then create tables (without enabling constraints) and then import data with IGNORE=Y of import command.