Configuring Oracle database server

You need to perform the following configuration on an Oracle database server before installing your Content Manager databases.

National language settings

The character set for the Oracle database must be one of the following:

  • UTF-8
  • AL32UTF8
Initialization Parameters

The following table lists the recommended settings for Oracle initialization parameters. Set these parameters in the init.ora or spfile of the Oracle Database instance used for the Content Manager. These recommendations are based on approximately 500 concurrent database connections. The recommended values apply for both Oracle Database 10g and 11g.

ParameterRecommended value
QUERY_REWRITE_ENABLEDtrue (default)
QUERY_REWRITE_INTEGRITYtrusted
OPTIMIZER_DYNAMIC_SAMPLING2 (default)
NLS_LENGTH_SEMANTICSBYTE
Tablespaces

Database objects are created in a number of tablespaces. Oracle Databases can use either dictionary-managed tablespaces or locally managed tablespaces. It is recommended to use locally managed tablespaces.

The following table presents the minimal properties of these tablespaces when using locally managed tablespaces. Note that you may need to modify these values depending on sizing.

TablespaceSizeUniform allocation size
USERS4096M4096M
INDX2048M2048M
LOBS4096M4096M
SESSION_DATA1024M1024M

The Content Manager installer expects a temporary tablespace called TEMP. This tablespace must be available during Content Manager installation. The TEMP tablespace is used, in part, for temporary LOBs. If you have many concurrent users, you may need to increase your TEMP tablespace up to 2 GB.

Database schemas

By default, the Content Manager uses the following database schemas:

TCMDBUSER
contains all editorial content of the Web site
TCMLogDBUser
contains all logging information

You create these schemas when you run the Tridion Database Manager tool. You can choose different names for these schemas.

When you create new databases, the database administrator you specify in the Tridion Database Manager tool grants the following roles and privileges:

  • CONNECT role
  • CREATE TABLE
  • CREATE PROCEDURE
  • CREATE TRIGGER
  • CREATE SEQUENCE
  • EXECUTE ON DBMS_UTILITY
  • UNLIMITED TABLESPACE
  • SELECT ANY DICTIONARY (to access SYS views such as v$parameter)

The user must also have execute permission on the dbms_utility package. The Tridion Database Manager tool sets these permissions during the installation process.

After the database has been created, the majority of these privileges are revoked. The following roles and privileges remain:

  • Required roles for normal use:

    • CONNECT
  • Required privileges for normal use:

    • GRANT UNLIMITED TABLESPACE
    • SELECT ANY DICTIONARY
Performance considerations

The Content Manager uses a cost-based optimization. You must therefore analyze tables and indexes regularly. To do this, use the packaged stored procedure:

dbms_stats.gather_schema_stats('[schema]', cascade=true)

where [schema] is the name of the schema that contains the tables and indexes to analyze.

Disk configuration

The Content Manager generates a lot of redo log data. To avoid contention on the disk where the redo log data is written, place the redo log file on its own disk or on a disk set where no other database files are placed. Make the redo log file large, because small redo log files result in frequent log switches that can degrade database performance.

The Content Manager typically uses different tablespaces for data (USERS), temporary data (TEMP), indexes (INDX) and binary data (LOBS) which can be specified during database installation using Tridion Database Manager.

You can optimize performance if each of these tablespaces is located on a separate physical disk or disk set.

Net configuration

The server running the Content Manager and the Oracle Database client must be able to connect to the Oracle database server using Local Net Service Naming.

To connect to the database server, use the Net Manager or the Net Configuration Assistant to create an entry for the database server in the local TNSNAME.ora file.

Troubleshooting ORAMTS

If you receive the error, "New transaction cannot enlist in the specified transaction coordinator", do the following:

  1. Check the following registry key, which should contain the computer name of the management system:

    HKLM\SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0\Host
  2. Check if the OracleMTSRecoveryService is started.
  3. Check for any pending distributed transactions. OracleMTSRecoveryService cannot start if this is the case.

    To check this, query:

    DBA_2PC_PENDING: SELECT LOCAL_TRAN_ID FROM DBA_2PC_PENDING;

    If there is a pending transaction, do a rollback:

    ROLLBACK FORCE local_tran_id from previous query; EXEC 
    DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (local_tran_id FROM 
    previous query)
Microsoft Transaction Server transaction recovery

To learn about Managing Recovery Scenarios related to scheduling automatic Microsoft Transaction Server Transaction recovery, consult your Oracle product documentation, specifically, one of the following resources:

Oracle 11
http://download.oracle.com/docs/cd/B28359_01/win.111/b28377/recovery.htm#CBADHGHH
Oracle 10
http://download.oracle.com/docs/cd/B19306_01/win.102/b14320/recovery.htm#NTMTS003
Creating an SQL SDL Tridion administrator user

SDL Tridion ships with a SQL script that creates an SDL Tridion administrator user that you can use to create a database using the SDL Tridion Database Manager tool.

The privileges and roles created for this user are the minimum requirements for database installation. Before you create a Content Manager database, run the SQL script CreateTridionSYSUser.sql (for example from SQL*Plus) located in the directory Database\TDM\Scripts\Oracle\Tools on your SDL Tridion installation media.

When you use this script, it prompts you for the following information:

Username
the name of the SDL Tridion administrator user that can create the database
Password
the password of this user
Default tablespace
the default tablespace for objects the user creates
Temporary tablespace
the tablespace for the user's temporary segments