Oracle 11g XE - проблемы с удаленным подключением (истекло время ожидания подключения)
Новичок в Oracle и попытка настроить среду разработки Oracle с 11g XE. Локальные соединения работают нормально с sqlplus и SQL Developer - кажется, что удаленные соединения не видят базу данных. Я пробовал различные конфигурации файлов ниже (с помощью google & oracle docs) на разных машинах, но я даже не могу заставить клиента зарегистрировать существование сервера. Моя текущая конфигурация выглядит следующим образом:
Сервер (ip 192.168.0.51):
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.51)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.51)(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)
)
)
sqlnet.ora
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
вывод статистики lsnrctl
C:\Windows\system32>lsnrctl stat
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 30-MAR-2015 18:31
:48
Copyright (c) 1991, 2010, 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 11.2.0.2.0 - Produ
ction
Start Date 30-MAR-2015 17:58:16
Uptime 0 days 0 hr. 33 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Enterprise\listene
r\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=8080
))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, 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 "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
вывод netstat -an | найти "1521"
C:\Windows\system32>netstat -an | find "1521"
TCP 192.168.0.51:1521 0.0.0.0:0 LISTENING
TCP 192.168.0.51:1521 192.168.0.51:3280 ESTABLISHED
TCP 192.168.0.51:3280 192.168.0.51:1521 ESTABLISHED
на сервере я могу соединиться со следующим (не знаю, имеет ли это смысл, т.е. через tcp):
sqlplus <user>/<pass>@192.168.0.51:1521/xe
Клиент:
на клиенте приведенная выше попытка подключения sqlplus приводит к следующему:
C:\>sqlplus <user>/<pass>@192.168.0.51:1521/xe
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 30 18:14:17 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12170: TNS:Connect timeout occurred
Использование Test-NetConnection
PS C:\Users> Test-NetConnection 192.168.0.51 -Port 1521
WARNING: TCP connect to 192.168.0.51:1521 failed
ComputerName : 192.168.0.51
RemoteAddress : 192.168.0.51
RemotePort : 1521
InterfaceAlias : Wi-Fi
SourceAddress : 192.168.0.88
PingSucceeded : True
PingReplyDetails (RTT) : 1165 ms
TcpTestSucceeded : False
Диагностика без порта 1521
PS C:\Users> ping 192.168.0.51
Pinging 192.168.0.51 with 32 bytes of data:
Reply from 192.168.0.51: bytes=32 time=5ms TTL=128
Reply from 192.168.0.51: bytes=32 time=7ms TTL=128
Reply from 192.168.0.51: bytes=32 time=4ms TTL=128
Reply from 192.168.0.51: bytes=32 time=32ms TTL=128
Ping statistics for 192.168.0.51:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 4ms, Maximum = 32ms, Average = 12ms
PS C:\Users> Test-NetConnection 192.168.0.51 -Port 80
ComputerName : 192.168.0.51
RemoteAddress : 192.168.0.51
RemotePort : 80
InterfaceAlias : Wi-Fi
SourceAddress : 192.168.0.88
PingSucceeded : True
PingReplyDetails (RTT) : 1920 ms
TcpTestSucceeded : True
Я отключил брандмауэры как на сервере, так и на клиентских машинах.
Где я могу пойти не так?