Ошибка ora 12541 tns no listener - Не ошибается лишь тот, кто ничего не делает!

I am new on Oracle database, but I have one issue.
On my Database server (server1) listener and database instance run correctly and I can use sqlplus to connect to this DB.
When I connect to database by using other server, I check the TNS configuration and it is correct but system says:

ERROR:
ORA-12541: TNS:no listener

My database is Oracle 10gR2

So how can I solve this issue?

Gryu

2,0622 gold badges16 silver badges29 bronze badges

asked Nov 13, 2012 at 9:58

You need to set oracle to listen on all ip addresses (by default, it listens only to localhost connections.)

Step 1 — Edit `listener.ora`

This file is located in:

Windows: %ORACLE_HOME%networkadminlistener.ora.
Linux: $ORACLE_HOME/network/admin/listener.ora

Replace localhost with 0.0.0.0

# ...

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

# ...

Step 2 — Restart Oracle services

Windows: WinKey + r
```
services.msc
```
Linux (CentOs):
```
sudo systemctl restart oracle-xe
```

answered Feb 19, 2014 at 14:58

Mikael HolmgrenMikael Holmgren

2,4161 gold badge18 silver badges27 bronze badges

I also faced the same problem but I resolved the issue by starting the TNS listener in control panel -> administrative tools -> services ->oracle TNS listener start.I am using windows Xp and Toad to connect to Oracle.

answered Jul 1, 2013 at 5:53

subhashissubhashis

4,6198 gold badges37 silver badges52 bronze badges

Check out your TNS Names, this must not have spaces at the left side of the ALIAS

Best regards

answered Oct 31, 2013 at 0:31

ArmandArmand

511 silver badge5 bronze badges

According to oracle online documentation

ORA-12541: TNS:no listener

Cause: The connection request could not be completed because the listener is not running.

Action: Ensure that the supplied destination address matches one of the addresses used by 
the listener - compare the TNSNAMES.ORA entry with the appropriate LISTENER.ORA file (or  
TNSNAV.ORA if the connection is to go by way of an Interchange). Start the listener on 
the remote machine.

answered Nov 13, 2012 at 11:37

dariyooshdariyoosh

6041 gold badge4 silver badges12 bronze badges

Источник

ORA-12541 is the same as TNS-12541, they all indicate that the listener is not running according to your specified destination.

Let’s see how we reproduce ORA-12541 and TNS-12541.

SQL*Plus throws ORA-12541

SQL> conn hr/hr@orcl ERROR: ORA-12541: TNS:no listener

TNSPING throws TNS-12541

C:Usersed>tnsping compdb


  TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:20:06
  Copyright (c) 1997, 2013, Oracle.  All rights reserved.
  Used parameter files:
 C:oracleappclientedproduct12.1.0client_1networkadminsqlnet.ora



  


                       
  




   Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
 TNS-12541: TNS:no listener

Cause

The connection cannot find the listener, it could be:

The listener is not started.

This is the most common problem of ORA-12541 we have met. Just start it up and make sure it’s running.

The listener is listening to another port other than the default one, e.g. 1522.

Backup port like 1522 may be used by the listener for connection, you should ask for DBA to handle it.

The connection went for the wrong database server.

Please check every parameter in your connect descriptor and make sure they are correct.

Routing to the wrong destination.

It’s rare, but it did happen in a very complex network environment. Part of connections are routed by some network device to an unknown server where no listener on port 1521.

Solution

Check the status and port of LISTENER on server side and start it if necessary.

[oracle@primary01 ~]$ lsnrctl status


  LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:22:34



  


                       
  




  Copyright (c) 1991, 2013, Oracle.  All rights reserved.
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 2: No such file or directory
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1521)))
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 111: Connection refused

As you can see, the database port is 1521 without questions. But the listener is not started. So let’s start it up.

[oracle@primary01 ~]$ lsnrctl start


  LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:23:59
  Copyright (c) 1991, 2013, Oracle.  All rights reserved.
  Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
  TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
 Log messages written to /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 Start Date                22-JUL-2014 19:23:59
 Uptime                    0 days 0 hr. 0 min. 2 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/primary01/listener/alert/log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1521)))
 The listener supports no services
 The command completed successfully

For more testing skills, you may check Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.

Источник

I’ve been working this issue all day, reading posts here and other locations. I’m just not able to get past this error. I can connect just fine locally on my server (CentOS), but continue to get the TNS:no listener error when trying to connect via my Mac (OSX Mavericks) which is on the same subnet. Below are some notes I’ve captured based on what information I see that has been requested for this type of issue:

--------------------------------------------------------------------------------
MISC NETWORK CONFIG:
--------------------------------------------------------------------------------
[root@rubyrailssvr ~]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.14   rubyrailssvr.homenet.com
[root@rubyrailssvr ~]#


[root@rubyrailssvr ~]# more /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=rubyrailssvr.homenet.com
[root@rubyrailssvr ~]#
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
LISTENER.ORA File:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ more /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)
[oracle@rubyrailssvr ~]$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
TNSNAMES.ORA File:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ more /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[oracle@rubyrailssvr ~]$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
SQLNET.ORA File:
--------------------------------------------------------------------------------
jfhogarty@rubyrailssvr:/u01/app/oracle/product/11.2.0/xe/network/admin$ more sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)
jfhogarty@rubyrailssvr:/u01/app/oracle/product/11.2.0/xe/network/admin$
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
CHECK LISTENERS:
--------------------------------------------------------------------------------
[oracle@rubyrailssvr ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-AUG-2014 17:56:21

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                13-AUG-2014 17:46:45
Uptime                    0 days 0 hr. 9 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rubyrailssvr/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.14)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rubyrailssvr.homenet.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
ON THE CLIENT:
--------------------------------------------------------------------------------
➜  ~  more /usr/local/oracle/admin/network/tnsnames.ora
rubyrailssvr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XE))
  )

rubyrailssvr.homenet.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XE))
  )
➜  ~
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
TRYING TO CONNECT:
--------------------------------------------------------------------------------
➜  ~  sqlplus system@rubyrailssvr

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 13 18:21:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener


Enter user-name: % 
➜  ~



➜  ~  sqlplus system@rubyrailssvr.homenet.com

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 13 18:22:17 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener


Enter user-name: 
➜  ~
--------------------------------------------------------------------------------

asked Aug 13, 2014 at 22:43

First, thanks for the response. I’m working on a Mac, and the instant client doesn’t include the tnsping utility. I had tried to login, from the remote computer, and that is where I got the TNS:no listener error. Logging in on the server is fine.

I had tried the telnet test before, but as I did not have telnet installed and running on the server, that test was not helpful. However, I decided to install telnet as it can be useful in testing. While doing so, I noticed that a command I had executed at the command line to open a port to iptables was not there. So I updated that file and now everything is working.

vim /etc/sysconfig/iptables

[BEFORE:]
# Generated by iptables-save v1.4.7 on Fri Jan 31 09:19:34 2014
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [6630:522769]
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Fri Jan 31 09:19:34 2014

[AFTER:]
# Generated by iptables-save v1.4.7 on Fri Jan 31 09:19:34 2014
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [6630:522769]
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p udp -m state --state NEW --dport 23 -j ACCEPT
-A INPUT -p tcp -m state --state NEW --dport 23 -j ACCEPT
-A INPUT -p tcp --dport 1521 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Fri Jan 31 09:19:34 2014

service iptables restart

Chased my tail so much yesterday that I never thought to review the iptables file — /slap head!

Thank you,

John…
(HoGi)

answered Aug 14, 2014 at 12:56

HoGiHoGi

711 gold badge1 silver badge6 bronze badges

My sequence of steps for troubleshooting

Is the listener resolvable? C:>tnsping MYDB (Confirms: the database listener is running on serverside)
Is there a firewall issue? C:>telnet hostname 1521
Can I login? C:>sqlplus system/pass@MYDB or C:>sqlplus system/pass@hostname:1521/MYDB (Confirms: The database is open)
Finally, chances of having a successful login with your favorite dev-tool has increased by 100%

answered Aug 14, 2014 at 5:20

In listener.ora file

set HOST=LOCALHOST

reason being that if your on a wireless network you can get assigned different ip addresses after every reboot which invalidates your listener settings if using a fixed ip address in your listener.ora file.

With HOST=LOCALHOST you are guaranteed consistency with regards the ip address settings between system reboots when it comes time that the listener service starts up.

answered Nov 6, 2015 at 15:48

Its too late but I also face this problem currently and I solved this by.

Go to Net Manager(Oracle Net Manager) in your oracle installed
system.
Expand Local > Listners > LISTNER.
Add new address protocol:TCP/IP, Host:YOURPCNAME.DOMAIN, Port:YOUR PORT.
Go to File > Save Network Configuration.
RESTART system.

below is snapshot for adding new address

answered Nov 19, 2016 at 9:04

Источник

N no. of articles you will find on the internet about “ORA-12541 TNS no listener error” but still finding an effective solution. Don’t worry this solution helped me a lot and I hope it will help you too.

I got the same issue while working on Oracle 10g database then I decided to share my experience and steps to resolve this issue.

Error Message:

ORA-12541 TNS no listener error

Resolution:

Now you have to follow below mentioned steps to resolve the ORA-12541 issue.

Step-1: First check your listener status.

C:>lsnrctl status

The above command will show the current status of your listener.

Step-2: Start the listener, if listener is not running.

C:>lsnrctl start

This command will start all your listener services.

Step-3: Stop the listener, if listener is running.

C:>lsnrctl stop

This command will stop all your listener services.

Step-4: Now restart the listener.

C:>lsnrctl reload

This command will restart all your listener services.

Note: If you want to see all the available listener commands. Type lsnrtcl help on command prompt and you will get all available options.

C:>lsnrctl help

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 02-JUL-2015 20:25:14

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

The following operations are available
An asterisk (*) denotes a modifier or extended command:

start                           stop                status
services             version           reload
save_config        trace               change_password
quit                exit                  set*
show*

Step-5: One last and very important thing which you have to do.

Open sqlnet.ora file and make the changes as suggested.

Change NTS to NONE

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Now restart the listener services and your listener problem ORA-12541 will be resolved.

If this is not working, do write in comment section. I will try to help you at the earliest.

Источник

Issue

How to troubleshoot Oracle Connection errors.

Oracle drivers require very specific connection statements in a unique format, though a TNSNames.ora file may not always be required. For instance, if you have installed only the Tableau-provided Oracle files and do not have a stand-alone Oracle client, the Oracle error messages will still refer to the TNSNames.ora file, making troubleshooting complicated.

Environment

Tableau Desktop
Tableau Server
Oracle

Resolution

Often, correcting route or naming syntax in the Advanced Oracle Connection dialog box or using your full .WORLD database name resolves most Oracle connection issues. If your connection error requires more troubleshooting, refer to the five common connection errors listed below.

ORA-03113: end-of-file on communication channel
ORA-12154: TNS: could not resolve the connect identifier specified: HOST value incorrect or Global name incorrect or unknown
ORA-12514: TNS listener does not currently know of service requested in connect descriptor: SERVICE value incorrect
ORA-12541: TNS: no listener: PORT value incorrect
ORA-00932: inconsistent data types

ORA-03113: end-of-file on communication channel

ORA-03113 is a catch-all type error for any problem interrupting an Oracle session. There can be numerous causes for this error. Please refer to the list below for some troubleshooting guidance.

Refer to Oracle documentation specific to this error: My Oracle Support.
- Refer to Oracle’s B Troubleshooting Common Errors page.
Oracle recommends that you check for network problems and review the SQL*Net setup.
If you are connecting to Oracle 9.2.0.5, in many cases the primary cause of this error is Oracle bug 3010227. Ask your Oracle database administrator to apply Oracle patch 9.2.0.6 or another patch appropriate for your server.
Set the Oracle initialization parameter ‘STAR_TRANSFORMATION_ENABLED’ to FALSE.
Test changing the scheduled time of the extract refresh
Alternatively, if you would like to test this issue further follow the optional procedure listed below.

Step 1

From the Tableau Desktop start page, select Connect to Data.

Step 2

On the Connect page, click Oracle, then click OK.

For more information about completing the connection steps, refer to the Oracle Database topic in the Desktop Help.

Step 3

In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.
Copy the query in the Edit Custom SQL dialog box.

SELECT "NumericBins", "Key" as "Key", "NumericBins", "Measure E-2" AS "Measure E-2", "NumericBins", "Measure E-1" AS "Measure E-1", "NumericBins", "Measure E+0" AS "Measure E+0", "NumericBins", "Measure E+1" AS "Measure E+1", "NumericBins", "Measure E+4" AS "Measure E+4", "NumericBins", "Measure E+7" AS "Measure E+7" FROM "TestV1", "NumericBins" "NumericBins"

Where «TestV1» is the name of your connection in Tableau.

Step 4

In a SQL session connected to this database, paste and run the query. The expected response is error ORA-7445: exception encountered: core dump, which confirms that the problem is ORA-3113, as expected.

ORA-12154: TNS: could not resolve the connect identifier specified

ORA-12154 occurs when the transparent network substrate (TNS) cannot resolve the service name. The service name is specified in the TNSNames.ora file, which is located in your %ORACLE_HOME%networkadmin folder. Most often, this error occurs when information in the TNSNames.ora file is incorrect. For example:

The .world extension is not included on the database name.
The SERVICE_NAME or HOST variable is incorrect.

To resolve this issue, try one of the three following troubleshooting options, in the order listed below.

Option 1: Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora
Option 2: Error «ORA-12154» Connecting to Oracle When Not Using TNSNames.ora

Option 1: Edit TNSNames.ora

Provide the full database name, including the .world extension in both of the following locations:

The TNSNames.ora file.
And
The Server text box of the Connect page.

Option 2: Ensure that Tableau Server Run As User account has permissions to TNSNames.ora (Tableau Server only)

If you have Tableau Server installed, complete the procedure below to ensure that the Tableau Server Run As user account has permissions to the location of the TNSNames.ora file. If the Run As user account does not have permissions, Tableau Server is unable to access the Oracle Data source details.

Step 1

Verify the location of the TNSNames.ora file, or the equivalent SQLNET.ora and LDAP.ora files on the machine.

Note: By default, the TNSNames.ora file is located in <oracle-directory>networkadmin directory. For example, C:Oracle_Clientnetworkadmin.

Step 2

Confirm that the TNS_ADMIN variable points to the location of the file or files described in step 1.

Note: To check the TNS_ADMIN variable, click the Start button, and select Control Panel > System. Click Advanced system settings, click the Advanced tab, and click Environmental Variables button.
The system variable file path must be in UNC format.

Step 3

Open TSM in a browser: https://<tsm-computer-name>:8850 For more information, see Sign in to Tableau Services Manager Web UI.

Step 4

Click the Security tab, and then click the Run As Service Account tab.
Under Server Run As User, copy the information in the Username field.

Step 5

Go to the folder where the TNSNames.ora file is located.

Step 6

Right-click the folder and select Properties. Click the Security tab and click the Edit button.

Step 7

Under Group or user names, click the Add button.

Step 8

In the Enter the object names to select text box, paste the details of the Run As User account you copied in step 6.

Step 9

When finished, click OK.

Step 10

In the Permissions area,ensure that the Full control and Modify check boxes are selected.

Step 11

Click OK to close the dialog boxes.

Option 3: Verify that all information in TNSNames.ora is correct

If the above troubleshooting steps do not resolve the issue, continue reading and complete the procedure to verify the other information in the TNSNames.ora file is provided correctly.

An example of a TNSNames.ora file is shown here:

QAORCL10.world =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = MY_HOST_NAME)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = MY_SERVICE_NAME)

)

The three variables of interest in the file are HOST, PORT, and SERVICE_NAME. Copy these variables from the TNSNames.ora file and keep them available. These variables are case sensitive.The following steps describe how to provide these variables for your connection.

Step 1

From the Tableau Desktop start page, select Connect to Data.

Step 2

On the Connect page, click Oracle.

Step 3

Provide the following information from the TNSNames.ora file:

In the Server name text box, type the HOST name.
In the Service text box, type the SERVICE_NAME.
In the Port text box, type the PORT number.
Specify whether to use Windows Authentication or a specific user name and password, and then click Connect.

Note: Variables are case sensitive.

Step 4

Select a schema from the Schema drop-down list, drag a table to the join area, and then click Go to Worksheet.

Step 5

Complete the steps in the Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora article.

Important:

Make sure that you save the TNSNames.ora file you use in ASCII encoding. Any other encoding besides ASCII, for example UTF-8 or Unicode, causes the ORA-12154 error message.
These steps are usually required even if the Oracle software is already installed on the machine.

Step 6

Download and install the appropriate Oracle drivers from the Tableau Drivers page. Even if an Oracle driver is installed on your computer, it could be incompatible with Tableau and will require the version specified on the Drivers page.

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

Typically this error occurs when the SERVICE value is incorrect.

To resolve this issue, find out what the correct SERVICE value is, open the TNSNames.ora file located in your %ORACLE_HOME%networkadmin folder. Refer to the steps under ORA_12154 if necessary.

ORA-12541: TNS: no listener

Typically this error occurs when the PORT value is incorrect.

To resolve this issue, replace the PORT value with either 1521 or 1526. Try the value that is currently not in use.

ORA-00932: inconsistent data types

This error occurs when connecting to Oracle or when creating an extract from an Oracle data source. Typically this error is caused by the installation of incorrect Oracle drivers.

To resolve this issue, install the correct Oracle drivers from the Drivers page for the version of Tableau you are using.

In addition to the above common errors, if you are using Tableau Desktop/Server 2020.2 or later and experiencing performance issues e.g. extract refresh taking long time, you can try downloading and installing the Oracle OCI driver. Refer to the article in Related Links. Driver can be downloaded from here.

Additional Information

Suggestions

If you do not have an Oracle Client installed on your machine, be sure to get the necessary files from your database administrator. If the Oracle data connection errors persist, do the following:

Check the TNSNames.ora folder path used to create the TNS_ADMIN variable.
Restart your machine to ensure that the TNS_ADMIN variable is recognized.
Check that the Oracle connection name used in Tableau exactly matches the TNSNames.ora Net Service Name entry. This name is case sensitive.
In some cases Windows will need to be restarted before the Oracle driver will pick up the TNS_ADMIN system variable
Contact local IT to verify that the TNSNames.ora file is current.
If the Oracle connection uses LDAP, make sure to include the SQLNet.ora file as well as the TNSNames.ora file.

Источник

Step 1 — Edit listener.ora

Step 2 — Restart Oracle services

SQL*Plus throws ORA-12541

TNSPING throws TNS-12541

Cause

Solution

Error Message:

Resolution:

Step-1: First check your listener status.

Step-2: Start the listener, if listener is not running.

Step-3: Stop the listener, if listener is running.

Step-4: Now restart the listener.

Step-5: One last and very important thing which you have to do.

Issue

Environment

Resolution

ORA-03113: end-of-file on communication channel

Step 1

Step 2

Step 3

Step 4

ORA-12154: TNS: could not resolve the connect identifier specified

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Step 8

Step 9

Step 10

Step 11

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

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

ORA-12541: TNS: no listener

ORA-00932: inconsistent data types

Additional Information

Suggestions

Step 1 — Edit `listener.ora`