Forum Stats

  • 3,826,586 Users
  • 2,260,667 Discussions
  • 7,897,010 Comments

Discussions

Listener port number

KSG
KSG Member Posts: 802
edited Apr 18, 2010 8:56AM in General Database Discussions
Hi,

Oracle 11.1.0.6.0 on IBM-AIX

When I try to connect database, I get the below error

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

When I check listerner.log...

16-APR-2010 12:59:49 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * reload * 0
16-APR-2010 12:59:57 * service_register * orcl * 0
WARNING: Subscription for node down event still pending
16-APR-2010 13:00:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Fri Apr 16 13:00:56 2010
16-APR-2010 13:00:56 * (CONNECT_DATA=(SERVICE_NAME=testdb)(CID=([email protected])(HOST=integ)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.92.5.37)*(PORT=34487))* * establish * testdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
Fri Apr 16 13:01:58 2010
16-APR-2010 13:01:58 * (CONNECT_DATA=(SERVICE_NAME=testdb)(CID=([email protected])(HOST=integ)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.92.5.37)*(PORT=34507))* * establish * testdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
WARNING: Subscription for node down event still pending
16-APR-2010 13:02:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Fri Apr 16 13:02:36 2010
16-APR-2010 12:59:49 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * reload * 0
16-APR-2010 12:59:57 * service_register * orcl * 0
WARNING: Subscription for node down event still pending
16-APR-2010 13:00:02 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Fri Apr 16 13:00:56 2010
16-APR-2010 13:00:56 * (CONNECT_DATA=(SERVICE_NAME=testdb)(CID=([email protected])(HOST=integ)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.92.5.37)(PORT=34487)) * establish * testdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
Fri Apr 16 13:01:58 2010
16-APR-2010 13:01:58 * (CONNECT_DATA=(SERVICE_NAME=testdb)(CID=([email protected])(HOST=integ)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.92.5.37)(PORT=34507)) * establish * testdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
WARNING: Subscription for node down event still pending
16-APR-2010 13:02:04 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=integ)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Fri Apr 16 13:02:36 2010


Kindly note my port number is keep on increasing...

Thanks in advance
KSG
«134

Answers

  • 725039
    725039 Member Posts: 637
    >
    Oracle 11.1.0.6.0 on IBM-AIX

    When I try to connect database, I get the below error

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified
    >

    Please show us exactly how you are trying to connect. Is it through a remote client? Copy and past both commands issued and errors.

    Regards,
    Phiri
  • Chinar
    Chinar Member Posts: 5,542
    paste there TNSNAMES.ORA and sqlnet.ora files context.
  • KSG
    KSG Member Posts: 802
    Hi Phiri,

    It's not client-server. I'm directly connecting to the database from the server itself.

    $sqlplus "/as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:09:34 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified


    Enter user-name:

    when I try to to connect using

    $sqlplus "sys/[email protected] as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:33:54 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor


    Enter user-name:


    But when I try to connect other database 'orcl'

    sqlplus "sys/[email protected] as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:35:25 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>

    But when I try to connect to orcl with /as sysdba

    sqlplus "/as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:37:05 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified


    Enter user-name:

    Please note: All the database are connecting porperly once the server is rebooted. I suspect there is lock in the port. Kindly note, listener is trying different different port no to connect to database (refer my first post pls)

    Thanks
    KSG
  • KSG
    KSG Member Posts: 802
    tnsnames.ora

    LISTENER_TESTDB =
    (ADDRESS = (PROTOCOL = TCP)(HOST = integ)(PORT = 1522))


    TESTDB =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = integ)(PORT = 1522))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = testdb)
    )
    )

    sqlnet.ora

    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
  • Chinar
    Chinar Member Posts: 5,542
    edited Apr 16, 2010 7:24AM
    Well
    first "ORA-12154: TNS:could not resolve the connect identifier specified" occur for you because
    you acn not set ORACLE_SID env variable fist SET ORACLE_SID=<SIDNAME>

    second "ORA-12514: TNS:listener does not currently know of service requested in connect" for
    this you must register service in listener as static or dynamic

    for example add to listener.ora file such as

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = SB)
    (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    (SID_NAME = SB)

    )

    then
    lsnrctl stop
    ;lsnrctl start; and try connect again

    Edited by: dba84 on Apr 16, 2010 4:23 AM
  • Chinar
    Chinar Member Posts: 5,542
    Can you connect your db?
  • Hans Forbrich
    Hans Forbrich Member Posts: 11,543 Gold Trophy
    KSG wrote:
    Hi Phiri,

    It's not client-server. I'm directly connecting to the database from the server itself.
    Oracle is ALWAYS client-server. Even when client and server are on the same computer.

    (Even the memory-only 'Bequeath adapter' mechanism is client-server.)

    >
    $sqlplus "/as sysdba"
    The use of double-quotes implies that you learned this several versions ago. Has not been required for several versions.

    Try

    $ sqlplus / as sysdba

    (and note the space after the '/'. Not absolutely required but highly recommended.)

    >
    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:09:34 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified

    assuming you are using bash, please display results of following (issued from command line)

    set | grep -i ora
    which sqlplus
    tail /var/opt/oracle/oratab (or /etc/oratab if Linux)
    lsnrctl status
    Enter user-name:

    when I try to to connect using

    $sqlplus "sys/[email protected] as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:33:54 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    please show response to command line

    grep -i testdb $ORACLE_HOME/network/admin/tnsnames.ora

    >
    Enter user-name:


    But when I try to connect other database 'orcl'

    sqlplus "sys/[email protected] as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:35:25 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>

    But when I try to connect to orcl with /as sysdba

    sqlplus "/as sysdba"

    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:37:05 2010

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12154: TNS:could not resolve the connect identifier specified


    Enter user-name:

    Please note: All the database are connecting porperly once the server is rebooted. I suspect there is lock in the port. Kindly note, listener is trying different different port no to connect to database (refer my first post pls)
    Listener does not 'try' on a different port.

    However, (under normal circumstances) when listener forks/execs the server process AND that new server process is instructed to use the TCP/IP protocol by the tns substrate due to configuration (even when on local environment), the new server process will open a new TCP port for communication and the listener will tell the client to communicate on that.

    >
    Thanks
    KSG
  • 752163
    752163 Member Posts: 83
    Hey DBA84,

    He is trying to connect the database locally.Then no concerned with listener here.

    Even if the listener is down also,he can able to connect to the database.

    Listener comes into picture when you are connecting remotely.

    KSG:set the oracle_sid & try to connect.


    Regards,
    khaja
  • KSG
    KSG Member Posts: 802
    Please find the output..


    set | grep -i ora

    HOME=/glods1/oracle
    LD_LIBRARY_PATH=/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib:
    LIBPATH=/usr/lib:/glods/serena/dimensions/10.1/cm/lib:/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib::/glods/serena/dimensions/10.1/cm//lib
    LOGIN=oracle
    LOGNAME=oracle
    MAIL=/usr/spool/mail/oracle
    OLDPWD=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/integ/listener/alert
    ORACLE_HOME=/glods1/oracle11g/app/oracle/product/11.1.0/db_1
    ORACLE_LPARGS=-p
    ORACLE_LPPROG=lpr
    ORACLE_SID=testdb
    PATH=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/glods1/oracle/bin:/usr/bin/X11:/sbin:.
    PS1='<[email protected]:${PWD:-$(pwd)}> '
    PWD=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/network/admin
    SELF=oracle
    SHLIB_PATH=/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib:
    USER=oracle
    _=ORACLE_SID=testdb


    which sql
    which: 0652-141 There is no sql in /glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin /usr/bin /etc /usr/sbin /usr/ucb /glods1/oracle/bin /usr/bin/X11 /sbin .


    more /etc/oratab

    orcl:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
    testdb:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N

    lsnrctl status

    LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 16-APR-2010 17:52:49

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=integ)(PORT=1522)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
    Start Date 16-APR-2010 16:52:13
    Uptime 0 days 1 hr. 0 min. 36 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /glods1/oracle11g/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
    Listener Log File /glods1/oracle11g/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/integ/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=integ)(PORT=1522)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 2 instance(s).
    Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    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...
    Service "orcl_XPT" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully


    grep -i testdb $ORACLE_HOME/network/admin/tnsnames.ora

    grep -i t24db $ORACLE_HOME/network/admin/tnsnames.ora
    LISTENER_TESTDB =
    T24DB =
    (SERVICE_NAME = TESTDB)


    Thanks
    KSG
  • Chinar
    Chinar Member Posts: 5,542
    if you use sqlplus > connect username/[email protected] then must use listener ok?
This discussion has been closed.