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?

Improve this question
1
  • 2
    Is that really your tnsnames.ora? That is just a template. Commented Mar 15, 2019 at 14:02

1 Answer 1

Reset to default
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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.