Listener trouble

**tracey** · 02-28-2017, 10:15 AM

I am having an issue across all databases on 3 separate environments. Database, listener, application on same local machine
Basic simple listener.ora, tnsnames.ora with one SID/ service name used DBCA to create database years ago and netca to
configure listener etc. This has been running seemingly without tons of issues till recently there has been many connection
time outs, socket errors. It seems the listener does not remain up and stable. I can start the listener; however, after the
initial start and maybe a status, the listener hangs on a status. Occasionally the listener will complete after a while but
more often, I grep for it and kill the PID.

2 things that I notice are missing but am unsure if this is an issue because this was a functioning system, sqlnet.ora and pfile
init file I added both and it didn’t seem to make any progress of course I can connect directly to the database but when I go
to thru the listener

Code:

Sqlplus (thru listener)
bin]$ ./sqlplus sys/pwd@cosga as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 27 22:21:39 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

Listener.ora
# listener.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server fully qualifed)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /app/oracle

Tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
COSGA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server fullyqualified)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cosga)
    )
  )
Tnsping
$ ./tnsping cosga
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2017 22:28:41
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server fully qualified)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cosga)))
TNS-12547: TNS:lost contact

Etc/hosts
127.0.0.1       localhost.localdomain localhost
xxx.xx.xxx.xxx  serverfullyqualified server

Show parameters (removed possibly not applicable)
SQL> show parameters;
audit_file_dest  /app/oracle/admin/cosga/adump
background_dump_dest  /app/oracle/diag/rdbms/cosga/cosga/trace
bitmap_merge_area_size  1048576
compatible  11.2.0.0.0
control_file_record_keep_time  7
control_files  /app/oracle/oradata/cosga/control01.ctl, /app/oracle/flash_recovery_area/cosga/control02.ctl
core_dump_dest  /app/oracle/diag/rdbms/cosga/cosga/cdump
create_bitmap_area_size   8388608
db_files	  200
db_name   cosga
db_recovery_file_dest  /app/oracle/flash_recovery_area
db_recovery_file_dest_size	3882M
db_unique_name  cosga
dg_broker_config_file1  /app/oracle/product/11.2.0/dbhome_1/dbs/dr1cosga.dat
dg_broker_config_file2  /app/oracle/product/11.2.0/dbhome_1/dbs/dr2cosga.dat
diagnostic_dest  /app/oracle
dispatchers  (PROTOCOL=TCP) (SERVICE=cosgaXDB)
distributed_lock_timeout  60
hash_area_size	  131072
ifile  file
instance_name  cosga
job_queue_processes  1000
max_enabled_roles  150
memory_max_target  7G
memory_target	  7G
open_cursors	  300
open_links	  4
open_links_per_instance   4
processes	  300
resource_manager_cpu_allocation  4
resumable_timeout  0
service_names  cosga
session_cached_cursors  50
session_max_open_files  10
sessions	  472
sga_max_size	  7G
sga_target	  0
shared_pool_reserved_size 144284057
standby_archive_dest  ?/dbs/arch
transactions	  519
transactions_per_rollback_segment   5
undo_tablespace  UNDOTBS1
user_dump_dest  /app/oracle/diag/rdbms/cosga/cosga/trace

Status listener
]$ ./lsnrctl status listener
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2017 18:30:46
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=servername.fullyqualified)(PORT=1521)))
TNS-01153: Failed to process string:

Does anyone have any suggestions? At this point it is affecting the vendor application at this major corporation. Not only
will the tomee server not start if the listener is not operating correctly but we and the vendor believe it is the source for
performance and capacity issues of the SLM interface. At one point this was working very well, even auto starting from
a reboot with dbora, apache, tomee etc. Oracle is 11.2

**gandolf989** · 02-28-2017, 10:49 AM

Did someone edit either the listener.ora or sqlnet.ora files recently? My guess is that you have a typo in one of the two files.
You should look at the listener.log file which should be /app/oracle/diag/tnslsnr/host_name/listener/alert/log.xml.
If you can't figure it out, post the last 1,000 lines of your log.xml file.

Also, you should use code tags for that.

**tracey** · 03-01-2017, 11:27 AM

Neither file was modified and there are 3 environments all independent of each other. One thing is, there is no sqlnet.ora file. Would that be an issue for a local db? I created one and it seemed not to help but it was only the basic string for NAMES.DIRECTORY_PATH.

Here is some of log.xml
type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43105)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43108)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43111)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43112)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43113)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43115)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43118)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43120)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43121)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43124)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
27-FEB-2017 18:52:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=bd4150))(SERVICE_NAME=cosga)) * (ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.XXX.XXX)(PORT=43126)) * establish * cosga * 12514

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

type='UNKNOWN' level='16' host_id='ServerFullyQualified'
host_addr='XXX.XX.XXX.XXX'>

Thread: Listener trouble

Thread Tools

Display

Listener trouble

Posting Permissions