ORA-12514错误排查：XAMPP中PHP通过OCI8连接Oracle失败

问题背景 #

此前使用SQL Plus 19.3，通过OCI8连接XAMPP中的PHP Web服务器可正常显示数据库内容，假期归来后未做任何修改，却出现错误：ORA-12514: TNS:listener does not currently know of service requested in connect descriptor。确认用户名、密码及数据库名称均正确。

相关代码与配置 #

测试用PHP网页代码 #

<!DOCTYPE html>
<html>

<head>
    <!-- Load Bootstrap CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

<!-- Load Bootstrap JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

    <title>Customer Data</title>
</head>
<body>
    <h2>SHOW CUSTOMER DATA</h2>

    <?php
        //Connect to Oracle database
        $conn = oci_connect('ali', 'abc123', '//localhost/orcl');
        
        if (!$conn) {
            $e = oci_error();
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
        } 

        $query = "SELECT table_name FROM USER_TABLES";
        $stid = oci_parse($conn, $query);
        oci_execute($stid);

        echo '<ul>';

        while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
            echo '<li>' . $row['TABLE_NAME'] . '</li>';
        }

        oci_close($conn);
    ?>
    
</body>

tnsnames.ora 配置 #

# tnsnames.ora Network Configuration File: C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

listener.ora 配置 #

# listener.ora Network Configuration File: C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

排查与解决步骤 #

• 检查Oracle数据库实例状态
  打开命令提示符，执行：

  sqlplus / as sysdba
  

  登录后执行启动命令（若实例未启动）：

  STARTUP
  

• 确认监听器已注册目标服务
  执行命令查看监听器状态：

  lsnrctl status
  

  查看Services Summary区域，确认是否存在orcl服务。若未找到，在SQL Plus中执行服务注册命令：

  ALTER SYSTEM REGISTER;
  

  再次执行lsnrctl status验证服务已注册。

• 核对服务名一致性
  在SQL Plus中查询数据库实际服务名：

  SELECT name, service_name FROM v$database;
  

  确保查询结果中的service_name与tnsnames.ora中配置的SERVICE_NAME = orcl完全一致。

• 手动添加实例到监听器配置（自动注册失败时）
  修改listener.ora，在SID_LIST_LISTENER中新增ORCL实例的描述：

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home)
        (PROGRAM = extproc)
        (ENVS = "EXTPROC_DLLS=ONLY:C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
      )
      (SID_DESC =
        (SID_NAME = ORCL)
        (ORACLE_HOME = C:\Users\aliba\Downloads\WINDOWS.X64_193000_db_home)
      )
    )
  

  修改后重启监听器：

  lsnrctl stop
  lsnrctl start
  

• 测试连接有效性
  先用SQL Plus测试命令行连接：

  sqlplus ali/abc123@//localhost/orcl
  

  若连接成功，再检查PHP的OCI8扩展是否正常加载（可通过phpinfo()页面查看OCI8相关信息）。

内容的提问来源于stack exchange，提问作者Xray25