4 Secondary Datasource Reporting

Oracle Identity Manager can be configured to use two databases, one for transactional data (current data) and another for historical data. The historical reporting database is called the secondary database through this guide. This secondary database eases the load on the transactional database.

Different data sources can be used as the secondary database. The following example describes how to configure Oracle Identity Manager to use JBoss, WebLogic, and WebSphere servers to configure a the secondary data source.

This chapter covers the following topics:

Writing User Profile Audit to Secondary Datasource
Steps to Set Up a Secondary Datasource
Using JBoss with a Secondary Datasource
Using WebLogic with a Secondary Datasource
Using WebSphere with a Secondary Datasource

Writing User Profile Audit to Secondary Datasource

Since user profile audit data can increase in size considerably and at a fast rate, it is recommended that a secondary database be used to store this information. Additionally, a system property is available to enable reading and writing to this database directly: XL.UserProfileAuditInSecondaryDS.

By default, this property is set to false. This means that there is no interaction with a secondary database. However, all historical reports are always queried from the secondary database. A restore of the transactional database should be done on the secondary data source on a regular basis. The advantage of this method is that restoring the database is simpler. On the other hand, more and more data might need to be restored in the long run and this method will take longer and longer to finish.

If this property is set to true, the system reads and writes all user profile data directly to and from the secondary database. The User Profile Audit interacts with the secondary database directly. However, other tables need to be replicated from the transactional database because the report needs them for access control and filtering of the report itself. These tables and constraints can be disabled for ease of data backup, restore, or replication, and are as follows:

AAD — ACT (ACT_Key) FK_AAD_FK_AAD_AC_ACT (<Table_name> (<Referenced_column_name>) <Name of the FK constraint>)

UGP (UGP_Key) FK_AAD_FK_AAD_UG_UGP
ACT — ACT (Parent_Key) FK_ACT_ACT

SRP (SRP_Key) FK_ACT_SRP
POL
REQ

ORC (ORC_Key) FK_REQ_ORC

OST (OST_Key) FK_REQ_OST

USR (USR_Key) FK_REQ_USR
UGP
USG

RUL (RUL_Key) FK_USG_RUL

UGP (UGP_Key) FK_USG_UGP

USR (USR_Key) FK_USG_USR
USR

ACT (ACT_Key) FK_USR_ACT

Steps to Set Up a Secondary Datasource

To set up a secondary database, do the following:

Create the secondary database by performing a backup and restore of the transactional database under a different database name, or by replicating the transactional database.
Setup the application server to use the secondary database (see the following sections).
Set the system property XL.UserProfileAuditInSecondaryDS to True if you want User Profile Audit data to go directly to the secondary database.
Set the daily restore or replication property to true for all the tables listed in the previous section. Otherwise, set up either a full restore or replication.
Make sure all stored procedures are also replicated correctly in the secondary database.

Note:

Define the 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 Datasource

To create a new data source running 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, perform the following steps:

Edit the xell-ds.xml file by adding the following as a second xa-datasource tag for 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 will be 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

Remember to change the database name, user name, and password to connect to the database you set up as the secondary database.

Delete the xlreportds-service.xml file.
Restart the JBoss server.

Note:

Do not add the xa-datasource block given in this section and 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 a clustered configuration, ensure that the changes made to the xell-ds.xml file reflect on all machines in the cluster. Then restart the JBoss servers on all the machines.

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

Using WebLogic with a Secondary Datasource

Before changing the data source used by Oracle Identity Manager for reporting, a new data source needs to be created in WebLogic. Follow the WebLogic manuals to setup a new data source.

To configure WebLogic with a secondary data source, using Oracle Database, do the following:

Log in to the WebLogic administrative console and navigate to JDBC Connection Pools.
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>
Create a secondary data source and deploy it on the server. To do this, 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
Change the weblogic.profile file to point to the new data source. The weblogic.profile file is located at XL_HOME/Xellerate/profiles. Add the JNDI name as

datasource.report=jdbc/xlXAReportingDS.
After modifiying the profile, run the patch command (patch_weblogic) for the changes to take effect.

Note:

For a WebLogic clustered configuration ,the secondary datasource must be deployed on all the cluster members participating in a cluster.

Using WebSphere with a Secondary Datasource

Before changing the data source used by Oracle Identity Manager for reporting, a new data source needs to be created in WebSphere. Follow the WebSphere manuals to set up a new data source.

To configure WebSphere with a secondary data source, using Oracle Database, do the following:

Log in to the WebSphere administrator console.
Create a new data source with the following details:
- Name: <XAReportingDataSource>
- JNDI name: jdbc/xlXAReportingDS
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
Use the following J2C authentication data values:
- Alias: <secondary user alias>
- User: <secondary user>
- Password: <secondary user password>
- Description: <Descriptive text for the data>
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>
Save and synchronize changes among all nodes.
Modify the websphere.profile to add the JNDI information that points to the new data source in the XL_HOME/xellerate/Profiles directory. For this comment out the existing datasource entry for xlXADS and add the information for xlXAReportingDS as follows:
```
# Reporting data source
#datasource.report=jdbc/xlXADS
 datasource.report=jdbc/xlXAReportingDS
```
Set the following Java Client System property to true:
```
XL.UserProfileAuditInSecondaryDS=True
```
After modifiying the profile, run the patch_websphere.cmd or patch_websphere.sh as applicable from the XL_HOME\xellerate\setup directory.

Cluster Configuration

For a clustered set up, you need to individually modify the websphere.profile file on all nodes participating in the cluster. Then 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. Finally, stop and restart all nodes and servers.