We are continuing with the same series of listener errors. Let’s have looked over the new error. Let’s go for the steps
[oracle@hostname]$ sqlplus sys@test_1as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 7 15:01:04 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
Condition for error
When 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-2017 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 = test_1) (UR = A) ) )
[oracle@hostname]$ sqlplus sys@test_1 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
Today’s Thought……
We can easily forgive a child who is afraid of the dark; the real tragedy of life is when men are afraid of the light. —Plato
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
