X

Welcome to All Things Warehouse Builder

  • ETL
    March 11, 2008

11g Remote Agent

David Allan
Architect

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.

Administration/Setup

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:

SQL> @?/rdbms/admin/prvtrsch.plb

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;

BEGIN

DBMS_XDB.SETHTTPPORT(1540);

END;

/

3. Set the agent registration password using a privileged user;

BEGIN

DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('agent_registration_password');

END;

/

4. Install the agent software on the remote node using the Gateways install:

DatabaseSchedulerAgent:

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.

Example Job

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;

BEGIN

DBMS_SCHEDULER.CREATE_JOB(job_name => 'UNLOADJOB',

job_type => 'EXECUTABLE',

job_action => 'c:\unloadutils\unload.bat',

enabled=> FALSE);

DBMS_SCHEDULER.SET_ATTRIBUTE('UNLOADJOB', 'logging_level',

DBMS_SCHEDULER.LOGGING_FULL);

DBMS_SCHEDULER.SET_ATTRIBUTE('UNLOADJOB', 'credential_name', 'TESTCRED');

DBMS_SCHEDULER.SET_ATTRIBUTE('UNLOADJOB', 'destination', 'mywindowshost:1539');

DBMS_SCHEDULER.ENABLE('UNLOADJOB');

END;

/

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.

Note:

If you are getting an error about bad certificates you may be hitting database scheduler bug

6862726. In 11.1.0.6 there was a bug where agent certificates would expire after 60 days (this has been fixed in 11.1.0.7).

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.