very help full
Pinakin, October 08, 2001 - 2:31 pm UTC
We just move all database server from one palce to
another(NT ,Unix),Then we got problem with Database
on NT box, we did not connect to database on NT from
outside, so I set USE_SHARED_SOCKET = TRUE in
Regdet32 and it works
Firewall Rules
T.Thiagarajan, January 04, 2002 - 4:46 am UTC
The answer was quite useful for WinNT box, but what about the solaris. Can u tell me how to configure firewall rules for solaris.
January 04, 2002 - 8:15 am UTC
use dedicated servers and you have no problems like you do on NT.
steps 1 and 2 apply to unix, step 3 is unneccessary, unix always has done "shared_sockets"
I think options 1 and 2 are not necessary on Unix boxes
Rajidhar Etta, January 15, 2003 - 7:27 pm UTC
Tom,
From the document , it seems we need to implement one of 3 options only if OS is Windows NT, as only 1521 port is to be opened for Unix.
Right now our application server (9iAS) communicates with DB server which is behind a firewall with out using connection manager (and only 1521 port is opened).
However, we did experienced firewall time out problem for which we are considering
1. Oracle Dead Connection Detection (DCD)
2. Our system administrator suggested tweaking NDD settings for 1521 on client/server boxes so that OS can send ACK packets periodically.
3. Write Custom code to loop through each connection in the middle tier and execute dummy SQL for every "N" minutes.
Is my approach correct?
January 16, 2003 - 8:14 am UTC
well, the article did start off with
This article describes how to get around firewall problems with SQL*Net/Net*8
on NT Servers
on NT servers....
1521 is not a fixed port, you can pick and choose it.
And it only applies with dedicated server, if you use shared server - you are back to CMAN again, even on unix.
It is funny how your company has a security policy that times out idle connections -- but you are trying to find ways to subvert such a policy... Why not have the connection pool time out its connections -- eg: if you havent been used in 5 minutes, disconnect, we don't need you anymore.
That way you would be in compliance with your companies desires.
Thanks ,
Rajidhar Etta, January 16, 2003 - 11:55 pm UTC
Thanks Tom for the reply,
I missed the point that those options are for shared server mode only - thanks for clarfication.
Our application is hosted in a shared environment and firewall rules cannot be changed easily for each customer- thats why we are figuring out ways to solve this problem.
and for Win2k?
Si, May 23, 2003 - 9:53 pm UTC
I think I understand the gist of what you have said about 1521 and firewalls on NT. I am trying to install the same on Win2k. Could you suggest any solutions to get past the block?
Thank you.
May 24, 2003 - 10:13 am UTC
this applies verbaitim to win2k.
Re: Win2k troubles
Si, May 24, 2003 - 10:25 am UTC
Thank you for answering so quickly.
Unfortunately, setting USE_SHARED_SOCKET = TRUE did not do anything. I have since seen other posts online saying that this does not work for most people, I am not sure why.
I am also unsure as to where the connection manager is. A search for the file cman.ora on my drives turned up no results.
Would you have any pointers?
Thanks.
May 24, 2003 - 10:32 am UTC
the documentation -- see the net admin guide.
have you actually tested that 1521 is in fact OPEN, you know, telnet to it and see if telnet can get to the listener. if not, it has nothing to do with shared sockets.
Re: Win2k troubles
Si, May 24, 2003 - 10:35 am UTC
1521 is open so I am ok there.
The Oracle CD I have is a freebie I got from the website many moons ago, and so I do not have any documentation links with me. Do you know where I can find this online? I also don't have a MetaLink account.
Thanks
May 24, 2003 - 11:39 am UTC
otn.oracle.com
Re: Win2k troubles
Si, May 24, 2003 - 12:05 pm UTC
Thank you.
Can you tell me how to telnet to 1521 and check if it is open?
My firewall settings say that it is but a second opinion would be good.
May 24, 2003 - 12:27 pm UTC
telnet hostname 1521
if you see:
[tkyte@tkyte-pc-isdn Desktop]$ telnet hostname 1521
Trying X.Y.Z.A...
Connected to hostname
Escape character is '^]'
it is open, if you see:
[tkyte@tkyte-pc-isdn Desktop]$ telnet tkyte-pc-isdn 1522
Trying 192.168.0.3...
telnet: connect to address 192.168.0.3: Connection refused
[tkyte@tkyte-pc-isdn Desktop]$
it isn't.
Re:Win2k troubles
Si, May 24, 2003 - 12:34 pm UTC
I'm having trouble with this.
When I open the window, I get a prompt saying
Microsoft Telnet>
I then type in telnet x 1521 where x is my computer name and I get an Invalid Command error.
I type in the same but where x is the hostname given by my ISP and I get the same error.
Am I not in the right window?
A reader, May 24, 2003 - 5:47 pm UTC
Si, You should go to command prompt not telnet prompt.
Just want to save Toms time :-)
Thanks
Re:Win2k troubles
Si, May 24, 2003 - 6:56 pm UTC
Thank you very much to both Tom and the kind reader.
Your help is greatly appreciated.
Getting Beyond Firewall
A reader, July 01, 2003 - 1:22 pm UTC
We have a situation where if we run the following command from our Oracle 8i Database, we get the following result:
SQL> select utl_http.request(' http://elocation.oracle.com' from dual;
UTL_HTTP.REQUEST(' HTTP://ELOCATION.ORACLE.COM' --------------------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Oracle eLocation Services</TITLE>
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
<META content="MSHTML 5.50.4134.600" name=GENERATOR><LINK
href="newimages/marlin.css" type=text/css rel=stylesheet>
</HEAD>
<BODY class=OraBody>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
<TR>
<TD vAlign=bottom rowSpan=2><IMG src="newimages/oraclelogo_sizewithprodbrand
.gif"><BR><IMG height=69 src="newimages/elocation.gif" width=340></TD>
<TD align=right>
<TABLE cellSpacing=0 cellPadding=0 align=right border=0>
<TBODY>
</TBODY>
</TABLE>
</TD>
</TR>
<TR>
<TD vAlign=bottom>
<TABLE border=0 cellPadding=0 cellSpacing=0 width="98%">
<TBODY>
<TR>
<TD> </TD>
<TD width=50> </TD>
<TD align=right noWrap width=16><IMG height=21
src="newimages/blue-head.gif" width=12 border=0></TD>
<TD background=newimages/blue-content.gif noWrap width="1%" class=OraN
av1Selected>Legal Notices</TD>
<TD align=right bgColor=#336699 vAlign=top width="12"><IMG height=21
src="newimages/blue-end.gif" width=12 border=0></TD>
</TR>
</TBODY>
</TABLE>
</TD>
</TR>
</TABLE>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD vAlign=bottom height=24>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY>
<TR>
<TD vAlign=bottom noWrap width="52%" bgColor=#336699 height=30> <
/TD>
<TD vAlign=top noWrap width="48%" background="newimages/asysrtb.gif" h
eight=30><IMG height=30 src="newimages/asysrt.gif" width=40 border=0></TD>
<TD width=8 height="30" xbackground="newimages/rhshadow.gif"><IMG heig
ht=30 src="newimages/asylrhs.gif" width=8 border=0></TD>
</TR>
<TR>
<TD vAlign=t
However, when our client issued the same command from their Oracle8i Database, they got:
SQL> select utl_http.request(' http://elocation.oracle.com' from dual;
UTL_HTTP.REQUEST(' HTTP://ELOCATION.ORACLE.COM' ----------------------------------------------------------------------------
----
1 row selected.
I was told that, on their database server, they need userid/password to access the internet. What can they do in this situation where they need to access the internet from a stored procedure in the datbase?
thanks
July 01, 2003 - 2:11 pm UTC
so you are saying that their proxy server challenges them with a user/pass prompt or that they need to use a wallet?
If the proxy needs a user/password, its going to be a bit hard. You would need to either
o write to the proxy server directly using utl_tcp, you would implement a small http client
o load up some java packages into the database that support this functionality.
But the really STICKY question you have to answer is "where do I get the user/password from". Hmm, you would need to get it from them - it would be messy at best.
A reader, July 01, 2003 - 1:54 pm UTC
Getting Beyond the Firewall
A reader, July 01, 2003 - 2:51 pm UTC
Hi Tom,
Is there no database configuration setting that takes proxy info (including userid/password) and allows the database server to access the Internet?
thanks
July 01, 2003 - 3:09 pm UTC
no. not with utl_http and definitely not in 8i (where utl_http was a tad "primitive"
authenticate proxy
Sudhakar Nagisetty, July 01, 2003 - 4:37 pm UTC
here is the code to authenticate to proxy...
handle exceptions accordingly.....
req utl_http.req;
resp utl_http.resp;
name VARCHAR2(256);
value clob ;
tValue clob ;
data VARCHAR2(255);
my_scheme VARCHAR2(256);
my_realm VARCHAR2(256);
my_proxy BOOLEAN;
-- When going through a firewall, pass requests through this host.
-- Specify sites inside the firewall that don't need the proxy host.
utl_http.set_proxy(proxy_info); -- this is your proxy ip address
-- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
-- rather than just returning the text of the error page.
utl_http.set_response_error_check(FALSE);
-- Begin retrieving this web page.
req := utl_http.begin_request(strUrl);
-- Identify ourselves. Some sites serve special pages for particular browsers.
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
utl_http.set_authentication(req, username, password,'Basic',true);
-- Now start receiving the HTML text.
resp := utl_http.get_response(req);
-- Look for client-side error and report it.
IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN
-- Detect whether the page is password protected, and we didn't supply
-- the right authorization.
IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN
utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy);
IF (my_proxy) THEN
dbms_output.put_line('Web proxy server is protected.');
dbms_output.put('Please supply the required ' || my_scheme ||
' authentication username/password for realm ' || my_realm ||
' for the proxy server.');
ELSE
dbms_output.put_line('Web page ' || strUrl || ' is protected.');
dbms_output.put('Please supplied the required ' || my_scheme ||
' authentication username/password for realm ' || my_realm ||
' for the Web page.');
END IF;
ELSE
dbms_output.put_line('Check the URL.');
END IF;
utl_http.end_response(resp);
-- Look for server-side error and report it.
ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN
dbms_output.put_line('Check if the web site is up.');
utl_http.end_response(resp);
END IF;
-- Keep reading lines until no more are left and an exception is raised.
tvalue := strUrl;
begin
LOOP
-- utl_http.read_line(resp, value);
utl_http.read_text(resp, value);
if tvalue = strUrl then
tvalue := value;
else
dbms_lob.append(tvalue,value);
end if;
END LOOP;
exception
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
when others then
utl_http.end_request(req);
end;
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
-- return value;
when others then
utl_http.end_request(req);
-- return value;
END;
authenticate proxy
Sudhakar Nagisetty, July 01, 2003 - 4:38 pm UTC
Sorry forgot to mention, the code is for Oracle 9i
How about Port on Client
LJ, September 17, 2003 - 3:27 pm UTC
How about the ports on the client side? Which port is used by Oracle client, e.g. sqlplus, OCI?
September 17, 2003 - 5:39 pm UTC
they use the ports you told them to -- "connect to host X port 1521 and request the service foo"
the tns connect string tells you which host/port they will use.
i do all the steps that you said but i still have problem
aldobai mostafa, December 25, 2003 - 11:44 am UTC
thanks Tom
my db server oracle 8.1.7 is on one side of firewall
in the other side of the firewall there are my clients and
DNS server ,DHCP server
i configure the firewall to pass any packets (for test prpose only) when i ping the database server i get replay
but when i try to use tnsping to the db server i get
ora-12560 errors
what shall i do to solve this problem ?
with my best regards
December 25, 2003 - 12:03 pm UTC
[tkyte@tkyte-pc-isdn tkyte]$ oerr ora 12560
12560, 00000, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors.For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.
JDBC connection across firewall
Ramadan, December 28, 2003 - 1:53 am UTC
We have a webMethods adapter which links between our SAP server and Oracle db (8.1.5 on NT4.0 WS) using JDBC connection. The connection string looks like this... jdbc:oracle:thin:@10.20.30.40:1521:TFMS
We experienced the same communication problem when we configured only port 1521 in the firewall. Will USE_SHARED_SOCKET = TRUE solve the problem? I checked the registery and didn't find it there. Should I add it?
December 28, 2003 - 11:04 am UTC
it could be it, we use port redirection, use shared socket should let us "inherit" the connection.
firewall rules for net8 connection
Steve, April 13, 2004 - 11:51 am UTC
Hi Tom
Dose the above Firewalling issue Affect Oracle 9i as well.
I seem to remember hitting the same issue with 8i in the past and using a Communications Manager box to act as a proxy for Oracle Client/Server Traffic.
I was Wondering if this is still the case with 9i
Regards
Steve
April 13, 2004 - 6:15 pm UTC
depends on your network topology, apparently with your firewall -- or the features you were using with 8i -- you had to use CMAN (shared server will do that for example).
In 8i with straight dedicated servers and a pretty "normal" firewall that let sqlnet traffic through, you would not have an issue.
firewall rules for net8 connection
Raj, April 13, 2004 - 5:48 pm UTC
Is it mandatory to use 1610 or 1630 as cman ports ? Can't we use port 1521 for cman ? When I start cman, listener in the same machine is not working. I get TNS Connection refused error, What could be the reason
April 13, 2004 - 7:02 pm UTC
please contact support for install/configuration questions....
the ports are not mandatory, no.
firewall rules for net8 connection
Steve, April 14, 2004 - 6:41 am UTC
Hi Tom
Thanks For The prompt Reply
Where you said
"In 8i with straight dedicated servers and a pretty "normal" firewall that let
sqlnet traffic through, you would not have an issue."
Did you meen 9i ??
and if so. That 9i no longer used the random port numbers to talk back to the client application.
Regards
Steve
April 14, 2004 - 8:54 am UTC
No, meant 8.0 and up really -- with use_shared_socket -- and dedicated servers, there is no port redirection going on.
A reader, April 23, 2004 - 10:36 am UTC
if i am trying to connect to oracle server through ms acces using odbc connection do i need to install oracle client on the workstation.
April 23, 2004 - 1:49 pm UTC
yes.
USE_SHARED_SOCKET = TRUE
MGRIEGO, April 23, 2004 - 8:08 pm UTC
I am confused on how to enter this parameter in the registry. Some sites say enter as a key and others as a string. Which is it? If it is entered as a string what do I enter for the "Value Data" field?
MGRIEGO
Not much interested
Venkataragavan.S, March 02, 2006 - 6:12 am UTC
Hi Tom,
Can you please provide the full details of the cman.ora and tnsnames.ora. B'ze i tried it for our dedicated server and the configuration is not at all working. Please help.
Thanks & regards,
Venkat.S
March 02, 2006 - 12:23 pm UTC
b'ze? that is a strange one.
Sure, I can provide all of the details:
a) goto otn.oracle.com
b) click on documentation
c) select your version
d) see the network guide for that release
sql*net
sam, February 20, 2007 - 11:10 pm UTC
tom:
1. IS sql*net oracle protocol for clients to connect to an oracle database?
2. Is sql*net a pience of software that gets installed with database and clients? Does it run on top of TCP/IP.
3. Whe you have a browser talking to an oracle http server using mod_plsql, how is the communication working
browser to http listener ---> HTTP
http listener to MOD-PLSQL -->HTTP
MOD_PLSQL to database --> SQL*NET
IS this correct?
4. Is there a way to talk to an oracle database usning HTTP as a protocol?
Thanks,
February 21, 2007 - 10:48 am UTC
1) generally speaking - yes. sql*net is our networking protocol and the set of software we have to implement it.
2) yes, yes.
3)
browser to web server (http)
mod_plsql is a piece of software run in web server, an apache module, it is "talked to" via the apache module API
mod_plsql to database over sqlnet
4) yes, we support that as well - dbms_epg for example (Oracle XE does this out of the box)
DB on Linux, listener on Win, USE_SHARED_SOCKET?
Kim, January 21, 2008 - 4:04 pm UTC
Hi Tom
Is it possible to use the USE_SHARED_SOCKET feature when the database is running on a Linux box, if we add a listener on a NT box?
( I have not been able to find a clear statement on this in the docs )
January 21, 2008 - 10:19 pm UTC
would not really be relevant since...
the listener will redirect the connection to the linux box and drop out of the picture entirely....
eg: after the connection is made to the database, you could turn off that windows machine, it isn't involved anymore.
AH!
Kim, January 22, 2008 - 8:43 am UTC
Got it - I was not confident that the normal connection rules still applied.
Thank You Tom!
troubleshoot intermitent performance issue between apps and db server
sh, December 09, 2011 - 11:13 am UTC
Hi Tom,
We have a intermitently performance issue lately and would like to seek your opinion on the steps to troubleshoot it.
From the database and application server layer, the cpu utilization is low (around 50% utilize). No blocking session or database activity (eg: checkpoint or log switch) happen when it slow for few seconds. The query is not a complicated and it uses to return within 3 seconds. When it intermitently slow, the respond can reach more than 10seconds.
Need your advice on how to check if the issue is not due to the network between application server and database server. What oracle tools or unix tool that we can use to nail down the issue. The number of user connection is high and we use connection pool. The issue happen for the past 2 weeks. Thanks
December 11, 2011 - 2:51 pm UTC
turn on your instrumentation that your developers put in there to see where they are spending time.
Oh wait, that is right, no one does that anymore, too old fashioned I guess.
do you have ASH/AWR access? ASH can be used to tell you how much of the time is spent in the database - what the query was waiting for.
Ports and protocols required
A reader, December 18, 2011 - 1:51 pm UTC
Tom:
We need to setup an oracle 11g enterprise production environment that uses one vmware machine (RHEL) for database server and one vmware machine (RHEL) for oracle application/web server.
There will be a couple of firewalls: one for internal components between database and web server and one for external users who want to connect to web application or adminster database (if needed).
I need to provide security a list of port numbers and protocols that need to be allowed for this to work to include an external machine for remote administration (SSH/telent & VPN).
For remote administration, I expect a company laptop with full VPN tunnel to server which if i undersand correctly should provide full functionality as if you are sitting internally on the network.
I was looking at this link
http://docs.oracle.com/html/B10262_01/ports.htm it says it uses the app server uses these ports by default
Oracle HTTP Server port = 7777
Oracle HTTP Server SSL port = 4443
Oracle HTTP Server listen port = 7778
Oracle HTTP Server SSL listen port = 4444
Oracle HTTP Server Jserv port = 8007
Enterprise Manager Servlet port = 1810
Oracle DB Server = 1521 (high level - default listener)
Oracle DB Server = 1021 to 65536
1) Are these all the ports and protocols I need I need in addition to port 1521 for client/server access to the server and SSH?
Protocols
TCP/IP
HTTP
HTTPS
NET*8
RDP
SSH/telnet
2) Can I let the mod-plsql application uses port 443 (instead of 4443) and port 80 instead of 7777 so that no ports will be ned to in the UEL that calls the application.
Port-redirection
misin, April 08, 2012 - 1:20 am UTC
Hi Tom,
Is the port re-direction also happens in oracle dedicated server environment,
as our replication environment having issue and one of our DBA thinks that we opened only 1521 port on firewall and due to the OraclNET port re-direction features, its using some random port which is already blocked by firewall.
so I want to know under what senario/configuration port re-direction is VALID or Is the port-ridirection happens in dedicated server environment ?
if yes, then how to handle port-redirection in dedicated server environment without going back to firewall ?
April 08, 2012 - 5:48 pm UTC
port redirection does not happen dedicated server - no. The dedicated server inherits the socket from the listener.
IBM AIX 61. to communicate with DB2
Rohit Khandelwal, May 09, 2012 - 10:36 pm UTC
Hi Tom,
Currently, I am using IBM AIX application server and I want the application server to communicate to with DB2.
I am behind the firewall. Can you please help me if I need to communicate with port 50000 from application server. What are the other ports that I should ask the firewall to open, so that communication is smooth.
May 10, 2012 - 7:58 am UTC
db2? seriously? did you happen to notice the domain here?
ports for oracle DBA
A reader, June 18, 2013 - 3:12 pm UTC
Tom:
If you are doing administative work on a RHEL remote server with oracle 11g database server and oracle 11g web server (apache/mod_plsql), what ports do you require to be opened for you in the firewall?
Would you just request SSH (22) and OEM (db console) 1158 and SQL (1521)?
There is a long list of ports here and I am not sure if you request
http://docs.oracle.com/cd/B28359_01/install.111/b32002/app_port.htm#CIHBJHGF Oracle Management Agent
Oracle Enterprise Manager Database Console
etc...
June 18, 2013 - 4:07 pm UTC
Sam
for support issues, for configuration issues, please utilize support (I do believe I've asked you to do that before, at least a few times....)
you would determine what ports YOU want to have open on YOUR firewall. Not us. You decide what tools you would like to be able to use.
ports
A reader, June 18, 2013 - 5:04 pm UTC
Tom:
I know that tools can be personal preference and each person may use something different.
But I would like to know what ports/tools *you* would want to have opened if I asked you to remote admin my server from your machine.
Thanks,
June 18, 2013 - 6:27 pm UTC
I'd want a VPN into your network, to be on the other side of the firewall.
ports
A reader, June 18, 2013 - 8:26 pm UTC
Tom:
If you use VPN to get into the netowrk, does that make everything (all ports) open?
My understanding is that even if you use VPN, that might land you into a different netowrk domain (i.e DMZ) than database machine (internal netowrk) and they still need to configure fireall for you. is this correct?
To: A reader (ports)
A reader, June 26, 2013 - 12:55 pm UTC
VPN products differ and VPN configurations differ. For example, in my company's VPN, we have to ask firewall admins to open specific ports for specific purpose. Even the access to VPN is controlled on a per user basis.
There are no generalized rules/recommendations here. Please refer to Oracle documentation for port numbers/protocols. Every Oracle product nowadays has information on accessing the service through firewalls. MOS has information, Google search can also bring up forum threads. Then ask your firewall admins to open these ports for the specified protocols and test.
Only your firewall admins can tell whether they are landing you in DMZ or within the firewall protected area when you connect via VPN.