actually, you would have to open port 1521 for INBOUND traffic no?

the client initiated the conversation from the outside didn't it?

yes, ok -- client firewall would have to open port NNNN to outbound traffic. (i was thinking firewall was protecting database ;)

many commercial firewalls have builtin "i know what sqlnet traffic looks like" so they can make sure that only sqlnet traffic goes out over this port (of your choosing)

your security folk should know how to configure a firewall to permit traffic through.

they should actually have been able to definitely predict this would happen.

opening 1521 may or may not be sufficient. depends on how you connect to the database, what your OS's are.

and opening 1521 opens a host of other questions...

I'm not going to directly answer this, for this is something that affects your security here and there are many ways to set things up (correctly and not correctly). Not knowing your setup, your needs, etc -- just saying "oh, open port 1521" isn't the best thing.

Have your security guys ever set up such a configuration?
Have they consulted with someone who has?

If not, they need to -- if they have, they should know what to do in your case.


My configuration for example is a DMZ setting. firewall between the outside world and my servers, firewall between my servers and me. the servers are all in the middle and are told which servers they are allowed to accept traffic from. My setup would not apply to you at all.

1) i've said "without me being there and understanding your needs, topology, infrastructure, it would be somewhat irresponsible to say "open this port" "

the web server needs access to the database -- they (the security team) needs to provide that access, they understand the layout, I do not.

2) that is saying "dns lookup failed" basically, it cannot find the host

3) you tnsping tns connect strings, not ip addresses.

#2 is not using a newwork to connect to anything. so not really sure what you are trying to show with that one.

#1 uses a network to connect to a remote server.
#2 is trying to connect locally and locally, well, guess there is nothing there.

perhaps you have an environment variable TWO_TASK set and it is trying a remote connection -- but to a host that doesn't exist.

umm, there is nothing wrong with it -- it is just that when you sqlplus system/password -- you are not even asking to connect over the network and since the database is across the network -- you sort of need too.

(you should be really careful cutting and pasting stuff like this, with ip addresses and other information)

ok, good on the ip stuff.


but -- look.  Your database is "over the network".  Your connection over the network:

[oracle@DN-VFWS2 runsqlplus]$ sqlplus 
system@"(description=(Address=(protocol=tcp)(host=100.50.11.88)(port=1521))(conne
ct_data=(service_name=pm.dddd.org)))"

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jan 26 10:54:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL>


<b>WORKED -- no surprises there.  But when you DON'T USE A TNS CONNECT STRING:</b>


[oracle@DN-VFWS2 bin]$ sqlplus

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jan 26 10:58:14 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

<b>it does not work -- why?  because your database is over the network perhaps?  I'm not sure what you think is wrong here -- you need to use "user/password@tnsname" to connect</b>


 

your tnsnames.ora file is botched then.

that is fairly clear at this point. am.dddd.org is not in there


[tkyte@localhost tkyte]$ oerr ora 12154
12154, 00000, "TNS:could not resolve service name"
// *Cause: The service name specified is not defined correctly in the
// TNSNAMES.ORA file.
// *Action: Make the following checks and correct the error:
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// place and accessible. See the operating system specific manual
// for details on the required name and location.
// - Check to see that the service name exists in one of the
// TNSNAMES.ORA files and add it if necessary.
// - Make sure there are no syntax errors anywhere in the file.
// Particularly look for unmatched parentheses or stray characters.
// Any error in a TNSNAMES.ORA file makes it unusable. See
// Chapter 4 in the SQL*Net V2 Administrator's Guide. If
// possible, regenerate the configuration files using the Oracle
// Network Manager.


If you believe it is "correct", perhaps you are using the wrong files - use (assuming LINUX here) strace to SEE what files it is looking at:

[tkyte@localhost tkyte]$ csh
[tkyte@localhost ~]$ strace $ORACLE_HOME/bin/sqlplus scott/tiger@foobar |& grep tns

access("/etc/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/ora9ir2/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/tkyte/.tnsnames.ora", F_OK) = 0
access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/ora9ir2/network/admin/tnsnames.ora", F_OK) = 0
stat64("/home/tkyte/.tnsnames.ora", {st_mode=S_IFREG|0664, st_size=698, ...}) = 0
open("/home/tkyte/.tnsnames.ora", O_RDONLY) = 7
stat64("/home/ora9ir2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=1739, ...}) = 0
open("/home/ora9ir2/network/admin/tnsnames.ora", O_RDONLY) = 7
stat64("/home/tkyte/.tnsnames.ora", {st_mode=S_IFREG|0664, st_size=698, ...}) = 0
stat64("/home/ora9ir2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=1739, ...}) = 0

scott@ORA9IR2> connect
Enter user-name: scott@ora9ir2.us.oracle.com
Enter password: *****
Connected.
scott@ORA9IR2> connect
Enter user-name: scott
Enter password: ***************************
Connected.
scott@ORA9IR2>


either or should be ok

tnsping am.dddd.org


period.

please contact support.

umm, that was over the network.

if the database is "local", you don't use @connect_string

could use lots of things, ssh included, sure.

typically means the hostname in the tnsnames.ora is wrong.....


check your environment, remember oc4j is running under the app server environment and that could be very different from yours.

they have different hostnames???? all this is saying is the machine you did the tnsping from (the test) was able to resolve ip_add but the other machine was NOT.

your tnsnames.ora file on the server doesn't have this entry or the sqlnet.ora on that server is different, causing a default domain to be added.


do this on the linux box:

$ strace tnsping SOPRA_TOSHIBA 2>&1 | grep tnsnames
access("/home/tkyte/.tnsnames.ora", F_OK) = 0
access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/home/ora9ir2/network/admin/tnsnames.ora", F_OK) = 0
stat64("/home/tkyte/.tnsnames.ora", {st_mode=S_IFREG|0664, st_size=698, ...}) = 0
open("/home/tkyte/.tnsnames.ora", O_RDONLY) = 3
stat64("/home/ora9ir2/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=823, ...}) = 0
open("/home/ora9ir2/network/admin/tnsnames.ora", O_RDONLY) = 3


and cut and paste your results.

can you show us that:

/opt/ora9/product/9.2/network/admin/tnsnames.ora

is "well formed"

Your sqlnet.ora for example is not if it ends with ) like that.

and that is not a well formed tnsnames.ora file.

SOPRA_TOSHIBA <<<<<<<<===== missing an = sign
(DESCRIPTION =
(ADDRESS_LIST =

the magic of fork and exec, it is the way it works. you "inherit" things.

yes, it is not only possible, it is recommended and done all of the time.

you use the listener of the HIGHEST release of Oracle, not the lowest.

umm, sort of sketchy information here.

no clue what you want us to comment on?

depends on how you open the firewall.

If you just ask them to open 1521 or whatever, you are just opening a port. Rather simplistic and yes, a problem

Just like if you asked me to open port 80 - and not restrict it to http traffic (eg: no TELNET over port 80, no smtp over port 80, just http).

does their firewall have a sqlnet filter (don't ask me, I wouldn't know)
do they have experience configuring it (apparently not)
is it against their security policy (probably)

I cannot sqlnet to my own database from outside Oracle. In fact, I can only sqlnet from inside Oracle to my database in the DMZ (my database in the DMZ cannot sqlnet into Oracle).

And even if I said "myth", I would pray they wouldn't pay attention to me - that would not be a very good approach either. Playing around with the network capabilities of a firewall isn't something you do without a bit of planning, research and thought.

none, we are firewall "unaware". If you cannot tnsping from the application server to the database, then it is not set up DMZ (networking) wise.

You should probably *not* be using 1521 (that is a rather obvious port)...


get onto the application server and tnsping the 10g database, if you cannot - then then either your sqlnet configuration is wrong (wrong host, wrong port) or your network rules don't permit it.