The tnsnames.ora file has an entry for the service alias ORCL as follows:
The TNS ping command executes successfully when tested with ORCL; however, from the same
OS user session, you are not able to connect to the database instance with the following
command:
SQL > CONNECT scott/tiger@orcl
What could be the reason for this?
A.
The listener is not running on the database node.
B.
The TNS_ADMIN environment variable is set to the wrong value.
C.
The orcl.oracle.com database service is not registered with the listener.
D.
The DEFAULT_DOMAIN parameter is set to the wrong value in the sqlnet.ora file.
E.
The listener is running on a different port.
Explanation:
Service registration enables the listener to determine whether a database service
and its service handlers are available. A service handler is a dedicated server process or
dispatcher that acts as a connection point to a database. During registration, the LREG process
provides the listener with the instance name, database service names, and the type and
addresses of service handlers. This information enables the listener to start a service handler
when a client request arrives.
Answer: C
Explanation: Service registration enables the listener to determine whether a database service and its service handlers are available. A service handler is a dedicated server process or dispatcher that acts as a connection point to a database. During registration, the LREG process provides the listener with the instance name, database service names, and the type and addresses of service handlers. This information enables the listener to start a service handler when a client request arrives.
C
C.
The orcl.oracle.com database service is not registered with the listener.
A) within port 1521 is an automatic listerner registration
If C were correct, wouldn’t TNSPing fail? TNSPing will return OK even if the listener is down, but when you try to connect via tnsnames.ora file if the listener is down, you’ll get an error.
So it must be A.
C
If A were correct, tnsping would also fail.
not true, tnsping will work even if the listener is down (tnsping doesn’t verify you can connect only that you’ve specified the right info in the connect string)
C
C
Dear Relo, if listener is down, tnsping will fail. tnsping will succeed even if database is down and not when listener is down.
Thanks, you just verified my point that the correct answer is A. It cannot be C because of automatic registration. It can be A if the listener id down.
C
D. DEFAULT_DOMAIN…
TNSPING ORCL, verifies both the host and database service are accessible, through the listener. The listener is already up, othewise TNSPING ORCL would return an error saying something there is not listener
TNS
====
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
SERVICE
=======
SQL> show parameter service
NAME TYPE VALUE
———————————— ———– ——————————
service_names string orcl
LISTENER
=========
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/db12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db12c)(PORT=1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully
TNSPING
=======
[oracle@db12c admin]$ tnsping orcl
..
….
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db12c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.oracle.com)))
OK (30 msec)
CONNECTION FAILS:
==============
SQL> CONNECT scott/tiger@orcl
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
STOP LISTENER
================
[oracle@db12c admin]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 26-SEP-2015 15:10:03
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
TNSPING FAILED
==============
[oracle@db12c admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 12.1.0.1.0 – Production on 26-SEP-2015 15:10:05
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db12c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.oracle.com)))
TNS-12541: TNS:no listener
So the answer is C
Yes C
A: The picture shows that the listener is awake. The opposite will trigger an error: TNS-12541: TNS: no Listener
B: If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory. The tns_admin parameter determines the location of the tns administration files (e.g. tnsnames.ora and listener.ora, etc.)
D: sqlnet.ora does not contains default_domain
E: The picture shows that the listener is awake. The opposite will trigger an error: TNS-12541: TNS: no Listener, and the running in the default port.
Correct answer is D
The sqlnet.ora file enables you to:
•Specify the client domain to append to unqualified names
NAMES.DEFAULT_DOMAIN
Purpose
Use the parameter NAMES.DEFAULT_DOMAIN to set the domain from which the client most often looks up names resolution requests. When this parameter is set, the default domain name is automatically appended to any unqualified net service name or service name.
For example, if the default domain is set to us.acme.com, the connect string CONNECT scott/tiger@sales gets searched as sales.us.acme.com. If the connect string includes the domain extension, such as CONNECT scott/[email protected], the domain is not appended.