Using Oracle and Other Databases for Alert Persistence

Oracle, and the other supported databases besides Derby, are also capable performing alert persistence. However, there a number of changes you must perform to get it to work.

Note - If you plan to use Oracle instead of Derby for alert persistence, make sure you read this topic.

To Set Up an Oracle Database for Alert Persistence

Modify the eventmanagement.properties file under appserver/domains/domain1/jbi/config.
1. Modify the DatabaseType to ORACLE.
2. Change the AlertTablename to EMHOSTNAMEVistastccom8080.
  Note - This step is required as the default name, EVENTMANAGEMENTHOSTNAMEistastccom8080, exceeded Oracle's 30-character limit for table names.
3. Change the DBJndiName to OracleXPDB.
  Note - You create this in the Admin Console; this is noted in step 2.
4. Change PersistenceEnbled to true.
5. Using Enterprise Manager, set the values for DatabaseType, DBJndiName, and PersistenceEnabled in the normal Alert Management Config Agent.
  Note - The database scripts should probably be run before enabling persistence. The table name must be changed in the file manually.
Create the JDBC connection pool and resource in the GlassFish Application Server Admin Console.
1. Add the location of classes12.zip to the classpath, JVM Settings->Path Settings->Classpath Suffix, and then restart the domain.
  Note - This is needed to get the datasource for Oracle.
2. Create the Connection Pool for Oracle.
  1. Enter a name, such as OracleXPPool, but this name can be your choice.
  2. Select javax.sql.DataSource for the Resource Type.
  3. Select Oracle for the Database Vendor.
  4. Set the appropriate properties:
    1. User: eventdb_user
    2. DatabaseName: orcl
      Note - This and other database specific configurations may change depending on how you configured the Oracle database.
    3. Password: eventdb_user
    4. ServerName: hostname
      Note - This is the server where the database is running.
    5. PortNumber: 1521
    6. URL: jdbc:oracle:thin:@hostname:1521:orcl
      
      Caution - This URL may actually override the other settings; it should match the other settings.
  5. Create the JDBC Resource.
    1. Enter a JNDI Name, for example OracleXPDB.
      Note - This should match what is set in the Alert Management Config Agent/eventmanagement.properties file mentioned above.
    2. Select the appropriate Pool Name; in our example we used OracleXPPool.
Create the user, tables, etc. needed for alert persistence and journaling manually.
Note - This is automatically done for Derby.

There are database scripts that are packaged in the jbi_rt.jar file under appserver/jbi/lib. However there are some errors, so you need to be correct these scripts manually.
- The example scripts have been modified to work with an Oracle 10 GB database; see Oracle Script Examples.
- Run the create_event_store_user.sql and create_event_store_schema.sql scripts, in that order, with the system (admin) user.
- Corrections made in this example:
  - Set the absolute path to tablespace data files (database installation dependent)
  - Modify the command to match Business Process persistence
  - Comment out the second data file
  - Move the comments
  - Fix the table name references to match user schema and table name, which were set above in eventmanagement.properties
  - Fix the column reference; that is, change the second column name from event_timestamp to timestamp
  - Change the datatype from timestamp to decimal
  - Fix the reference for sequence

Oracle Script Examples

truncate_event_store_schema.sql

TRUNCATE TABLE eventdb_user.EMHostNameVistastccom8080;

create_event_store_schema.sql

create table eventdb_user.EMHostNameVistastccom8080(
      id NUMBER CONSTRAINT ID_PK PRIMARY KEY, 
            timeStamp decimal,
         physicalHostName varchar(256),
            environmentName varchar(256),
            logicalHostName varchar(256),
            serverType varchar(256), 
            serverName varchar(256), 
            componentType varchar(256),
            componentProjectPathName varchar(1024),
            componentName varchar(256),
            eventType varchar(256),
            severity integer,
            operationalState int,
            messageCode varchar(256),
            messageDetail varchar(4000),
            observationalState int,
            deploymentName varchar(256));
);
-- INSERT statement need to use it to insure autoincrement functionality
CREATE SEQUENCE eventdb_user.autoincrement_id; 
create index  eventTime on eventdb_user.EMHostNameVistastccom8080(timeStamp);

create_event_store_user.sql

--Create a tablespace named EVENTDB_USER_DB. Change this value if a different 
name is desired.
--Specify the name and the location of the file where the data related to
the tablespace 
--created above will be stored. The location is by default the location determined by
--the database server/instance on which this script is run
--For example, for Windows c:\MyDatafiles\EVENTDB_USER_DB.dat, for Unix
/dev/home1/EVENTDB_USER_DB.dat
--Note that the name of the actual file need not be EVENTDB_USER_DB.dat
--Specify the size constraints

    -- Window and Oracle 9i there is a limitation on file size, it is 2 GB. 
This by default creats 4GB, add more files if you need more than 4 GB.
    --- provide abosolute path if you preference is not default location 
'C:\OracleDirectory\EVENTDB_USER_DB.dat' SIZE 2000M,

CREATE TABLESPACE EM_EVENTSTORE_DB
 DATAFILE
    'C:\oracle\product\10.2.0\oradata\orcl\EVENTDB_USER_DB.dat' SIZE 512M REUSE 
AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED;

    -- 'C:\oracle\product\10.2.0\oradata\orcl\EVENTDB_USER_DB1.dat' SIZE 512M 
REUSE AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED --- provide abosolute path 
if you preference is not defaultlocation 'C:\OracleDirectory\EVENTDB_USER_DB1.dat' 
SIZE 2000M
        -- when TABLESPACE is created with these options performance is degrading 
gradually as more and more records added to schema  EXTENT MANAGEMENT LOCAL SEGMENT 
SPACE MANAGEMENT AUTO


--Create a new user EVENTDB_USER. Change the name if so desired. Password will 
be same as
--the user name by default. This username and password will be used to create the 
--connection pool on the application server. Also specify the tablespace 
and the quota on
--the tablespace the user has. Note that if you used a different tablespace 
name above,
--you will have to specify that tablespace name here.

CREATE USER EVENTDB_USER IDENTIFIED BY EVENTDB_USER
DEFAULT TABLESPACE EM_EVENTSTORE_DB
QUOTA UNLIMITED ON EM_EVENTSTORE_DB
TEMPORARY TABLESPACE temp
QUOTA 0M ON system;

--Modify the user name if the default user name was changed

GRANT CREATE session to EVENTDB_USER;
GRANT CREATE table to EVENTDB_USER;
GRANT CREATE procedure to EVENTDB_USER;

drop_event_store_schema.sql

DROP TABLE eventdb_user.EMHostNameVistastccom8080;
drop sequence eventdb_user.autoincrement_id;

drop_event_store_user.sql

--Drop the user that was created earlier. Note that if you chose a 
different name for the 
--user while creating the user, you will have to specify that name here.
DROP USER EVENTDB_USER CASCADE;

--Drop the tablespace that was created earlier. Note that if you chose a 
different name for 
--the tablespace while creating the user, you will have to specify that name here.
DROP TABLESPACE EM_EVENTSTORE_DB INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

--Manually delete the datafiles that were created. If you used the defaults 
while creating
--the datafiles, the names would be EVENTDB_USER_DB1.dat'and 'EVENTDB_USER_DB2.dat'

Skip Navigation Links
Exit Print View
	Oracle Java CAPS Management and Monitoring APIs Java CAPS Documentation