I have oracle 11g XE everything running well except that I can't connect to oracle via SQLplus client from remote mashine.
My server running centos with oracle 11g XE.
My listener.ora file looks like: cat /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
listener.ora Network Configuration File:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = extproc) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = servername.domain.com)(PORT = 1521)) ) )
DEFAULT_SERVICE_LISTENER = (XE)
My tnsnames.ora looks like:
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )
lsnrctl status shows:
-bash-4.1$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 21-MAR-2014 14:00:28
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date
21-MAR-2014 13:39:10 Uptime 0 days 0 hr. 21 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service
XE Listener Parameter File
/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora Listener Log File
/u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/venera/listener/alert/log.xml Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername.domain.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername.domain.com)(PORT=8080))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully
File /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/venera/listener/alert/log.xml when I try to connect:
21-MAR-2014 14:00:03 * service_update * XE * 0 WARNING: Subscription for node down event still pending 21-MAR-2014 14:00:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=servername.domain.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0 21-MAR-2014 14:00:33 * service_update * XE * 0 21-MAR-2014 14:04:09 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\ORANT\BIN\PLUS80W.EXE)(HOST=client_name)(USER=client_username))) * (ADDRESS=(PROTOCOL=tcp)(HOST=REMTE_IP)(PORT=56366)) * establish * XE * 0
my /etc/hosts looks like:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.100.70 servername.domain.com servername
I have NAT and open port 1521
in /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
Btw I can connect to oracle local:
-bash-4.1$ sqlplus system@XE
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 21 14:19:07 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
I also try to run sqlplus as administrator. No luck. Any idea?
UPDATE CLIENT SITE:
C:\Users\ClientUsername>tnsping80 venera
TNS Ping Utility for 32-bit Windows: Version 8.0.6.3.0 - Production on 21-MAR-20 14 14:27:17
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=servername.domain.com)(PORT=152 1)) OK (80 msec)
