0

I'm receiving the error below and as I'm not a DB expert, would love to have someone to help me.

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

listener.ora:

cdrraw =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =<hostname>)(PORT = 1521))
    )
  )

SID_LIST_prod =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = cdrraw)
      (ORACLE_HOME = /u01/home)
    )
  )

tnsnames.ora:

cdrraw= [ (DESCRIPTION_LIST =  # Optional depending on whether u have
                                # one or more descriptions
                                # If there is just one description, unnecessary ]
          (DESCRIPTION=
            [ (SDU=2048) ]      # Optional, defaults to 2048
                                # Can take values between 512 and 32K
            [ (ADDRESS_LIST=    # Optional depending on whether u have
                                # one or more addresses
                                # If there is just one address, unnecessary ]
              (ADDRESS=
                [ (COMMUNITY=<community_name>) ]
                (PROTOCOL=tcp)
                (HOST=<hostname>)
                (PORT=<portnumber (1521 is a standard port used)>)
              )
              [ (ADDRESS=
                  (PROTOCOL=ipc)
                  (KEY=<ipckey (PNPKEY is a standard key used)>)
                )
              ]
              [ (ADDRESS=
                  [ (COMMUNITY=<community_name>) ]
                  (PROTOCOL=decnet)
                  (NODE=<nodename>)
                  (OBJECT=<objectname>)
                )
              ]
              ... # More addresses
            [ ) ] # Optional depending on whether ADDRESS_LIST is used or not
            [ (CONNECT_DATA=
                (SID=<oracle_sid>)
                [ (GLOBAL_NAME=<global_database_name>) ]
              )
            ]
            [ (SOURCE_ROUTE=yes) ]
          )
          (DESCRIPTION=
            [ (SDU=2048) ]      # Optional, defaults to 2048
                                # Can take values between 512 and 32K
            [ (ADDRESS_LIST= ]  # Optional depending on whether u have more
                                # than one address or not
                                # If there is just one address, unnecessary
              (ADDRESS
                [ (COMMUNITY=<community_name>) ]
                (PROTOCOL=tcp)
                (HOST=<hostname>)
                (PORT=<portnumber (1521 is a standard port used)>)
              )
              [ (ADDRESS=
                  (PROTOCOL=ipc)
                  (KEY=<ipckey (PNPKEY is a standard key used)>)
                 )
              ]
              ...               # More addresses
            [ ) ]               # Optional depending on whether ADDRESS_LIST
                                # is being used
            [ (CONNECT_DATA=
                (SID=<oracle_sid>)
                [ (GLOBAL_NAME=<global_database_name>) ]
              )
            ]
            [ (SOURCE_ROUTE=yes) ]
          )
          [ (CONNECT_DATA=
              (SID=<oracle_sid>)
              [ (GLOBAL_NAME=<global_database_name>) ]
            )
          ]
          ...   # More descriptions
        [ ) ]   # Optional depending on whether DESCRIPTION_LIST is used or not
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cdrraw)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                15-MAR-2019 10:42:18
Uptime                    0 days 0 hr. 14 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/cdrraw/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cdrraw)(PORT=1521)))
Services Summary...
Service "cdrraw" has 1 instance(s).
  Instance "cdrraw", status READY, has 1 handler(s) for this service...
Service "cdrrawXDB" has 1 instance(s).
  Instance "cdrraw", status READY, has 1 handler(s) for this service...
The command completed successfully

Can someone help me?

  • 2
    Is that really your tnsnames.ora? That is just a template. – Balazs Papp Mar 15 '19 at 14:02
1

As @BalazsPapp has already pointed out, the tnsnames.ora file you listed is just a template explaining the values you could enter.

Analysis

If you perform a tnsping cdrraw do you receive an answer?

Input

tnsping cdrraw

Output Failed Connection

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 15-MAR-2019 15:26:57

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

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

Output Successful Connection

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 15-MAR-2019 15:28:57

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

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (COMMUNITY=TCPIP.YOURDOMAIN.COM) (PROTOCOL=TCP) (HOST=10.0.0.1) (PORT=1521)) (CONNECT_DATA= (GLOBAL_NAME=cdrraw.YOURDOMAIN.COM) (SID=cdrraw)))

OK (0 msec)

The OK confirms that the instance can be reached.

Possible Solution

Your tnsnames.ora file might be located under /u00/app/oracle/network/admin/. Find the correct tnsnames.ora file for your instance and enter the details for your Oracle instance.

A correct entry will be similar to the following:


cdrraw.YOURDOMAIN.COM=
  (DESCRIPTION=
    (ADDRESS=
      (COMMUNITY=TCPIP.TG.CH)
      (PROTOCOL=TCP)
      (HOST=10.0.0.1) # == ENTER THE IP ADDRESS OR FULLY QUALIFIED HOSTNAME OF YOUR SERVER!!
      (PORT=1521)
    )
    (CONNECT_DATA=
      (GLOBAL_NAME= cdrraw.YOURDOMAIN.COM)
      (SID=cdrraw)
    )
  )

Once you have done this try pinging the instance again using the following:

tnsping cdrraw.DOMAIN.COM

You should get a response from your instance.

Bonus Information

The listener.ora configuration file is for incoming external client connections to the listener on the Oracle server. The listener then hands over the client connection to the Oracle instance, which requires everything to be configured in the tnsnames.ora configuration file.

If you are logged in to the server, then you don't really need the Oracle Listener. Connections are made directly with the instance.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.