Oracle Database Discussions

Good day all,

I’ve got 2 virtual machines running on my Mac:
- Centos 5.4 with Oracle 11gR2
- Windows 2003 server with Oracle 10gR2

From Linux box I have access to Oracle database on Windows box (both directly and via a database link), however from Windows I have problems accessing the oracle database on the Linux box.
From Windows, if I try to set up a connection using Oracle’s SQL Developer using connection type “Basic”, I get “IO Error: The Network Adapter could not establish the connection”. When I use “TNS”, then it works.
From the linux box it all works fine as the “oracle” user, but when I log on as another user (in this case “informatica”) it doesn’t work properly any more.

To sketch what both environments look like.

Windows:
- Machine name WS2003Ora10g
- Etc/hosts
o 127.0.0.1 localhost
o #loopback adapter
o 192.168.12.100 localhost
o #network adapter
o 192.168.12.10 WS2003Ora10g
o # external machine
o 192.168.12.110 ilmserver
- system variables
o TNS_ADMIN, set to network/admin path in Oracle home dir

listener
C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 24-AUG-2011 17:38:06

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 24-AUG-2011 16:29:33
Uptime 0 days 1 hr. 8 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WS2003Ora10g)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora10gr2" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Service "ora10gr2XDB" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Service "ora10gr2_XPT" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Documents and Settings\Administrator>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 24-AUG-2011 17:40:43

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
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 "ora10gr2" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:25 refused:0 state:ready
LOCAL SERVER
Service "ora10gr2XDB" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: WS2003ORA10G, pid: 2852>
(ADDRESS=(PROTOCOL=tcp)(HOST=WS2003Ora10g)(PORT=1031))
Service "ora10gr2_XPT" has 1 instance(s).
Instance "ora10gr2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:25 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Linux
- Machine name ilmserver
- Etc/hosts
o 127.0.0.1 localhost.localdomain localhost
o ::1 localhost6.localdomain6 localhost6
o 192.168.12.1 routerip rtrip
o 192.168.12.110 ilmserver ilmsvr
o 192.168.12.10 WS2003Ora10g winora

system variables (in bashprofile)_
o export PATH=$PATH:/usr/java/jdk1.7.0/bin
o export PATH=$PATH:/usr/sbin
o export PATH=$PATH:/sbin
o export JAVA_HOME=/usr/java/jdk1.7.0
o PATH=$PATH:$HOME/bin
o export PATH
o export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
o export ORACLE_BASE=/home/oracle/app/oracle
o export ORACLE_SID=orcl
o export TNS_ADMIN=$ORACLE_HOME/network/admin
o export ORACLE_HOSTNAME=ilmserver
o export PATH=$PATH:$ORACLE_HOME/bin
Both the "oracle" and the "informatica" user have these environment variables.

listener

Logged in as “oracle” user:*
[oracle@ilmserver ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-AUG-2011 19:12:35

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 24-AUG-2011 17:30:39
Uptime 0 days 1 hr. 41 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/ilmserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@ilmserver ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-AUG-2011 19:13:28

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:19 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ilmserver, pid: 9648>
(ADDRESS=(PROTOCOL=tcp)(HOST=ilmserver)(PORT=52395))
The command completed successfully

But logged in as “informatica” user I get following:
[informatica@ilmserver ~]$ lsnrctl status
bash: lsnrctl: command not found
[informatica@ilmserver ~]$ lsnrctl services
bash: lsnrctl: command not found
even though the oracle bin directory sits in PATH.

Pinging from windows:
C:\Documents and Settings\Administrator>ping ilmserver

Pinging ilmserver [192.168.12.110] with 32 bytes of data:

Reply from 192.168.12.110: bytes=32 time=1ms TTL=64
Reply from 192.168.12.110: bytes=32 time<1ms TTL=64
Reply from 192.168.12.110: bytes=32 time<1ms TTL=64
Reply from 192.168.12.110: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.12.110:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 1ms, Average = 0ms
That works fine.

Telnet from Windows:
C:\Documents and Settings\Administrator>telnet 192.168.12.110 1521
Connecting To 192.168.12.110...Could not open connection to the host, on port 1521: Connect failed
This result is the same for either just having port 1521 open, or turning the Linux firewall off altogether. Also same when using IP address.

Just to be complete, here are the results from the Linux box.

*[oracle@ilmserver informatica]$ ping WS2003Ora10g*
PING WS2003Ora10g (192.168.12.10) 56(84) bytes of data.
64 bytes from WS2003Ora10g (192.168.12.10): icmp_seq=1 ttl=128 time=1.63 ms
64 bytes from WS2003Ora10g (192.168.12.10): icmp_seq=2 ttl=128 time=0.222 ms
64 bytes from WS2003Ora10g (192.168.12.10): icmp_seq=3 ttl=128 time=0.255 ms
64 bytes from WS2003Ora10g (192.168.12.10): icmp_seq=4 ttl=128 time=0.258 ms

--- WS2003Ora10g ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.222/0.593/1.639/0.604 ms

*[oracle@ilmserver ~]$ telnet WS2003Ora10g 1521*
Trying 192.168.12.10...
Connected to WS2003Ora10g (192.168.12.10).
Escape character is '^]'.

... works for all users.

Checking out the port situation on Linux:
[informatica@ilmserver ~]$ netstat -an | grep 1521
tcp 0 0 127.0.0.1:1521 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:1521 127.0.0.1:43782 ESTABLISHED
…..
tcp 0 0 127.0.0.1:1521 127.0.0.1:44290 ESTABLISHED
tcp 0 0 127.0.0.1:58289 127.0.0.1:1521 TIME_WAIT
tcp 0 0 127.0.0.1:58287 127.0.0.1:1521 TIME_WAIT
tcp 0 0 127.0.0.1:1521 127.0.0.1:43792 ESTABLISHED
tcp 0 0 127.0.0.1:54116 127.0.0.1:1521 ESTABLISHED
……
tcp 0 0 127.0.0.1:1521 127.0.0.1:44283 ESTABLISHED
tcp 0 0 ::ffff:127.0.0.1:54123 ::ffff:127.0.0.1:1521 ESTABLISHED
…..
tcp 0 0 ::ffff:127.0.0.1:30135 ::ffff:127.0.0.1:1521 ESTABLISHED
unix 2 [ ACC ] STREAM LISTENING 129262 /var/tmp/.oracle/sEXTPROC1521

[ the sequence of dots means I took out a whole lot or lines with virtually the same information, only different port(s) ]

What’s noticeable is that port 1521 seems solely assigned to the localhost IP-address, 127.0.0.1.

Another thing I noticed is that from a different user in Linux, so not “oracle”, using SQL Developer I cannot access the local Oracle database via TNS. When choosing TNS the dropdown box for “Network Alias” – which normally is populated with entries from TNSNAMES.ORA – is completely empty. On a “users and groups” level I basically assigned all the groups the oracle user is member of, to the other user (called informatica).
And to make it even more interesting, at first it worked using “Basic” (in SQL Developer), but since implementing the TNS_ADMIN environment variable, it doesn’t work at all !!!

Trying it via SQLPLUS as the “informatica” doesn’t work either:
[informatica@ilmserver ~]$ sqlplus system
bash: sqlplus: command not found
I’ve assigned the “dba” group to “informatica”, or rather, I have assigned all groups to the “informatica” user as the “oracle” user has.
Needless to say I am no Linux expert, rather a quite inexperienced Linux-user.

So bottom line, the situation I’d like to be able to create is the following:
• Linux Oracle DB accessible from “informatica” (or any other) user on Linux, via both TNS and java-based
• Linux Oracle DB accessible from remote machine (running Windows 2003 server) via both TNS and java-based
• And for Informatica to work properly, it says it needs the TNS_ADMIN environment variable, so the “informatica” user must have full Oracle connectivity with TNS_ADMIN in place.

Any help is greatly appreciated.

Thanks, Patrick