Home » RDBMS Server » Server Administration » TNS connection closed - Remote Job (Oracle Scheduler, Enterprise 11gR2 - 11.2.0.3.0, Oracle Linux release 5.8)
| TNS connection closed - Remote Job [message #590862] |
Tue, 23 July 2013 11:29  |
 |
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
|
Junior Member |
|
|
I am trying to create a job that will execute a shell script on a remote server (Server B) when an existing job, on the local server (Server A), completes.
I have spent the last couple of days combing through Metalink, Oracle Documentation, AskTom, and Google. I know this is a relatively "new" feature/ability - released with 11.2 - but this particular error stack is no where to be found. Any help would be greatly appreciated. I have attempted to be completely thorough with the information I provided, along with being diligent about masking important configuration information.
While the calling Job is starting the second job, the second job appears to timeout at 60 seconds and the job status is reported as failed with this error stack:
ORA-12537: TNS:connection closed
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 6853
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29261: bad argument
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3975
ORA-06512: at line 1
Configuration information:
Server A and Server B OS Version/Kernal Information:
Oracle Linux Server release 5.8
x86_64
2.6.32-300.24.1.el5uek
Database Version, on all databases, is:
Oracle Database 11g Enterprise Edition
11.2.0.3.0 - 64bit
Remote Scheduler Agent version, on both servers is: 11.2.0.3.1
Here is the listener status on Server B for db2:
serverb $$ lsnrctl status listdb2
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2013 11:39:27
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=***)))
STATUS of the LISTENER
------------------------
Alias listdb2
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 22-JUL-2013 16:20:24
Uptime 0 days 19 hr. 19 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File $ORACLE_HOME/network/admin/listener.ora
Listener Log File $DIAG/diag/tnslsnr/serverb/listdb2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=****)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverb.someplace.net)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverb.someplace.net)(PORT=15212))
(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "db2" has 2 instance(s).
Instance "db2", status UNKNOWN, has 1 handler(s) for this service...
Instance "db2", status READY, has 2 handler(s) for this service...
The command completed successfully
Here are the entries from the listener log file from the failed job time (the job started at 10:05:09 and failed at 10:06:09
<msg time='2013-07-23T10:06:02.630-04:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='serverb' host_addr='127.0.0.1'>
<txt>23-JUL-2013 10:06:02 * ping * 0
</txt>
</msg>
<msg time='2013-07-23T10:06:02.691-04:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='serverb' host_addr='127.0.0.1'>
<txt>23-JUL-2013 10:06:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=serverb)
(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=
(PROTOCOL=TCP)(HOST=serverb.someplace.net)(PORT=1521)))(VERSION=186647296)) * status * 0
</txt>
</msg>
Here is the entry from the alert.log:
Tue Jul 23 10:06:09 2013
Errors in file $DIAG/rdbms/db1/db1/trace/db1_j000_29764.trc:
ORA-12012: error on auto execute of job "USER"."DATA_SYNC_TEST"
ORA-12537: TNS:connection closed
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 6853
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29261: bad argument
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3975
ORA-06512: at line 1
...and the trace file:
Trace file $DIAG/rdbms/db1/db1/trace/db1_j000_29764.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = $ORACLE_HOME
System name: Linux
Node name: servera
Release: 2.6.32-400.26.1.el5uek
Version: #1 SMP Wed Apr 3 14:33:57 PDT 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: db2
Redo thread mounted by this instance: 1
Oracle process number: 55
Unix process pid: 29764, image: oracle@servera (J000)
*** 2013-07-23 10:06:09.641
*** SESSION ID:(393.12163) 2013-07-23 10:06:09.641
*** CLIENT ID:() 2013-07-23 10:06:09.641
*** SERVICE NAME:(SYS$USERS) 2013-07-23 10:06:09.641
*** MODULE NAME:(DBMS_SCHEDULER) 2013-07-23 10:06:09.641
*** ACTION NAME:(DATA_SYNC_TEST) 2013-07-23 10:06:09.641
ORA-12012: error on auto execute of job "USER"."DATA_SYNC_TEST"
ORA-12537: TNS:connection closed
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 6853
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29261: bad argument
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3975
ORA-06512: at line 1
Job Definition in DB1 on Server A:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"USER"."DATA_SYNC_TEST"',
job_type => 'EXECUTABLE',
job_action => '/ServerB/scripts/db2/user/run_data_sync.sh',
event_condition => 'tab.user_data.event_type = ''JOB_SUCCEEDED''
and tab.user_data.object_name = ''ACCEPT_SUBMISSIONS''',
queue_spec => '"SYS"."SCHEDULER$_EVENT_QUEUE","MONITOR_SUBM_ACCPT"',
start_date => to_timestamp_tz('2013-07-23 11:09:30 America/New_York',
'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Test Job - Runs when job1 completes successfully',
auto_drop => TRUE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute(
name => '"USER"."DATA_SYNC_TEST"',
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_FULL);
sys.dbms_scheduler.set_attribute(
name => '"USER"."DATA_SYNC_TEST"',
attribute => 'job_weight',
value => 1);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"USER"."DATA_SYNC_TEST"',
attribute => 'destination',
value => 'SYS.DB2' );
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"USER"."DATA_SYNC_TEST"',
attribute => 'credential_name',
value => '"SYS"."REM_JOB"' );
sys.dbms_scheduler.enable( '"USER"."DATA_SYNC_TEST"' );
END;
"USER" has these permissions:
OWNER TABLE_NAME PRIVILEGE
----- ----------------------- ----------
SYS DEFAULT_JOB_CLASS EXECUTE
SYS REM_JOB EXECUTE
SYS DB2 ALTER
SYS SCHEDULER$_EVENT_QUEUE ENQUEUE
SYS UTL_HTTP EXECUTE
SYS UTL_MAIL EXECUTE
shell script contents:
#!/bin/bash
source /home/directory/db2_env
export MAILLIST=user@email.addr
export LOG_FILE=/serverb/scripts/logs/db2/user/data_sync.txt
rm /serverb/scripts/logs/db2/user/data_sync.txt
$ORACLE_HOME/bin/sqlplus -S \/ as sysdba >> $LOG_FILE << EOF
connect user/****
set scan off
set line 200 pages 100
select systimestamp BEGIN from dual;
@/serverb/scripts/db2/user/run_data_sync.sql
select systimestamp END from dual;
exit
EOF
mail -iInv $MAILLIST -s "DB2 - Data Sync - Job Completed" < $LOG_FILE
I have configured both servers with remote Scheduler agents and both databases have been configured and registered with the agents.
I have executed the shell script on Server B and it performs as expected.
I have the primary job calling the remote job, and that is performing as expected.
I have tested the connection from DB1 on Server A to DB2 on Server B with this code:
SQL> set serveroutput on
SQL> DECLARE
2 versionnum VARCHAR2(30);
3 BEGIN
4 versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('ServerB.someplace.net');
5 DBMS_OUTPUT.PUT_LINE(versionnum);
6 END;
7 /
11.2.0.3.1
PL/SQL procedure successfully completed.
and I have tested the connection from DB2 on Server B to DB1 on Server A with this code:
SQL> set serveroutput on
SQL> DECLARE
2 versionnum VARCHAR2(30);
3 BEGIN
4 versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('ServerA.someplace.net');
5 DBMS_OUTPUT.PUT_LINE(versionnum);
6 END;
7 /
11.2.0.3.1
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
|
| Re: TNS connection closed - Remote Job [message #590870 is a reply to message #590862] |
Tue, 23 July 2013 12:29  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - this can be really awkward.
How did you register the agents with the Scheduler? Was it something like this -
$AGENT_HOME/bin/schagent -registerdatabase serverb.someplace.net 15212
If you are using the default agent listening port of 1500, is there any possibility that it is being blocked? Can you wget to it from the DB server?
Can you post your schagent.conf file?
|
|
|
|
|
|
|
|
| Re: TNS connection closed - Remote Job [message #590879 is a reply to message #590870] |
Tue, 23 July 2013 13:05 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've just had another look at your post, and I'm a bit confused. As I understand it, you have defined the job in the database on servera, and the job is to run a shell script through an agent on serverb. Is that correct? If so, then the listener and database on serverb are completely irrelevant, all you need on B is the agent. But you seem to be testing the connection from the database on B to an agent on A. Can you describe the problem again, without ANY references to a database or listener on B? And also, what is the credential you have set up and passed to the job? It needs to be an OS logon to server B.
|
|
|
|
|
|
| Re: TNS connection closed - Remote Job [message #590882 is a reply to message #590870] |
Tue, 23 July 2013 13:14 |
|
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
|
Junior Member |
|
|
Yes, that is exactly how I registered the database, with a non-default port number of course.
$AGENT_HOME/bin/schagent -registerdatabase serverb.someplace.net 15212
Here is the contents of my schagent.conf file with the port and host_name consistently altered.
PORT=51210
HOST_NAME = serverb.someplace.net
AGENT_NAME=MONITOR_SUBM_ACCPT
MAX_RUNNING_JOBS=5
DISABLE_PUT_FILE=FALSE
DISABLE_GET_FILE=FALSE
DISABLE_JOB_EXECUTION=FALSE
DENY_USERS=root,administrator,guest
# ALLOW_USERS=
SECURE_DATABASES_ONLY=TRUE
LOGGING_LEVEL=ALL
# ORACLE_SID=
# ORACLE_HOME=
The Port Number for the agent and for the database had to be different, so they are.
Here are the results of running wget (I had to look that little tool up first) from ServerA to ServerB. This was the best result - I attempted it with just serverb to begin with, and it defaulted to port :80 which failed: Connection refused. After that I tried it with the database port number which I configured - and that connected, but Authorization failed.
servera $$ wget serverb.someplace.net:51210
--2013-07-23 13:58:02-- http://serverb.someplace.net:51210/
Resolving serverb.someplace.net... 10.200.99.35
Connecting to dev02.flbog.local|10.202.23.128|:51210... connected.
HTTP request sent, awaiting response... 200 No headers, assuming HTTP/0.9
Length: unspecified
Saving to: `index.html'
[ <=> ] 7 --.-K/s in 0s
2013-07-23 13:58:02 (583 KB/s) - `index.html' saved [7]
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 01 14:43:39 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
