1 Reply Latest reply: Mar 3, 2013 1:03 AM by 934265 RSS

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

    934265
      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
          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!!