Tnsnames ora ошибка

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 

      1. In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.
      2. 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%\network\admin\ 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>\network\admin directory. For example, C:\Oracle_Client\network\admin.

      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%\network\admin\ 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.




          Источник

          automated-testing.info

          Загрузка…

          • testomat.io управление авто тестами
          • Python
          • Реклама
          • Работа
          • Консультации
          • Обучение
          Источник

          Have you gotten an “ORA-12154: TNS:could not resolve the connect identifier specified” error? Learn what causes it and how to resolve it in this article.

          ORA-12154 Cause

          If you attempt to access or log on to an Oracle database, you might get this error:

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

          This means that the tnsnames.ora file was not found or has an error within it.

          There are a few steps you can take to resolve this ORA-12154 error.

          Check that the tnsnames.ora file exists

          There is a tnsnames.ora on both the client and server systems. It’s located in the ORACLE_HOME/network/admin directory. I’ve written a guide to the TNSNAMES file here which has more information.

          ORACLE_HOME is where your Oracle database is installed on the server, or on your own computer if you’re using Oracle Express.

          For example, in my installed version of Oracle Express, my ORACLE_HOME is:

          C:\oraclexe\app\oracle\product\11.2.0\server

          If I open the network then admin folders, I will see a tnsnames.ora file.

          TNSNAMES Location

          If it exists in this folder, then you need to check that it has no errors (see the next step).

          If it doesn’t exist, then you can create one.

          To do this:

          1. Create a new file in this folder and call it tnsnames.ora.
          2. Open the file in a text editor and add the information in this format:

          The syntax of the tnsnames.ora file is:

          <addressname> =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
  )
(CONNECT_DATA =
  (SERVICE_NAME = <service_name>)
)
)

          The example in my Oracle Express instance is:

          XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

          So, just copy and paste this into your new tnsnames.ora file, make changes as necessary, and save it.

          Try your connection again (the one where you got the error) and see if it works.

          Check that TNSNAMES.ORA has no syntax errors

          If the file exists, open it and see that there are no syntax errors.

          Using the example above:

          XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

          Check that the brackets are all in the right place, there are no quotes in there, no missing lines or anything unexpected.

          Check that TNSNAMES.ORA has your service name in it

          To be able to connect to your database, the tnsnames.ora file needs to have your service name in it.

          Open the tnsnames.ora file and add it in there if it does not exist, using the examples above.

          Check that TNSNAMES.ORA has read permission

          Sometimes, the file can exist and be syntactically correct, but doesn’t have any permissions.

          If other users or processes cannot read the file, you’ll get the ORA-12154 error.

          So, check that the file can be read by other users by applying read permissions to it.

          Run the TNSPING Utility

          Oracle includes a tnsping utility for checking that the TNSNAMES is OK.

          You can find this by going to ORACLE_HOME/bin/tnsping.exe

          For example:

          C:\oraclexe\app\oracle\product\11.2.0\server\bin

          If you’re on Windows, you can open the Command Prompt and CD to this directory.

          Then, run tnsping xe (or your service name you want to check)

          This should show if it is OK or not.

          TNSADMIN Environment Variable is Missing

          If you’re connecting on Windows, this error can sometimes happen if the TNSADMIN environment variable is missing.

          To check this:

          1. Go to Start > Control Panel
          2. Open System
          3. Click “Advanced system settings”
          4. Click Environment Variables
          5. Add a new system variable called TNSADMIN with a value of ORACLE HOME\network\admin

          This is often not needed, but if you’ve tried everything else, and are still getting the ORA-12154 error, you can try adding the TNSADMIN environment variable.

          So, there are a few solutions to the “ORA-12154: TNS:could not resolve the connect identifier specified” error.

          Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

          Источник

          This is an old question but Oracle’s latest installers are no improvement, so I recently found myself back in this swamp, thrashing around for several days …

          My scenario was SQL Server 2016 RTM. 32-bit Oracle 12c Open Client + ODAC was eventually working fine for Visual Studio Report Designer and Integration Services designer, and also SSIS packages run through SQL Server Agent (with 32-bit option). 64-bit was working fine for Report Portal when defining and Testing an Data Source, but running the reports always gave the dreaded «ORA-12154» error.

          My final solution was to switch to an EZCONNECT connection string — this avoids the TNSNAMES mess altogether. Here’s a link to a detailed description, but it’s basically just: host:port/sid

          http://www.oracledistilled.com/oracle-database/oracle-net/using-easy-connect-ezconnect-naming-method-to-connect-to-oracle-databases/

          In case it helps anyone in the future (or I get stuck on this again), here are my Oracle install steps (the full horror):

          Install Oracle drivers: Oracle Client 12c (32-bit) plus ODAC.

          a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html ):

          i. winnt_12102_client32.zip

          ii. ODAC112040Xcopy_32bit.zip

          b. Run winnt_12102_client32\client32\setup.exe. For the Installation Type, choose Admin. For the installation location enter C:\Oracle\Oracle12. Accept other defaults.

          c. Start a Command Prompt “As Administrator” and change directory (cd) to your ODAC112040Xcopy_32bit folder.

          d. Enter the command: install.bat all C:\Oracle\Oracle12 odac

          e. Copy the tnsnames.ora file from another machine to these folders: *

          i. C:\Oracle\Oracle12\network\admin *

          ii. C:\Oracle\Oracle12\product\12.1.0\client_1\network\admin *

          Install Oracle Client 12c (x64) plus ODAC

          a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html ):

          i. winx64_12102_client.zip

          ii. ODAC121024Xcopy_x64.zip

          b. Run winx64_12102_client\client\setup.exe. For the Installation Type, choose Admin. For the installation location enter C:\Oracle\Oracle12_x64. Accept other defaults.

          c. Start a Command Prompt “As Administrator” and change directory (cd) to the C:\Software\Oracle Client\ODAC121024Xcopy_x64 folder.

          d. Enter the command: install.bat all C:\Oracle\Oracle12_x64 odac

          e. Copy the tnsnames.ora file from another machine to these folders: *

          i. C:\Oracle\Oracle12_x64\network\admin *

          ii. C:\Oracle\Oracle12_x64\product\12.1.0\client_1\network\admin *

          * If you are going with the EZCONNECT method, then these steps are not required.

          The ODAC installs are tricky and obscure — thanks to Dan English who gave me the method (detailed above) for that.

          Источник

          ORA-12154: TNS could not resolve the connect identifier specified error comes when you try to connect Oracle database using clients/tools like SQL Plus, Toad, Oracle Forms, Oracle Reports, and SQL*Net could not find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapters.

          Now, let’s reproduce the error and find out solution for ora 12154. We are going to us Oracle Forms 11G on the Oracle Web logic server for demonstration.

          Reproduce ORA-12154

          Open the Oracle form builder and connect it to the database. Create a custom form. Compile the form and then start the web logic server. Run the form.

          Internet Explorer (IE) opens and launches the form service, but you get the error as shown below,

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

          What is wrong here?

          The form builder is able to connect to the database during design time what happens when you run the form.

          Let me explains,

          ORA 12154 Solution

          Form builder uses default.env file during run time and TNS_ADMIN variable to locate the tnsnames.ora file. Below is the value of this variable.

          TNS_ADMIN – C:\Oracle\Middleware\asinst_1\config

          So copy the tnsnames.ora file to the location specified by the TNS_ADMIN variable to resolve ORA 12154 error in Oracle forms. If this does not work, just comment the TNS_ADMIN variable entry in default.env file as shown below and copy tnsnames.ora file to ORACLE_HOME path.

          ORACLE_HOME=C:\Oracle\Middleware\Oracle_FRHome1

          Then run the forms, your forms should work fine.

          Still facing the issue, it means something wrong with TNS entries.

          Verify TNSNAMES.ORA and SQLNET.ORA files

          First, verify tnsnames.ora and sqlnet.ora file exists in ORACLE_HOME and there is a proper entry of the database which you are trying to connect.

          Below is the default location for tnsnames.ora and sqlnet.ora files for Windows and Linux /Unix Operating System.

          Windows Client

          ORACLE_HOME\network\admin

          UNIX Client

          $ORACLE_HOME/NETWORK/ADMIN
          or /etc
          or /var/opt/oracle

          Sample TNS entry,

          TEST =
     (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (Host = xxx.yy.zz.56)
             (Port = 1521)
           )
       )
       (CONNECT_DATA = (SERVICE_NAME = TEST)
       )
     )

          Sample SQLNET.ora entry,

          TRACE_LEVEL_CLIENT = OFF
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)

          Summary

          I am sure solution mentioned in this article should help you to resolve this TNS 12154 error. If you still facing issue and not able to resolve, I would recommend to read below articles.

          • ORA-12154-TNS Could not Resolve the Connect identifier Specificed when Connecting from Forms Builder (Doc ID 1604843.1)
          • TNS Issues when connecting to database with Forms Builder 12.2.1.2.0
          Источник

          Понравилась статья? Поделить с друзьями:
        • To be going to найти ошибки
        • Tns 03505 failed to resolve name ошибка
        • Tmodloader terraria выдает ошибку
        • Tnf ошибка atv312
        • Tmps ошибка ауди q5