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-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.Parameter Recommended value QUERY_REWRITE_ENABLEDtrue(default)QUERY_REWRITE_INTEGRITYtrustedOPTIMIZER_DYNAMIC_SAMPLING2(default)NLS_LENGTH_SEMANTICSBYTEThese 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.
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. - 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:
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 - 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:
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:
Version Link 12 http://docs.oracle.com/cd/E16655_01/win.121/e18595/recovery.htm 11 http://download.oracle.com/docs/cd/B28359_01/win.111/b28377/recovery.htm 10 http://download.oracle.com/docs/cd/B19306_01/win.102/b14320/recovery.htm