转载于--->>http://blog.csdn.net/leshami/article/details/6688380
ORA-02019 错误提示是未找到远程数据库的连接说明，通常发生在本地数据库无法连接到远程数据库。引发该问题的原因很多，比如网络连接，连接方式(tnsnames)，dblinkc的创建等等。而下面的描述的故障则比较奇特。

一、错误现象

[sql] view plain copy print ?

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] view plain copy print ?

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] view plain copy print ?

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] view plain copy print ?

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] view plain copy print ?

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] view plain copy print ?

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测试

[sql] view plain copy print ?

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

[sql] view plain copy print ?

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也可以正常使用。

ITPub博客

ORA-02019 错误处理

最新文章