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-8AL32UTF8
- Initialization Parameters
-
The following table lists the recommended settings for Oracle initialization parameters. Set these parameters in the init.ora or
spfileof 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.Parameter Recommended value QUERY_REWRITE_ENABLEDtrue(default)QUERY_REWRITE_INTEGRITYtrustedOPTIMIZER_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.
Tablespace Size Uniform allocation size USERS4096M 4096M INDX2048M 2048M LOBS4096M 4096M SESSION_DATA1024M 1024M The Content Manager installer expects a temporary tablespace called
TEMP. This tablespace must be available during Content Manager installation. TheTEMPtablespace is used, in part, for temporary LOBs. If you have many concurrent users, you may need to increase yourTEMPtablespace 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:
CONNECTroleCREATE TABLECREATE PROCEDURECREATE TRIGGERCREATE SEQUENCEEXECUTE ON DBMS_UTILITYUNLIMITED TABLESPACESELECT ANY DICTIONARY(to access SYS views such asv$parameter)
The user must also have execute permission on the
dbms_utilitypackage. 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 TABLESPACESELECT 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:
Check the following registry key, which should contain the computer name of the management system:
HKLM\SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0\Host- Check if the
OracleMTSRecoveryServiceis started. Check for any pending distributed transactions.
OracleMTSRecoveryServicecannot 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:
- 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