Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Exclusive offer: get 50% off this eBook here
Mastering Oracle Scheduler in Oracle 11g Databases

Mastering Oracle Scheduler in Oracle 11g Databases — Save 50%

Schedule, manage, and execute jobs in Oracle 11g Databases that automate your business processes using Oracle Scheduler with this book and eBook

$23.99    $12.00
by Ronald Rood | July 2009 | Oracle

This article by Ronald Rood will be of a great help in setting up remote external jobs introduced in Oracle 11g. This article also answers questions like how is this related to the old-fashioned local external jobs that we know since Oracle 10g and why we should get rid of the old external jobs.

Oracle Scheduler in Oracle 10g is a very powerful tool. However, Oracle 11g has many added advantages that give you more power. In this article by Ronald Rood, we will get our hands on the most important addition to the Scheduler—the remote job agent . This is a whole new kind of process, which allows us to run jobs on machines that do not have a running database. However, they must have Oracle Scheduler Agent installed, as this agent is responsible for executing the remote job. This gives us a lot of extra power and also solves the process owner's problem that exists in classical local external jobs. In classical local external jobs, the process owner is by default nobody and is controlled by $ORACLE_HOME/rdbms/admin/externaljob.ora. This creates problems in installation, where the software is shared between multiple databases because it is not possible to separate the processes. In this article, we will start by installing the software, and then see how we can make good use of it. After this, you will want to get rid of the classical local external jobs as soon as possible because you will want to embrace all the improvements in the remote job agent over the old job type.

Security

Anything that runs on our database server can cause havoc to our databases. No matter what happens, we want to be sure that our databases cannot be harmed. As we have no control over the contents of scripts that can be called from the database, it seems logical not to have these scripts run by the same operating system user who also owns the Oracle database files and processes. This is why, by default, Oracle chose the user nobody as the default user to run the classical local external jobs. This can be adjusted by editing the contents of $ORACLE_HOME/rdbms/admin/externaljob.ora.

On systems where more databases are using the same $ORACLE_HOME directory, this automatically means that all the databases run their external jobs using the same operating system account. This is not very flexible. Luckily for us, Oracle has changed this in the 11g release where remote external jobs are introduced. In this release, Oracle decoupled the job runner process and the database processes. The job runner process, that is the job agent, now runs as a remote process and is contacted using a host:port combination over TCP/IP.

The complete name for the agent is remote job agent, but this does not mean the job agent can be installed only remotely. It can be installed on the same machine where the database runs, and where it can easily replace the old-fashioned remote jobs. As the communication is done by TCP/IP, this job agent process can be run using any account on the machine. Oracle has no recommendations for the account, but this could very well be nobody. The operating system user who runs the job agent does need some privileges in the $ORACLE_HOME directory of the remote job agent, namely, an execution privilege on $ORACLE_HOME/bin/* as well as read privileges on $ORACLE_HOME/lib/*. At the end of the day, the user has to be able to use the software. The remote job agent should also have the ability to write its administration (log) in a location that (by default) is in $ORACLE_HOME/data, but it can be configured to a different location by setting the EXECUTION_AGENT_DATA environment variable.

In 11g, Oracle also introduced a new object type called CREDENTIAL. We can create credentials using dbms_scheduler.create_credential. This allows us to administrate which operating system user is going to run our jobs in the database. This also allows us to have control over who can use this credential. To see which credentials are defined, we can use the *_SCHEDULER_CREDENTIAL views. We can grant access to a credential by granting execute privilege on the credential. This adds lots more control than we ever had in Oracle 10gR2. Currently, the Scheduler Agent can only use a username-password combination to authenticate against the operating system.

The jobs scheduled on the remote job agent will run using the account specified in the credential that we use in the job definition. Check the Creating job section to see how this works. This does introduce a small problem in maintenance. On many systems, customers are forced to use security policies such as password aging. When combining with credentials, this might cause a credential to become invalid.

Any change in the password of a job runtime account needs to be reflected in the credential definition that uses the account.

As we get much more control over who executes a job, it is strongly recommend to use the new remote job agent in favor of the classical local external jobs, even locally. The classical external job type will soon become history.

A quick glimpse with a wireshark, a network sniffer, does not reveal the credentials in the clear text, so it looks like it's secure by default. However, the job results do pass in clear text. The agent and the database communicate using SSL and because of this, a certificate is installed in the ${EXECUTION_AGENT_DATA}/agent.key.  You can check this certificate using Firefox. Just point your browser to the host:port where the Scheduler Agent is running and use Firefox to examine the certificate.

There is a bug in 11.1.0.6 that generates a certificate with an expiration date of 90 days past the agent's registration date. In such a case, you will start receiving certificate validation errors when trying to launch a job. Stopping the agent can solve this. Just remove the agent.key and re-register the agent with the database.

The registration will be explained in this article shortly.

Installation on Windows

We need to get the software before the installation can take place. The Scheduler Agent can be found on the Transparent Gateways disk, which can be downloaded from Oracle technet at http://www.oracle.com/technology/software/products/database/index.html.

There's no direct link to this software, so find a platform of your choice and click on See All to get the complete list of database software products for that platform. Then download the Oracle Database Gateways CD. Unzip the installation CD, and then navigate to the setup program found in the top level folder and start it.

The following screenshot shows the download directory where you run the setup file:

After running the setup, the following Welcome screen will appear. The installation process is simple.

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Click on the Next button to continue to the product selection screen.

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Select Oracle Scheduler Agent 11.1.0.6.0 and click on the Next button to continue. Enter Name and Path for ORACLE_HOME (we can keep the default values).

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Now click on Next to reach the screen where we can choose a port on which the database can contact the agent.

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

I chose 15021. On Unix systems, pick a port above 1023 because the lower ports require root privileges to open. The port should be unused and easily memorizable, and should not be used by the database's listener process. If possible, keep all the remote job agents registered to the same database and the same port. Also, don't forget to open the firewall for that port. Hitting the Next button brings us to the following Summary screen:

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

We click on the Install button to complete the installation. If everything goes as expected, the End of Installation screen pops up as follows:

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Click on the Exit button and confirm the exit.

We can find Oracle Execution Agent in the services control panel. Make sure it is running when you want to use the agent to run jobs.

Mastering Oracle Scheduler in Oracle 11g Databases Schedule, manage, and execute jobs in Oracle 11g Databases that automate your business processes using Oracle Scheduler with this book and eBook
Published: June 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Installation on Linux

Having downloaded and unzipped the installer, navigate to the gateways directory and start the installer with ./runInstaller. Now we will get the same screens as we did for the Windows installation. The only difference, apart from the slashes pointing in the right direction, is the pop up asking to run root.sh at the end of the installation. Running root.sh sets setuid root on the $ORACLE_HOME/bin/jssu executable to the setuid root.

Remote Job Agent in Oracle 11g Database with Oracle Scheduler

Run root.sh as requested and finish the installation. The root.sh only sets setuid root on the $ORACLE_HOME/bin/jssu executable using the following code:

# jssu must be setuid and owned by root
if [ -f $ORACLE_HOME/bin/jssu ]; then
$CHOWN root $ORACLE_HOME/bin/jssu
$CHMOD 4750 $ORACLE_HOME/bin/jssu
fi

Put a schagent:/data/app/oracle/product/schagent/11.1.0.6:N tag in /etc/oratab and use oraenv to set the environment for the agent as follows:

In the listing mentioned in the screenshot above, it is clearly visible that jssu has the setuid root privileges. This also means that the root.sh has been successfully run. The setuid root privilege connotes that the executable can use the privileges of the owner of this executable, which is root in this case.

For Linux, Mac OS X, and AIX, the default location for oratab is /etc/; and for Solaris, it is /var/opt/oracle/. Check the platform-dependent installation manual for the default location on your platform.

Upgrading the remote job agent

It's a good idea to apply any new updates published by Oracle. If you are still using the 11.1.0.6 agent, you will see some problems that are fixed by the 11.1.0.7 upgrade. The upgrade of the agent is not very difficult once you know that the same patch set should be applied as that for the database server installation. This is not very well documented, but this is how it works—start the installer found in the patch set, and then select the agent's ORACLE_HOME to upgrade.

The upgrade process is quick and easy till the last part—the execution of the root shell. In the 11.1.0.7 patch, you are asked to run the root.sh. Run it as root and check the properties of ${ORACLE_HOME}/bin/jssu. This file should have the setuid root privileges to function correctly. The root.sh that we are requested to run contains the following:

#!/bin/sh
/data/app/oracle/schagent/11.1.0.7.0/install/root_schagent.sh

The root_schagent.sh file contains an error that prevents the setting of the setuid root on the jssu file. The root_schagent.sh file begins with this:
#!/bin/sh
ORACLE_HOME=%ORACLE_HOME%
This is fine for a Windows system, but not for a Unix or Linux system. In these systems, we expect the following:
#!/bin/sh
ORACLE_HOME=$ORACLE_HOME

Change the root_schagent.sh file accordingly. Make sure that ORACLE_HOME is defined and run the root.sh again. Now the setuid root bits should be in place.

Silent install of the remote job agent

When you decide to use the remote job agent, there will be a lot of installation work. We can use the interactive installer as described before, but this can also be done in a much faster way by using the silent install option. This will work best when the machines on which you are installing have uniform installation. First, decide what the ORACLE_HOME_NAME and ORACLE_HOME locations will be. This is the standard for all silent Oracle installations. For the agent, we also need HOST_NAME and PORT where the agent will listen at the time of installation. We can easily change that later, as most of us prefer to do. Here we will see the installation of the base release followed by the patch.

Base release

For the base release, use the same Gateways CD that we used in the interactive installation. Just change the current directory to the gateways directory where you can see the runInstaller executable. The base release is 11.1.0.6. We will immediately upgrade it to the latest possible release, which (at the time of writing this) is 11.1.0.7; so we are going to put the base release in the 11.1.0.7.0 directory. If you are not planning to do this upgrade, it would make more sense to install the base release in a 11.1.0.6.0 directory with an adjusted ORACLE_HOME_NAME of the schagent111060.

export s_HOST_NAME=pantzer.lan
export s_PORT=15021
./runInstaller -silent -responseFile $PWD/response/schagent.rsp
ORACLE_HOME_NAME=schagent111070
ORACLE_HOME=/data/app/oracle/schagent/11.1.0.7.0
s_nameForDBAGrp=dba n_configurationOption=3
FROM_LOCATION="$PWD/stage/products.xml"
s_hostName=${s_HOST_NAME} s_port=${s_PORT}

When the installer is ready, run the root.sh script as is usually done after an installation. After this, check that jssu has the setuid root privileges.

ls -l /data/app/oracle/schagent/11.1.0.7.0/bin/jssu

Patch to the latest available level

The Scheduler is improving rapidly, so it is preferable to perform the upgrades at regular intervals. We use the same source of installation for the agent as we do for the database software upgrade. Again, start in the same working directory as that of the interactive installation where you can see the runInstaller executable.

Since 11.1.0.7, Oracle asks during the installation whether (or not) you want to be notified by email when a new security patch is released. Normally, we would already have this notification in place. So during the installation, we will most likely decline this using:

./runInstaller -silent -responseFile $PWD/response/patchset.rsp
ORACLE_HOME_NAME=schagent111070
ORACLE_HOME=/data/app/oracle/schagent/11.1.0.7.0
DECLINE_SECURITY_UPDATES=true

This starts the installer and performs the upgrade of the previously installed ORACLE_HOME. There is a problem in the script that is called from the root.sh, which is generated for us. We should fix this using the following before running the root.sh script:

cat /data/app/oracle/schagent/11.1.0.7.0/install/root_schagent.sh
|sed "s/%ORACLE_HOME%/$ORACLE_HOME/" >/tmp/rs$$
cp -p /tmp/rs$$
/data/app/oracle/schagent/11.1.0.7.0/install/root_schagent.sh
rm /tmp/rs$$

Now run the root.sh as we normally do. It is important that jssu has the setuid root privileges, so check them using:

ls -l /data/app/oracle/schagent/11.1.0.7.0/bin/jssu

This should look as follows:

-rwsr-x--- 1 root dba 29497 Sep 11 2008
/data/app/oracle/schagent/11.1.0.7.0/bin/jssu
Mastering Oracle Scheduler in Oracle 11g Databases Schedule, manage, and execute jobs in Oracle 11g Databases that automate your business processes using Oracle Scheduler with this book and eBook
Published: June 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Preparing the database for remote agent usage

There are a few things to check and do before we can use the remote job agent. The following steps will be explained in detail:

  1. Verify that XDB is installed.
  2. Set HTTP port, if not set.
  3. Install the remote job agent objects.
  4. Set the registration password.

Verifying the XDB installation

In order to use a remote Scheduler, the database needs to have XML database installed.

Check this by verifying dba_registry for the existence and validity of the comp_id 'XDB'as follows:

In this case, the XML Database is installed and valid.

Setting the HTTP port

The agent uses the database's HTTP port to contact the database. Check which port is in use and set a port if needed. As with the agent, make sure that the port is above 1023, as the lower ports are reserved for the root. There is not much to say about which port is good or bad, but don't take the port that the remote job agent is listening on. As we poor humans have trouble remembering simple numbers, it might be convenient to pick 15021 for the agents and 16021 for the database.

Other Oracle products, such as Application Express, use the database HTTP port. When setting this port, ensure that all of the uses are taken into account.

Check the port with this:

SELECT DBMS_XDB.gethttpport FROM dual;

If needed, set the port with this:

--/
begin
DBMS_XDB.sethttpport(16021);
end;
/

The agent can contact the database on port 16021 to make sure that it is not messed up by any firewalls. Now, create the database objects used by the Scheduler Agent.

Creating remote Scheduler objects

You can create the remote_scheduler_agent user in the database and a few lines of code using the following:

sqlplus '/ as sysdba' @?/rdbms/admin/prvtrsch.plb

This password will only be used to register the agent. After the registration, an SSL certificate will be used to authenticate the agent.

Configuring a remote agent

The remote agent's configuration is simple. It has to be registered as a target for the database. The registration has to be done manually using the schagent utility found in $ORACLE_HOME/bin.

On Linux, I added a pseudo ORACLE_SID (schagent) to the oratab file (schagent:/data/app/oracle/product/schagent/11.1.0.6:N ). We can use that and oraenv to set the right environment variables for us—this was nice and easy.

Now that we know the registration password, we can use it to register the agent from the machine on which the agent works for us. There is no need to keep a record for this password. It is only used during the registration process and we can change it whenever we want. The following screenshot shows the registration of the agent:

On Windows, the screen output should look like this:

From now on, the agents should be useable.

Troubleshooting

In case of problems with the installation, check if the port is reachable with telnet to the host and port, for example telnet pantzer.lan 16021. This should give a clear screen as a response—definitely not "connection refused" (wrong machine/port or service is not running) or no response at all (firewall). If you get the response "connection refused", check whether the agent is running or not. If the agent is not running, start it using schagent-start and try again. If the agent was running, check the port that the agent is using in the agent configuration file.

The agent configuration file is found in $ORACLE_HOME/schagent.conf and in my system contains the following:

# This is the configuration file for the Oracle Scheduler Agent.
#
# The agent will look first for schagent.conf in {EXECUTION_AGENT_DATA}
# and then in {EXECUTION_AGENT_HOME}. It will only use whichever is
# found first and throw an error if none is found.
#
# You can change these values to suit your environment.
# network port to listen on for requests (between 1 and 65536)
PORT=15021
# host name of the host the agent is running on. If this is not
# specified, the resolved name of the local address will be used
HOST_NAME = NLLRONROO.lan
# maximum number of jobs to run simultaneously (between 1 and 1000)
MAX_RUNNING_JOBS=5
# if this is set to TRUE, the agent will reject put_file requests
DISABLE_PUT_FILE=FALSE
# if this is set to TRUE, the agent will reject get_file requests
DISABLE_GET_FILE=FALSE
# if this is set to TRUE, the agent will reject job execution requests
DISABLE_JOB_EXECUTION=FALSE
# the agent will reject any attempt to use any user on this list.
# This list should be comma-separated and is case-insensitive.
DENY_USERS=root,administrator,guest
# if this list is not empty, the agent will only allow use of a user
# on this list. This list should be comma-separated and is
# case-insensitive.
# ALLOW_USERS=
# types of logging to do. Zero or more of the following options:
# OVERVIEW, WARNINGS, ALL, ERROR_STACKS, MILLISECONDS
LOGGING_LEVEL=OVERVIEW,WARNINGS

The first thing that comes to my mind when a registration has been done is: "How can I check this?" Unfortunately, there appears to be no way to check the status of the agent's registration—not even in the database. It would be very convenient to have an Oracle view that gives an oversight of which agents are talking with the database. I filed an enhancement request (7462577) for this. So with a little luck, we can check the status of remote agents in the near future. What we can do is see if the agent is running on the host and check the log file as follows:

Or we can check a specific agent's process as follows:

Or we can check the logfile of the agent as follows:

Summary

In this article series, we have seen the most important change in Scheduler 11g—the remote external job. It is easy to set up and configure. But at the same time, it seems impossible to see what agent is configured against the database.

To summarize, we can say, we have covered the following in this article series:

  • How robust the agent is
  • How easy it is to configure multiple agents on a single host using a single software installation
  • How to get remote log files from the agent to the originating database
  • How to target a Windows system as a remote platform
  • How to target a Unix/Linux system as a remote platform
  • How to check the port that the agent is using
  • Troubleshooting

 

If you have read this article you may be interested to view :

About the Author :


Ronald Rood

Ronald Rood has been an IT professional for over 20 years. His roots are in the Bull gcos8 mainframes where he played with assembly language until Oracle and UNIX came across his path. He eagerly joined the Oracle community and became a skilled innovating DBA and trouble shooter. Ronald's real power is in the combination of the rich UNIX world and Oracle. There is no such thing as a problem that can not be solved; it just might take a little time.

Next to C, PRO*C, lots of scripting languages and – of course – PL/SQL, he also speaks Dutch, English, German, and some French. In his private time that he shares with his family with two children he likes to take an occasional dive (from the sky), fly radio controlled models, ride recumbent, and work as a volunteer for a local Water Scouts group.

Currently Ronald is one of the Ciber oracle consultants in The Netherlands and cooperates in many projects for many large companies. Before writing this book Ronald wrote oracle related articles for the Dutch oracle user group magazine. On his blog you can find some short articles about various items, mostly oracle related.

Ronald Rood has been interviewed by Oracle about Oracle Scheduler. Listen to it here here.

Books From Packt

Oracle Coherence 3.5
Oracle Coherence 3.5

Oracle Essbase 9 Implementation Guide
Oracle Essbase 9 Implementation Guide

Oracle 10g/11g Data and Database Management Utilities
Oracle 10g/11g Data and Database Management Utilities

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

Processing XML documents with Oracle JDeveloper 11g
Processing XML documents with Oracle JDeveloper 11g

SOA Cookbook
SOA Cookbook

Oracle Web Services Manager
Oracle Web Services Manager

Oracle VM Manager 2.1.2
Oracle VM Manager 2.1.2

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
4
s
k
S
S
9
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software