Oracle DBA Lesson 36A — Create and Verify a Local Connection Alias

A local connection alias gives a repeatable script a short client-side name. Its connection descriptor supplies the address and service. Learn to inspect that mapping, verify the resulting session, and predict what happens when only the alias is renamed.

Oracle DBA Lesson 36A — Create and Verify a Local Connection Alias

Alias and destination

A repeatable script uses a local name such as LABPDB_LAB. The selected client's tnsnames.ora maps that name to a connection descriptor. This authored example continues Lesson 35's supplied host dbhost, TCP port 1521, registered service labpdb.example.com, and intended container LABPDB. Service and container names are independent.

LABPDB_LAB =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = labpdb.example.com))
  )

The private sqlnet.ora profile permits TNSNAMES. Each intended client must select and verify its configuration. A central copy must be distributed to, or made accessible by, each intended client. Lesson 34 explains effective client-directory selection. This part applies it to one alias. See local naming parameters, configuring naming methods, and sqlnet.ora parameters.

Verify the mapping and the session

Use tnsping and SQL*Plus from the same Oracle 19c client installation:

tnsping LABPDB_LAB
sqlplus -L COURSE_READER@LABPDB_LAB

Compare the resolved host, port, and SERVICE_NAME with the supplied facts. The TNSNAMES adapter identifies local naming. An OK response establishes listener reachability. SQL*Plus prompts for the password. -L suppresses repeated login prompts after the initial failure. See testing connections.

After a successful login, run these in SQL*Plus:

SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV','SERVICE_NAME')
FROM dual;

Expected authored results: container LABPDB and service labpdb.example.com. SHOW CON_NAME is a SQL*Plus command, while SELECT reads own-session database context. Compare both with the intended destination before other work. No privileged inventory query or management pack is needed. See SYS_CONTEXT.

End the exercise session with:

EXIT ROLLBACK

See SQL*Plus EXIT.

Rename and predict

Change only LABPDB_LAB on the left of the private entry to LABPDB_ALT. Keep the entire descriptor unchanged. Repeat both resolution and login/session checks using LABPDB_ALT. The success criterion is the same intended service and container. Two different local aliases can also map to the same descriptor and PDB service.

Multiple protocol addresses can support connect-time failover according to the descriptor's policy. That occurs during connection establishment. Replaying an interrupted transaction requires separately supported and configured recovery. This lesson enables no replay feature and gives no advanced failover configuration. See connect-time failover and Application Continuity.

Independent practice and cleanup

Use the instructor-designated disposable lab and the actual supplied endpoint and service names. Confirm the reachable listener, open LABPDB, an ordinary COURSE_READER account with CREATE SESSION, the installed Oracle 19c client and release update, and the availability of both utilities. Use your actual registered name. No server or shared-client configuration changes are required.

Start a dedicated child shell. The parent's original TNS_ADMIN state, including unset, set, and export state, remains intact:

bash --noprofile --norc

Inside that child shell, create a fresh private directory and only these two exercise files. First replace dbhost and labpdb.example.com below with instructor-supplied facts if needed:

lesson36_config=$(mktemp -d /tmp/dba-lesson36.XXXXXX)
export TNS_ADMIN="$lesson36_config"
cat > "$lesson36_config/tnsnames.ora" <<'ALIAS'
LABPDB_LAB =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = labpdb.example.com))
  )
ALIAS
cat > "$lesson36_config/sqlnet.ora" <<'PROFILE'
NAMES.DIRECTORY_PATH=(TNSNAMES)
PROFILE
tnsping LABPDB_LAB
sqlplus -L COURSE_READER@LABPDB_LAB

Run the two SQL*Plus session checks above and EXIT ROLLBACK. Edit only the alias in the private tnsnames.ora, then test:

tnsping LABPDB_ALT
sqlplus -L COURSE_READER@LABPDB_ALT

Repeat both session checks and EXIT ROLLBACK. Retain sanitized evidence: the actual client version and location, the selected private directory, both resolved descriptors, and both container and service results. Record whether the prediction held. Omit passwords and private production data. If a result differs, check the actual client's selected files and endpoint facts before continuing.

After retaining evidence and ending both sessions, remove exactly the two files created above and their private directory. Run this inside the same child shell:

rm -- "$lesson36_config/tnsnames.ora" "$lesson36_config/sqlnet.ora"
rmdir -- "$lesson36_config"
exit

Returning to the parent restores its original environment without rewriting its TNS_ADMIN value. If any step fails, preserve the private directory for diagnosis, identify the failed step, and clean up only its exercise files after retaining evidence.

Recap

Define the alias with the intended address and service, inspect resolution, and verify the logged-in destination. Renaming only the alias preserves its descriptor. Select and verify the configuration on each intended client.

Quiz

1. Can two different local aliases refer to the same PDB service?

No. Each alias must create a new PDB Yes. Both can map to the same connection descriptor Only after changing ORACLE_SID Only if their names are identical

2. Only LABPDB_LAB is renamed to LABPDB_ALT, and the descriptor stays unchanged. What is expected?

A different host automatically A new service registration A new database identity The same requested service and session destination

3. What does an OK response from tnsping LABPDB_LAB establish?

Listener reachability for the resolved target A successful authenticated PDB session Transaction replay support The existence of COURSE_READER in every container

4. What does distributing an alias require?

Editing only the central copy Creating a listener.ora for every user Selecting and verifying the intended configuration on each actual client Renaming the PDB to match the alias

5. Multiple addresses support connect-time alternatives. What about replaying interrupted transactions?

It is guaranteed by adding any second address It needs separately supported and configured recovery It is proved by TNSPING It always changes the alias name

Names, paths, marker values, and expected results are teaching examples. Perform the practice in the designated Oracle Database 19c lab, confirm the exact release update level, and record actual observations against the stated success criteria.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro