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

[oracle@hostname]$ sqlplus sys@teston as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 15:01:04 2015

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

Enter password:

ERROR:

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

 

When do you get this error?

Database is on in restricted on no mount status and you will see service handler for this instance is on BLOCKED or RESTRICTED state.

[oracle@hostname]$ lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-JUL-2015 15:20:35

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

 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date                07-JUL-2015 14:18:31

Uptime                    0 days 1 hr. 2 min. 3 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/product/11.2.0.3/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/stagedevdb2/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedevdb2.ipx.com)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "teston" has 1 instance(s).

  Instance "teston", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

 Solution:

Add (UR=A) in tnsnames.ora file.

 TESTON =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = stagedevdb2.ipx.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = teston)

      (UR = A)

    )

  )

 [oracle@hostname]$ sqlplus sys@teston as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 15:25:19 2015

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

 Enter password:

 Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Comments