Install and configure Scheduler Agent in Oracle 11g

Install and configure Scheduler Agent in Oracle 11g

Oracle sched is a powerful scheduled scheduling program that not only schedules jobs to run in the local database, but also initiates these jobs on the remote host. You do not have to install the Oracle database on the remote host, however, Scheduler Agent must be installed. The following describes the complete installation process.

The installation involves the following two steps:

1. Configure the master database that stores remote job information and initiates Scheduling

2. Install Scheduler Agent on the remote host that runs the remote job. If Oracle Database is not installed on this remote host, you can only run the remote external job, if Oracle Database is installed, you can run the remote database job.

The complete installation process is as follows, which is verified in version 11.2.0.3.

=========== 1. Configure the master database for storing the remote job information ==========
///////////////////////////////////
/// Step 1: Install the XDB component
///////////////////////////////////
### Check whether XDB has been installed in the library before installation. If XDB has been installed, check whether all XDB components are in a valid state;
--- The following method checks the XDB component. If it is VALID, the XDB component is in normal state.
Col comp_name format a30
Set linesize 130
Select comp_name, status from dba_registry where comp_name = 'oracle XML database ';

COMP_NAME STATUS
----------------------------------------------------
Oracle XML Database VALID

--- If the status is not VALID, you need to run the following script and decide whether to reinstall it based on the Script output.
Set serveroutput on

DECLARE
V_xdb_installation_trigger number;
V_dropped_xdb_instll_trigger number;
V_dropped_xdb_instll_tab number;

BEGIN
Select count (*) into v_xdb_installation_trigger
From dba_triggers
Where trigger_name = 'xdb _ INSTALLATION_TRIGGER 'and owner = 'sys ';

Select count (*) into v_dropped_xdb_instll_trigger
From dba_triggers
Where trigger_name = 'dropped _ XDB_TRIGGER 'and owner = 'sys ';

Select count (*) into V_dropped_xdb_instll_tab
From dba_tables
Where table_name = 'dropped _ XDB_INSTLL_TAB 'and owner = 'sys ';

IF v_xdb_installation_trigger> 0 OR v_dropped_xdb_instll_trigger> 0 OR v_dropped_xdb_instll_tab> 0 then

IF v_xdb_installation_trigger> 0 THEN

Dbms_output.put_line ('please proceed to run the command SQL> drop trigger sys. xdb_installation_trigger ');
-- Drop trigger sys. xdb_installation_trigger;
End if;

IF v_dropped_xdb_instll_trigger> 0 THEN
Dbms_output.put_line ('please proceed to run the command SQL> drop trigger sys. dropped_xdb_instll_trigger ');
-- Drop trigger sys. dropped_xdb_instll_trigger;
End if;

IF v_dropped_xdb_instll_tab> 0 THEN
Dbms_output.put_line ('please proceed to run the command SQL> drop table sys. dropped_xdb_instll_tab ');
-- Drop table sys. dropped_xdb_instll_tab;
End if;

ELSE
Dbms_output.put_line ('please proceed to run the XDB install or upgrade ');

End if;

END;
/

### The method for installing XDB is summarized as follows:
--- Create a table space dedicated to XDB. If you want to use SecureFile Lobs, you must use segment space management auto
Create tablespace xdbts datafile '/oradata06/testaaaaa/xdbts1.dbf' size 500 m extent management local segment space management auto;

--- Run @? /Rdbms/admin/catqm. SQL <XDB pwd> <XDB default tbs> <XDB temporary tbs> <YES or NO>
The parameter values are as follows:
<XDB pwd>: XDB user password
<XDB default tbs>: XDB user default tablespace
<XDB temporary tbs>: XDB user temporary tablespace
<XDB temporary tbs>: YES or NO (YES-> Use SecureFile lobs; NO-> Use BasicFile Lobs)

--- Run the catqm. SQL script
Spool xdb_install.log
Set echo on;
@? /Rdbms/admin/catqm. SQL asdf3_14 xdbts temp YES
@? /Rdbms/admin/utlrp. SQL
Set echo off;
Spool off;

--- Check the status of the XDB component after installation.
Col comp_name format a30
Set linesize 130
Select comp_name, status from dba_registry where comp_name = 'oracle XML database ';

COMP_NAME STATUS
----------------------------------------------------
Oracle XML Database VALID

///////////////////////////////////
/// Step 2: Install the Oracle Text Group
///////////////////////////////////
The Oracle Text Component is usually automatically installed when dbca is used to create a database. However, if the database is created in Manual mode, you need to install it manually.
--- Use the following method to determine whether Oracle Text has been installed. If Oracle Text is installed
Col comp_name format a20
Set linesize
Select comp_name, status, substr (version, 1, 10) as version from dba_registry where comp_id = 'context ';

COMP_NAME STATUS VERSION
--------------------------------------------------------------
Oracle Text VALID 11.2.0.3.0

### If Oracle Text is not installed, follow these steps
--- Install the Oracle Text Component on the database side and configure the default language
Sqlplus '/as sysdba'
SQL> @? /Ctx/admin/catctx. SQL asdf3_14 SYSAUX TEMP NOLOCK --- four parameters are represented in sequence: the password of the ctxsys user, the default tablespace OF THE ctxsys user, the temporary tablespace OF THE ctxsys user, and whether the ctxsys is locked after being created (LOCK: locked; NOLOCK: Not locked)

--- The default language for ctxsys user login configuration is English
Sqlplus ctxsys/asdf3_14
SQL> @? /Ctx/admin/defaults/dr0defin. SQL "AMERICAN ";

--- Lock ctxsys for security reasons
Sqlplus '/as sysdba'
Alter user ctxsys account lock password expire;

--- Add the $ ORACLE_HOME/ctx/lib path to the OS environment variable of the database.
Export LD_LIBRARY_PATH = $ ORACLE_HOME/ctx/lib: $ LD_LIBRARY_PATH

--- Verify whether the oracle text Component is successfully installed
SQL> select comp_name, status, substr (version, 1, 10) as version from dba_registry where comp_id = 'context ';

COMP_NAME STATUS VERSION
-------------------------------------
Oracle Text VALID 11.2.0.3.0

SQL> select * from ctxsys. ctx_version;

VER_DICT VER_CODE
----------------------
11.2.0.3.0 11.2.0.3.0

SQL> select object_name, object_type, status from dba_objects where owner = 'ctxsys 'and status! = 'Valid' order by object_name;

No rows selected

///////////////////////////////////
/// Step 3: configure the database environment required for running remote jobs
///////////////////////////////////
### Configure an HTTP service port for communication between the master database server of the job and the remote server with the schedule agent. Do not repeat the defined port with other applications.
Sqlplus/as sysdba
Exec dbms_xdb.sethttpport (30405 );
Commit;

### Check whether the configuration is effective at the db and OS levels
Select dbms_xdb.GETHTTPPORT () from dual;
DBMS_XDB.GETHTTPPORT ()
----------------------
30405

! Netstat-an | grep 30405
Tcp 0 0 *. 30405 *. * LISTEN

### Run the prvtrsch. plb script to create a new user named REMOTE_SCHEDULER_AGENT
@? /Rdbms/admin/prvtrsch. plb

SQL> select username, account_status from dba_users where username = 'remote _ SCHEDULER_AGENT ';

USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
REMOTE_SCHEDULER_AGENT EXPIRED & LOCKED

### Set the password for the scheduler agent to connect to the master database
Exec dbms_scheduler.set_agent_registration_pass (registration_password => 'asdf3 _ 14 ');

### It is important to set shared_servers to a non-zero value. Otherwise, the scheduler agent cannot be successfully registered.
Alter system set shared_servers = 1 scope = both;

### The lnsrctl status output on the job master server shows that PORT = 30405 is being listened on.
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/aix risc System/6000: Version 11.2.0.3.0-Production
Start Date 06-FEB-2015 07:49:08
Uptime 9 days 3 hr. 8 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener. ora
Listener Log File/oracle/app/oracle/diag/tnslsnr/jq570322b/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 10.10.141.209) (PORT = 1521 )))
(DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1521 )))
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = jq570322b) (PORT = 30405) (Presentation = HTTP) (Session = RAW ))

###/Etc/hosts Add the remote host IP address and host name ing to install scheduler agent, this step can avoid the ORA-29257 generated later when the registration
10.10.141.206 jq570321a

At this point, the configuration of the master database server is complete.

For more details, please continue to read the highlights on the next page:

1 2 Next Page