No Image

Ora 12514 tns listener

СОДЕРЖАНИЕ
390 просмотров
10 марта 2020

Expert Oracle Database Tips by Donald Burleson August 27, 2015

Question: I am attempting to enter SQL*Plus and I am getting this error:

oracle> sqlplus system/manager@mysid

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

How do I prevent this form of the ORA-12514 error? Also, I often get a TNS-12514 along with the ORA-12514. Are these the same error? If not, how do I avoid the TNS-12514?

Answer: First, the ORA error is a permutation of the ORA-12514: TNS listener cannot resolve service name error as shown below. This form of the ORA-12541 error commonly happens when the database or the listener processes are in the middle of a startup, or when the database (mysid in your case) has not been registered with the listener.

root> oerr ora 12514

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

Cause:

The listener received a request to establish a connection to a database or other service.

The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener.

This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

Wait a moment and try to connect a second time.

Check which services are currently known by the listener by executing: lsnrctl services

Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

Check for an event in the listener.log file.

To register the database with the listener

You have two options to register your database with the listener:

  • If you have the multiple listeners and have specified the database name in your pfile/spfile with local_listener=mysid then you can dynamically register the database with the listener.
  • The TNS-12514 is very similar to the ORA-12514 for a reason. Sometimes Oracle does not get far enough along in the process for an Oracle error, so a similar network error will appear. Resolving one should take care of the other.

    Read on for specific information on dealing with the TNS-12514 on Windows.

    ORA-12514: TNS listener cannot resolve service name error

    Question: I have an issue causing a ORA-12514: TNS: listener could not resolve SERVICE_NAME error:

    SQL> shutdown immediate

    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> startup mount

    ORACLE instance started.
    ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

    I’m using Oracle 10g on Windows 2000.

    What’s cause of this error and how to resolve it?

    Answer by Edward Stoever:

    I avoid using a listener connection when I startup or shutdown, as a matter of practice. I realize that cannot always be done. Are you physically on the machine? Can you ssh or telnet in and run sqlplus on the box? Then connect like so:

    export ORACLE_S >sqlplus sys as sysdba
    [no password needed – just hit return ]
    SQL> startup mount
    .

    Читайте также:  Драйвера на ноутбук prestigio

    Do not connect with the @mydb to avoid the listener.

    Also, Windows systems are subject to a variety of other issues from screen savers that hog the CPU to the Catch-22 of either anti-virus protection or a virus interfering with connections. These conditions and a few others can cause TNS-12514 to appear.

    Also note the similarities in numbering and content for ORA-12154, which deals with TNS inability to resolve the service name.

    Burleson is the American Team

    Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .

    Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

    Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

    and include the URL for the page.


    В общем в одной подсети инстансы oracle с одинаковыми service name и портами жить могут.

    в listener.ora добавляем информацию о новом подключении по IP и меняем порт

    В tnsnames.ora заносим информацию о новых экземплярах

    После перезапускаем службы листенера и самой БД.

    Проверяем командой
    lsnrctl status

    The command completed successfully

    обязательно должна быть информация о

    lsnrctl reload – переинициализировать листенер иногда помагает, когда инстанс не инициализовался листенером

    We have an application running locally where we’re experiencing the following error:

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

    I’ve tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try connecting, which failed with the same error as above. I used this syntax for SQLPlus :

    We have verified that:

    • the TNS Listener on the server is running.
    • Oracle itself on the server is running.

    We don’t know of any changes that were made to this environment. Anything else we can test?

    21 Answers 21

    I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

    Once I updated tnsnames.ora to:

    Success! The listener is basically telling you that whatever service_name you are using isn’t a valid service according to the DB.

    (*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs 😉 *)

    I know this is an old question, but still unanswered. It took me a day of research, but I found the simplest solution, at least in my case (Oracle 11.2 on Windows 2008 R2) and wanted to share.

    Читайте также:  Minecraft как перенести карту

    The error, if looked at directly, indicates that the listener does not recognize the service name. But where does it keep service names? In %ORACLE_HOME%NETWORKADMINlistener.ora

    The “SID_LIST” is just that, a list of SIDs and service names paired up in a format you can copy or lookup.

    I added the problem Service Name, then in Windows “Services” control panel, I did a “Restart” on the Oracle listener service. Now all is well.

    For example, your listener.ora file might initially look like:

    . And to make it recognize a service name of orcl , you might change it to:

    I had this issue at Windows server 2008 R2 and Oracle 11g

    go to Net Manager > Listener > select database services form the combox > “Global Database Name” must be same as “SID” and “Oracle Home Directory” must be correct.

    If you don’t have any entry for database services, create one and set correct global database , sid and oracle home.

    In my circumstances the error was due to the fact the listener did not have the db’s service registered. I solved this by registering the services. Example:

    My descriptor in tnsnames.ora :

    So, I proceed to register the service in the listener.ora manually:

    Finally, restart the listener by command:

    Starting the OracleServiceXXX from the services.msc worked for me in Windows.

    This really should be a comment to Brad Rippe’s answer, but alas, not enough rep. That answer got me 90% of the way there. In my case, the installation and configuration of the databases put entries in the tnsnames.ora file for the databases I was running. First, I was able to connect to the database by setting the environment variables (Windows):

    and then connecting using

    Next, running the command from Brad Rippe’s answer:

    showed that the names didn’t match exactly. The entries as created using Oracle’s Database Configuration Assistant where originally:

    The service name from the query was just mydatabase rather than mydatabase.mydomain.com . I edited the tnsnames.ora file to just the base name without the domain portion so they looked like this:

    I restarted the TNS Listener service (I often use lsnrctl stop and lsnrctl start from an administrator command window [or Windows Powershell] instead of the Services control panel, but both work.) After that, I was able to connect.

    what worked for me was really simple, I just needed to initiate the service manually in the “Windows Services” (services.msc in cmd trompt). my service name is: OracleServiceXXXXX.

    Check to see the database is up. Log onto the server, set the ORACLE_SID environment variable to your database SID, and run SQL*Plus as a local connection.

    This error can occur when an application makes a new connection for every database interaction or the connections are not closed properly. One of the free tools to monitor and confirm this is Oracle Sql developer (although this is not the only tool you can use to monitor DB sessions).

    you can download the tool from oracle site Sql Developer

    here is a screenshot of how to monitor you sessions. (if you see many sessions piling up for your application user during when you see the ORA-12514 error then it’s a good indication that you may have connection pool problem).

    Читайте также:  Варочная панель на краю столешницы

    i had also faced same problem and spent 3 days to dig it out. This happens because of your wrong TNS service entry. First check whether you are able to connect to standby database from primary database using sql > sqlplus sys@orastand as sysdba ( orastand is standby database) , if you are not able to connect then it is problem with service. Correct the entry of service name in TNS file at primary end. Check same way in standby database , if requires make the changes here too. and Make sure the log_archive_dest_2 parmater has correct service name.

    I resolved this issue in my linux enviroment updating the IP of my machine in /etc/hosts file.

    You can verify your network IP (inet end.) with:

    See if your IP matches with /etc/hosts file:

    Edit your /etc/hosts file, if nedded:

    I had the same problem, for me just writing

    did the trick, doing so makes it connect to the default service name i guess.

    For those that may be running Oracle in a VM (like me) I saw this issue because my VM was running out of memory, which seems to have prevented OracleDB from starting up/running correctly. Increasing my VM memory and restarting fixed the issue.

    Lots of answers here, but here comes a working example with code that you can copy and paste and test immediately:

    For me the error 12514 was solved after specifying the correct SERVICE_NAME. You find that on the server in the file tnsnames.ora which comes with 3 predefined service names (one of them is “XE”).

    1. I installed the Oracle Express database OracleXE112 which already comes with some preinstalled demo tables.
    2. When you start the installer you are asked for a password. I entered “xxx” as password. (not used in production)
    3. My server runs on the machine 192.168.1.158
    4. On the server you must explicitely allow access for the process TNSLSNR.exe in the Windows Firewall. This process listens on port 1521.
    5. OPTION A: For C# (.NET2 or .NET4) you can download ODAC11, from which you have to add Oracle.DataAccess.dll to your project. Additionally this DLL depends on: OraOps11w.dll, oci.dll, oraociei11.dll (130MB!), msvcr80.dll. These DLLs must be in the same directory as the EXE or you must specify the DLL path in: HKEY_LOCAL_MACHINESOFTWAREOracleODP.NET4.112.4.0DllPath . On 64 bit machines write additionally to HKLMSOFTWAREWow6432NodeOracle.
    6. OPTION B: If you have downloaded ODAC12 you need Oracle.DataAccess.dll, OraOps12w.dll, oci.dll, oraociei12.dll (160MB!), oraons.dll, msvcr100.dll. The Registry path is HKEY_LOCAL_MACHINESOFTWAREOracleODP.NET4.121.2.0DllPath
    7. OPTION C: If you don’t want huge DLL’s of more than 100 MB you should download ODP.NET_Managed12.x.x.x.xxxxx.zip in which you find Oracle.ManagedDataAccess.dll which is only 4 MB and is a pure managed DLL which works in 32 bit and 64 bit processes as well and depends on no other DLL and does not require any registry entries.
    8. The following C# code works for me without any configuration on the server side (just the default installation):

    If the SERVICE_NAME=XE is wrong you get error 12514. The SERVICE_NAME is optional. You can also leave it away.

    “>

    Комментировать
    390 просмотров
    Комментариев нет, будьте первым кто его оставит

    Это интересно
    © 2019 | All rights reserved.
    Adblock
    detector