Creating the OracleJob Job Server

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

The Job Server requires a schema for each Partition where OracleJob Job Definitions are stored. For each schema, it needs a JDBC credential, with Endpoint set to System_Oracle. This is checked at Job Definition creation time. Each database user you run code under needs to exist in the RunMyJobs 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 Job Definitions if you have not created the required Credentials and imported the required users.

Note: There must be equivalent users in your RunMyJobs authentication system for you to be able to import them.

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

The Database object is created by the system. If you want to change the connection URL, you must edit the redwood.xml file (or scheduler.xml if you upgraded from version 9.1.4 or lower) used by your RunMyJobs application server. Note that this change will also impact the RunMyJobs 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, submit the Job Definition, and the process will run as the user specified in the JDBC connection. You can inspect the logs for the missing privileges, and you can grant the privileges to the user and submit the Job Definition once more.

Using the Database Connection Pool

By default, the OracleJob Job 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 large number of OracleJob processes in status Queued, you might be running low on available connections.

You can 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 Job 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 create a thread dump and see how many threads are waiting on a connection from the pool (you can then 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 Support if you need assistance, and provide them with the thread dump for analysis.

Redwood Platform

You can increase the size of the connection pool on the RunMyJobs server 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:

Copy 
   <!-- 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.
Copy 
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 RunMyJobs Job 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 Job Server

To create the System_Oracle Job Server:

  1. Navigate to Configure > Automate > Job Definitions.
  2. Right-click the System_InitializeOracleService Job Definition and choose Run from the context menu.
  3. Optionally specify the SYS password. If you do not want to specify this password, the Job will be run as the user specified on the JDBC connection.
  4. Run the Job Definition.
  5. If you did not specify the SYS password, the Job may reach status Error. In this case, inspect the stderr.log of the Job, grant any missing privileges to the user, and submit the Job Definition again.

Create a Partition

To create a Partition:

  1. Navigate to Configure > Admin > Security > Partitions.
  2. Click New .
  3. Enter 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.
  4. Click Save & Close.

Create a Credential

To create a Credential:

  1. Navigate to Admin > Security > Credentials.
  2. Click New .
  3. Choose JDBC from the CredentialProtocol dropdown list.
  4. Enter the name of a Real User. This field is case-sensitive.
  5. In the Endpoint field, enter System_Oracle.
  6. Click Save & Close.

Import Users

To import users:

  1. Navigate to Configure > Admin > Security > Users.
  2. Choose Import Users.
  3. Fill in a name into the Search field and choose Search.
  4. 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