A new Oracle connection must reach a listener endpoint, request a service that the listener can route, and authenticate to the intended database container. Each observation answers a different diagnostic question. Use the client's exact error to decide what evidence to collect next.
Oracle DBA Lesson 39A — Diagnose a New Connection with Listener Evidence
For an Easy Connect request such as //lab-db.example:1521/LABPDB, lab-db.example:1521 identifies the listener endpoint and LABPDB identifies the requested database service. A service name may include a domain and may differ from its PDB's name. Use the approved service name in full.
Locate the listener's evidence
On the listener host, use the home and OS identity that own the selected listener:
lsnrctl status LISTENERLISTENER explicitly selects the listener in this example. A different listener requires its own name and corresponding configuration. The report provides basic status, endpoint addresses, configuration and log locations, and a service summary.
| Report field | How to use it |
|---|---|
| Alias and version | Confirm which listener answered and its software version. |
| Listening Endpoints Summary | Match the protocol, resolved host/address and port to the client request. |
| Listener Parameter File | Locate the configuration that this listener reports using. |
| Listener Log File | Locate this listener's diagnostic log and correlate the failed attempt's time. |
| Services Summary | Obtain an initial view of the services and instances known to the listener. |
Follow the reported file locations. Under ADR, the listener log location may end in diag/tnslsnr/<host>/<listener>/alert/log.xml. Related text diagnostics can be in that ADR home's trace directory. ADR settings and the platform affect the layout. Avoid guessing network/log, and do not assume that the database home owns this listener. See the Listener Control Utility for these fields, including STATUS and SERVICES.
A server-side status check establishes what answered that administration request. Confirm client reachability from the affected client's own network path.
Inspect services and handlers
lsnrctl services LISTENERThis adds detail about services, instances, and service handlers. Consider an authored excerpt:
Service "LABPDB" has 1 instance(s).
Instance "LABCDB", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVERLABPDB is the requested service. LABCDB is the instance associated with it. Instance READY indicates it can accept connections. The dedicated handler's state:ready indicates that handler can accept new connections. established and refused are handler counters. The displayed zeros are example values. Use actual counters and actual readiness from your report.
Instance BLOCKED means it cannot accept connections. UNKNOWN indicates a static entry whose instance status is unknown to the listener. A static UNKNOWN entry alone does not establish that the database is down. Keep instance status and handler state distinct. See Oracle 19c listener administration and handler states.
Choose the next check from the error
| Client observation | What it tells you | Next discriminating check |
|---|---|---|
ORA-12541 | The requested address has no listener available to this attempt. | Compare the client's destination with the running listener's address and port. |
| Refusal or timeout while a server-side listener check succeeds | The affected client path needs investigation. The exact cause remains to be established. | Verify host resolution, routing, and firewall behavior from that client, and correlate timestamped logs. |
ORA-12514 | A listener received the request and lacks the requested service entry. | Compare the full requested service name with SERVICES from that same listener. |
ORA-01017 | The connection reached database login processing and authentication failed. | Verify the intended service/container and the correct account credentials there. |
For ORA-12541, a listener running at another port does not satisfy the destination requested by the client. For ORA-12514, verify both spelling and any domain suffix. Registration can briefly lag after startup. A missing service also warrants checking its availability and registration with the responsible administrator. Diagnose the evidence before choosing a configuration change. See ORA-12541 guidance, ORA-12514 guidance, and the 19c troubleshooting guide.
A local account belongs to its PDB. Credentials for an account in one container may fail when a service routes to another container. An authentication error provides a later stopping point than an unknown-service error. Check the exact login error. Locked accounts, expired passwords, and missing CREATE SESSION have their own diagnoses. ORA-01017 specifically concerns invalid username/password in the 19c message. See Oracle's ORA-01017 explanation.
Confirm a fresh login and its destination
From the approved client, substitute the designated lab endpoint:
sqlplus -L course_reader@//lab-db.example:1521/LABPDBSQL*Plus prompts for the password. -L prevents another username/password prompt after an unsuccessful initial connection. It does not set a network timeout or guarantee authentication. See Starting SQL*Plus.
After a successful connection:
SHOW USER
SHOW CON_NAMEThe intended observation is COURSE_READER in LABPDB. SHOW commands are SQL*Plus commands, and these two checks identify your own session's account and container. If either is wrong, stop and resolve the destination before continuing work. This fresh login establishes access for the tested account and path at that time. Application operations may require additional object privileges or application-specific validation. See the SQL*Plus SHOW reference.
Practice and conditions
Use a designated disposable Oracle Database/Net 19c single-instance multitenant lab on Linux. Record its exact release update, edition, listener OS owner, home, and target name. The listener may belong to a Grid installation user. Use the designated owner and home. Have the instructor provide an open LABPDB, its correct service name, an approved endpoint, and a COURSE_READER account with CREATE SESSION. These own-session checks require no database-wide administrative privilege. Do not connect to a production system for this exercise.
Without changing configuration, capture the selected listener's endpoint, reported parameter and log paths, full service name, and instance and handler state. Test a fresh login from the designated client and capture SHOW USER and SHOW CON_NAME. Pass when the destination matches the endpoint evidence and the connected account is COURSE_READER in LABPDB. If a check fails, record the exact error, timestamp, observation, and next discriminating check. Obtain actual evidence rather than filling gaps with expected output.
Disconnect with EXIT afterward. This exercise creates no database object or configuration change to reverse. Keep credentials out of evidence records and inspect only the designated listener's diagnostic files.
Quiz
1. Which part of //lab-db.example:1521/LABPDB identifies the listener endpoint?
2. Where should you start locating the configuration and log for the selected listener?
3. A client receives ORA-12514. What is the next useful check?
4. Does a successful listener status prove that an application account can log in?
5. A fresh login succeeds. Which checks establish the account and current container?
The excerpt and intended results above are teaching examples, not captured execution. No database connection or listener operation was performed while preparing this lesson. Actual results depend on the lab configuration and account state.
No comments:
Post a Comment