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 Go to next message
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 #590866 is a reply to message #590862] Tue, 23 July 2013 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
any host/server that uses 127.0.0.1 can never be accessed by any remote client.
Re: TNS connection closed - Remote Job [message #590869 is a reply to message #590866] Tue, 23 July 2013 12:24 Go to previous messageGo to next message
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
Junior Member
Very true, so now where is the spot where ServerA is identifying itself as 127.0.0.1 instead of serverA.someplace.net - as I have established it's credentials to be. The only reference to 127.0.0.1 that I have located is in the init.ora file as the smtp_out_server value. However, I tested changing that which produced no change.

Here is an entry in the listener log from another server which is successfully connecting. This server only has an Oracle client installed as it is not an Oracle server.

23-JUL-2013 13:00:01 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=db2)(CID=(PROGRAM=sas)(HOST=serverc.someplace.net)(USER=userc))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.210.98.217)(PORT=42815)) * establish * db2 * 0


... and here are the results from altering the smtp_out_server.

23-JUL-2013 13:08:43 * (CONNECT_DATA=(SID=db2)(CID=(PROGRAM=perl@serverb)(HOST=serverb)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59292)) * establish * db2 * 0


The IP address and the fully qualified hostname are listed in the /etc/hosts file, which is one other location where the localhost entry is found.

There is an ACL set up in db1, which specifically identifies the IP address and fully qualified hostname and is granted to the user account with resolve permissions.

The tnsnames.ora and listener.ora files also use fully qualified hostnames AND IP addresses.
Re: TNS connection closed - Remote Job [message #590870 is a reply to message #590862] Tue, 23 July 2013 12:29 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #590871 is a reply to message #590869] Tue, 23 July 2013 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
content of listener log file that you posted would disagree.
<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>

I am just commenting upon what I observe.

can you post URL to Oracle documentation that leads you to believe that DBMS_SCHEDULER can launch script on remote server?
Re: TNS connection closed - Remote Job [message #590874 is a reply to message #590871] Tue, 23 July 2013 12:58 Go to previous messageGo to next message
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
Junior Member
I understand about the observation - I know it's a networking piece that Oracle has neglected to mention needs to be set/adjusted in their documentation. Here's the documentation url:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/schedadmin001.htm#BAJDFIAB - just in case the url doesn't take you to the right spot - it's in the Database Administrator's Guide - under Configuring Oracle Scheduler in the Using the Oracle Scheduler to Run Remote Jobs section.
Re: TNS connection closed - Remote Job [message #590879 is a reply to message #590870] Tue, 23 July 2013 13:05 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #590881 is a reply to message #590874] Tue, 23 July 2013 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.packtpub.com/article/remote-job-agent-oracle-11g-database-oracle-scheduler

HTH & YMMV
Re: TNS connection closed - Remote Job [message #590882 is a reply to message #590870] Tue, 23 July 2013 13:14 Go to previous messageGo to next message
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]



Re: TNS connection closed - Remote Job [message #590885 is a reply to message #590879] Tue, 23 July 2013 13:41 Go to previous messageGo to next message
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
Junior Member
You can ignore the test from ServerB back to ServerA. With some of Oracle's other products it has been important to ensure that the communication gateway worked bi-directionally, hence the extra, unnecessary test.

The dbms job on db1@servera, runs, completes successfully and places a Successful status in the queue.

The test job, reads the Successful status in the queue and starts the remote job.

The credential being passed is a valid OS user account - in the sandbox environment where I am testing this I am using the Oracle account - and I made the necessary changes to allow that account to run an external job.
Re: TNS connection closed - Remote Job [message #590887 is a reply to message #590881] Tue, 23 July 2013 13:58 Go to previous messageGo to next message
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
Junior Member
Thanks, that is one of the many articles I had already reviewed. I triple checked it again - just in case I had missed a reference to something which the Oracle documentation had not included. However, this is just a replication of the Oracle documentation.

Oh well, off to Oracle Support, the joys of having a use for something still on the bleeding edge of new.
Re: TNS connection closed - Remote Job [message #591082 is a reply to message #590887] Wed, 24 July 2013 12:35 Go to previous messageGo to next message
FL_Snowflake
Messages: 7
Registered: July 2013
Location: Florida
Junior Member
I have found the resolution to my issue, while I was waiting for Oracle Support to figure out what timezone I am really in {SIGH}. Thanks to both of you for your assistance. Here is where to look should this particular issue get someone else.

It was necessary to set the SCHEDULER DESTINATION of the job to the scheduler destination that was created when the database was registered with the remote scheduler agent. The Oracle documentation does not cover using the scheduler destination - or viewing the data dictionary views to track down that information. (These views can be listed with select view_name from dba_views where view_name like 'DBA_SCHED%' order by 1;)

I did stumble across discussion on the scheduler destination, within the documentation for the Scheduler - it just did not quite cover remote jobs - and I had inadvertently created a destination pointing to a database, rather than pointing to the agent. Therefore, the credentials I was using simply weren't valid.

New Features - New Learning opportunites. Smile
Re: TNS connection closed - Remote Job [message #591083 is a reply to message #591082] Wed, 24 July 2013 12:36 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
THANKS for the feedback!
Previous Topic: table query
Next Topic: Installation Oracle Grid Infrastructure 11gR2 when Oracle Enterprise finished
Goto Forum:
  


Current Time: Fri Mar 29 04:16:20 CDT 2024