ORA-02019 错误提示是未找到远程数据库的连接说明，通常发生在本地数据库无法连接到远程数据库。引发该问题的原因很多，比如网络连接，连接方式(tnsnames)，dblinkc的创建等等。而下面的描述的故障则比较奇特。
一、错误现象
SQL> select * from scanfilename@dss.m85;
select * from scanfilename@dss.m85
ORA-02019: 未找到远程数据库的连接说明
SQL> select * from xdoc_file@DSS.M85;
select * from xdoc_file@DSS.M85
ORA-02019: 未找到远程数据库的连接说明
二、分析与解决
1.当前数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
2.当前已创建的database link
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ---------- ---------- --------------- -----------
DIMS DSS.M85 VIEWPIC PIC 2007-11-8 1
SQL> select * from v$dblink; -->当前没有正在使用的database link
DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH
---------- ---------- --------- ------------- -------- ------------ -------------- ----------- ---------------------
3.下面的存储过程使用了分布式数据库
SQL> select text from dba_source where name='P_PROCESS_PIC_VALID';
TEXT
--------------------------------------------------------------------------------
PROCEDURE "P_PROCESS_PIC_VALID"
(processDate IN DATE --处理日期，如果不传入，就表示当天
) AS
recCount INT;
pictureName NVARCHAR2(50);
mntnOperMask VARCHAR2(10); --维护部操作人员账号前缀
oper NVARCHAR2(50);
PicId NVARCHAR2(50);
FilmId NVARCHAR2(50);
v_maxid number; --20100720
BEGIN
-------------------------------------------------20100720
-- 已导入的数据最大的ID
select max(id) into v_maxid from T_scanfilename_m85;
-- 导入新增的数据
execute immediate 'insert into T_scanfilename_m85
select * from scanfilename@dss.m85
TEXT
--------------------------------------------------------------------------------
where id>:COL_1'
using v_maxid;
commit;
-------------------------------------------------20100720
mntnOperMask := 'gl';
--从图系统导入有效片数据
INSERT INTO t_pic_xdoc_file(cph,cl,wldd,fx,wlrq,wlxm,lrsj,oper,picture_name
SELECT T1.cph,T1.cl,T1.wldd,T1.fx,T1.wlrq,T1.wlxm,T1.lrsj,T1.oper,T2.FILE
/*FROM xdoc_file@DSS.M85 T1,scanfilename@DSS.M85 T2*/ --20100720
FROM xdoc_file@DSS.M85 T1,T_scanfilename_m85 T2
WHERE T1.XH=T2.XH
and t2.id>15717858
and not exists (select picture_name from t_pic_xdoc_file where pict
AND substr(T1.oper,1,2)= mntnOperMask
and (t1.lrsj >=sysdate-3);
-- 将图文系统过来的有效片数据，分析处理后保存到统计表中
--处理有效片
FOR c IN(SELECT xf.*,ROWID FROM t_pic_xdoc_file xf WHERE status=1)
LOOP
SQL> select table_name,owner from dba_tables where table_name in ('SCANFILENAME','XDOC_FILE');
TABLE_NAME OWNER
------------------------------ ------------------------------
SCANFILENAME DIMS
SQL> select * from dims.scanfilename where rownum<5;
ID FILENAME XH
--------------------------------------- -------------------------------------------------- ------------
8606178 V1_00000020361002320070614095523017900004.jpg
8605441 V1_00000002101002120070614095523011900023.jpg
8605445 V1_00000002101002120070614095523011900025.jpg
8605449 V1_00000002101002120070614095523011900027.jpg
4.尝试执行操作
SQL> select * from scanfilename@dss.m85;
select * from scanfilename@dss.m85
ORA-02019: 未找到远程数据库的连接说明
SQL> select * from xdoc_file@DSS.M85;
select * from xdoc_file@DSS.M85
ORA-02019: 未找到远程数据库的连接说明
SQL> show user;
User is "SYS"
SQL> conn dims/dims_12345@dmsdb
Connected to Oracle8i Enterprise Edition Release 8.1.7.4.1
Connected as dims
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
--------------- --------------- --------------- --------------- -----------
DIMS DSS.M85 VIEWPIC PIC 2007-11-8 1
SQL> select * from user_db_links;
DB_LINK USERNAME PASSWORD HOST CREATED
--------------- --------------- ------------------------------ --------------- -----------
DSS.M85 VIEWPIC VIEWPIC PIC 2007-11-8 1
5.切换帐户后使用分布式数据库，提示监听不可用
SQL> select * from xdoc_file@DSS.M85
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
SQL> select * from scanfilename@DSS.M85;
select * from scanfilename@DSS.M85
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
pic =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DSS)
)
)
6.使用tnsping测试
C:\>tnsping pic
TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on 26-7月 -2
011 15:32:35
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=100.100.1.31)(PORT=1521))
OK（20毫秒）
C:\>sqlplus viewpic/viewpic@pic -->sqlplus 不能登陆
SQL*Plus: Release 8.1.7.0.0 - Production on 星期二 7月 26 15:33:15 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR：
ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
7.查看本地tnsnames.ora
pic =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.31)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DSS) -->(原来为service_name,将其改为SID)
)
)
C:\>sqlplus viewpic/viewpic@pic -->再次使用sqlplus登陆正常
SQL*Plus: Release 8.1.7.0.0 - Production on 星期三 7月 27 09:49:57 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.0.0 - 64bit Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Developmen
NLSRTL Version 3.4.1.0.0 - Production
SQL> select * from scanfilename@DSS.M85 where rownum < 5;
ID FILENAME XH
---------- -------------------------------------------------- --------------------
8606178 V1_00000020361002320070614095523017900004.jpg
8605441 V1_00000002101002120070614095523011900023.jpg
8605445 V1_00000002101002120070614095523011900025.jpg
8.是什么原因造成了需要将service_name改为SID，以前使用service_name一直正常。由于DSS.M85在故障前前曾重
未做任何更改。Oracle 8之后推荐使用service_name，但实际上在Oracle 8使用service_name也可以正常使用。
三、更多参考
有关性能优化请参考
共享池的调整与优化(Shared pool Tuning)
Oracle 表缓存(caching table)的使用
有关ORACLE体系结构请参考
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 实例和Oracle数据库(Oracle体系结构)
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query、Flashback Table)
Oracle 闪回特性(Flashback Version、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 基于用户管理恢复的处理 (详细描述了介质恢复及其处理)
有关RMAN的备份恢复与管理请参考
RMAN 备份路径困惑(使用plus archivelog时)
有关ORACLE故障请参考
对参数FAST_START_MTTR_TARGET = 0 的误解及设定
SPFILE 错误导致数据库无法启动(ORA-01565)
有关ASM请参考
有关SQL/PLSQL请参考
SQL 基础 --> 集合运算(UNION 与UNION ALL)
SQL 基础 --> 视图(CREATE VIEW)
SQL 基础 --> 层次化查询(START BY ... CONNECT BY PRIOR)
SQL 基础 --> ROLLUP与CUBE运算符实现数据汇总
PL/SQL --> 异常处理(Exception)
PL/SQL --> 隐式游标(SQL%FOUND)
有关ORACLE其它特性
使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例
日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)
表段、索引段上的LOGGING与NOLOGGING
使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
簇表及簇表管理(Index clustered tables)
system sys，sysoper sysdba 的区别
ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME
Oracle 补丁全集 (Oracle 9i 10g 11g Path)
Oracle 10.2.0.1 升级到 10.2.0.4
