This discussion is archived
7 Replies Latest reply: Feb 5, 2013 3:12 AM by Athurumithuru RSS

ORA-12514: TNS:listener does not currently know of service

Athurumithuru Newbie
Currently Being Moderated
Hi All,

I've created physical standby in my production 'dbe'. All the steps were success expect listner connections. on my primary site error as


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

Standby listner file as bellow:

# listener.ora Network Configuration File: /u02/app/oradbe/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbdbe.expolanka.com)(PORT = 1522))
)

ADR_BASE_LISTENER = /u02/app/oradbe

================================================

last lines of log.xml


<msg time='2013-02-01T05:52:17.260+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2013-02-01T05:52:17.269+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>01-FEB-2013 05:52:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stbdbe.expolanka.com)(CID=(PROGRAM=oracle)(HOST=expdb.expolanka.com)(USER=oradbe))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.20.66)(PORT=46579)) * establish * stbdbe.expolanka.com * 12514
</txt>
</msg>
<msg time='2013-02-01T05:52:17.269+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2013-02-01T05:53:17.283+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>01-FEB-2013 05:53:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stbdbe.expolanka.com)(CID=(PROGRAM=oracle)(HOST=expdb.expolanka.com)(USER=oradbe))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.20.66)(PORT=12037)) * establish * stbdbe.expolanka.com * 12514
</txt>
</msg>
<msg time='2013-02-01T05:53:17.284+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2013-02-01T05:53:17.292+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>01-FEB-2013 05:53:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stbdbe.expolanka.com)(CID=(PROGRAM=oracle)(HOST=expdb.expolanka.com)(USER=oradbe))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.20.66)(PORT=12038)) * establish * stbdbe.expolanka.com * 12514
</txt>
</msg>
<msg time='2013-02-01T05:53:17.292+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2013-02-01T05:53:17.300+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>01-FEB-2013 05:53:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=stbdbe.expolanka.com)(CID=(PROGRAM=oracle)(HOST=expdb.expolanka.com)(USER=oradbe))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.2.20.66)(PORT=12039)) * establish * stbdbe.expolanka.com * 12514
</txt>
</msg>
<msg time='2013-02-01T05:53:17.300+05:30' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='stbdbe.expolanka.com'
host_addr='10.2.2.12'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>

Pls help me.

Rgds,
Athurumithuru
  • 1. Re: ORA-12514: TNS:listener does not currently know of service
    mseberg Guru
    Currently Being Moderated
    Hello;

    ORA-12154



    Until you can do the following:



    sqlplus sys/password@Standby as sysdba (from the primary system)



    and



    sqlplus sys/password@primary as sysdba (from the standby system)



    You will not be able to ship redo. You have an error in your tnsnames or listener configuration.



    Most likely your tnsname is missing an entry or has an incorrect entry.

    Best Regards

    mseberg
  • 2. Re: ORA-12514: TNS:listener does not currently know of service
    Athurumithuru Newbie
    Currently Being Moderated
    Hi,

    Thanks for the reply.. Here are results.

    On my primary side
    =============


    [oradbe@expdb ~]$
    [oradbe@expdb ~]$ sqlplus "sys/oracle@stbdbe as sysdba"

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 1 06:52:11 2013

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

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


    Enter user-name: ^C
    [oradbe@expdb ~]$
    [oradbe@expdb ~]$ tnsping stbdbe

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 01-FEB-2013 06:52:26

    Copyright (c) 1997, 2011, Oracle. All rights reserved.

    Used parameter files:
    /u02/app/oradbe/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stbdbe.expolanka.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbdbe.expolanka.com)))
    OK (10 msec)
    [oradbe@expdb ~]$
    [oradbe@expdb ~]$
    [

    On my standby site
    =============

    [oradbe@stbdbe ~]$
    [oradbe@stbdbe ~]$ sqlplus "sys/oracle@dbe as sysdba"

    SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 1 06:48:57 2013

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

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


    Enter user-name: ^C
    [oradbe@stbdbe ~]$ tnsping dbe

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-FEB-2013 06:49:14

    Copyright (c) 1997, 2009, Oracle. All rights reserved.

    Used parameter files:
    /u02/app/oradbe/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = expdb.expolanka.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbe)))
    OK (10 msec)
    [oradbe@stbdbe ~]$

    But on my client machine, I can log into primary
    =================

    C:\Users\Amila>sqlplus sys/oracle@dbe as sysdba;

    SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 1 06:55:38 2013

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


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

    SQL>
  • 3. Re: ORA-12514: TNS:listener does not currently know of service
    mseberg Guru
    Currently Being Moderated
    Hello;

    tnsping can only offer so much help. By doing the connect test you know you one have a problem on one side.

    You probably have a listener.ora issue on the standby side.

    Compare to my example here :

    http://www.visi.com/~mseberg/data_guard/Data_Guard_Listener_Example.html

    Change as needed and restart the listener.
    ORA-12514:
         TNS:listener does not currently know of service requested in connect descriptor
    Cause:      The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
    Action:      
    
    - Wait a moment and try to connect a second time.
    
    - Check which services are currently known by the listener by executing: lsnrctl services <listener name>
    
    - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
    
    - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
    
    - Check for an event in the listener.log file.
    Best Regards

    mseberg
  • 4. Re: ORA-12514: TNS:listener does not currently know of service
    Athurumithuru Newbie
    Currently Being Moderated
    Hello,

    I have change the standby listner file as follow:

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbdbe.expolanka.com)(PORT = 1522))
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u02/app/oradbe/product/11.2.0/dbhome_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (global_dbname = dbe.expolanka.com)
    (ORACLE_HOME = /u02/app/oradbe/product/11.2.0/dbhome_1)
    (sid_name = dbe.expolanka.com)
    )
    )
    ADR_BASE_LISTENER = /u02/app/oradbe

    ============================================

    But from primary side.

    [oradbe@expdb ~]$ sqlplus "sys/oracle@stbdbe as sysdba"

    SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 1 09:21:04 2013

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

    ERROR:
    ORA-01031: insufficient privileges


    Enter user-name:
    ERROR:
    ORA-01017: invalid username/password; logon denied


    ===============================================

    Primary tns are:

    LISTENER_DBE =
    (ADDRESS = (PROTOCOL = TCP)(HOST = expdb.expolanka.com)(PORT = 1522))


    DBE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = expdb.expolanka.com)(PORT = 1522))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = dbe.expolanka.com)
    )
    )

    STBDBE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = stbdbe.expolanka.com)(PORT = 1522))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = dbe.expolanka.com)
    )
    )
  • 5. Re: ORA-12514: TNS:listener does not currently know of service
    Sunny kichloo Expert
    Currently Being Moderated
    How did you created password file of standby database??

    Try to copy the password file of primary database to standby database password file location and the try to rename it as per standby database sid.
  • 6. Re: ORA-12514: TNS:listener does not currently know of service
    mseberg Guru
    Currently Being Moderated
    OK.

    I think you have/had more than one issue.

    I agree with Sunny. For Oracle you must copy the password file from the Primary to the Standby and rename it.

    Example :

    orapwPRIMARY ----> orapwSTANDBY


    You have to shutdown and start the Standby on the new Password file for it to work.

    Best Regards

    mseberg
  • 7. Re: ORA-12514: TNS:listener does not currently know of service
    Athurumithuru Newbie
    Currently Being Moderated
    Hi All,

    Thank you very much for the hard efforts and sorry for the delay reply. I got the answer. I've setup the DG for second time and got the same error. I got to know my Stand by Listner doesnt have the service register with it. So I ran the 'netmgr' and register the service. Thats all and its solve my issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points