RCM Oracle best practices

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

Issue/Introduction

Best practices for running RCM on Oracle.

Environment

Release:
Component: SGRM

Resolution

Running RCM on Oracle

RCM Server (including Workpoint) may require up to 240 connections to the database.
Additional connections may be opened by applications like sqlPlus or sqlDeveloper.
We recommend allowing 250 connections for each RCM server connected to the same database (even if they connect to different scehmas...) to set the number of connections preform the following actions:

1. connect to the database with system account.
2. run the following commands:
   alter system set sessions=250 scope=spfile;
   alter system set processes=250 scope=spfile;
3. restart the database.

 

Performance

After importing large amounts of data (i.e. saving a configuration from client tools / import from connector) it is recommended to allow the database to create statistics by running the following command:

execute dbms_stats.gather_schema_stats('${SCHEMA}', DBMS_STATS.AUTO_SAMPLE_SIZE);

replace ${SCHEMA} with the database schema name (user name)

Connecting to Oracle from Client Tools

1. Install Oracle Client as instructed in the RCM installation document.
2. Edit (or create) ${ORACLE_HOME}\NETWORK\ADMIN\tnsnames.ora to add a TNS entry for your database server
   Example for a tnsnames could be like the following:

   DB1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = example.ca.com)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = DB1)    )  )XTPROC_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))    )    (CONNECT_DATA =      (SID = PLSExtProc)      (PRESENTATION = RO)    )  )ORACLR_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))     )     (CONNECT_DATA =       (SID = CLRExtProc)       (PRESENTATION = RO)     )   )

3. When using the Client Tools to connect directly to the database, set the tnsnames.ora entry name as server name (DB! in our example).