Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    Unanswered: sqlplus session starting question

    If I type like below , I always get the 12514 error which I pasted.

    u060cat2:/home/oracle>sqlplus sysmanager/dba@tstdb

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:14:57 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
    descriptor


    Enter user-name:

    When I go like this, it is not giving me any problem.
    u060cat2:/home/oracle>sqlplus /nolog

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:17:42 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SQL> connect sysman/manager
    Connected.
    SQL>


    Can somebody tellme what is wrong in my tnsnames.ora file, I am also pasting that file for your reference.
    # TNSNAMES.ORA.C3F11N1 Network Configuration File: /home/oracle/app/oracle/pr
    oduct/8.1.7/network/admin/tnsnames.ora.c3f11n1
    # Generated by Oracle configuration tools.

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 70.17.1.7)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = tstdb)
    (PRESENTATION = http://admin)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )
    TSTDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )

    NEWDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = newdb)
    )
    )
    tstdb.world =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )
    u060cat2:/home/oracle>tnsping tstdb

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 17-SEP-2003 16:20:04

    (c) Copyright 1997 Oracle Corporation. All rights reserved.

    Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=u060cat2.kroger.com)( PORT=1521))
    OK (20 msec)
    u060cat2:/home/oracle>

    u060cat2:/home/oracle>env | grep -i tns
    TNS_ADMIN=/home/oracle/app/oracle/product/8.1.7/network/admin



    I am also pasting some more info
    jagadish dara

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: sqlplus session starting question

    Originally posted by jdara1
    If I type like below , I always get the 12514 error which I pasted.

    u060cat2:/home/oracle>sqlplus sysmanager/dba@tstdb

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:14:57 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
    descriptor


    Enter user-name:

    When I go like this, it is not giving me any problem.
    u060cat2:/home/oracle>sqlplus /nolog

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:17:42 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SQL> connect sysman/manager
    Connected.
    SQL>


    Can somebody tellme what is wrong in my tnsnames.ora file, I am also pasting that file for your reference.
    # TNSNAMES.ORA.C3F11N1 Network Configuration File: /home/oracle/app/oracle/pr
    oduct/8.1.7/network/admin/tnsnames.ora.c3f11n1
    # Generated by Oracle configuration tools.

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 70.17.1.7)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = tstdb)
    (PRESENTATION = http://admin)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )
    TSTDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )

    NEWDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = newdb)
    )
    )
    tstdb.world =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )
    u060cat2:/home/oracle>tnsping tstdb

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 17-SEP-2003 16:20:04

    (c) Copyright 1997 Oracle Corporation. All rights reserved.

    Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=u060cat2.kroger.com)( PORT=1521))
    OK (20 msec)
    u060cat2:/home/oracle>

    u060cat2:/home/oracle>env | grep -i tns
    TNS_ADMIN=/home/oracle/app/oracle/product/8.1.7/network/admin



    I am also pasting some more info
    What happens when you try the following:

    sqlplus sysmanager/dba@tstdb.world

  3. #3
    Join Date
    Sep 2003
    Location
    Delhi
    Posts
    15

    Cool Re: sqlplus session starting question

    Originally posted by jdara1
    If I type like below , I always get the 12514 error which I pasted.

    u060cat2:/home/oracle>sqlplus sysmanager/dba@tstdb

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:14:57 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    ERROR:
    ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
    descriptor


    Enter user-name:

    When I go like this, it is not giving me any problem.
    u060cat2:/home/oracle>sqlplus /nolog

    SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 17 16:17:42 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SQL> connect sysman/manager
    Connected.
    SQL>


    Can somebody tellme what is wrong in my tnsnames.ora file, I am also pasting that file for your reference.
    # TNSNAMES.ORA.C3F11N1 Network Configuration File: /home/oracle/app/oracle/pr
    oduct/8.1.7/network/admin/tnsnames.ora.c3f11n1
    # Generated by Oracle configuration tools.

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 70.17.1.7)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = tstdb)
    (PRESENTATION = http://admin)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )
    TSTDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )

    NEWDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = newdb)
    )
    )
    tstdb.world =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )
    u060cat2:/home/oracle>tnsping tstdb

    TNS Ping Utility for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 17-SEP-2003 16:20:04

    (c) Copyright 1997 Oracle Corporation. All rights reserved.

    Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=u060cat2.kroger.com)( PORT=1521))
    OK (20 msec)
    u060cat2:/home/oracle>

    u060cat2:/home/oracle>env | grep -i tns
    TNS_ADMIN=/home/oracle/app/oracle/product/8.1.7/network/admin



    I am also pasting some more info


    Hi,
    Just check the TNSNAMES.ORA file . You can manually edit the file or use the Net 8 configuration tool tool to rectify the database...

    Ur tnsnames.ora file should look something like this


    <connect_string>=
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = <db_name>)
    )
    )

    All the values in the tags should be customized according to your own requirment.

    Cheers,
    Pinaki

  4. #4
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18
    The tnsnames.ora file is create net configuration assistant, it already has got the correct entries like this, I am pasting for your reference.

    TSTDB =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb)
    )
    )

    I am also pasting the lsnrctl status output. This is all on aix 4.3.3 server side only.

    u060cat2:/home/oracle>lsnrctl status

    LSNRCTL for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production on 22-SEP-2003 10:59:13

    (c) Copyright 1998 Oracle Corporation. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=u060cat2 .kroger.com)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Production
    Start Date 18-SEP-2003 14:41:22
    Uptime 3 days 20 hr. 17 min. 52 sec
    Trace Level off
    Security OFF
    SNMP ON
    Listener Parameter File /home/oracle/app/oracle/product/8.1.7/network/admin/listener.ora
    Listener Log File /home/oracle/app/oracle/product/8.1.7/network/log/listener.log
    Services Summary...
    PLSExtProc has 1 service handler(s)
    newdb has 1 service handler(s)
    newdb has 1 service handler(s)
    tstdb has 1 service handler(s)
    tstdb has 1 service handler(s)
    The command completed successfully


    Thanks
    Jagadish
    jagadish dara

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    looks like you have multiple tnsnames, with only one configured right and when u start like u wrote, it uses the wrong one.
    Try setting the TNS_ADMIN variable in your environment, pointing to the tnsnames.ora u configured using the config assistant.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    don't forget your sqlnet.ora file.

    they all work together.
    I always see the most problems with connection problems having to do with the tnsnames, listener and sqlnet files all not matching.

    for example, it looks like your domain is kroger.com yet this is not replicated anywhere else. Your tnsnames should look more like:

    TSTDB.kroger.com =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = u060cat2.kroger.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = tstdb.kroger.com)
    )


    you also need to set domain= kroger.com in your sqlnet.ora file.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •