← Cronacle PL/SQL API ModuleUsing the OracleJob Definition Type →

Creating the OracleJob Process Server

The OracleJob process server allows you to execute legacy Redwood Server code from Redwood Server version 7. Once you have completed this procedure, you will have a GLOBAL.System_Oracle process server, the only process server that can be used to execute version 7 code.

The process server requires a schema for each partition where OracleJob process definitions are stored. For each schema, it needs a JDBC credential, with Endpoint set to System_Oracle. This is checked at process definition creation time. Each database user you run code under needs to exist in the Redwood Server authentication system as well, needs at least the scheduler-user or redwood-login role and needs to be imported. Users are imported automatically at first login and can be imported using Import Users from the context-menu of "Security > Users".

note

You will not be able to import OracleJob process definitions if you have not created the required credentials and the required users are not imported.

note

There must be equivalent users in your Redwood Server authentication system for you to be able to import them.

The OracleJob process server is a special JDBC process server that uses the same connection information that Redwood Server uses. See process server parameters for a list of process server parameters.

The Database object is created by the system; if you wish to change the connection URL, you will have to edit the redwood.xml file (or scheduler.xml if you upgraded from version 9.1.4 or lower) used your Redwood Server application server. Note that this change will also impact Redwood Server.

The Jdbc Url has a default of jdbc:oracle:thin:@%h:1521:%s; In this connect string, the %h is replaced with the host running the instance and %s with the Oracle SID of the instance. This string can be changed.

Initializing the Oracle Service

When you initialize the Oracle Service, changes need to be performed in the Oracle database. By default, the process expects the password of user SYS. If you do not wish to specify the SYS password, you submit the process definition, the process will run as the user specified in the JDBC connection. You inspect the logs for the missing privileges; you grant the privileges to the user and submit the process definition once more.

Using the Database Connection Pool

By default, the OracleJob process server does not use the JDBC connection pool to connect to the database. You can enable connection pooling to improve the performance by editing the System_Oracle database. See Connecting to Remote Databases with JDBC for more information.

Careful considerations need to be made when you enable connection pooling, as dirty connections will be used from the pool where variables may no longer hold their initial value. Also, database locks are not automatically released when a connection is put back into the pool after a process using it has reached a final state. Other connection-related assumptions should be verified prior to using connection pooling for OracleJob processes.

Troubleshooting the Connection Pool

When enabled, the default maxActive connections is set to 8 by default. If you are experiencing a great number of OracleJob processes in status Queued, you might be running low on available connections.

You inspect how many connections are required by issuing a SQL statement in the database or creating a thread dump. This should be done at the busiest time for the OracleJob process server to identify if the connection pool is a bottleneck.

Issue select count(*) from v$session where status = 'ACTIVE' and username = '<Redwood user>' on the database to see how many connections are active, or even better (requires more knowhow) create a thread dump, see how many threads are waiting on a connection from the pool and increase the pool by that number. See Redwood Note 78774 or the documentation that came with your JVM for information on how to create a thread dump. Contact Redwood Server customer support if you need assistance; provide them with the thread dump for analysis.

Redwood Platform

You increase the size of the connection pool on Redwood Platform by increasing the value for maxActive in the datasource definition section of <install_dir>/j2ee/cluster/server1/conf/Catalina/localhost/scheduler.xml.

Example Redwood Platform Datasource Definition section:

   <!-- Start of datasource definition -->
   <Resource name="jdbc/com.redwood.scheduler"
     type="javax.sql.DataSource"
     auth="Container"
     factory="com.redwood.platform.datasource.RPDataSourceFactory"
     description="Oracle"
     driverClassName="oracle.jdbc.OracleDriver"
     url="jdbc:oracle:thin:@ora.example.com:1521:ora"
     username="example"
     password="{RedwoodSecureStorage}:1;j3nnqA3vbqff1bjza4kQuYUU1Mmj+La0XFV+HPnOFMQ="
     maxActive="30"
   />

Procedure

Create Oracle Schemas

  1. Connect to the Oracle database with sqlplus as a privileged user and perform the following commands.
SQL> create user <partition> identified by <password>;

User created.

SQL> grant create session to <partition>;

Grant succeeded.

SQL> grant create procedure to <partition>;

Grant succeeded.
  • <partition> - the partition of the Redwood Server process definitions you want to use against this instance
  • <password> - a password of your choice
tip

Redwood recommends you use all uppercase names for the partition in Redwood Server.

Create the System_Oracle Process Server

  1. Log in to central Redwood Server.
  2. Navigate to "Definitions > Processes".
  3. Locate the System_InitializeOracleService process definition, choose Submit from the context-menu.
  4. Optionally specify the SYS password, if you do not wish to specify this password, the process will be run as the user specified on the JDBC connection.
  5. Choose Submit twice.
  6. If you did not specify the SYS password, the process definition might reach status Error. In this case, you inspect the stderr.log of the process, and grant any missing privileges to the user and submit the process definition once more.

Create a Partition

  1. Log in to central Redwood Server.
  2. Navigate to "Security > Partitions".
  3. Choose New Partition from the context-menu.
  4. Fill in a name, the case must match the case of the Oracle user (if you did not explicitly use quotes in Oracle, then it must be upper case).
  5. Choose Save & Close.

Create a Credential

  1. Log in to central Redwood Server.
  2. Navigate to "Security > Credentials".
  3. Choose New Credential from the context-menu and select the JDBC credential protocol, set the Endpoint to System_Oracle, the Real User is case sensitive, the partition must be in the same case as well.
  4. Choose Save & Close.

Import Users

  1. Log in to central Redwood Server.
  2. Navigate to "Security > Users".
  3. Choose Import Users.
  4. Fill in a name into the Search field and choose Search.
  5. Select the Import checkbox for the users you wish to import and choose Import in the lower right-hand side corner of the screen.

See Also

Using the OracleJob Definition Type

← Cronacle PL/SQL API ModuleUsing the OracleJob Definition Type →