Related Documents
Document Properties
Kbid25C904
Last Modified03-Feb-2020
Added to KB11-Aug-2014
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
Product
  • ICM 7.6
  • ICM 7.7
  • ICM 7.8
  • ICM 7.10

Guide - General Database Setup

1 Introduction

This guide is addressed to system administrators/DBAs who have to set up an Oracle database instance for Intershop 7. It does not describe the steps to do in detail, but summarizes the general requirements and common settings that an Intershop 7 cluster expects as preconditions to function properly.

The installation of the Oracle database software and the creation of the database instance for Intershop 7 is performed using Oracle tools. This section summarizes the specific Intershop 7 requirements and outlines the tablespace and database user creation.

For installation options and details on individual installation steps, refer to the respective Oracle documentation, which can be found on the dedicated Oracle devices (CD, DVD, HD, etc.) and on the Oracle web site. The relevant volumes include:

  • Database Installation Guide for Linux
  • Database Client Installation Guide for Linux
  • Database Installation Guide for Microsoft Windows
  • Database Client Installation Guide for Microsoft Windows

1.1 References

2 Oracle Database Software

The following table summarizes the version and edition information of the Oracle software used by Intershop 7.

SoftwareVersionEdition/Components
Oracle Server

11.2.0.3.4 or

12.1.0.1.0

minimum: Standard Edition, Standard Edition One

also supported: Enterprise Edition, Express Edition

Oracle Client

11.2.0.3.0 or

12.1.0.1.0

custom installation including

  • Oracle Database Utilities
  • SQL*Plus
  • Oracle JDBC/THIN Interfaces
  • Oracle Net

When installing the Oracle database software, be aware of the following issues:

  • By default, the Oracle Universal Installer runs in the operating environment's native language. To disable the installation session translation, set NLS_ENABLED = FALSE in the oraparam.ini file, located in the /install sub directory of the Oracle installation package.
  • The client setup process includes the installation of network configuration files (<ora_home>/network/admin/*.*). Note that these files will not be used by Intershop 7. Intershop 7 uses <IS.INSTANCE.SHARE>/system/config/oracle/*.* via setting the TNS_ADMIN environment variable. However, it is recommended to install the network configuration files as this makes it possible to test immediately whether the client can connect to the database.
  • Intershop 7 communicates with the Oracle database in the "dedicated server mode". This means that each client session is served by a dedicated server process (or thread).

3 Oracle Database Instance

This section summarizes the requirements for the Intershop 7 database instance.

3.1 General Requirements

3.1.1 Database Character Set

The required database character set is AL32UTF8. Upon setting up the database instance, use

CREATE DATABASE <DB_NAME>
CONTROLFILE ...
...
CHARACTER SET AL32UTF8
...
DATAFILE ...

To check the character set:

SELECT * FROM v$nls_parameters WHERE
parameter LIKE '%CHARACTERSET%';
PARAMETER                      VALUE
------------------------------------
NLS_CHARACTERSET            AL32UTF8

The character set must be set to AL32UTF8.

3.1.2 Oracle Text

Oracle Text must be installed, and the database user ctxsys must exist. Upon setting up the database instance, use

connect sys as sysdba
@ORACLE_HOME/ctx/admin/catctx <ctxsys_pw> SYSAUX TEMP NOLOCK
 
connect ctxsys/<ctxsys_pw>
REM NLS_LANGUAGE input for
REM dr0defin.sql = AMERICAN|GERMAN|GERMAN DIN|...
REM see ORACLE_HOME/ctx/admin/defaults/dr0defin.sql
@ORACLE_HOME/ctx/admin/defaults/dr0defin.sql AMERICAN

To check Oracle Text:

SELECT username FROM dba_users WHERE username = 'CTXSYS';
USERNAME
-----------
CTXSYS

3.1.3 Open Cursors

The parameter open_cursors must be set to 500. Upon setting up the database instance, use

ALTER SYSTEM SET open_cursors = 500 SCOPE = both;

To check the setting:

show parameter open_cursors

3.1.4 Processes

The recommended number of processes is 150 per application server, that is

150*<#appserver>

Add 20 Oracle background/slave processes and 10 administrative processes on top, that is

150*<#appserver> + 30

Hence, use for one application server, for example, the following command to set up the number of processes:

ALTER SYSTEM SET processes = 180 SCOPE = spfile;

To check the setting:

show parameter processes

3.1.5 Schema Naming

To prevent any malfunctions with the Intershop Application Server, Intershop strongly recommends to use only non quoted identifiers for the name of the database schema. As a consequence, you may not use schema names that, according to Oracle's Database SQL Language Reference, would require to be always quoted.

For example, the schema name 7_IS_DBUSER would not be allowed, as non quoted identifiers must begin with an alphabetic character from your database character set.

3.1.6 Password Security Parameters

For the default profile, the following parameters should be set UNLIMITED:

  • failed_login_attempts
  • password_grace_time
  • password_life_time
  • password_lock_time

Upon setting up the database instance, use

ALTER PROFILE default LIMIT failed_login_attempts UNLIMITED;
ALTER PROFILE default LIMIT password_grace_time UNLIMITED;
ALTER PROFILE default LIMIT password_life_time UNLIMITED;
ALTER PROFILE default LIMIT password_lock_time UNLIMITED;

To check the setting:

SELECT * FROM dba_profiles
 WHERE profile = 'DEFAULT'
 AND LOWER(resource_name) IN
 (
 'failed_login_attempts',
 'password_grace_time',
 'password_life_time',
 'password_lock_time'
 );

3.2 Tablespaces

Intershop 7 requires the following default tablespaces (see also in <IS.INSTANCE.SHARE>/system/config/cluster/orm.properties):

intershop.jdbc.tablespaces.index = IS_INDX
intershop.jdbc.tablespaces.contextIndex = IS_INDX_CTX
intershop.jdbc.tablespaces.users = IS_USERS
intershop.jdbc.tablespaces.temp = IS_TEMP

To create the Intershop 7 tablespaces:

connect sys as sysdba
 
DEFINE _ts_temp     = IS_TEMP
DEFINE _ts_user     = IS_USERS
DEFINE _ts_indx     = IS_INDX
DEFINE _ts_indx_ctx = IS_INDX_CTX
DEFINE _ts_size     = 1G
DEFINE _uni_size    = 2M
DEFINE _sys_ts      = SYSTEM
 
-- Determine the file system path for the system tablespace and
-- create the Intershop tablespace files within this location.
 
COL system_ts_path NEW_VALUE path
SELECT regexp_substr(df.name, '^(.*)[\\/]') AS system_ts_path
  FROM v$tablespace ts
  JOIN v$datafile df ON (df.ts#=ts.ts#)
  WHERE UPPER(ts.name) = '&_sys_ts';
 
CREATE TEMPORARY TABLESPACE &_ts_temp TEMPFILE '&path.&_ts_temp._01.dbf'
  SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE &_uni_size;
 
CREATE TABLESPACE &_ts_user DATAFILE '&path.&_ts_user._01.dbf'
  SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE TABLESPACE &_ts_indx DATAFILE '&path.&_ts_indx._01.dbf'
  SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE TABLESPACE &_ts_indx_ctx DATAFILE '&path.&_ts_indx_ctx._01.dbf'
  SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

All tablespaces are referred to by the orm.properties file (located in <IS.INSTANCE.SHARE>/system/config/cluster) by the key intershop.jdbc.tablespaces.*. IS_TEMP and IS_USERS are also referred to when creating the database user.

3.3 Miscellaneous Settings

3.3.1 Intershop 7 Database User

At least one database user is required. It is referred to by the orm.properties file (located in <IS.INSTANCE.SHARE>/system/config/cluster) by the following keys:

intershop.jdbc.user = <user>
intershop.jdbc.password = <password>

Note

Make sure to consider the user naming conventions. For details, refer to Schema Naming.

To create a database user for Intershop 7:

connect sys as sysdba

DEFINE _us          = intershop
DEFINE _pw          = intershop
DEFINE _ts_temp     = IS_TEMP
DEFINE _ts_user     = IS_USERS
DEFINE _ts_indx     = IS_INDX
DEFINE _ts_indx_ctx = IS_INDX_CTX

CREATE USER &_us
  IDENTIFIED BY &_pw
  DEFAULT TABLESPACE &_ts_user
  TEMPORARY TABLESPACE &_ts_temp
  PROFILE DEFAULT ACCOUNT UNLOCK;

ALTER USER &_us DEFAULT ROLE ALL;

GRANT CONNECT                     TO &_us;
GRANT RESOURCE                    TO &_us;
GRANT CTXAPP                      TO &_us;
REVOKE UNLIMITED TABLESPACE       FROM &_us;
ALTER USER &_us QUOTA UNLIMITED   ON &_ts_user;
ALTER USER &_us QUOTA UNLIMITED   ON &_ts_indx;
ALTER USER &_us QUOTA UNLIMITED   ON &_ts_indx_ctx;
GRANT CREATE CLUSTER              TO &_us;
GRANT CREATE DATABASE LINK        TO &_us;
GRANT CREATE SEQUENCE             TO &_us;
GRANT CREATE SYNONYM              TO &_us;
GRANT CREATE TABLE                TO &_us;
GRANT CREATE VIEW                 TO &_us;
GRANT CREATE PROCEDURE            TO &_us;
GRANT CREATE TRIGGER              TO &_us;
GRANT CREATE TYPE                 TO &_us;
GRANT CREATE SNAPSHOT             TO &_us;
GRANT ANALYZE ANY                 TO &_us;
GRANT EXECUTE ON dbms_streams_adm TO &_us;

connect ctxsys

GRANT EXECUTE ON ctx_ddl          TO &_us;

More complex Intershop 7 deployments may require additional database users. For example, in data replication scenarios, an additional database user is required for the source system, referred to by the staging.properties file (located in <IS.INSTANCE.SHARE>/system/config/cluster) by the key staging.editing.schema.name.

3.3.2 JDBC Settings

JDBC settings are required to configure the database server access. The relevant keys (in the orm.properties file located in <IS.INSTANCE.SHARE>/system/config/cluster) include:

intershop.jdbc.url = jdbc:oracle:thin:@//<db-hostname>:<db-port>/<db-servicename>
intershop.jdbc.portNumber = <db-port>
intershop.jdbc.databaseName = <db-servicename>
intershop.jdbc.driverType = thin
intershop.jdbc.networkProtocol = tcp

Note

These properties are set upon deploying Intershop 7.

3.3.3 TNS Alias

In the file tnsnames.ora (located in <IS.INSTANCE.SHARE>/system/config/oracle), adjust the following properties for ISSERVER:

host = <db-hostname>
port = <db-port>
service_name = <db-servicename>

For example:

ISSERVER.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <db-hostname>)(PORT = <db-port>))
    (CONNECT_DATA = (SERVICE_NAME = <db-servicename>))
  )

Note

These properties are set upon deploying Intershop 7.

3.3.4 Oracle Client Home

The file intershop.properties (located in <IS.INSTANCE.LOCAL>) must specify the path to the Oracle client home for Intershop 7.

ORACLE_HOME = <ORACLE.CLIENT.DIR>

Note

These properties are set upon deploying Intershop 7.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Tickets