Document Properties
Kbid
K22983
Last Modified
16-Dec-2021
Added to KB
09-Jan-2012
Public Access
Everyone
Status
Online
Doc Type
Guidelines
Product
ICM 7.10
Guide - Setup Oracle XE as Intershop Development Database (valid to 7.10)

Table of Contents


Introduction

The installation of the Oracle database software and database instance, especially XE, is not part of the Intershop deployment tools, but is performed by the Oracle setup routine. This document describes how to configure Oracle Database XE as well as Intershop 7 to be used as a development database.

For installation options and details on each individual installation step, refer to the respective Oracle Database Express Edition documentation:

The Oracle Express Edition (XE)

Known XE Versions

  • Oracle Database 18c Express Edition Release 18.4.0.0.0
  • Oracle Database 11g Express Edition Release 11.2.0.2.0

Recommended XE Version

Intershop Version

Oracle XE Version

6.4

10gR2

6.6.x

11gR2

7.x

11gR2

7.3+11gR2 or 18c (development only)

Limitations

Oracle Database XE can be installed on a host machine with any number of CPUs (one database per host), but is limited to

  • 11 GB of user data,
  • 1 GB RAM (SGA and PGA combined) and
  • 1 CPU for foreground processes.

Oracle Database XE 18c increased these limits to

  • 12 GB of user data,
  • 2 GB of RAM (SGA and PGA combined),
  • 2 CPUs for foreground processes and
  • 3 pluggable databases (PDB).

Installation Steps to Repeat

Download

Download the current version of Oracle XE Edition from:

18c

11gR2

Installation

Windows

Requirements

Requirement

Value

System Architecture

  • Intel (x86), AMD64, and Intel EM64T

  • AMD64 and Intel EM64T

Operating System

certified Windows, XE Installation Guide for Microsoft Windows
or Microsoft Windows 7 64-bit

Setup File

64-bit: OracleXE112_Win64.zip

32-bit: OracleXE112_Win32.zip

Setup Modes

Installation of XE

Installation of XE Version 11g Release 2 (11.2)

For the Oracle certified systems refer the complete documentation Oracle® Database Express Edition Installation Guide 11g Release 2 (11.2) for Microsoft Windows on Database Express Edition Documentation.

The following steps to repeat describes the latest Oracle Database 11g Express Edition 11.2 installation on Microsoft Windows 7 64-bit:

  • Only one XE installation per system is possible, the XE 11gR2 installer stops with "... existing XE 11g service found!"
  • To avoid trouble with tasks as user sys as sysdba or system see below, please use the password: intershop



For Windows 64-bit ignore the 32-bit setup error:

The installer is unable to instantiate the file
C:\Users\...\AppData\Local\Temp{...-...-...-...-...}KEY_XE.reg.
The file does not appear to exits.

How to display the Oracle Database XE Home Page in your default browser?

See Section 1.2, "Database Home Page" within Oracle Database Express Edition Getting Started Guide

Usage:

http://127.0.0.1:<port>/apex/f?p=4950, e.g. <port> = 8080
http://127.0.0.1:8080/apex/f?p=4950

Home

Storage (default)

Installation of XE Version 10g Release 2 (10.2)
  • Only one XE installation per system is possible, the XE 10gR2 installer stops with "... existing XE 10g service found!"
  • To avoid trouble with tasks as user sys as sysdba or system see below, please use the password: intershop



Linux x86-64

Requirements

Requirement

Value

System Architecture

x86-64

Operating System

Certified Linux, XE Installation Guide for Linux x86-64
or other free Linux distributions like:

Setup File64-bit: oracle-xe-11.2.0-1.0.x86_64.rpm.zip

Swap Space

Minimum swap space is 2 GB or twice the size of RAM,
whichever is less.

Installation of XE Version 11g Release 2 (11.2)

For the Oracle certified systems refer to the complete documentation of Oracle® Database Express Edition Installation Guide 11g Release 2 (11.2) for Linux x86-64 on Database Express Edition Documentation.

The following steps to repeat describe the latest Oracle Database 11g Express Edition 11.2 installation for the following Linux systems:

Linux x86-64
System

uname -a

/etc/release

OpenSUSE 11.4

Linux ... 2.6.37.1-1.2-desktop ... x86_64 ... GNU/Linux

/etc/SuSE-release cat /ec
openSUSE 11.4 (x86_64)
VERSION = 11.4
CODENAME = Celadon

CentOS 5.8

Linux ... 2.6.18-308.13.1.el5 ... x86_64 ... GNU/Linux

/etc/redhat-release
CentOS release 5.8 (Final)

host:~ # unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

Archive: oracle-xe-11.2.0-1.0.x86_64.rpm.zip
   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

host:~ # cd Disk1

host:~ # rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

Preparing...   ########################################### [100%]
   1:oracle-xe ########################################### [100%]
Executing post-install steps...

You must run '/etc/init.d/oracle-xe configure'
  as the root user to configure the database.

host:~ # /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after

initial configuration: *********
Confirm the password:  *********

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

Check the running database, e.g., as user oracle.

host:~ # su - oracle

oracle@host:~> . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
oracle@host:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 04:36:25 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

oracle@host:~>

Download Oracle Connection Jar Files (ojdbc6.jar and ucp.jar) for Intershop Appserver

Intershop 7 requires the Oracle JDBC driver version 11.2.0.3.0 as well as the Oracle Universal Connection Pool version 11.2.0.3.0.
The respective files (ojdbc6.jar, JDBC thin driver for use with JDK 1.6, and ucp.jar, classes required for the universal connection pooling feature) can be downloaded from the Oracle Web site.

ojdbc6.jar and ucp.jar version 11.2.0.3.0 are not included with the Oracle XE:

ojdbc6.jar and ucp.jar are not included with the Oracle XE installation, only the otn.oracle.com.
See also: Guide - Oracle JDBC Universal Connection Pool (UCP) (valid to 7.4)

To make sure you are using the correct version of the files, check the Manifest.mf files contained in ojdbc6.jar and ucp.jar. The specification version must be 11.2.0.3.0.

Linux x86-64 - Add the Intershop Appserver user isas1 to the Oracle dba group

usermod -aG dba isas1

Setup or Configure Intershop Application Server Database Properties

Oracle XE Server as Client for the Intershop Appserver:

During the Intershop setup the installer prompts for the Oracle Client. It is possible and recommended to use the Oracle XE Server also as Oracle Client regarding the Intershop Application Server.

Check and update the files orm.properties, tnsnames.ora and intershop.properties.

IS_SHARE/system/config/cluster/orm.properties

Used Tablespaces:

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

JDBC Url and others:

intershop.jdbc.url=jdbc:oracle:thin:@localhost:1521:XE
intershop.jdbc.portNumber=1521
intershop.jdbc.driverType=thin
intershop.jdbc.networkProtocol=tcp
intershop.jdbc.databaseName=XE

# used for DataSource access via JDBC Driver and Oracle
# tools support via ant tasks and SQL*Plus calls from
# dbinit and dbmigrate. It maps to tnsnames.ora
# default: ISSERVER.world
intershop.jdbc.serverName=ISSERVER.world

Intershop Database User:

intershop.jdbc.user=intershop
intershop.jdbc.password=intershop

IS_SHARE/system/config/oracle/tnsnames.ora

Change the following properties for ISSERVER TNS-alias:

# Example:
#	host=localhost
#	port=1521
#	sid=XE

ISSERVER.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SID = XE))
  )

IS_HOME/intershop.properties

Change the following properties, if you plan to use or switch to the XE 11gR2 as Oracle Client for Intershop:

ORACLE_HOME=<ORACLE_XE_11gR2_HOME>

Examples:
Windows: ORACLE_HOME=c:/oraclexe/app/oracle/product/11.2.0/server
Linux: ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

Tasks Running as Oracle User sys and ctxsys with SQL*Plus

Automated, Script-Based Setup with dbsetup.bat|sh

Oracle Database Express 18c creates a container database (CDB) by default, which is not supported by the scripts used in this section. You have to setup the database manually.

  • Open a normal command line or Intershop "ANT Build Shell".
  • Set environment variables ORACLE_HOME (only for normal shell).
  • Change the directory to IS_HOME/tools/dbinit/bin (only server with version 7 and higher).
  • Start the script dbsetup.bat or dbsetup.sh.
  • Restart the database.

For Enfinity installations without the dbsetup tool use the following attachment.

How to Start

dbsetup.bat

set ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server
cd <eserver>\tools\dbinit\bin
dbsetup.bat

dbsetup.sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
cd <eserver>/tools/dbinit/bin
chmod 700 dbsetup.sh
./dbsetup.sh

Running with Introduction Output

The dbsetup.bat|sh script configure existing database instances
running as Oracle Express (XE), Standard / One (SE) or Enterprise Edition (EE)
to allow the Intershop application server to store all persistent data.

Requirements for the existing database instance are:

  * created with CHARACTER SET = AL32UTF8
  * installed Text component (user ctxsys)   ORACLE_HOME/ctx/admin/catctx.sql
    and language-specific default preference ORACLE_HOME/ctx/admin/defaults/dr0defin.sql

Usage: dbsetup.bat|sh [<sys_username> <sys_password> <connect_identifier>]

  sys_username:       the database sys user
  sys_password:       the database sys password
  connect_identifier: the connect_identifier can be in the form of
                      Net Service Name or Easy Connect.
                      [<net_service_name> | [//]host[:port]/<service_name>]

  Specify the database account sys username, sys password and connect identifier
  for the database connection.

  The <service_name> is configured with the database instance
  and can be detected by querying the running instance with

    SHOW PARAMETER service_names or
    SELECT value FROM v$parameter WHERE name = 'service_names';

  Examples:

    localhost XE database server, service name = 'XE'
      dbsetup.bat|sh or
      dbsetup.bat|sh sys intershop //localhost:1521/XE

    remote XE database server, service name = 'XE'
      dbsetup.bat|sh sys intershop //remote_host:1521/XE

    remote SE/EE database server, service name = 'isdb.world'
      dbsetup.bat|sh sys intershop //remote_host:1521/isdb.world

  Notes:

  * SQL*Plus version 11g is required to connect
    via easy connect like //Host:Port/<service_name>
    Check with sqlplus -v

  * Connection errors (ORA-12154, ORA-12638):
    ORA-12154: TNS:could not resolve the connect identifier specified
    ORA-12638: Credential retrieval failed

    can be resolved by the following settings:

    NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT) within TNS_ADMIN\tnsnames.ora
    SQLNET.AUTHENTICATION_SERVICES=(NONE)     within TNS_ADMIN\sqlnet.ora

No sys user with password and no connect identifier was given.
Continue with the following defaults?

  sys_username:       sys
  sys_password:       intershop
  connect_identifier: //localhost:1521/XE

  dbsetup.bat|sh sys intershop //localhost:1521/XE

Press any key to continue or CTRL+C to stop this script.

Specify all Necessary Properties

Starting . . .

sqlplus /nolog @../scripts/dbsetup.sql sys intershop //localhost:1521/XE

. . .

Specify the Number of Intershop AppServer, Oracle Processes (default 150 per AppServer instance) and Open Cursors:
Number of AppServer [1]:
Processes per AppServer at least [150]:
Additional Oracle Processes [30]:
Open cursors at least [500]:

Specify the Intershop Tablespace Names:
Temp Tablespace [IS_TEMP]:
User Tablespace [IS_USERS]:
Index Tablespace [IS_INDX]:
Text Index Tablespace [IS_INDX_CTX]:

Specify Intershop Tablespace Initial Size:
Initial Tablespace Size, e.g. 100M or 1G [100M]:

Specify Intershop Schema User:
User [intershop]:
Password [intershop]:

Summary of user provided information ...
AppServer: 1
Processes/AppServer: 150
Additional Processes: 30
Open Cursors: 500
Temp TS: IS_TEMP
User TS: IS_USERS
Index TS: IS_INDX
Ctx Index TS: IS_INDX_CTX
Initial Size TS: 100M
User: intershop
Password: intershop

Press [Return] to continue or [Ctrl+C] to terminate . . .

Console Output

CONNECT sys/intershop@//localhost:1521/XE AS sysdba
Connected.

You are currently connected to: XE

1) Database Instance Configuration
sqlplus /nolog @../scripts/configure_instance.sql sys intershop //localhost:1521/XE 1 150 30 500
CONNECT sys/intershop@//localhost:1521/XE AS sysdba
Connected.

1.1) Unlock the CTXSYS user

old   1: ALTER USER &_ctx ACCOUNT UNLOCK
new   1: ALTER USER ctxsys ACCOUNT UNLOCK

User altered.

old   1: ALTER USER &_ctx IDENTIFIED BY &_ctx_pw
new   1: ALTER USER ctxsys IDENTIFIED BY ctxsys

User altered.

1.2) Grants (EXECUTE ON ctx_ddl) with Grant Option from ctxsys user to sys and system

CONNECT ctxsys/ctxsys@//localhost:1521/XE
Connected.

Grant succeeded.
Grant succeeded.

CONNECT sys/intershop@//localhost:1521/XE AS sysdba
Connected.

1.3) Lock the CTXSYS user again

old   1: ALTER USER &_ctx ACCOUNT LOCK
new   1: ALTER USER ctxsys ACCOUNT LOCK

User altered.

1.4) Increase Oracle open_cursors and processes

#AppServer       = 1
Sum Processes    = (1*150)+30 = 180 (minimum required 150 for each Intershop AppServer instance)
set processes    = 180
set open_cursors = 500 (minimum required 500)

old   1: ALTER SYSTEM SET open_cursors = &_open_cursors scope = both
new   1: ALTER SYSTEM SET open_cursors = 500 scope = both

System altered.

old   1: ALTER SYSTEM SET processes = &procs_sum scope = spfile
new   1: ALTER SYSTEM SET processes = 180 scope = spfile

System altered.

1.5) Set default password security profile parameters to unlimited (since 11gR2)

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

Profile altered.
Profile altered.
Profile altered.
Profile altered.

1.6) Disable password case sensitivity

set sec_case_sensitive_logon = false

System altered.

Check all settings

INST_ID NAME                      VALUE   ISMODIFIED
------- ------------------------- ------- ----------
      1 open_cursors              500     SYSTEM_MOD
      1 processes                 50      FALSE
      1 sec_case_sensitive_logon  FALSE   SYSTEM_MOD

3 rows selected.

INST_ID SID  NAME                       VALUE
------- ---- -------------------------- -------
      1 *    open_cursors               500
      1 *    processes                  180
      1 *    sec_case_sensitive_logon   FALSE

3 rows selected.

PROFILE    RESOURCE_NAME             RESOURCE LIMIT
---------- ------------------------- -------- ----------
DEFAULT    FAILED_LOGIN_ATTEMPTS     PASSWORD UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME        PASSWORD UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME        PASSWORD UNLIMITED

4 rows selected.

2) Create Intershop IS_* Tablespaces
sqlplus /nolog @../scripts/create_tablespaces.sql sys intershop //localhost:1521/XE IS_TEMP IS_USERS IS_INDX IS_INDX_CTX 100M
CONNECT sys/intershop@//localhost:1521/XE AS sysdba
Connected.

Determine the file system path for the system tablespace and
create the Intershop tablespace files within this location.

The file system path for system tablespace is: /u01/app/oracle/oradata/XE/

********** Create Tablespace IS_TEMP *****************

old   1: CREATE TEMPORARY TABLESPACE &_ts_temp TEMPFILE '&path.&_ts_temp._01.dbf'
new   1: CREATE TEMPORARY TABLESPACE IS_TEMP TEMPFILE '/u01/app/oracle/oradata/XE/IS_TEMP_01.dbf'
old   2:   SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
new   2:   SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
old   3:   EXTENT MANAGEMENT LOCAL UNIFORM SIZE &_uni_size
new   3:   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M

Tablespace created.

********** Create Tablespace IS_USERS *****************

old   1: CREATE TABLESPACE &_ts_user DATAFILE '&path.&_ts_user._01.dbf'
new   1: CREATE TABLESPACE IS_USERS DATAFILE '/u01/app/oracle/oradata/XE/IS_USERS_01.dbf'
old   2:   SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
new   2:   SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

Tablespace created.

********** Create Tablespace IS_INDX *****************

old   1: CREATE TABLESPACE &_ts_indx DATAFILE '&path.&_ts_indx._01.dbf'
new   1: CREATE TABLESPACE IS_INDX DATAFILE '/u01/app/oracle/oradata/XE/IS_INDX_01.dbf'
old   2:   SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
new   2:   SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

Tablespace created.

********** Create Tablespace IS_INDX_CTX *************

old   1: CREATE TABLESPACE &_ts_indx_ctx DATAFILE '&path.&_ts_indx_ctx._01.dbf'
new   1: CREATE TABLESPACE IS_INDX_CTX DATAFILE '/u01/app/oracle/oradata/XE/IS_INDX_CTX_01.dbf'
old   2:   SIZE &_ts_size AUTOEXTEND ON NEXT &_ts_size MAXSIZE UNLIMITED
new   2:   SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

Tablespace created.

Check all settings

old   4:  WHERE ts.name IN ('&_ts_user','&_ts_indx','&_ts_indx_ctx')
new   4:  WHERE ts.name IN ('IS_USERS','IS_INDX','IS_INDX_CTX')
old   9:  WHERE ts.name IN ('&_ts_temp')
new   9:  WHERE ts.name IN ('IS_TEMP')

TABLESPACE_NAME        BYTES CREATION_TIME  FILE_NAME
----------------- ---------- -------------- ----------------------------------------------
IS_INDX            104857600 04-JAN-12      /u01/app/oracle/oradata/XE/IS_INDX_01.dbf
IS_INDX_CTX        104857600 04-JAN-12      /u01/app/oracle/oradata/XE/IS_INDX_CTX_01.dbf
IS_TEMP            104857600 04-JAN-12      /u01/app/oracle/oradata/XE/IS_TEMP_01.dbf
IS_USERS           104857600 04-JAN-12      /u01/app/oracle/oradata/XE/IS_USERS_01.dbf

4 rows selected.

3) Create Intershop User Schema
sqlplus /nolog @../scripts/create_user.sql sys intershop //localhost:1521/XE intershop intershop IS_TEMP IS_USERS
CONNECT sys/intershop@//localhost:1521/XE AS sysdba
Connected.

Create Intershop User: intershop

old   1: CREATE USER &_us
new   1: CREATE USER intershop
old   2:   IDENTIFIED BY &_pw
new   2:   IDENTIFIED BY intershop
old   3:   DEFAULT TABLESPACE &_ts_user
new   3:   DEFAULT TABLESPACE IS_USERS
old   4:   TEMPORARY TABLESPACE &_ts_temp
new   4:   TEMPORARY TABLESPACE IS_TEMP

User created.

old   1: ALTER USER &_us DEFAULT ROLE ALL
new   1: ALTER USER intershop DEFAULT ROLE ALL

User altered.

Grant all necessary Privileges
CONNECT, RESOURCE, CTXAPP, UNLIMITED TABLESPACE
to Intershop User: intershop

old   1: GRANT CONNECT               TO &_us
new   1: GRANT CONNECT               TO intershop
Grant succeeded.

old   1: GRANT RESOURCE              TO &_us
new   1: GRANT RESOURCE              TO intershop
Grant succeeded.

old   1: GRANT CTXAPP                TO &_us
new   1: GRANT CTXAPP                TO intershop
Grant succeeded.

old   1: GRANT UNLIMITED TABLESPACE  TO &_us
new   1: GRANT UNLIMITED TABLESPACE  TO intershop
Grant succeeded.

Grant CONNECT-Privileges directly

old   1: GRANT CREATE CLUSTER        TO &_us
new   1: GRANT CREATE CLUSTER        TO intershop
Grant succeeded.

old   1: GRANT CREATE DATABASE LINK  TO &_us
new   1: GRANT CREATE DATABASE LINK  TO intershop
Grant succeeded.

old   1: GRANT CREATE SEQUENCE       TO &_us
new   1: GRANT CREATE SEQUENCE       TO intershop
Grant succeeded.

old   1: GRANT CREATE SYNONYM        TO &_us
new   1: GRANT CREATE SYNONYM        TO intershop
Grant succeeded.

old   1: GRANT CREATE TABLE          TO &_us
new   1: GRANT CREATE TABLE          TO intershop
Grant succeeded.

old   1: GRANT CREATE VIEW           TO &_us
new   1: GRANT CREATE VIEW           TO intershop
Grant succeeded.

Grant RESOURCE-Privileges directly

old   1: GRANT CREATE PROCEDURE      TO &_us
new   1: GRANT CREATE PROCEDURE      TO intershop
Grant succeeded.

old   1: GRANT CREATE TRIGGER        TO &_us
new   1: GRANT CREATE TRIGGER        TO intershop
Grant succeeded.

old   1: GRANT CREATE TYPE           TO &_us
new   1: GRANT CREATE TYPE           TO intershop
Grant succeeded.

Grant the CREATE SNAPSHOT privilege

old   1: GRANT CREATE SNAPSHOT       TO &_us
new   1: GRANT CREATE SNAPSHOT       TO intershop
Grant succeeded.

Grant CTXAPP-Privileges directly

old   1: GRANT EXECUTE ON CTX_DDL    TO &_us
new   1: GRANT EXECUTE ON CTX_DDL    TO intershop
Grant succeeded.

Grant ANALYZE ANY

old   1: GRANT ANALYZE ANY           TO &_us
new   1: GRANT ANALYZE ANY           TO intershop
Grant succeeded.

Grant DBMS_STREAMS_ADM to enable/suppress Streams via set_tag
Only used within multi data center environments.

old   1: GRANT EXECUTE ON DBMS_STREAMS_ADM TO &_us
new   1: GRANT EXECUTE ON DBMS_STREAMS_ADM TO intershop
Grant succeeded.

Check all settings

old   3:  WHERE UPPER(username) = UPPER('&_us')
new   3:  WHERE UPPER(username) = UPPER('intershop')

USERNAME   ACCOUNT_STATUS  DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE  CREATED    PROFILE
---------- --------------- -------------------- --------------------- ---------- -------
INTERSHOP  OPEN            IS_USERS             IS_TEMP               04-JAN-12  DEFAULT

1 row selected.

old   3:  WHERE UPPER(grantee) = UPPER('&_us')
new   3:  WHERE UPPER(grantee) = UPPER('intershop')

GRANTEE    GRANTED_ROLE   ADM DEF
---------- -------------- --- ---
INTERSHOP  CONNECT        NO  YES
INTERSHOP  CTXAPP         NO  YES
INTERSHOP  RESOURCE       NO  YES

3 rows selected.

old   3:  WHERE UPPER(grantee) = UPPER('&_us')
new   3:  WHERE UPPER(grantee) = UPPER('intershop')

GRANTEE    PRIVILEGE                 ADM
---------- ------------------------- ---
INTERSHOP  ANALYZE ANY               NO
INTERSHOP  CREATE CLUSTER            NO
INTERSHOP  CREATE DATABASE LINK      NO
INTERSHOP  CREATE MATERIALIZED VIEW  NO
INTERSHOP  CREATE PROCEDURE          NO
INTERSHOP  CREATE SEQUENCE           NO
INTERSHOP  CREATE SYNONYM            NO
INTERSHOP  CREATE TABLE              NO
INTERSHOP  CREATE TRIGGER            NO
INTERSHOP  CREATE TYPE               NO
INTERSHOP  CREATE VIEW               NO
INTERSHOP  UNLIMITED TABLESPACE      NO

12 rows selected.

old   3:  WHERE UPPER(grantee) = UPPER('&_us')
new   3:  WHERE UPPER(grantee) = UPPER('intershop')

GRANTEE    OWNER   TABLE_NAME         GRANTOR  PRIVILEGE  GRA HIE
---------- ------- ------------------ -------- ---------- --- ---
INTERSHOP  CTXSYS  CTX_DDL            SYS      EXECUTE    NO  NO
INTERSHOP  SYS     DBMS_STREAMS_ADM   SYS      EXECUTE    NO  NO

2 rows selected.

4) Database Instance Restart
sqlplus /nolog @../scripts/restart_instance.sql sys intershop

A Database instance re-start is necessary. Make a local
"CONNECT sys AS sysdba" with SQL*Plus and type:

SHUTDOWN IMMEDIATE
STARTUP

or re-start the database instance within the installation on

* Windows as Administrator with:

** XE database
NET STOP  OracleServiceXE
NET START OracleServiceXE

** other <SID> database
NET STOP  OracleService<SID>
NET START OracleService<SID>

* Linux as root with:

** XE database
/etc/init.d/oracle-xe stop
/etc/init.d/oracle-xe start

** other <SID> database
/etc/init.d/oracle stop
/etc/init.d/oracle start

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Database Instance Restart

To restart the XE database:

Windows

Open a command prompt as administrator:

C:\> net stop OracleServiceXE
The OracleServiceXE service is stopping....
The OracleServiceXE service was stopped successfully.

C:\> net start OracleServiceXE
The OracleServiceXE service is starting........
The OracleServiceXE service was started successfully.

Linux

Open a shell as root:

# /etc/init.d/oracle-xe stop
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.

# /etc/init.d/oracle-xe start
Starting Oracle Database 11g Express Edition instance.

Manually Step by Step

Open an SQL*Plus command line:

set ORACLE_SID=XE
sqlplus /nolog

Optional: Determine the Name of the Pluggable Database to Be Configured

This step is only needed if you configure a pluggable database (PDB), running inside a container database. Use the following command to list all available PDBs and replace <pdb_name> in all remaining sections of this document with the appropriate database name:

-- this connects you to the container database (CDB), NOT the actual database you want to configure!
sqlplus sys/intershop as sysdba

SELECT name FROM v$pdbs;

Unlock the CTXSYS User

Note

Use the sys password given at Define sys and system passwords:

connect sys/intershop as sysdba

-- CDB only: ALTER SESSION SET CONTAINER = <pdb_name>

ALTER user ctxsys account unlock;
ALTER user ctxsys identified by ctxsys;

Increase open_cursors and processes and Disable sec_case_sensitive_logon

  • Oracle open_cursors to 500 and
  • processes to a minimum of 150 for each connected appserver plus about 30 Oracle processes on top
  • Oracle 12.1 and below only: disable password case sensitivity
connect sys/intershop as sysdba

-- CDB only: ALTER SESSION SET CONTAINER = <pdb_name>

ALTER SYSTEM SET processes                = 180    scope = spfile;
ALTER SYSTEM SET open_cursors             = 500    scope = both;

-- do NOT execute this if you're using Oracle 12.2 or higher (you will not be able to connect to the database)
ALTER SYSTEM SET sec_case_sensitive_logon = false  scope = both;

show parameter processes
show parameter open_cursors
show parameter sec_case_sensitive_logon

Set Default Password Security Profile Parameters to Unlimited Within 11gR2

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;

Intershop IS_* Tablespaces

Find the path location for the default XE tablespaces and create all necessary IS_* tablespaces in this location:

connect sys/intershop as sysdba

-- CDB only: ALTER SESSION SET CONTAINER = <pdb_name>

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     = 100M
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;

Check the correct Intershop tablespace creation, e.g., with Oracle Database XE Home Page - Section Storage.

Intershop Database User

Create an intershop Oracle user schema with SQL*Plus.

Use the system password given at Define sys and system passwords:


connect sys/intershop as sysdba

-- CDB only: ALTER SESSION SET CONTAINER = <pdb_name>

DEFINE _us         = intershop
DEFINE _pw         = intershop
DEFINE _ts_temp    = IS_TEMP
DEFINE _ts_user    = IS_USERS

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;
GRANT UNLIMITED TABLESPACE          TO &_us;
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 CTX_DDL            TO &_us;
GRANT EXECUTE ON DBMS_STREAMS_ADM   TO &_us;

Restart the XE Database

connect sys/intershop as sysdba

shutdown immediate
startup

Listener Settings

Add a SID_DESC entry for the XE SID.

Example, C:\oraclexe\app\oracle\product\11.2.0\server\netwotk\admin\listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = ...
    )
    (SID_DESC = ...
    )
    (SID_DESC =
      (GLOBAL_DBNAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (SID_NAME = XE)
    )
  )

LISTENER =
  (DESCRIPTION_LIST = ...
  )

DEFAULT_SERVICE_LISTENER = (XE)

Optional - Create Additional Database User / Account / Schema

Oracle Database Express 18c creates a container database (CDB) by default, which is not supported by the scripts used in this section. You have to setup the database manually.

Re-run the dbsetup to create additional database users for extra connected application servers. See step Guide - Setup Oracle XE as Intershop Development Database (valid to 7.10)#dbsetup and specify only neccessary properties.

Example for two running application servers with separate database users in parallel:

Specify the Number of Intershop AppServer, Oracle Processes (default 150 per AppServer instance) and Open Cursors:
Number of AppServer [1]: 2
Processes per AppServer at least [150]:
...
Open cursors at least [500]:

The Intershop Tablespace Names are already specified, created before and can be ignored.

Specify the Intershop Tablespace Names:
Temp Tablespace [IS_TEMP]:
User Tablespace [IS_USERS]:
Index Tablespace [IS_INDX]:
Text Index Tablespace [IS_INDX_CTX]:

The important input for the additional DB user | account | schema is to set here.

Specify Intershop Schema User:
User [intershop]: youradditionaluser
Password [intershop]: youradditionaluser

Last you apply or cancel your settings.

Summary of user provided information ...
AppServer: 2
Processes/AppServer: 150
...
Open Cursors: 500
Temp TS: IS_TEMP
User TS: IS_USERS
Index TS: IS_INDX
Ctx Index TS: IS_INDX_CTX
Initial Size TS: 100M
User: youradditionaluser
Password: youradditionaluser

Press [Return] to continue or [Ctrl+C] to terminate . . .

Problems

Tasks Necessary for "ant import" Problems

Due to problems with the Oracle imp tool with Oracle Text indexes (IS_INDX_CTX is the default for Intershop) it is necessary to create the missing tablespace. The ant import error message is:

[exec] IMP-00017: following statement failed with ORACLE error 29855:
[exec]   CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT ...
[exec] IMP-00003: ORACLE error 29855 encountered
[exec] ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
[exec] ORA-20000: Oracle Text error:
[exec] DRG-50857: oracle error in drvxtab.create_index_tables
[exec] ORA-00959: tablespace 'IS_INDX_CTX' does not exist

see section Intershop IS_* Tablespaces.

ORA-12952: The Request Exceeds the Maximum Allowed Database Size of (4GB 10gR2 or 11GB in 11gR2)

Monitoring Storage Space Usage

Because Oracle Database Express Edition (Oracle Database XE) is limited to just over

  • 4 gigabytes (10g) or
  • 11 gigabytes (11g)

of user data, your most important storage management task is monitoring the amount of storage space remaining. Find hints from the Oracle documentation:

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.
The Intershop Knowledge Portal uses only technically necessary cookies. We do not track visitors or have visitors tracked by 3rd parties. Please find further information on privacy in the Intershop Privacy Policy and Legal Notice.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.