Linux error: ORA-12514

oracle

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?

Best Answer

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.

Related Question