Configuring the probe to read events from an Oracle database

You can use the JDBC Probe to read events from an Oracle database.

To do so, configure the following properties in the jdbc.props file:

  • SelectSqlFile: Specify a file that contains the SQL select statement that the probe will use to retrieve data from the Oracle database.
  • MessageLog : Specify a log file to which the probe writes messages.
  • PidFile: Specify the PID file that the identifies this probe.
  • JdbcDriver: Specify oracle.jdbc.driver.OracleDriver.
  • JdbcUrl: Specify the URL of your target database.
  • DBUsername: Specify the username that the probes uses to log into the database.
  • DBPassword : Specify the password associated with the username specified by the DBUsername property.
  • DataBackupFile : Specify the file that the probe will use to back up data. For details about configuring this property, see Configuring partial resynchronization.
  • MarkerColumn: Specify the name of the column to use as a marker for partial resynchronization, converting its contents into a number if necessary.

The column from the Oracle database that you specify using the MarkerColumn property should be equivalent to the LastOccurrence field of the ObjectServer table that the probe is reading. The column that you specify must be of type NUMBER. If the column is not of type NUMBER, (for example, if it is a date column) you must convert it to a number.

Then specify a select statement in the SelectSqlFile for the probe to use to compare the chosen MarkerColumn with the local system time of the database.

Example configuration: reading data where the LastOccurrence field is compared to a date

To configure the probe to read data from an Oracle database where the LastOccurrence field is compared to a date as seen in the REPORTER_STATUS table created by historical reporting gateways, use the following steps:

  1. Set the following properties in the jdbc.props file:
    

   SelectSqlFile   : '/opt/nrv731/IBM/tivoli/netcool/omnibus/probes/linux2x86/oracle11g_jdbc_probe.sql'
   MessageLog      : '/opt/nrv731/IBM/tivoli/netcool/omnibus/log/jdbc_probe_oracle11g.log'
   PidFile         : '/opt/nrv731/IBM/tivoli/netcool/omnibus/var/jdbc_probe_oracle11g.pid'
   JdbcDriver      : 'oracle.jdbc.driver.OracleDriver'
   JdbcUrl         : 'jdbc:oracle:thin:@<SERVERIP>:1521:<SID>'
   DBPassword      : 'password'
   DBUsername      : 'user_name'
   DataBackupFile  : '/opt /nrv731/IBM/tivoli/netcool/omnibus/var/jdbc_probe_oracle11g'
   MarkerColumn    : "TO_NUMBER(TO_CHAR(LASTOCCURRENCE ,'yyyymmddhh24miss')) AS MARKER"
  2. Include the following SQL in the oracle11g_jdbc_probe.sql specified by the SelectSqlFile property:

    select TO_NUMBER(TO_CHAR(LASTOCCURRENCE ,'yyyymmddhh24miss')) as MARKER, EVENT_ID, IDENTIFIER, NODE, FIRSTOCCURRENCE, LASTOCCURRENCE , DELETETIMESTAMP, SHORT_DESCRIPTION, LONG_DESCRIPTION from ORACLE_ALERTS

  3. Include the following path in the CLASSPATH:

    /opt/instantclient_10_2/classes12.jar:/opt/instantclient_10_2/ojdbc14.jar