4 Secondary Datasource Reporting

You can configure Oracle Identity Manager to use one database for current transactional data and a secondary database for historical data. The secondary database eases the load on the transactional database.

You can use different data sources for the secondary database. The following sections describe how to configure Oracle Identity Manager and your application server to use a secondary data source.

This chapter discusses the following topics:

• Writing User Profile Audits to a Secondary Datasource

• Steps to Set Up a Secondary Data Source

• Using JBoss with a Secondary Data Source

• Using WebLogic with a Secondary Data Source

• Using WebSphere with a Secondary Data Source

• Using OC4J with a Secondary Data Source

Writing User Profile Audits to a Secondary Datasource

User profile audit data can increase in size quickly. Oracle recommends that you use a secondary database to store this information. The following system property enables reading and writing to this database directly:

XL.UserProfileAuditInSecondaryDS.

By default, the XL.UserProfileAuditInSecondaryDS property is set to false. If this property is set to true, the system reads and writes all user profile data directly to and from the secondary database.

If you configure a secondary database, all historical reports are automatically configured to run against it.

The user profile audit interacts directly with the secondary database. You must replicate other tables from the transactional database because the report needs them for access control and filtering of the report. You can disable these tables and constraints for ease of data backup, restore, or replication.

Table 4-1 lists the tables and contraints.

Table 4-1 Tables and Constraints Used in Historical Reports

Table Name	Foreign Key Constraint Name	Referenced Table Name	Referenced Column Name
AAD	FK_AAD_FK_AAD_AC_ACT	ACT	ACT_KEY
	FK_AAD_FK_AAD_UG_UGP	UGP	UGP_KEY
ACT	FK_ACT_ACT	ACT	PARENT_KEY
	FK_ACT_SRP	SRP	SRP_KEY
GPG	FK_GPG_UGP	UGP	UGP_KEY
	FK_GPG_UGP_KEY_UGP	UGP	GPG_UGP_KEY
OUG	FK_OUG_OBJ	OBJ	OBJ_KEY
	FK_OUG_UGP	UGP	UGP_KEY
POL
PTY
REQ	FK_REQ_ORC	ORC	ORC_KEY
	FK_REQ_OST	OST	OST_KEY
	FK_REQ_USR	USR	USR_KEY
UGP
USG	FK_USG_RUL	RUL	RUL_KEY
	FK_USG_UGP	UGP	UGP_KEY
	FK_USG_USR	USR	USR_KEY
USR	FK_USR_ACT	ACT	ACT_KEY

Steps to Set Up a Secondary Data Source

To set up a secondary database:

1. Create the secondary database.

   You can back up and restore the transactional database under a different database name, or you can replicate the transactional database.

2. Set up the application server to use the secondary database.

   See the following sections for details.

3. Set the system property XL.UserProfileAuditInSecondaryDS to True.

   This stores user profile audit data in the secondary database.

4. Configure daily replication of the data so that the tables in the secondary database listed in Table 4-1 are updated from the primary database. After the secondary database is functional, do not replicate the entire primary database in the secondary database or any audit data that has been stored in the secondary database will be deleted.

   Or, set up either a full restore or replication.

5. Make sure all stored procedures are replicated correctly in the secondary database.

6. Define a connection URL as follows:

   • For Oracle Database:

     jdbc:oracle:thin:@<IP of database>:<SID>

   • For SQL Server:

     jdbc:Microsoft:sqlserver://<IP of database>:<Port>;DatabaseName=<SID>;SelectMethod=Cursor

Using JBoss with a Secondary Data Source

To create a new data source on JBoss, a new file called xlreportds-service.xml is created by the setup in the deployment directory. This file creates an alias to the transactional database using the java:jdbc/xlXAReportingDS setting.

To point to a secondary database on JBoss:

1. Open the xell-ds.xml file in an editor.

2. Add the following to xell-ds.xml as a second xa-datasource tag for the Oracle database:

   <xa-datasource>
   <jndi-name>jdbc/xlXAReportingDS</jndi-name>
   <track-connection-by-tx>true</track-connection-by-tx>
   <isSameRM-override-value>false</isSameRM-override-value>
   <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource </xa-datasource-
   class>
   <xa-datasource-property name="URL">jdbc:oracle:thin:@<IP of database system>:
   1521:XELL </xa-datasource-property> 
   <xa-datasource-property name="User">sysadm</xa-datasource-property> 
   <xa-datasource-property name="Password">sysadm</xa-datasource-property> 
   <exception-sorter-class-name> org.jboss.resource.adapter.jdbc.vendor.
   OracleExceptionSorter </exception-sorter-class-name> 
   <no-tx-separate-pools/> 
   <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.
   OracleValidConnectionChecker </valid-connection-checker-class-name> 
   </xa-datasource>
   
   

   For SQL Server, the secondary database tag is as follows:

   <xa-datasource>
       <jndi-name>jdbc/xlXADS</jndi-name>
       <track-connection-by-tx>true</track-connection-by-tx>
       <xa-datasource-class>
         com.microsoft.jdbcx.sqlserver.SQLServerDataSource</xa-datasource-class>
       <xa-datasource-property name="ServerName"><IP of database system>      </xa-datasource-property>
       <xa-datasource-property name="DatabaseName">XELL</xa-datasource-property>
       <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>
       <xa-datasource-property name="PortNumber">1433</xa-datasource-property>
       <user-name>sysadm</user-name>
       <password>sysadm</password>
       <check-valid-connection-sql>
         select 1 from USR where 1=2
       </check-valid-connection-sql>
     </xa-datasource>
   
   

   Note that the class names for Oracle Database and SQL Server vary as indicated:

   • Oracle:

     oracle.jdbc.xa.client.OracleXADataSource

   • SQL Server:

     com.microsoft.jdbcx.sqlserver.SQLServerDataSource

3. Change the database name, user name, and password to connect to the database you set up as the secondary database.

4. Delete the xlreportds-service.xml file.

5. Restart the JBoss server.

Note:

Do not add the xa-datasource block in this section or point the jdbc/xlXAReportingDS to the transactional database because it causes errors. To point to the same transactional database, keep the xlreportds-service.xml file as is.

Cluster Configuration for JBoss

In a standalone setup, the xell-ds.xml and xlreportds-service.xml files are in the JBOSS_HOME\server\default\deploy\ directory.

In a clustered setup, the file xell-ds.xml is in the JBOSS_HOME\server\all\farm\ directory, and xlreportds-service.xml in the JBOSS_HOME\server\all\deploy\ directory.

To configure a cluster for JBoss:

1. Copy the changes to the xell-ds.xml file to all computers in the cluster.

2. Restart the JBoss servers on all computers in the cluster.

Using WebLogic with a Secondary Data Source

Before changing the data source that Oracle Identity Manager uses for reporting, create a new data source in WebLogic. Follow the WebLogic manuals to set up a new data source.

To configure WebLogic with a secondary data source using the Oracle database:

1. Log in to the WebLogic administrative console.

2. Navigate to JDBC Connection Pools

3. Create a Connection Pool with the following credentials:

   • Name: xlXAReportConnectionPool

   • URL: jdbc:oracle:thin:@<database IP address>:<port no>:<SID>

   • Class Name: oracle.jdbc.xa.client.OracleXADataSource

   • Username: <secondary database user name>

   • Password: <secondary database password>

4. Create a secondary data source and deploy it on the server.

   Navigate to JDBC Data Sources on the WebLogic administrative console and create a data source with the following credentials:

   • JNDI name: jdbc/xlXAReportingDS

   • Pool Name: xlXAReportConnectionPool

5. Edit the file weblogic.profile XL_HOME/xellerate/Profiles/ to point to the new data source.

   Add the JNDI name to weblogic.profile as follows:

   datasource.report=jdbc/xlXAReportingDS

6. Run the patch command (patch_weblogic) for the changes to take effect.

Cluster Configuration for WebLogic

To configure a cluster for WebLogic, you must deploy the secondary data source on all members of the cluster.

Using WebSphere with a Secondary Data Source

Before changing the data source used by Oracle Identity Manager for reporting, you must create a new data source in WebSphere. See the WebSphere manuals for information on setting up a new data source.

To configure WebSphere with a secondary data source using the Oracle database:

1. Log in to the WebSphere administrator console.

2. Create a new data source with the following details:

   • Name: <XAReportingDataSource>

   • JNDI name: jdbc/xlXAReportingDS

3. Define the connection URL as follows:

   jdbc:oracle:thin:@<IP of database>:<port_number>:<SID>

   For example: jdbc:oracle:thin:@192.168.161.134:1521:xeltest

4. Use the following J2C authentication data values:

   • Alias: <secondary user alias>

   • User: <secondary user>

   • Password: <secondary user password>

   • Description: <Descriptive text for the data>

5. Select the component-managed authentication aliases for XAReportingDatasource with the following values:

   • Component-managed authentication alias: <J2C Authentication Data Entries>

   • Container-managed authentication alias: <J2C Authentication Data Entries>

6. Save and synchronize changes among all nodes.

7. Open the file websphere.profile, and add the JNDI information that points to the new data source in the XL_HOME/xellerate/Profiles/ directory.

   Comment out the existing data source entry for xlXADS and add the information for xlXAReportingDS as follows:

   # Reporting data source
   #datasource.report=jdbc/xlXADS
    datasource.report=jdbc/xlXAReportingDS
   
   

8. Set the following Java Client System property to true:

   XL.UserProfileAuditInSecondaryDS=True
   
   

9. Run patch_websphere.cmd or patch_websphere.sh as applicable from the XL_HOME\xellerate\setup directory.

Cluster Configuration for WebSphere

To configure a cluster for WebSphere:

1. Modify each websphere.profile file on all nodes participating in the cluster.

2. Run the patch_websphere.cmd or patch_websphere.sh as applicable from the XL_HOME\xellerate\setup directory from the network deployment manager (NDM) node.

3. Stop and restart all nodes and servers.

Using OC4J with a Secondary Data Source

Before changing the data source used by Oracle Identity Manager for reporting, you must create a new data source in OC4J.

To configure OC4J with a secondary data source using the Oracle database:

1. Log in to the Oracle Enterprise Manager 10g Application Server Control Console for the OC4J instance.

2. Click Administration to display the list of administration tasks you can perform on the selected OC4J instance.

3. If necessary, expand the Services section of the table by clicking the expand icon or by clicking Expand All.

4. Click the task icon in the table's JDBC Resources row.

   The Application Server Control Console displays the JDBC Resources page, which lists the data sources and connection pools currently available in the OC4J instance.

5. Click the xlXAReportingConnectionPool link under the connection pools section.

6. Define the connection URL as follows:

   jdbc:oracle:thin:@<IP of database>:<port_number>:<SID>

   For example: jdbc:oracle:thin:@192.168.161.134:1521:xeltest

7. Enter the secondary database user name in the User Name field.

8. Select the Use Cleartext Password option and provide the secondary database user password.

9. Click Apply.

10. Restart the OC4J instance.