Ora 12154 как исправить

• testomat.io управление авто тестами

• Python
• Реклама
• Работа
• Консультации
• Обучение

I am trying to connect to oracle 11g installed on Linux EL 5 and and getting the following error

SQL> connect sys/password@ud06 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

my listener.ora under network/admin is as follows

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ud06)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (SID_NAME=orcl))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (PROGRAM=extproc)))

MY tnsnames.ora is as follows

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

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

UD06=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ud06)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

MY lsnrctl status shows as follows:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-FEB-2010 16:23:06
Uptime                    0 days 0 hr. 12 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11g/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/11g/log/diag/tnslsnr/ud06/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

While using Oracle database, you may come across several errors due to some uncertain reasons and one of the error is the ORA-12154 error. If you are the one who has got this error then you don’t have to worry at all. I am saying so because here I am going to show you how to fix ora-12154 error with ease. Yes, you are so lucky that you have visited this webpage because after going through the solutions, you will be definitely able to resolve ora-12154 error in no time.

But, before we proceed to the solutions let us know something more about this ORA-12154 error that takes place while using Oracle database.

If you receive ora-12154 error then it means that the Oracle database client has been failed while he/she tries to connect with the listener on the desired server.

Or, in Oracle language, this error is defined as ‘a connection to the service or the database was requested using a connect identifier, as well as the connect identifier that is specified could not be resolved into a connect descriptor with the use of one of the naming methods configured.’ As for example, if the type of connect identifier used was actually a net service name then the net service name is not found in a naming method repository or the repository is actually be reached or located.

There are several reasons that are responsible for causing this oracle error and due to the unknown reasons, it has become quite tricky to fix ora-12154 error. However, here I have tried to solve this error based on some reasons that may cause this error to occur.

What Causes ORA-12154 Error To Occur?

As I have already mentioned that there are several causes due to which ora-12154 error takes place. Here are some of the major known causes:

• Missing tnsnames.ora File
• Searching for Wrong Domain
• Absent Local Naming Method
• Missing Parenthesis
• Connect Identifier Mismatch

How To Fix ORA-12154: TNS:could not resolve the connect identifier specified?

Here, you will get to know the best ways to fix ORA-12154: TNS:could not resolve the connect identifier specified error based on the error message you get. All these solutions are very easy and effective that will definitely fix ora-12154 error.

Error Message #1: ORA-12154 Due to Missing tnsnames.ora File

When you deliberately deleted tnsnames.ora file for testing the effect, you get the message:

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

File Not Found

After getting this message, when you try to connect to the database, you get the below message:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:12:14 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

You get these message because of the sqlplus found no file to lookup the connect identifier.

Solution To Fix This Error:

In order to fix this error, you can create a new tnsnames.ora or you can also restore the original one.

C:UsersEd>dir /w %TNS_ADMIN%tnsnames.ora
Volume in drive C has no label.
Volume Serial Number is C4BB-3A0E

Directory of C:appclientAdministratorproduct12.2.0client_1networkadmin

tnsnames.ora
1 File(s)            388 bytes
0 Dir(s)  179,702,697,984 bytes free

After doing so, you can again try to connect to the Oracle database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:20:41 2019

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

SQL> conn hr/hr@ORCL
Connected.

Error Message #2: ORA-12154 Due to Absent Local Naming Method

When you set TNS_ADMIN environment variable explicitly, you try this:

C:UsersEd>set TNS_ADMIN=C:appclientAdministratorproduct12.2.0client_1networkadmin

Check the content of sqlnet.ora. There’s no TNSNAMES naming method.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT)

After this, you try to connect to the database:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:02:33 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Here, you can get ora-12154 error in sqlplus because there is no TNSNAMES naming method available to support the connection.

#1: Solution To Fix This Error: Add TNSNAMES Method

You can fix ora-12154 error here by adding TNSNAMES method back to NAMES.DIRECTORY_PATH.

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Then we tried to connect to the database again.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:05:24 2019

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

SQL> conn hr/hr@ORCL
Connected.

Using this way, you will be able to fix this ora-12154 error with ease.

#2: Solution To Fix This Error: Easy Connect Naming Method

You can also try connect naming method to fix ora-12154 error and connect to the database. For this, you have to make sure that you have EZCONNECT in the list of NAMES.DIRECTORY_PATH.

If you don’t have then you will get ora-12154 like this and it will solve your problem soon:

SQL> conn hr/hr@ora11g.example.com:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Error Message #3: ORA-12154 Due to Connect Identifier Mismatch

If you changed the connect identifier while connecting the database using the method below then you can sometime get this error:

SQL> conn hr/hr@ORCL123
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution To Fix This Error

If you get this error due to connect identifier mismatch then you can try the correct connect identifier so that you can connect to the database easily. To do so, you have to try the below query:

SQL> conn hr/hr@ORCL
Connected.

It is always advisable that you should always use a matched identifier which also exists in tnsnames.ora.

Error Message #4: ORA-12154 Due to Missing Parenthesis

Sometimes it happens that you may not notice that there is one parenthesis that is missing from the connect descriptor; generally it is the right side one. You can take an entry in tnsnames.ora, as for example:

ERPAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERPAPP)
(SERVER = DEDICATED)
)

Have you found any error in the above entry? No…..but I have noticed that there is a single parenthesis missing. Even if the connect identifier is correct but its connect descriptor is not correct then it will cause ORA-12154 error in oracle.

Solution To Fix This Error:

You can try the below codes to fix ORA-12154 due to missing parenthesis:

 ERPAPP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.42.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ERPAPP)
(SERVER = DEDICATED)
)
)

Using the above codes, the connect identifier is correct.

Error Message #5: ORA-12154 Due to Searching for Wrong Domain

There are some database environments that have been set to the default domain for search which may result ora-12154 error is there is some connect descriptor are configured wrong by these queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = example.com

Then we tried to connect to the database.

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:31:50 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution To Fix This Error

If the ora-12154 error takes place due to wrong domain then you can turn off by connecting out NAMES.DEFAULT_DOMAIN by the following queries:

C:UsersEd>type %TNS_ADMIN%sqlnet.ora
…
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#NAMES.DEFAULT_DOMAIN = example.com

After this, you can again try to connect to the database by following these queries:

C:UsersEd>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:33:55 2019

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

SQL> conn hr/hr@ORCL
Connected.

You will then see that it has been connected.

Ultimate Solution: Fix ORA-12154 Error Using Oracle File Repair Tool

Apart from all these fixes, if you are unable to fix ORA-12154: TNS:could not resolve the connect identifier specified you get due to any of the reasons mentioned above then just Oracle File Repair Tool. This tool has the capability to fix any kind of error you get while using Oracle database. Several reasons are there that may cause this error but now you can easily fix it by using this tool. It has great features that allow your oracle database to overcome any kind of error and get back the database easily as it was previously saved before. All you have to do is to download and install Oracle File Repair Tool and use the step by step guide and fix the Oracle database error easily.

Steps To Fix ORA-12154 Error Using Oracle File Repair Tool

Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.