本文档风哥主要介绍Oracle ORA-12514监听程序无法识别服务相关知识，包括ORA-12514的概念、ORA-12514的常见原因、ORA-12514分析方法、ORA-12514错误分析配置、ORA-12514错误监控、ORA-12514错误故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 ORA-12514的概念
Oracle ORA-12514是Oracle数据库的监听程序无法识别服务错误，表示TNS监听程序当前无法识别连接描述符中请求的服务。ORA-12514错误通常发生在服务名不正确或服务未注册到监听程序时。ORA-12514错误需要检查服务名配置或确保服务已注册到监听程序。更多视频教程www.fgedu.net.cn
- 服务未识别：监听程序无法识别请求的服务
- 服务名错误：服务名配置不正确
- 服务未注册：服务未注册到监听程序
- 动态注册失败：PMON进程未注册服务
- 静态配置缺失：listener.ora中缺少静态服务配置
1.2 ORA-12514的常见原因
Oracle ORA-12514的常见原因：
- 服务名错误：连接字符串中的服务名不正确
- 服务未注册：数据库服务未注册到监听程序
- PMON进程问题：PMON进程未正常注册服务
- 监听程序配置错误：listener.ora配置不正确
- 数据库未启动：数据库实例未启动
- LOCAL_LISTENER参数错误：LOCAL_LISTENER参数配置不正确
- 网络配置问题：网络配置不正确
1.3 ORA-12514分析方法
Oracle ORA-12514分析方法：
- 错误信息分析：分析错误信息含义
- 监听程序状态分析：分析监听程序状态和服务列表
- 服务名验证：验证服务名是否正确
- 数据库状态分析：分析数据库实例状态
- 参数配置分析：分析相关参数配置
- 告警日志分析：分析告警日志
Part02-生产环境规划与建议
2.1 ORA-12514错误处理规划
Oracle ORA-12514错误处理规划要点：
– 错误监控：监控ORA-12514错误发生
– 错误记录：记录ORA-12514错误信息
– 错误分析：分析ORA-12514错误原因
– 错误处理：处理ORA-12514错误问题
# 错误监控规划
– 监控工具：选择监控工具
– 监控频率：设置监控频率
– 监控范围：定义监控范围
– 告警配置：配置告警规则
# 错误记录规划
– 记录方式：选择记录方式
– 记录格式：定义记录格式
– 记录保留：设置记录保留时间
– 记录备份：备份错误记录
# 错误分析规划
– 分析方法：选择分析方法
– 分析工具：选择分析工具
– 分析流程：定义分析流程
– 分析报告：生成分析报告
# 错误处理规划
– 处理流程：定义处理流程
– 处理优先级：设置处理优先级
– 处理责任人：指定处理责任人
– 处理验证：验证处理结果
2.2 ORA-12514错误分析工具
Oracle ORA-12514错误分析工具：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- lsnrctl：监听程序控制工具
- tnsping：TNS解析测试工具
- srvctl：服务管理工具
– SQL*Plus：快速查询错误信息
– SQL Developer：图形化分析错误
– Enterprise Manager：集中管理错误
– lsnrctl：管理监听程序和服务
– tnsping：测试TNS解析
– srvctl：管理数据库服务
2.3 ORA-12514错误处理最佳实践
Oracle ORA-12514错误处理最佳实践：
- 定期监控：定期监控ORA-12514错误
- 及时处理：及时处理ORA-12514错误
- 文档记录：记录错误处理过程
- 经验积累：积累错误处理经验
- 预防措施：制定预防措施
Part03-生产环境项目实施方案
3.1 ORA-12514错误分析配置
3.1.1 配置监听程序服务管理
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 31-MAR-2026 09:00:00
Uptime 0 days 1 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
The command completed successfully
# 2. 查看监听程序服务
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:100 refused:0 state:ready
LOCAL SERVER
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
# 3. 查看数据库服务名
SQL> show parameter service_names
NAME TYPE VALUE
———————————— ———– ——————————
service_names string FGEDUDB
SQL> select name, network_name from dba_services;
NAME NETWORK_NAME
—————————— ——————————
SYS$BACKGROUND
SYS$USERS
FGEDUDB FGEDUDB
FGEDUDBXDB FGEDUDBXDB
# 4. 查看LOCAL_LISTENER参数
SQL> show parameter local_listener
NAME TYPE VALUE
———————————— ———– ——————————
local_listener string
# 5. 设置LOCAL_LISTENER参数
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))’ scope=both;
System altered.
# 6. 注册服务到监听程序
SQL> alter system register;
System altered.
# 7. 验证服务注册
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:100 refused:0 state:ready
LOCAL SERVER
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
3.1.2 配置静态服务注册
$ cat /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
# 2. 添加静态服务配置
$ vi /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = FGEDUDB)
(ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
(SID_NAME = FGEDUDB)
)
)
# 3. 重启监听程序
$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
The command completed successfully
$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 31-MAR-2026 10:00:00
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
# 4. 验证静态服务
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
The command completed successfully
# 5. 测试连接
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select instance_name, status, database_status, startup_time
from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
—————- ———— —————– ——————-
FGEDUDB OPEN ACTIVE 31-MAR-26 09:00:00
3.2 ORA-12514错误监控
3.2.1 配置ORA-12514错误监控脚本
$ vi /home/oracle/scripts/monitor_ora12514.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ORA-12514错误监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
# 定义变量
ERROR_LOG=”/home/oracle/scripts/ora12514_errors.log”
EMAIL=”admin@fgedu.net.cn”
# 检查ORA-12514错误
check_ora12514_errors() {
local last_check_file=”/home/oracle/scripts/last_ora12514_check.txt”
local current_time=$(date +%s)
if [ -f “$last_check_file” ]; then
local last_check=$(cat “$last_check_file”)
local time_diff=$((current_time – last_check))
if [ $time_diff -lt 3600 ]; then
echo “Last check was $time_diff seconds ago, skipping…”
return 0
fi
fi
# 检查监听程序服务
local listener_services=$(lsnrctl services 2>&1)
if echo “$listener_services” | grep -q “TNS-12514”; then
echo “Found ORA-12514 error:” >> “$ERROR_LOG”
echo “$listener_services” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
# 发送告警邮件
echo “ORA-12514 error detected: listener cannot identify service” | mail -s “ORA-12514 Alert” “$EMAIL”
# 分析监听程序服务
analyze_listener_services
fi
# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}
# 分析监听程序服务
analyze_listener_services() {
local listener_services=$(lsnrctl services 2>&1)
echo “Listener Services:” >> “$ERROR_LOG”
echo “$listener_services” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
# 检查数据库服务名
local db_services=$(sqlplus -s / as sysdba <
EOF
)
echo “Database Services:” >> “$ERROR_LOG”
echo “$db_services” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}
# 主函数
main() {
check_ora12514_errors
echo “ORA-12514 error monitoring completed at $(date)” >> “$ERROR_LOG”
}
# 执行主函数
main
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora12514.sh
# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora12514.sh
# 4. 设置定期监控
$ crontab -e
# 每小时检查ORA-12514错误
0 * * * * /home/oracle/scripts/monitor_ora12514.sh >> /home/oracle/scripts/monitor_ora12514.log 2>&1
3.2.2 配置数据库监控
SQL> create or replace view listener_service_monitor as
select
‘LISTENER’ as listener_name,
‘192.168.1.100’ as host,
‘1521’ as port,
‘RUNNING’ as status,
(select count(*) from dba_services where name not like ‘SYS%’) as service_count,
(select count(*) from v$session where type = ‘USER’) as active_sessions
from dual;
View created.
# 2. 查询监听程序服务状态
SQL> select * from listener_service_monitor;
LISTENER_NAME HOST PORT STATUS SERVICE_COUNT ACTIVE_SESSIONS
————- ————— —- ——– ————- —————
LISTENER 192.168.1.100 1521 RUNNING 2 50
# 3. 创建服务注册监控视图
SQL> create or replace view service_registration_monitor as
select
name as service_name,
network_name,
creation_date,
(select value from v$parameter where name = ‘local_listener’) as local_listener,
(select status from v$instance) as instance_status
from dba_services
where name not like ‘SYS%’;
View created.
# 4. 查询服务注册状态
SQL> select * from service_registration_monitor;
SERVICE_NAME NETWORK_NAME CREATION_DATE LOCAL_LISTENER INSTANCE_STATUS
———— ———— ——————– ————————————– —————
FGEDUDB FGEDUDB 31-MAR-26 09:00:00 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) OPEN
(HOST=192.168.1.100)(PORT=1521)))
FGEDUDBXDB FGEDUDBXDB 31-MAR-26 09:00:00 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) OPEN
(HOST=192.168.1.100)(PORT=1521)))
# 5. 创建ORA-12514错误告警
SQL> create or replace procedure ora12514_error_alert as
v_service_count number;
v_listener_status varchar2(20);
v_local_listener varchar2(4000);
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 检查服务数量
select count(*) into v_service_count
from dba_services
where name not like ‘SYS%’;
— 检查监听程序状态
select status into v_listener_status
from listener_service_monitor;
— 检查LOCAL_LISTENER参数
select value into v_local_listener
from v$parameter
where name = ‘local_listener’;
if v_service_count = 0 or v_listener_status != ‘RUNNING’ or v_local_listener is null then
— 发送告警
dbms_output.put_line(‘Service count: ‘ || v_service_count);
dbms_output.put_line(‘Listener status: ‘ || v_listener_status);
dbms_output.put_line(‘Local listener: ‘ || v_local_listener);
dbms_output.put_line(‘Recommendation: Check service registration or listener configuration’);
end if;
end ora12514_error_alert;
/
Procedure created.
# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora12514_error_alert;
Service count: 2
Listener status: RUNNING
Local listener: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
PL/SQL procedure successfully completed.
# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA12514_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA12514_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
3.3 ORA-12514错误故障处理
3.3.1 ORA-12514错误处理
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
# 分析步骤
# 1. 查看监听程序状态
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 31-MAR-2026 09:00:00
Uptime 0 days 1 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
# 2. 查看监听程序服务
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
The listener supports no services
The command completed successfully
# 3. 查看数据库服务名
SQL> show parameter service_names
NAME TYPE VALUE
———————————— ———– ——————————
service_names string FGEDUDB
# 4. 查看LOCAL_LISTENER参数
SQL> show parameter local_listener
NAME TYPE VALUE
———————————— ———– ——————————
local_listener string
# 5. 分析错误原因
# 错误代码：ORA-12514
# 错误信息：TNS:listener does not currently know of service requested in connect descriptor
# 监听程序状态：The listener supports no services
# 错误原因：服务未注册到监听程序
# 6. 解决方案：注册服务到监听程序
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))’ scope=both;
System altered.
SQL> alter system register;
System altered.
# 7. 验证服务注册
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
# 8. 验证问题解决
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select instance_name, status, database_status, startup_time
from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
—————- ———— —————– ——————-
FGEDUDB OPEN ACTIVE 31-MAR-26 09:00:00
# 9. 预防措施
# – 配置LOCAL_LISTENER参数
# – 定期检查监听程序服务
# – 配置静态服务注册
# – 定期测试连接
3.3.2 服务名错误处理
$ cat /oracle/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
FGEDUDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FGEDUDB)
)
)
# 2. 检查数据库服务名
SQL> select name, network_name from dba_services where name not like ‘SYS%’;
NAME NETWORK_NAME
—————————— ——————————
FGEDUDB FGEDUDB
FGEDUDBXDB FGEDUDBXDB
# 3. 修改TNS配置
$ vi /oracle/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
FGEDUDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FGEDUDB)
)
)
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FGEDUDB)
)
)
# 4. 测试TNS解析
$ tnsping TESTDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FGEDUDB)))
OK (10 msec)
# 5. 测试连接
$ sqlplus scott/tiger@TESTDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select instance_name, status, database_status, startup_time
from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
—————- ———— —————– ——————-
FGEDUDB OPEN ACTIVE 31-MAR-26 09:00:00
Part04-生产案例与实战讲解
4.1 ORA-12514错误分析案例
在生产环境中分析ORA-12514错误的完整案例：
4.1.1 场景描述
某企业生产数据库出现ORA-12514监听程序无法识别服务错误，需要分析错误原因并解决问题。
4.1.2 分析步骤
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
# 2. 查看监听程序状态
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 31-MAR-2026 09:00:00
Uptime 0 days 1 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
# 3. 查看数据库服务名
SQL> show parameter service_names
NAME TYPE VALUE
———————————— ———– ——————————
service_names string FGEDUDB
# 4. 查看LOCAL_LISTENER参数
SQL> show parameter local_listener
NAME TYPE VALUE
———————————— ———– ——————————
local_listener string
# 5. 分析错误原因
# 错误代码：ORA-12514
# 错误信息：TNS:listener does not currently know of service requested in connect descriptor
# 监听程序状态：The listener supports no services
# 错误原因：服务未注册到监听程序
# 6. 解决方案：注册服务到监听程序
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))’ scope=both;
System altered.
SQL> alter system register;
System altered.
# 7. 验证问题解决
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select instance_name, status, database_status, startup_time
from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS STARTUP_TIME
—————- ———— —————– ——————-
FGEDUDB OPEN ACTIVE 31-MAR-26 09:00:00
4.2 ORA-12514错误故障处理
在ORA-12514错误故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 错误识别
# – 监控监听程序服务
# – 检查错误信息
# – 确认错误参数
# 2. 错误分析
# – 查看监听程序状态
# – 检查监听程序服务
# – 分析数据库服务名
# 3. 错误处理
# – 注册服务到监听程序
# – 修正服务名配置
# – 配置静态服务注册
# 4. 错误预防
# – 配置LOCAL_LISTENER参数
# – 定期检查监听程序服务
# – 制定预防措施
# 示例：ORA-12514错误处理
# 1. 错误识别
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
# 2. 错误分析
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 31-MAR-2026 09:00:00
Uptime 0 days 1 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
# 3. 错误处理
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))’ scope=both;
System altered.
SQL> alter system register;
System altered.
# 4. 验证处理结果
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
# 5. 错误预防
# – 配置LOCAL_LISTENER参数
# – 定期检查监听程序服务
# – 配置静态服务注册
# – 定期测试连接
4.3 ORA-12514错误优化
优化ORA-12514错误处理配置的最佳实践：
4.3.1 优化服务注册管理
SQL> create or replace procedure optimize_service_registration as
v_service_count number;
v_listener_status varchar2(20);
v_local_listener varchar2(4000);
begin
— 获取服务数量
select count(*) into v_service_count
from dba_services
where name not like ‘SYS%’;
— 获取监听程序状态
select status into v_listener_status
from listener_service_monitor;
— 获取LOCAL_LISTENER参数
select value into v_local_listener
from v$parameter
where name = ‘local_listener’;
— 如果服务未注册，建议注册
if v_service_count = 0 or v_listener_status != ‘RUNNING’ or v_local_listener is null then
dbms_output.put_line(‘Service count: ‘ || v_service_count);
dbms_output.put_line(‘Listener status: ‘ || v_listener_status);
dbms_output.put_line(‘Local listener: ‘ || v_local_listener);
dbms_output.put_line(‘Recommendation: Register service to listener or check listener configuration’);
end if;
end optimize_service_registration;
/
Procedure created.
# 2. 执行优化存储过程
SQL> set serveroutput on
SQL> exec optimize_service_registration;
Service count: 0
Listener status: RUNNING
Local listener:
Recommendation: Register service to listener or check listener configuration
PL/SQL procedure successfully completed.
# 3. 应用优化建议
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))’ scope=both;
System altered.
SQL> alter system register;
System altered.
# 4. 验证优化结果
$ lsnrctl services
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 31-MAR-2026 10:00:00
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
Services Summary…
Service “FGEDUDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “FGEDUDBXDB” has 1 instance(s).
Instance “FGEDUDB”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
# 5. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘OPTIMIZE_SERVICE_REGISTRATION_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘OPTIMIZE_SERVICE_REGISTRATION’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
# 6. 配置静态服务注册
$ vi /oracle/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = FGEDUDB)
(ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
(SID_NAME = FGEDUDB)
)
)
# 7. 重启监听程序
$ lsnrctl stop
$ lsnrctl start
Part05-风哥经验总结与分享
5.1 ORA-12514错误总结
Oracle ORA-12514错误是监听程序无法识别服务错误，具有以下特点：
- 服务未识别：监听程序无法识别请求的服务
- 服务名错误：服务名配置不正确
- 服务未注册：服务未注册到监听程序
- 动态注册失败：PMON进程未注册服务
- 静态配置缺失：listener.ora中缺少静态服务配置
5.2 ORA-12514错误检查清单
Oracle ORA-12514错误检查清单：
- 错误识别：识别错误信息和参数
- 错误分析：分析监听程序服务状态
- 错误处理：处理错误问题
- 错误验证：验证处理结果
- 错误预防：制定预防措施
- 错误记录：记录处理过程
5.3 ORA-12514错误工具推荐
Oracle ORA-12514错误工具推荐：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- lsnrctl：监听程序控制工具
- tnsping：TNS解析测试工具
- srvctl：服务管理工具
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
