This discussion is archived
1 Reply Latest reply: Mar 2, 2013 11:03 PM by 934265 RSS

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

934265 Newbie
Currently Being Moderated
Hi All,

I am trying to create a physical standby using RMAN duplicate command.Yesterday I created it succesfully ,today I dropped the standby database I tried to create new one when I got hit by this error"

<code>
oracle@standby.swapnilkambli.com(standby) $ rman target sys/root123#@db1 auxiliary sys/root123#@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 3 11:38:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
oracle@standby.swapnilkambli.com(standby) $
</code>

below is my tnsname and listener files from primary


oracle@primary.swapnilkambli.com(db1) $ pwd
/u01/app/oracle/dbhome_1/network/admin
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $ ls -ltr
total 28
-rw-r--r-- 1 oracle oinstall 187 May 7 2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Sep 12 22:12 samples
-rw-r--r-- 1 oracle oinstall 40 Jan 20 03:38 sqlnet.ora_1
-rw-r----- 1 oracle oinstall 1004 Mar 3 00:33 tnsnames.ora
-rw-r--r-- 1 oracle oinstall 1004 Mar 3 11:27 tnsnames13030311AM2745.bak
-rw-r--r-- 1 oracle oinstall 514 Mar 3 11:27 listener13030311AM2745.bak
-rw-r--r-- 1 oracle oinstall 508 Mar 3 11:28 listener.ora
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1.primary.swapnilkambli.com)
)
)

RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rcat.primary.swapnilkambli.com)
)
)

DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2.primary.swapnilkambli.com)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby.standby.swapnilkambli.com)
)
)

oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rcat.primary.swapnilkambli.com)
(ORACLE_HOME = /u01/app/oracle/dbhome_1)
(SID_NAME = rcat)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.swapnilkambli.com)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

oracle@primary.swapnilkambli.com(db1) $

oracle@primary.swapnilkambli.com(db1) $ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:42:52

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.swapnilkambli.com)(PORT=1521)))
Services Summary...
Service "db1.primary.swapnilkambli.com" has 1 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6 refused:0 state:ready
LOCAL SERVER
Service "db1XDB.primary.swapnilkambli.com" has 1 instance(s).
Instance "db1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: primary.swapnilkambli.com, pid: 2617>
(ADDRESS=(PROTOCOL=tcp)(HOST=primary.swapnilkambli.com)(PORT=23611))
Service "rcat.primary.swapnilkambli.com" has 1 instance(s).
Instance "rcat", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully



oracle@primary.swapnilkambli.com(db1) $ tnsping db1

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:43:36

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 = primary.swapnilkambli.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.primary.swapnilkambli.com)))
OK (0 msec)
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $
oracle@primary.swapnilkambli.com(db1) $ tnsping standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:43:44

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 = standby.swapnilkambli.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.standby.swapnilkambli.com)))
OK (0 msec)




---------------------------------------------------------------------------

and below are my entries from standby server


oracle@standby.swapnilkambli.com(standby) $ pwd
/u01/app/oracle/dbhome_1/network/admin
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $ ls -ltr
total 24
-rw-r--r-- 1 oracle oinstall 187 May 7 2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Sep 12 22:12 samples
-rw-r----- 1 oracle oinstall 576 Mar 3 11:21 tnsnames.ora
-rw-r--r-- 1 oracle oinstall 576 Mar 3 11:25 tnsnames13030311AM2540.bak
-rw-r--r-- 1 oracle oinstall 514 Mar 3 11:25 listener13030311AM2540.bak
-rw-r--r-- 1 oracle oinstall 514 Mar 3 11:26 listener.ora
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1.primary.swapnilkambli.com)
)
)


STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.swapnilkambli.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby.standby.swapnilkambli.com)
)
)

oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby.standby.swapnilkambli.com)
(ORACLE_HOME = /u01/app/oracle/dbhome_1)
(SID_NAME = standby)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.swapnilkambli.com)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

oracle@standby.swapnilkambli.com(standby) $


oracle@standby.swapnilkambli.com(standby) $ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:43:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby.swapnilkambli.com)(PORT=1521)))
Services Summary...
Service "standby" has 1 instance(s).
Instance "standby", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "standby.standby.swapnilkambli.com" has 1 instance(s).
Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0
LOCAL SERVER
The command completed successfully

oracle@standby.swapnilkambli.com(standby) $ tnsping db1

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:49:12

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 = primary.swapnilkambli.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.primary.swapnilkambli.com)))
OK (0 msec)
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $
oracle@standby.swapnilkambli.com(standby) $ tnsping standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-MAR-2013 11:49:18

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 = standby.swapnilkambli.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby.standby.swapnilkambli.com)))
OK (0 msec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



tnsping works from both side I am able to connect from primary as below:


oracle@primary.swapnilkambli.com(db1) $ rman target sys/root123#@db1 auxiliary sys/root123#@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 3 11:44:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: DB1 (DBID=1402881212)
connected to auxiliary database: DB1 (not mounted)

RMAN>



DB version 11.2.0.1.0
OS version Linux 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

primary server--- primary.swapnilkambli.com
primary database--- db1

standby server--- standby.swapnilkambli.com
standby database--- standby

After I dropped the physical standby first time I did make some changes in tns of standby server which I dont recollect.

Help on this error ORA-12514 is available on internet but I did not find anything helpful to my situation

Edited by: user12860499 on Mar 2, 2013 10:45 PM
  • 1. Re: ORA-12514: TNS:listener does not currently know of service requested in con
    934265 Newbie
    Currently Being Moderated
    GOT IT!!!!

    I check listener trace and found below:

    Log messages written to /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml
    03-MAR-2013 12:21:59 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=standby.swapnilkambli.com)(USER=oracle))(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * reload * 0
    03-MAR-2013 12:22:00 * service_register * standby * 0
    Sun Mar 03 12:22:16 2013
    03-MAR-2013 12:22:16 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1.primary.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=standby.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=39512)) * establish * db1.primary.swapnilkambli.com * 12514
    TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
    03-MAR-2013 12:22:20 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=standby.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=54862)) * establish * standby.standby.swapnilkambli.com * 0
    03-MAR-2013 12:22:20 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=standby.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=54863)) * establish * standby.standby.swapnilkambli.com * 0
    03-MAR-2013 12:22:20 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=standby.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=54864)) * establish * standby.standby.swapnilkambli.com * 0
    Sun Mar 03 12:22:27 2013
    03-MAR-2013 12:22:27 * service_update * standby * 0
    Sun Mar 03 12:22:40 2013
    03-MAR-2013 12:22:40 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=primary.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=60821)) * establish * standby.standby.swapnilkambli.com * 0
    03-MAR-2013 12:22:40 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=primary.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=60822)) * establish * standby.standby.swapnilkambli.com * 0
    03-MAR-2013 12:22:40 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standby.standby.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=primary.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=60823)) * establish * standby.standby.swapnilkambli.com * 0
    03-MAR-2013 12:22:42 * service_update * standby * 0
    03-MAR-2013 12:22:45 * service_update * standby * 0
    Sun Mar 03 12:22:51 2013
    03-MAR-2013 12:22:51 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1.primary.swapnilkambli.com)(CID=(PROGRAM=rman)(HOST=standby.swapnilkambli.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=39536)) * establish * db1.primary.swapnilkambli.com * 12514
    TNS-12514: TNS:listener does not currently know of service requested in connect descriptor





    there was a entry in /etc/hosts which was directing primary.swapnilkambli.com to 127.0.01
    This was because I created this OS from a Virtual box template wherein that entry was made.

    I changed the /etc/hosts to refelct the true address and it worked!!

Legend

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