A failed connection gives evidence about how far the request progressed. Use the exact error, the connection identifier, and earlier successful checks to choose the next investigation. A useful next check narrows the possibilities before any configuration or account change.
Oracle DBA Lesson 33B — Find the Next Check for a Failed Connection
Scope and assumptions
Use Oracle Database 19c, a single-instance CDB/PDB on Linux, a normal TCP listener, and the ordinary local account COURSE_READER in LABPDB, with CREATE SESSION. dbhost, port 1521, alias and service LABPDB, and listener LISTENER are teaching placeholders. A service can differ from the PDB name. The instructor provides the disposable authorized lab's exact release and update, host, port, service, account, client home, and correct listener name. This lesson performs no live operation.
No displayed results are captured lab measurements. The abbreviated TNSPING descriptor and OK (10 msec) are authored assumptions adapted from Oracle's documented example. Root/PDB state and service registration are separate evidence. Do not certify a unique cause solely from one error number.
Error to next evidence
| Error | First investigation | Useful read-only evidence |
|---|---|---|
| ORA-12154 | Connect-identifier resolution | Exact alias or connect string, client binary and home, effective naming method, and accessible entry. For EasyConnect, also inspect hostname resolution and the supplied syntax. |
| ORA-12541 | Addressed listener endpoint | Resolved or requested host and port against the authorized listener STATUS. A wrong destination and listener unavailability are both possible. |
| ORA-12514 | Requested-service recognition | Exact service against SERVICES on the addressed listener, plus target-PDB state and registration evidence, including time since startup. |
| ORA-01017 | Credential and local-user context | Username, credential entry, and service-to-container mapping. A local user belongs to its PDB. The wrong container can produce this error. |
CREATE SESSION is an ordinary login prerequisite. Missing that privilege has its own error behavior. It is not asserted as the unique cause of ORA-01017. Other errors require their own documented investigation. See naming methods.
Worked case and commands
On the designated client, resolve the provided alias:
tnsping LABPDBRead the naming adapter and the resolved descriptor. For this case it contains HOST=dbhost, PORT=1521, and SERVICE_NAME=LABPDB. If the real descriptor differs from the application target, compare like-for-like targets first. TNSPING measures listener reachability and round-trip time. Its OK result supplies no database authentication, executable session, or target-PDB usability evidence. See testing connections.
Attempt the identical endpoint and service using SQL*Plus, entering the credential at its password prompt:
sqlplus -L COURSE_READER@"//dbhost:1521/LABPDB"-L suppresses username and password reprompt after an unsuccessful initial login. The login requests the service and attempts an authenticated database session. In the case, it reports ORA-12514. Record the actual error and time. Compare the exact service and the current registration rather than immediately resetting a password. See starting SQL*Plus.
On the listener host, the authorized listener owner uses the correct Oracle or Grid home and listener name to collect only informational results:
lsnrctl status
lsnrctl services
# A nondefault listener uses its actual name:
lsnrctl status <listener_name>
lsnrctl services <listener_name>The placeholders require substitution. STATUS exposes listening endpoints. SERVICES exposes listener-known services and handlers. Compare the listener address with the client target, then compare the requested service spelling with the listed services. If the service is absent, gather the relevant registration and startup evidence. No start, stop, reload, register, password reset, or firewall change is taught. See listener control.
An authorized database administrator with the appropriate root or container dictionary visibility can provide this read-only target-state check:
SELECT name, open_mode, restricted
FROM v$pdbs
WHERE name = 'LABPDB';Interpret open mode and restriction for that target alongside the service evidence. An ordinary user needs the PDB open and the appropriate login privileges. An open-mode row alone supplies no observation of this listener's service registration. If an authorized login later succeeds, verify its service and container using the read-only session check from Lesson 33A before other work. See V$PDBS.
Independent practical
Use instructor-provided sanitized examples for all four errors, with the exact client home, identifier, time, and observed prior checks. For each case:
- Name the first investigation area and the next read-only check.
- State the result that would narrow the possibilities, and what remains unknown.
- Identify a premature change, for example a restart for an unresolved alias or a credential reset for an unrecognized service.
- Produce an evidence record: identifier, client and home, timestamp, exact error, furthest successful check, next check, and resulting interpretation.
Optionally test a deliberately nonexistent alias using an instructor-approved private client configuration. No shared Net file may be edited. Restore that private configuration afterward if it was changed, and close any test session. Listener and root-state observations are supplied by their authorized owners. No restarts, resets, firewall changes, or PDB-state changes are part of this exercise.
Pass criterion: correctly choose the next check for all four cases, compare same-target TNSPING versus SQL*Plus evidence, avoid a restart for alias resolution and a password reset for an unknown service, and keep untested conclusions pending.
Recap
Use the error to choose the next resolution, endpoint, service, or login-context check. Compare the same target throughout, and retain the observed evidence before deciding on a change.
Quiz
1. ORA-12154 is reported for a short alias. Which area should be checked first?
2. ORA-12541 is reported. What should be compared first?
3. TNSPING succeeds but SQL*Plus reports ORA-12514. What is the next useful check?
4. For ORA-01017 with a local PDB account, what belongs in the investigation?
5. What does a successful TNSPING establish by itself?
Names, paths, marker values, and expected results are teaching examples. Perform the practice in the designated Oracle Database 19c lab, confirm the exact release and update level, and record actual observations against the stated success criteria.
No comments:
Post a Comment