Documentation Center

Configuring Oracle database server

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

Distributed Transaction Processing-enabled Oracle service

If you use Real Application Clusters (RAC) with your Oracle database, your Oracle database servers must make a Distributed Transaction Processing-enabled (DTP-enabled) Oracle service available for Content Manager to connect to. A Content Manager instance must connect to a DTP service that is available on one and only one Oracle RAC instance.

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.

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

These recommendations are based on approximately 500 concurrent database connections. The recommended values apply for both Oracle Database 10g and 11g.

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.

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 when you run the PowerShell script.

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\Scripts\Oracle\Tools on your SDL Tridion installation media.

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

UsernameThe name of the SDL Tridion administrator user that can create the database
PasswordThe password of this user
Default tablespaceThe default tablespace for objects the user creates
Temporary tablespaceThe tablespace for the user's temporary segments
Database schemas

By default, the Content Manager uses a database schemas called TCMDBUSER. You create this schema when you run the PowerShell script (as the SDL Tridion administrator user). You can choose a different name for this schema.

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 when you run the PowerShell script.

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. Alternatively, you can specify the properties of your connection in PowerShell when you install or upgrade the database.

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: