Home » RDBMS Server » Networking and Gateways » Oracle Database not connect from outside lan via static ip (Oracle 9.0.1.0.1)
Oracle Database not connect from outside lan via static ip [message #531668] Thu, 17 November 2011 04:49 Go to next message
rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU
Junior Member
Dear friends,

I have created two database one for production and another one for test.I created both database in shared_server mode(by using dbca),after than I did not change anything.My problem is when i connect to test database from outside lan through static ip its working well,but when i connect to production database which is not connected.I dont know why,I checked and compared two databases,then I find there are some changes in dispatcher and mts_dispatcher parameters.And I used this query to check the shared_server status,
ie.select * from v$shared_server,the output of this query is attached with this post.

Please some one help me on this.Tell me where the problem is.Thanks in advance.
Re: Oracle Database not connect from outside lan via static ip [message #531743 is a reply to message #531668] Thu, 17 November 2011 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

seeing the actual connection attempt & error code/message would provide meaningful details.
Re: Oracle Database not connect from outside lan via static ip [message #531900 is a reply to message #531668] Fri, 18 November 2011 06:13 Go to previous messageGo to next message
rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU
Junior Member
Sorry for my mistake.

I created two databases via dbca in shared_server mode,one database name is PRODUCTION and another database name is TESTDB.I did not change any parameters in both databases.When I connect both databases from outside lan(through internet) via static ip,My TESTDB is connected but PRODUCTION is not connected.My tnsnames.ora entry is,


PRODUCTION =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 115.245.202.192)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODUCTION)
)
)

TESTDB=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 115.245.202.192)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTDB)
)
)

Here 115.245.202.192 is an static ip address.My TESTDB database is connected via this static ip,but PRODUCTION database not connected.Then I check the shared server configuration of both databases and found the difference in V$shared_server view.

C:\>Sqlplus /nolog

sQL>conn sys/iorakle@production as sysdba
connected.

SQL>Show parameter shared_servers;

NAME ITYPE VALUE
--------------------- ------- --------------------------
max_shared_servers integer 20
shared_servers integer 1

SQL>show parameter dispatcher;

NAME ITYPE VALUE
--------------------- -------- ------------------------
dispatchers string (PROTOCOL=TCP)(SER=MODOSE),(PROTOCOL=TCP)
(PRE=oracle.aurora.server.GiopServer),(PROTOCOL=TCP)
(PRE=oracle.aurora.server.SGiopServer)

max_dispatchers interger 5

mts_dispatchers string (PROTOCOL=TCP)(SER=MODOSE),(PROTOCOL=TCP)
(PRE=oracle.aurora.server.GiopServer),(PROTOCOL=TCP)
(PRE=oracle.aurora.server.SGiopServer)


NAME TYPE VALUE
-------------------- ---------- ----------
mts_max_dispatchers integer 5


SQL>select * from v$shared_server;

NAME PADDR STATUS MESSAGES BYTES BREAKS CIRCUIT IDLE BUSY REQUESTS
---- -------- ---------------- ---------- ------- ---- ---- ----- ---------
S000 66D9222C WAIT(COMMON) 0 0 0 0 0 00 16947130

SQL>select username,server from v$session;

USERNAME SERVER
---------------- -------
SYS DEDICATED

The SYS user is connected as dedicated mode,but I created PRODUCTION database in shared server mode.Please tell me the problem and how can i convert dedicated server mode to shared server mode.For clear view see the text attachment.
Re: Oracle Database not connect from outside lan via static ip [message #531918 is a reply to message #531900] Fri, 18 November 2011 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>I created two databases via dbca in shared_server mode,
I believe that above is NOT correct.
No Oracle DB knows or care about shared_server mode.
I am not sure what shared_server has to do with a connection problem in any case.

Why did you ignore my request for error code & message?

Do both DBs reside on a single system?
Re: Oracle Database not connect from outside lan via static ip [message #531988 is a reply to message #531668] Sat, 19 November 2011 02:53 Go to previous messageGo to next message
rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU
Junior Member
Yes,both databases are resides on a same system.Am talking about multi-threaded server feature(shared_server).When i connect to PRODUCTION database its not showing any error message,its again and again displays the username,password screen.Please see the screen attachement to understand.I am not getting any error messages so I dont know the problem.
Re: Oracle Database not connect from outside lan via static ip [message #532004 is a reply to message #531988] Sat, 19 November 2011 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
open Command Window & issue following OS commands

set
lsnrctl status
lsnrctl service

COPY commands (above) & results then PASTE all back here
Re: Oracle Database not connect from outside lan via static ip [message #532163 is a reply to message #531668] Mon, 21 November 2011 02:20 Go to previous messageGo to next message
rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU
Junior Member
C:\>set
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\Administrator.DIETECH.000\Application
CLIENTNAME=LINUX
ClusterLog=C:\WINDOWS\Cluster\cluster.log
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=ERPSERVER
ComSpec=C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\Administrator.DIETECH.000
JSERV=D:\oracle\ora90/Apache/Jserv/conf
LOGONSERVER=\\MAINSERVER
NUMBER_OF_PROCESSORS=4
OLAP_HOME=D:\oracle\ora90\olap
OS=Windows_NT
Path=D:\oracle\ora90\bin;D:\oracle\ora90\Apache\Perl\5.00503\bin\mswin32
Program Files\Oracle\jre\1.1.8\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WIN
tem32\Wbem
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 23 Stepping 10, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=170a
ProgramFiles=C:\Program Files
PROMPT=$P$G
SESSIONNAME=RDP-Tcp#1
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\ADMINI~1.000\LOCALS~1\Temp\1
TMP=C:\DOCUME~1\ADMINI~1.000\LOCALS~1\Temp\1
USERDNSDOMAIN=DIETECH.COM
USERDOMAIN=DIETECH
USERNAME=administrator
USERPROFILE=C:\Documents and Settings\Administrator.DIETECH.000
windir=C:\WINDOWS
WV_GATEWAY_CFG=D:\oracle\ora90\Apache\modplsql\cfg\wdbsvr.app


C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 21-NOV-2011 13:46:
09

Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.0.1.1.1 - Produc
tion
Start Date 20-NOV-2011 08:24:23
Uptime 1 days 5 hr. 21 min. 46 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File D:\oracle\ora90\network\admin\listener.ora
Listener Log File D:\oracle\ora90\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.52.52.61)(PORT=8080))(PRESENTATI
ON=http://admin)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.52.52.61)(PORT=9090))(PRESENTAT
ION=http://admin)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.52.52.61)(PORT=2481))(PRESENTATI
ON=GIOP)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.52.52.61)(PORT=2482))(PRESENTAT
ION=GIOP)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.52.52.61)(PORT=8080))(PRESENTATI
ON=http://admin)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.52.52.61)(PORT=9090))(PRESENTAT
ION=http://admin)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.52.52.61)(PORT=2481))(PRESENTATI
ON=GIOP)(SESSION=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.52.52.61)(PORT=2482))(PRESENTAT
ION=GIOP)(SESSION=RAW))
Services Summary...
Service "PRODUCTION" has 2 instance(s).
Instance "PRODUCTION", status UNKNOWN, has 1 handler(s) for this service...
Instance "PRODUCTION", status READY, has 3 handler(s) for this service...
Service "MODOSE" has 2 instance(s).
Instance "PRODUCTION", status READY, has 1 handler(s) for this service...
Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTDB" has 2 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "TESTDB", status READY, has 4 handler(s) for this service...
The command completed successfully

C:\>lsnrctl service

LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 21-NOV-2011 13:46:
44

Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
Services Summary...
Service "PRODUCTION" has 2 instance(s).
Instance "PRODUCTION", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:86 refused:0
LOCAL SERVER
Instance "PRODUCTION", status READY, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:144 refused:0 state:ready
LOCAL SERVER
"D002" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 2476>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1
786))(PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
"D001" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 996>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1
785))(PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
Service "MODOSE" has 2 instance(s).
Instance "PRODUCTION", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 2504>
(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1783))
Instance "TESTDB", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 1716>
(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1037))
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "TESTDB" has 2 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "TESTDB", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D003" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 2176>
(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1041))
"D002" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 884>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1
040))(PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
"D001" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: ERPSERVER, pid: 1728>
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpserver.dietech.com)(PORT=1
039))(PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
The command completed successfully

I issued all the commands and post you the reply here.For your reference I have attached the text file of this output also.
Thanks for your reply.Am waiting for your next reply.
Re: Oracle Database not connect from outside lan via static ip [message #532224 is a reply to message #532163] Mon, 21 November 2011 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>sQL>conn sys/iorakle@production as sysdba
>connected.

above proves that SQL*Net into Production DB works OK.
Any problem that exists while connecting from the outside must result from external mis-configuration.
Oracle is victim; not culprit.
So find & fix external root cause.
Re: Oracle Database not connect from outside lan via static ip [message #532378 is a reply to message #531668] Tue, 22 November 2011 04:10 Go to previous message
rajamohankumar
Messages: 26
Registered: July 2011
Location: TAMIL NADU
Junior Member
I accept you.But I connect the both databases from outside on a same external configuration,one database is connected(testdb) and another one is not(production).Do you think the problem might be external or with the database parameter.Here I would like to show the mts_dispatcher difference between these two databases what I was found in this issue.

1.Testdb database:


SQL> show parameter mts_dispatcher;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer), (protocol=TCP)

SQL> show parameter dispatcher;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer), (protocol=TCP)
max_dispatchers integer 5
mts_dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer), (protocol=TCP)

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_max_dispatchers integer 5


2.production database


SQL> show parameter mts_dispatcher;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer)

SQL> show parameter dispatcher;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer)
max_dispatchers integer 5
mts_dispatchers string (PROTOCOL=TCP)(SER=MODOSE), (P
ROTOCOL=TCP)(PRE=oracle.aurora
.server.GiopServer), (PROTOCOL
=TCP)(PRE=oracle.aurora.server
.SGiopServer)

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_max_dispatchers integer 5


the underlined (protocol=TCP) parameter not exist in production database but is in testdb database at the mts_dispatcher parameter.Will this be the reason?I am not sure,but one thing I checked if a database is created in dedicated server mode you cannot connect it via internet(through static ip).Only you can connect database via internet if it has been created in shared_server mode.Please go through this and reply me.

Thanks for your support again
Previous Topic: Help Required in accessing MSACCESS from Oracle using heterogeneous Service Agents
Next Topic: PMON register problem
Goto Forum:
  


Current Time: Mon Sep 15 23:58:15 CDT 2014

Total time taken to generate the page: 0.08618 seconds