Udi 12154 operation generated oracle error 12154

Problem background

When the new environment IMPDP is newly created, the problem is discovered, and this fault is reported after the SID has been put on.

[[email protected] ~]$ impdp dcps/[email protected] directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:26:56 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

UDI-12154: operation generated ORACLE error 12154
ORA-12154: TNS:could not resolve the connect identifier specified

UDI-12154: Operation creation Oracle error 12154
ORA-12154: TNS: Unable to resolve the specified connection identifier

problem solved

reason

TNS error, the check found that the database connection identifier in TNSNames.ora has no changes after changing the database, or ORCL causing the error.

[[email protected] u01]$ vi ./database/network/admin/tnsnames.ora

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Solution

Method 1: Modify the TNS identifier as SID

LISTENER_UPBS =
  (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))


UPBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = POC-ORACLE)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = upbs)
    )
  )

Import directly after modification

[[email protected] ~]$ impdp dcps/[email protected] directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:42:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DCPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DCPS"."SYS_IMPORT_FULL_01":  dcps/********@upbs directory=dump_dir dumpfile=dcps20210630.dmp 
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Method 2: Remove the SID when importing

[[email protected] ~]$ impdp dcps/dcps directory=dump_dir dumpfile=dcps20210630.dmp

Import: Release 19.0.0.0.0 - Production on Mon Jul 5 17:29:16 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "DCPS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DCPS"."SYS_IMPORT_FULL_01":  dcps/******** directory=dump_dir dumpfile=dcps20210630.dmp 

Although TNS is definitely changed

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:oraclexeapporacleproduct11.2.0server

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

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:oraclexeapporacleproduct11.2.0serverbin

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 HOMEnetworkadmin

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!

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators. When this occurs, there’s an issue with creating a connection with one of your Oracle services or database instances. In some Oracle database versions, this error may be called “ORA-12154: TNS:could not resolve service name.” The connect identifier is not able to resolve and may be caused by one or more of the following issues:

• Inability to connect to the repository due to unplanned server and network outages
• The entry is missing from tnsnames.ora
• The entry in tnsnames.ora is malformed
• The program is using tnsnames.ora from the wrong ORACLE_HOME
• The program is not using a fully qualified service name, but no default domain is enabled in sqlnet.ora

Because there is more than one cause of the ORA-12154 error, you need to troubleshoot precisely what’s going on with your database connections. You’ll typically see this error in the Oracle client application during the connection process, not the server itself. While it can be frustrating to see this error when you’re working on an application, the fix is relatively straightforward.

Resolving ORA-12154 Error Codes

The Oracle client code uses one of three ways to look up connect data:

• A flat file named tnsnames.ora
• Oracle Names service
• LDAP

When the complete ORA-12154 error appears with the text line, your program has found a working Oracle client install. However, the specified Oracle service is not listed in tnsnames.ora, Oracle Names or LDAP.

The first step in the troubleshooting process is to determine which name resolution method is deployed at your site. Most sites use tnsnames.ora, but enough use Oracle Names and LDAP, so it’s best to confirm this information.

If you are not the database administrator, get in touch with the people managing your Oracle systems and find out which method you should be using. They may be able to guide you in fixing the problem in accordance with your site’s standards.

The client code decides which mechanism to use based on the file sqlnet.ora. This file and tnsnames can usually both be found in the Oracle install directory (“ORACLE_HOME”), under network/admin/. This location may be overridden with the environment variable TNS_ADMIN.

If the sqlnet.ora file does not exist or does not specify a resolution method, then Oracle Net uses tnsnames.ora.

Example locations of Oracle networking files include:

Windows

• ORANTNET80ADMIN
• ORACLEORA81NETWORKADMIN
• ORAWIN95NETWORKADMIN
• ORAWINNETWORKADMIN

UNIX / Linux

• $ORACLE_HOME/network/admin/
• /etc/
• /var/opt/oracle/

If you fix the naming issues, but you still see the ORA-12154 error, check the Oracle service to confirm that it’s available for connections. A power outage, server failure, or network connectivity issue will make this resource inaccessible. It’s also possible that scheduled maintenance or repairs of an unrelated Oracle issue may take that resource temporarily offline.

Get Expert Help with Resolving Your ORA-12154 Errors

Datavail’s Oracle experts have an average of 15 years of experience and are well-versed in resolving common connection problems with this database technology. We offer Oracle services tailored to your needs, whether you need occasional assistance with troubleshooting or end-to-end solutions for your business.

Don’t let Oracle errors get in the way of creating high-availability, stable applications that your organization depends on. Get the most out of your technology investments by contacting us today.

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

hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

could anyone help me on this?

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

Answers

here is my tns.ora

GISDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = gisdb)
)
)

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

here is my listener .ora

# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:oracleproduct10.2.0db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = alias-PC)(PORT = 1521))
)
)

here is my sqlnet.ora

# sqlnet.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminsqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install «Software Only», this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Hi,
I have some guidelines for you,can you please go through the details below:

Error: ORA-12154 / TNS-12154
Text: TNS:could not resolve service name
——————————————————————————-
Cause: The service name specified is not defined correctly in the
TNSNAMES.ORA file.
Action: Make the following checks and correct the error:
— Verify that a TNSNAMES.ORA file exists and is in the proper
place and accessible. See the operating system specific manual
for details on the required name and location.
— Check to see that the service name exists in one of the
TNSNAMES.ORA files and add it if necessary.
— Make sure there are no syntax errors anywhere in the file.
Particularly look for unmatched parentheses or stray characters.
Any error in a TNSNAMES.ORA file makes it unusable. See
Chapter 4 in the SQL*Net V2 Administrator’s Guide. If
possible, regenerate the configuration files using the Oracle
Network Manager.

*** Important: The notes below are for experienced users — See Note:22080.1

Explanation:
The SQL*Net layer cannot find a definition for the alias supplied
(as in «sqlplus scott/[email protected]»).

Diagnosis:
1) Check the tnsnames.ora file you are using, verify that it is accessible.
Eg: On Unix:
— Check if you have a $HOME/.tnsnames.ora file — This will be
used in addition to ‘tnsnames.ora’.
— Check TNS_ADMIN is set in your environment.
— There is a readable tnsnames.ora file in $TNS_ADMIN

2) Ensure that the tnsnames.ora file contains a line of the form
‘alias=(. )’ for the alias you are specifying.
Aliases are NOT case sensitive.

3) Make sure that there are no mismatched parentheses in the
tnsnames.ora file.

4) Even if TNS_ADMIN is set SQL*Net looks in other locations for
configuration files. Check the default directories for old (or bad)
copies of TNS_NAMES.ORA. Eg: /etc, /var/opt/oracle,
$ORACLE_HOME/network/admin

5) Check the default domain name being used, and the path used to
locate aliases, in the SQLNET.ORA file.
The default domain is specified in the NAMES.DEFAULT_DOMAIN
parameter — this is appended to the alias specified in the
connect string if there is no domain given.
Eg: If NAMES.DEFAULT_DOMAIN=mydom.uk
and a connect to «scott/[email protected]» is requested
SQL*Net will look for the alias «mydb.mydom.uk»
If NAMES.DIRECTORY_PATH is also specified this determines where
SQL*Net looks for the alias expansion.

6) If none of these show an error enable client side tracing
at level 16 and see what has been written to the client trace
file. There list of aliases in the trace file under the heading
‘TNS.NAMES.ORA TABLE HAS THE FOLLOWING CONTENTS’.

7) If ORA-12154 is returned when selecting over a database link from a
client check that the alias in the link can be resolved in the
tnsnames.ora file ON THE SERVER.

If you are connecting from a login dialog box, verify that you are
not placing an «@» symbol before your connect net service name.

9) When going from Windows to Linux/Unix platforms,
you can see characters at the
end of each line instead of . Be sure you
use ascii mode when ftp’ing this between Windows and Linux/Unix

please can you post the output from tnsping GISDB executed on the host from where you are trying to connect to the DB?

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 — Production on 29-APR-2
010 17:02:17

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
C:oracleproduct10.2.0db_1networkADMINsqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = alias-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gisdb)))
OK (20 msec)

the output of tnsping shows that you are able to connect to the listener listening on alias-PC on port 1521 and the listener knows of service gisdb.

Does an sqlplus /

@gisdb work on the host were you executed the tnsping command?

What does an lsnrctl status executed on alias-PC telling you?

pimpom wrote:
hi i got this error when i try to connect through database client and through interoperability function from othe software to access the database.

could anyone help me on this?

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

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

This error means one thing, and one thing only. The client could not find the specified entry in the tnsnames.ora file being used.

As a follow-on to that statement, remember that when you use a dblink, the database in which the link is defined is acting as a client to the database that is the target of the link. So in this case, the tnsnames.ora file on the host of your source should have an entry for your target db, as defined in the db_link.

And for the umpteenth time . this error has nothing to do with the status of a listener. The connection request never got far enough to reach a listener. If anyone tells you to check a listener, they are not paying attention, or do not understand how TNS works. This error is the equivelent of not being able to place a telephone call because you don’t know the number of the party you want to reach. You wouldn’t debug that situation by going to the other guy’s house and testing his telephone, or by going to the phone company and testing the switchboard. And you don’t debug a ORA-12154 by checking the listener. If I had a top ten list of «Incredibly Simple Concepts ™» that should be burned into the brain of everyone who claims to be an Oracle DBA, it would include «ORA-12154 Has Nothing To Do With The Listener».

Assume you have the following in your tnsnames.ora:
Now, when you issue a connect, say like this:
tns will look in your tnsnames.ora for an entry called ‘larry’. Next, tns sends a request to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

Next, the request arrives at port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, you’ll be connected.

A couple of important points.

First, the listener is a server side only process. It’s entire purpose in life is the receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn’t sustain the connection. One listener, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners. That would be like the telephone company building a separate switchboard for each customer.

Second, the tnsnames.ora file is a client side issue. It’s purpose is for addressess resolution — the tns equivelent of the ‘hosts’ file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.

What can go wrong?

First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get «ORA-12154: TNS:could not resolve the connect identifier specified» No need to go looking for a problem on the host, with the listener, etc. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory (tnsnames.ora) or can’t find the party you are looking for listed in it (no entry for larry)) you don’t look for problems at the telephone switchboard.

Maybe the entry for larry was found, but myhost couldn’t be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in «ORA-12545: Connect failed because target host or object does not exist»

Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in «ORA-12545: Connect failed because target host or object does not exist»

Maybe the IP was good, but there is no listener running: «ORA-12541: TNS:no listener»

Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. «ORA-12560: TNS:protocol adapter error»

Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME = curley. «ORA-12514: TNS:listener does not currently know of service requested in connect descriptor»

Источник

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

Windows Oracle Database 18c Express Edition

imp ‘system/[email protected]’ file=C:SHARECPSDBBACKUP_08152019.DMP full=yes log=C:SHAREimplog.txt

Import: Release 18.0.0.0.0 — Production on Wed Aug 21 14:55:32 2019

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

IMP-00058: ORACLE error 12154 encountered

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

IMP-00000: Import terminated unsuccessfully

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 — Production on 21-AUG-2019 14:58:26

Copyright (c) 1997, 2018, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.155.197.46)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

How can I fix ORA-12154: TNS:could not resolve the connect identifier specified?

Best Answer

Please READ all the answers .

What your vendor told you is an example from 20 years ago, when «manager» was the default password for «system» user >

The syntax for imp is

If you need to use SYSTEM user, that’s perfect, just use

Replace XXXXXX with SYSTEM user password on the target database, and ZZZZZZ with the connection name (it should be XE since you tried to connec to XE on your tnsping example)

Answers

Since the service name seems to be XE (you tested tnsping using that name!)

PD: also check that system/manager are the actual user/password you want to use.

And try to use expdp/impdp instead of imp/exp, they have many new options and are quicker and more efficient.

IMP-00058: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon deniedUsername:

I have to run imp as system/manager

impdp system/[email protected] DIRECTORY=C:SHARE DUMPFILE=CPSDBBACKUP_08152019.DMP

Import: Release 18.0.0.0.0 — Production on Wed Aug 21 15:29:49 2019

UDI-12154: operation generated ORACLE error 12154

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

No, you don’t have to run imp as system/manager, you have to run it as some valid username/password combination. If you got this error by supplying ‘system/manager’ on the command line you failed to show us, then that is simple and conclusive proof that ‘manager’ is not the password of user SYSTEM on whatever database you tried to connect to. Oracle is too dumb to lie about that particular error message.

But then, since you didn’t show the command line that produced this new error, we can only guess at what you used there.

manager used to be the default password for system user 20 years ago! It is highly unlikely that a 18c database has that password for the system user.

Pease take a look at my post, I exaplined how to fix the service name error and that you need to provide a valid user / password:

Since you receive ORA-01017, that means that imp was able to contact the database (XE) but the password (manager) provided is wrong.

Your initial error said it could not even try to contact the database since xyz was not found as a network service name.

It seems that you have just copied an example of imp and did not manage to understand what parts are actually keywrds syntax and what parts need to be replaced with your actual values (system, manager and xyz)

Источник