Environment (New installl 19/12/2018)
SERVER
Windows Server 2012 R2 Standard 64bit
Oracle Grid 12.2.0.1 Standalone server (Oracle restart)
Oracle Database 12.2.0.1
ASM 12.2.0.1
Single standalone database configured
Single tenant
SID=UAT
LISTENER running from grid - D:\Oracle\Grid\BIN\TNSLSNR
LISTENER.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = GIG06SRVCYPORAT.cymru.nhs.uk)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
SQLNET.ora
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
LISTENER Status
C:\Users\nattd_ch080070>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 20-JAN-2018 11:14:25
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date 19-JAN-2018 14:57:41
Uptime 0 days 20 hr. 16 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File D:\Oracle\diag\tnslsnr\GIG06SRVCYPORAT\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GIG06SRVCYPORAT.cymru.nhs.uk)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.57.106.241)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+asm", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+asm", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+asm", status READY, has 1 handler(s) for this service...
Service "uat.cymru.nhs.uk" has 1 instance(s).
Instance "uat", status READY, has 1 handler(s) for this service...
Service "uatXDB.cymru.nhs.uk" has 1 instance(s).
Instance "uat", status READY, has 1 handler(s) for this service...
The command completed successfully
C:\Users\nattd_ch080070>
FIREWALL
I can telnet to port 1521
PROCESSES
I have enough processes
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
asm_io_processes integer 20
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 4
processes integer 960
processor_group_name string
SQL> select resource_name, current_utilization, max_utilization, limit_value
2 from v$resource_limit
3 where resource_name in ('sessions', 'processes');
RESOURCE_NAME
--------------------------------------------------------------------------------
CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALU
------------------- --------------- ----------
processes
97 111 960
sessions
62 87 1464
CLIENT (Windows 7)
SQL Developer Version 17.3.1.279 Build 279.0537
PROBLEM
When connecting to our new environment from any client program (I have used SQL Developer as an example) to UAT or any database the listener refuses connection. As you can see there are services available for that connection. Any advice would be appreciated.
ERROR
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
Many thanks.