Some more cool stuff in 11g, the Gateways install has a lightweight remote/scheduler agent for executing tasks such as native unloaders or any custom activity. This follows on from the post on the 11g Heterogeneous Agent, and the agent is installed from the software downloaded in that post.
The setup and configuration is fairly simple but is kind of scattered across a few manuals, here is a simple example that steps through executing a batch script on a Windows system from an 11g instance on Linux. The database user defining the job needs CREATE JOB and CREATE EXTERNAL JOB database privileges.
Here are the administration/setup steps of agent (documented in the Oracle Database Administrator's Guide - Enabling and Disabling Remote External Jobs, in the Administering Oracle Scheduler chapter);
1. Run the script prvtrsch.plb with following command:
2. configure the http port on the database instance, so the remote agent can register itself as a valid agent. For example below I used port 1540 using a privileged user;
3. Set the agent registration password using a privileged user;
4. Install the agent software on the remote node using the Gateways install:
I then changed the port number in my case in the sched.conf file in the agent's ORACLE_HOME ( I set PORT=1539)
5. On the remote node, register the agent with the database instance using the port used above and your will be prompted for the agent registration password;
.\schagent -registerdatabase database_hostname database_http_port
6. Start the agent (using the Windows services if Windows for example, or 'schagent -start' or 'schagent -stop' for UNIX.
That's the administration side of the configuration complete. Now we can move on to an example simple job, which will execute a batch script for example.
I created a user in Windows for running the job and gave the user the right to 'Log on as a batch job', under 'Local Security Settings' in the administrative tools in Windows XP for example.
To setup the job I had to create a credential for the remote system with the user name and password
EXEC DBMS_SCHEDULER.CREATE_CREDENTIAL('TESTCRED', 'TEST', 'test_user_windows_password');
I can then use the credential TESTCRED in a remote job, note I used enabled=false in the CREATE_JOB otherwise the job would have been deemed local and executed - the remote details of the job are defined via the SET_ATTRIBUTE api call;
DBMS_SCHEDULER.CREATE_JOB(job_name => 'UNLOADJOB',
job_type => 'EXECUTABLE',
job_action => 'c:\unloadutils\unload.bat',
DBMS_SCHEDULER.SET_ATTRIBUTE('UNLOADJOB', 'credential_name', 'TESTCRED');
DBMS_SCHEDULER.SET_ATTRIBUTE('UNLOADJOB', 'destination', 'mywindowshost:1539');
Whilst I was debugging and building the example I used the LOGGING_FULL logging level to get diagnostics, job details can be found in the view USER_SCHEDULER_JOB_RUN_DETAILS.
For example one time I forgot to register the agent with the database and received errors such as (this was recorded in the job run details view above);
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-28759: failure to open file
Above the port number 1539 is the port number my remote agent service is using. This port number is defined in the schagent.conf on the remote system where the agent is running. In that file you can define other useful info like tracing levels that will get written to the agent.log file on the remote system.
You can incorporate file retrieval and pushing also using this remote agent using the DBMS_SCHEDULER apis, for example to retrieve the unloaded data file from above. The command below will retrieve the remote file scottish_sales.dat via the remote agent on mywindowshost, to the database systems DATA_FILE_DIR SQL directory, in a file named scottish_sales.dat;
exec DBMS_SCHEDULER.GET_FILE ('c:\stage\scottish_sales.dat', 'mywindowshost:1539','TESTCRED','scottish_sales.dat', 'DATA_FILE_DIR')
So here we've seen a few useful areas for executing remote programs based on a lightweight agent, and retrieval/pushing of files via the agent. These APIs can be incorporated in OWB mappings or process flow objects. Only an 11g database instance can
schedule remote jobs using this agent (unlink the Gateway agent where prior releases can be patched to take advantage of it).
Hope this 'hello world' style example of executing remote jobs is a useful overview of some interesting new capabilities in 11g applicable to OWB and SQL/PLSQL development in general. The full documentation for DBMS_SCHEDULER provides a rich set of functionality you can check for much more besides.
If you are getting an error about bad certificates you may be hitting database scheduler bug
6862726. In 22.214.171.124 there was a bug where agent certificates would expire after 60 days (this has been fixed in 126.96.36.199).
To resolve this is what you can do
- stop the agent
- remove the file $AGENT_HOME/data/agent.key
- re-register the agent with the database
- restart the agent