ORA-12154 — 6-Hour ETL Outage from tnsnames.ora Overwrite

-- Step 1: Check TNS_ADMIN environment variable
-- Run from the OS shell, not from SQL*Plus
-- Linux:
-- $ echo $TNS_ADMIN
-- /opt/oracle/network/admin
--
-- Windows:
-- > echo %TNS_ADMIN%
-- C:\oracle\network\admin
--
-- If empty: Oracle uses $ORACLE_HOME/network/admin

-- Step 2: Identify which tnsnames.ora Oracle is actually reading
-- tnsping prints the parameter file path in its output
-- $ tnsping DUMMY_ALIAS 2>&1
-- Used parameter files:
--   /opt/oracle/network/admin/sqlnet.ora
-- Used TNSNAMES adapter to resolve the alias
-- TNS-03505: Failed to resolve name
-- The "parameter files" line shows the active sqlnet.ora location
-- tnsnames.ora is in the same directory

-- Step 3: Verify tnsnames.ora contains the alias
-- $ grep -i "^PRODDB" $TNS_ADMIN/tnsnames.ora
-- PRODDB =
--   (DESCRIPTION =
--     (ADDRESS = (PROTOCOL = TCP)(HOST = proddb.example.com)(PORT = 1521))
--     (CONNECT_DATA = (SERVICE_NAME = PRODDB))
--   )

-- Step 4: Check sqlnet.ora for resolution order
-- $ grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
-- If EZCONNECT is not listed, direct connection strings fail

-- Step 5: Check for NAMES.DEFAULT_DOMAIN
-- $ grep -i NAMES.DEFAULT_DOMAIN $TNS_ADMIN/sqlnet.ora
-- NAMES.DEFAULT_DOMAIN = example.com
-- If set: PRODDB -> PRODDB.EXAMPLE.COM during resolution
-- Fix: add domain to tnsnames.ora entry or clear the parameter

-- Step 6: Test resolution with tnsping
-- $ tnsping PRODDB
-- OK (10 msec)       -- Alias resolved successfully
-- TNS-03505          -- Alias not found in any naming method

-- Step 7: Test actual database connectivity
-- $ sqlplus user/password@PRODDB
-- Connected.                          -- Full success
-- ORA-12154: TNS:could not resolve... -- Resolution failed
-- ORA-12541: TNS:no listener          -- Resolution OK, listener down
-- ORA-12543: TNS:destination host unreachable -- Resolution OK, network issue

-- Step 8: Check database links on the server (run inside SQL*Plus)
SELECT
  owner,
  db_link,
  username,
  host AS tns_alias,
  created
FROM dba_db_links
ORDER BY owner, db_link;
-- The HOST column shows the TNS alias each link uses
-- Verify each alias exists in the SERVER's tnsnames.ora

-- Step 9: Test a specific database link
SELECT 1 FROM dual@REMOTE_PROD;
-- Returns 1: link works end-to-end
-- ORA-12154: link's alias not found in server's tnsnames.ora

-- Diagnostic 1: Find ALL tnsnames.ora files on the machine
-- Linux:
-- $ find / -name tnsnames.ora -type f 2>/dev/null
-- /opt/oracle/19c/network/admin/tnsnames.ora
-- /opt/oracle/21c/network/admin/tnsnames.ora
-- /home/appuser/oracle/network/admin/tnsnames.ora
-- Each file may contain different aliases

-- Diagnostic 2: Identify which ORACLE_HOME the application process uses
-- Linux — check the running process:
-- $ ps aux | grep <app_process_name>
-- $ ls -la /proc/<pid>/exe
-- $ cat /proc/<pid>/environ | tr '\0' '\n' | grep -E 'ORACLE|TNS'
-- This shows the exact ORACLE_HOME and TNS_ADMIN for the running process

-- Diagnostic 3: Verify TNS_ADMIN for the application's runtime context
-- For systemd services, check the unit file:
-- $ systemctl show <service_name> | grep Environment
-- For Docker containers:
-- $ docker exec <container> env | grep -E 'ORACLE|TNS'
-- For cron jobs, environment variables may not be set at all

-- Diagnostic 4: Test with tnsping using the SAME environment as the app
-- Set TNS_ADMIN to match the application, then test:
-- $ export TNS_ADMIN=/opt/oracle/19c/network/admin
-- $ tnsping PRODDB
-- OK (10 msec)  -- Alias found in that file
-- TNS-03505     -- Alias NOT found in that file

-- Diagnostic 5: Test with EZCONNECT (bypasses tnsnames.ora entirely)
-- $ sqlplus user/password@proddb.example.com:1521/PRODDB
-- Connected.  -- Database is reachable; problem is tnsnames.ora, not the database
-- If EZCONNECT fails with ORA-12154: check sqlnet.ora for NAMES.DIRECTORY_PATH

-- Diagnostic 6: Check for syntax errors in tnsnames.ora
-- Common syntax errors:
-- 1. Missing closing parenthesis — breaks all entries after the error
-- 2. Extra whitespace before the alias name — alias becomes ' PRODDB' not 'PRODDB'
-- 3. Alias defined twice — first definition wins, second is silently ignored
-- 4. Incorrect comment syntax — # is correct, -- is NOT a valid comment in tnsnames.ora
-- 5. File saved with BOM (Byte Order Mark) — invisible characters at the start of the file
-- Quick validation:
-- $ tnsping PRODDB  -- If TNS-03505, the file has a problem for this alias

-- Diagnostic 7: Verify EZCONNECT is enabled in sqlnet.ora
-- $ grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
-- If EZCONNECT is not listed, direct connection strings raise ORA-12154

-- Diagnostic 1: List all database links and their TNS aliases
SELECT
  owner,
  db_link,
  username,
  host AS tns_alias,
  created
FROM dba_db_links
ORDER BY owner, db_link;
-- The HOST column shows the TNS alias used by each link
-- Verify EACH alias exists in the DATABASE SERVER's tnsnames.ora

-- Diagnostic 2: Test every database link in the current schema
SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
  FOR rec IN (
    SELECT db_link FROM user_db_links ORDER BY db_link
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM dual@' || rec.db_link;
      DBMS_OUTPUT.PUT_LINE('OK:   ' || rec.db_link);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: ' || rec.db_link || ' — ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- Diagnostic 3: Test all database links across the entire database (run as DBA)
SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
  FOR rec IN (
    SELECT owner, db_link FROM dba_db_links ORDER BY owner, db_link
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'SELECT 1 FROM dual@' || rec.db_link;
      DBMS_OUTPUT.PUT_LINE('OK:   ' || rec.owner || '.' || rec.db_link);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAIL: ' || rec.owner || '.' || rec.db_link || ' — ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- Diagnostic 4: Check the server's TNS_ADMIN
-- From the database, query the server's environment:
SELECT
  SYS_CONTEXT('USERENV', 'ORACLE_HOME') AS oracle_home
FROM dual;
-- tnsnames.ora is at: <oracle_home>/network/admin/tnsnames.ora
-- unless TNS_ADMIN overrides the location

-- Fix 1: Add the alias to the server's tnsnames.ora
-- Edit the file on the DATABASE SERVER (not the client):
-- REMOTE_PROD =
--   (DESCRIPTION =
--     (ADDRESS = (PROTOCOL = TCP)(HOST = remote.example.com)(PORT = 1521))
--     (CONNECT_DATA = (SERVICE_NAME = REMOTE_PROD))
--   )

-- Fix 2: Recreate the database link with an inline TNS descriptor
-- This eliminates tnsnames.ora dependency for this link
DROP DATABASE LINK remote_prod;

CREATE DATABASE LINK remote_prod
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=remote.example.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=REMOTE_PROD))
  )';

-- Fix 3: Inline descriptor with failover for high availability
CREATE DATABASE LINK remote_prod_ha
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
    (FAILOVER=ON)
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=remote-primary.example.com)(PORT=1521))
      (ADDRESS=(PROTOCOL=TCP)(HOST=remote-standby.example.com)(PORT=1521))
    )
    (CONNECT_DATA=(SERVICE_NAME=REMOTE_PROD))
  )';

-- Verify the recreated link works
SELECT 1 FROM dual@remote_prod;

-- EZCONNECT: Basic syntax
-- Format: user/password@host:port/service_name
-- $ sqlplus app_user/password@proddb.example.com:1521/PRODDB

-- EZCONNECT: With connect timeout (Oracle 19c+)
-- $ sqlplus app_user/password@"proddb.example.com:1521/PRODDB?connect_timeout=10"

-- EZCONNECT: With retry parameters (Oracle 19c+)
-- $ sqlplus app_user/password@"proddb.example.com:1521/PRODDB?connect_timeout=10&retry_count=3&retry_delay=3"

-- EZCONNECT: In JDBC connection string
-- jdbc:oracle:thin:@proddb.example.com:1521/PRODDB
-- jdbc:oracle:thin:@//proddb.example.com:1521/PRODDB  (alternate format with //)

-- EZCONNECT: In Python (python-oracledb)
-- import oracledb
-- connection = oracledb.connect(
--     user="app_user",
--     password="password",
--     dsn="proddb.example.com:1521/PRODDB"
-- )

-- EZCONNECT: In .NET (ODP.NET)
-- string connStr = "User Id=app_user;Password=password;Data Source=proddb.example.com:1521/PRODDB";

-- sqlnet.ora: Enable EZCONNECT
-- File: $TNS_ADMIN/sqlnet.ora
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
-- Restart the application after modifying sqlnet.ora

-- Inline TNS descriptor for database links (no tnsnames.ora needed)
CREATE DATABASE LINK remote_prod
  CONNECT TO app_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=proddb.example.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=PRODDB))
  )';

-- Inline TNS descriptor with failover for high availability
CREATE DATABASE LINK remote_prod_ha
  CONNECT TO app_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
    (FAILOVER=ON)
    (LOAD_BALANCE=OFF)
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=proddb-primary.example.com)(PORT=1521))
      (ADDRESS=(PROTOCOL=TCP)(HOST=proddb-standby.example.com)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVICE_NAME=PRODDB)
      (FAILOVER_MODE=
        (TYPE=SELECT)
        (METHOD=BASIC)
        (RETRIES=3)
        (DELAY=5)
      )
    )
  )';

-- Inline TNS descriptor with TCPS (TLS encryption)
CREATE DATABASE LINK remote_prod_secure
  CONNECT TO app_user IDENTIFIED BY "password"
  USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=proddb.example.com)(PORT=2484))
    (CONNECT_DATA=(SERVICE_NAME=PRODDB))
    (SECURITY=(SSL_SERVER_CERT_DN="CN=proddb.example.com"))
  )';

-- Verify the link works
SELECT 1 FROM dual@remote_prod;

-- Production sqlnet.ora configuration
-- File: $TNS_ADMIN/sqlnet.ora

-- Resolution order: local file first, then EZCONNECT as fallback
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

-- Disable domain suffix appending
-- Prevents PRODDB from being mangled to PRODDB.EXAMPLE.COM
NAMES.DEFAULT_DOMAIN =

-- TCP keepalive: detect dead connections through firewalls (minutes)
SQLNET.EXPIRE_TIME = 10

-- Connection timeout: prevent indefinite hangs to unreachable hosts (seconds)
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 30

-- Receive timeout: limit how long to wait for data on an established connection (seconds)
SQLNET.RECV_TIMEOUT = 300

-- Send timeout: limit how long to wait for a send to complete (seconds)
SQLNET.SEND_TIMEOUT = 300

-- Diagnostic 1: Find the active sqlnet.ora
-- $ tnsping DUMMY_ALIAS 2>&1 | head -5
-- Used parameter files:
--   /opt/oracle/19c/network/admin/sqlnet.ora
-- This shows which sqlnet.ora is actually being used

-- Diagnostic 2: Check NAMES.DIRECTORY_PATH
-- $ grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
-- If EZCONNECT is missing: direct connection strings fail with ORA-12154

-- Diagnostic 3: Check NAMES.DEFAULT_DOMAIN
-- $ grep -i NAMES.DEFAULT_DOMAIN $TNS_ADMIN/sqlnet.ora
-- NAMES.DEFAULT_DOMAIN = example.com
-- If set: unqualified aliases get the domain appended silently
-- Fix: clear the value or add the domain to tnsnames.ora entries

-- Diagnostic 4: Verify TNS_ADMIN is set and points to the right directory
-- $ echo $TNS_ADMIN
-- /opt/oracle/network/admin
-- $ ls -la $TNS_ADMIN/
-- sqlnet.ora    <-- must exist
-- tnsnames.ora  <-- must exist
-- If either file is missing, Oracle uses built-in defaults

-- Diagnostic 5: Check for conflicting sqlnet.ora files
-- $ find / -name sqlnet.ora -type f 2>/dev/null
-- /opt/oracle/19c/network/admin/sqlnet.ora
-- /opt/oracle/21c/network/admin/sqlnet.ora
-- /home/appuser/oracle/sqlnet.ora
-- Multiple files with different settings cause inconsistent behavior

-- Check 1: Test all database link connectivity
-- Run daily via DBMS_SCHEDULER
CREATE OR REPLACE PROCEDURE app_schema.test_all_dblinks IS
  v_result    NUMBER;
  v_fail_count NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT owner, db_link, host AS tns_alias
    FROM dba_db_links
    ORDER BY owner, db_link
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM dual@' || rec.db_link
        INTO v_result;

      app_schema.logger_pkg.info(
        'test_all_dblinks',
        'OK: ' || rec.owner || '.' || rec.db_link
      );
    EXCEPTION
      WHEN OTHERS THEN
        v_fail_count := v_fail_count + 1;
        app_schema.logger_pkg.error(
          'test_all_dblinks',
          'FAIL: ' || rec.owner || '.' || rec.db_link
          || ' (alias: ' || rec.tns_alias || ')'
          || ' — ' || SQLERRM,
          DBMS_UTILITY.FORMAT_ERROR_STACK
        );
    END;
  END LOOP;

  IF v_fail_count > 0 THEN
    app_schema.logger_pkg.error(
      'test_all_dblinks',
      v_fail_count || ' database link(s) failed connectivity test — investigate before business hours'
    );
  ELSE
    app_schema.logger_pkg.info(
      'test_all_dblinks',
      'All database links passed connectivity test'
    );
  END IF;
END test_all_dblinks;
/

-- Check 2: Verify sqlnet.ora configuration is correct
CREATE OR REPLACE PROCEDURE app_schema.check_sqlnet_config IS
  v_oracle_home VARCHAR2(500);
BEGIN
  -- Get the database server's ORACLE_HOME
  SELECT SYS_CONTEXT('USERENV', 'ORACLE_HOME')
  INTO v_oracle_home
  FROM dual;

  app_schema.logger_pkg.info(
    'check_sqlnet_config',
    'ORACLE_HOME: ' || v_oracle_home
    || ' | tnsnames.ora expected at: ' || v_oracle_home || '/network/admin/tnsnames.ora'
  );

  -- Additional checks would read the file via UTL_FILE or an external table
  -- and verify NAMES.DIRECTORY_PATH includes EZCONNECT,
  -- NAMES.DEFAULT_DOMAIN is empty, and SQLNET.EXPIRE_TIME is set
END check_sqlnet_config;
/

-- Check 3: Pre-deployment database link alias verification
CREATE OR REPLACE PROCEDURE app_schema.verify_dblink_aliases IS
  v_missing_count NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT owner, db_link, host AS tns_alias
    FROM dba_db_links
    WHERE host IS NOT NULL
      AND host NOT LIKE '%(DESCRIPTION%'  -- Skip inline descriptors
    ORDER BY owner, db_link
  ) LOOP
    -- Test resolution by attempting a connection
    BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM dual@' || rec.db_link;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -12154 THEN
          v_missing_count := v_missing_count + 1;
          DBMS_OUTPUT.PUT_LINE(
            'MISSING ALIAS: ' || rec.owner || '.' || rec.db_link
            || ' references TNS alias "' || rec.tns_alias
            || '" which cannot be resolved on this server'
          );
        END IF;
    END;
  END LOOP;

  IF v_missing_count > 0 THEN
    RAISE_APPLICATION_ERROR(
      -20080,
      'Pre-deployment check failed: ' || v_missing_count
      || ' database link(s) reference unresolvable TNS aliases on this server'
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE('All database link TNS aliases resolve successfully');
  END IF;
END verify_dblink_aliases;
/

-- Schedule the daily connectivity check
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'APP_SCHEMA.DAILY_DBLINK_CHECK',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN app_schema.test_all_dblinks; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=5; BYMINUTE=30',
    enabled         => TRUE,
    comments        => 'Daily database link connectivity test — detects ORA-12154 before business hours'
  );
END;
/