?????? ORA-02019 錯(cuò)誤提示是未找到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接說(shuō)明，通常發(fā)生在本地?cái)?shù)據(jù)庫(kù)無(wú)法連接到遠(yuǎn)程數(shù)據(jù)庫(kù)。引發(fā)該問(wèn)題的原因很多，比如網(wǎng)絡(luò)連接，連接方式(tnsnames)，dblinkc的創(chuàng)建等等。而下面的描述的故障則比較奇特。??????????????????????????????????????????????????????????????????????????????????????????????????

一、錯(cuò)誤現(xiàn)象? ?

SQL> select * from scanfilename@dss.m85; select * from scanfilename@dss.m85 ORA-02019: 未找到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接說(shuō)明 SQL> select * from xdoc_file@DSS.M85; select * from xdoc_file@DSS.M85 ORA-02019: 未找到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接說(shuō)明

二、分析與解決?? ????????????????????????????????????????????????????????????????????????????????????? ?
?? ? 1.當(dāng)前數(shù)據(jù)庫(kù)版本??? ??????????????????????????????????????????????????????????????????????????????? ??? ????

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.當(dāng)前已創(chuàng)建的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; -->當(dāng)前沒(méi)有正在使用的database link DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH ---------- ---------- --------- ------------- -------- ------------ -------------- ----------- ---------------------

??? 3.下面的存儲(chǔ)過(guò)程使用了分布式數(shù)據(jù)庫(kù) ???????????????????????????????????????????????????????????????? ??? ???

SQL> select text from dba_source where name='P_PROCESS_PIC_VALID'; TEXT -------------------------------------------------------------------------------- PROCEDURE "P_PROCESS_PIC_VALID" (processDate IN DATE --處理日期，如果不傳入，就表示當(dāng)天 ) AS recCount INT; pictureName NVARCHAR2(50); mntnOperMask VARCHAR2(10); --維護(hù)部操作人員賬號(hào)前綴 oper NVARCHAR2(50); PicId NVARCHAR2(50); FilmId NVARCHAR2(50); v_maxid number; --20100720 BEGIN -------------------------------------------------20100720 -- 已導(dǎo)入的數(shù)據(jù)最大的ID select max(id) into v_maxid from T_scanfilename_m85; -- 導(dǎo)入新增的數(shù)據(jù) execute immediate 'insert into T_scanfilename_m85 select * from scanfilename@dss.m85 TEXT -------------------------------------------------------------------------------- where id>:COL_1' using v_maxid; commit; -------------------------------------------------20100720 mntnOperMask := 'gl'; --從圖系統(tǒng)導(dǎo)入有效片數(shù)據(jù) 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); -- 將圖文系統(tǒng)過(guò)來(lái)的有效片數(shù)據(jù)，分析處理后保存到統(tǒng)計(jì)表中 --處理有效片 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.嘗試執(zhí)行操作? ??????????????????????????????????????????????????????????????????????????????????? ??? ???

SQL> select * from scanfilename@dss.m85; select * from scanfilename@dss.m85 ORA-02019: 未找到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接說(shuō)明 SQL> select * from xdoc_file@DSS.M85; select * from xdoc_file@DSS.M85 ORA-02019: 未找到遠(yuǎn)程數(shù)據(jù)庫(kù)的連接說(shuō)明 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.切換帳戶后使用分布式數(shù)據(jù)庫(kù)，提示監(jiān)聽(tīng)不可用???? ?????????????????????????????????????????????????? ????

SQL> select * from xdoc_file@DSS.M85 ORA-12514: TNS: 監(jiān)聽(tīng)進(jìn)程不能解析在連接描述符中給出的 SERVICE_NAME SQL> select * from scanfilename@DSS.M85; select * from scanfilename@DSS.M85 ORA-12514: TNS: 監(jiān)聽(tīng)進(jìn)程不能解析在連接描述符中給出的 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è)試???? ????????????????????????????????????????????????????????????????????????????? ??? ?

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: 監(jiān)聽(tīng)進(jìn)程不能解析在連接描述符中給出的 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) -->(原來(lái)為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，但實(shí)際上在Oracle 8使用service_name也可以正常使用。

三、更多參考

有關(guān)性能優(yōu)化請(qǐng)參考

??? Oracle 硬解析與軟解析

??? 共享池的調(diào)整與優(yōu)化(Shared pool Tuning)

??? Buffer cache 的調(diào)整與優(yōu)化(一)

??? Oracle 表緩存(caching table)的使用

?

有關(guān)ORACLE體系結(jié)構(gòu)請(qǐng)參考

??? Oracle 表空間與數(shù)據(jù)文件

??? Oracle 密碼文件

??? Oracle 參數(shù)文件

??? Oracle 聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)

??? Oracle 控制文件(CONTROLFILE)

? ?? Oracle 歸檔日志

??? Oracle 回滾(ROLLBACK)和撤銷(xiāo)(UNDO)

??? Oracle 數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)關(guān)閉過(guò)程

??? Oracle 10g SGA 的自動(dòng)化管理

??? Oracle 實(shí)例和Oracle數(shù)據(jù)庫(kù)(Oracle體系結(jié)構(gòu))

?

有關(guān)閃回特性請(qǐng)參考

??? Oracle 閃回特性(FLASHBACK DATABASE)

??? Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)

??? Oracle 閃回特性(Flashback Query、Flashback Table)

??? Oracle 閃回特性(Flashback Version、Flashback Transaction)

?

有關(guān)基于用戶管理的備份和備份恢復(fù)的概念請(qǐng)參考

??? Oracle 冷備份

??? Oracle 熱備份

??? Oracle 備份恢復(fù)概念

??? Oracle 實(shí)例恢復(fù)

??? Oracle 基于用戶管理恢復(fù)的處理 (詳細(xì)描述了介質(zhì)恢復(fù)及其處理)

??? SYSTEM 表空間管理及備份恢復(fù)

??? SYSAUX表空間管理及恢復(fù)

?

有關(guān)RMAN的備份恢復(fù)與管理請(qǐng)參考

??? RMAN 概述及其體系結(jié)構(gòu)

??? RMAN 配置、監(jiān)控與管理

??? RMAN 備份詳解

??? RMAN 還原與恢復(fù)

??? RMAN catalog 的創(chuàng)建和使用

??? 基于catalog 創(chuàng)建RMAN存儲(chǔ)腳本

基于catalog 的RMAN 備份與恢復(fù)

使用RMAN遷移文件系統(tǒng)數(shù)據(jù)庫(kù)到ASM

??? RMAN 備份路徑困惑(使用plus archivelog時(shí))

?

有關(guān)ORACLE故障請(qǐng)參考

??? ORA-32004 的錯(cuò)誤處理

??? ORA-01658 錯(cuò)誤

??? CRS-0215 錯(cuò)誤處理

??? ORA-00119，ORA-00132 錯(cuò)誤處理

??? 又一例SPFILE設(shè)置錯(cuò)誤導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)

??? 對(duì)參數(shù)FAST_START_MTTR_TARGET = 0 的誤解及設(shè)定

??? SPFILE 錯(cuò)誤導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)(ORA-01565)

?

有關(guān)ASM請(qǐng)參考

??? 創(chuàng)建ASM實(shí)例及ASM數(shù)據(jù)庫(kù)

??? ASM 磁盤(pán)、目錄的管理

??? 使用 ASMCMD 工具管理ASM目錄及文件

?

有關(guān)SQL/PLSQL請(qǐng)參考

??? SQLPlus 常用命令

??? 替代變量與SQL*Plus環(huán)境設(shè)置

??? 使用Uniread實(shí)現(xiàn)SQLplus翻頁(yè)功能

??? SQL 基礎(chǔ) -->SELECT 查詢

??? SQL 基礎(chǔ) --> NEW_VALUE 的使用

??? SQL 基礎(chǔ) --> 集合運(yùn)算(UNION 與UNION ALL)

??? SQL 基礎(chǔ) --> 常用函數(shù)

??? SQL 基礎(chǔ) --> 視圖(CREATE VIEW)

??? SQL 基礎(chǔ) --> 創(chuàng)建和管理表

??? SQL 基礎(chǔ) --> 多表查詢

??? SQL 基礎(chǔ) --> 過(guò)濾和排序

??? SQL 基礎(chǔ) --> 子查詢

??? SQL 基礎(chǔ) --> 分組與分組函數(shù)

??? SQL 基礎(chǔ) --> 層次化查詢(START BY ... CONNECT BY PRIOR)

??? SQL 基礎(chǔ) --> ROLLUP與CUBE運(yùn)算符實(shí)現(xiàn)數(shù)據(jù)匯總

??? PL/SQL --> 游標(biāo)

??? PL/SQL --> 異常處理(Exception)

??? PL/SQL --> 語(yǔ)言基礎(chǔ)

??? PL/SQL --> 流程控制

??? PL/SQL --> PL/SQL記錄

??? PL/SQL --> 包的創(chuàng)建與管理

??? PL/SQL --> 隱式游標(biāo)(SQL%FOUND)

??? PL/SQL --> 包重載、初始化

??? PL/SQL --> DBMS_DDL包的使用

??? PL/SQL --> DML 觸發(fā)器

??? PL/SQL --> INSTEAD OF 觸發(fā)器

??? PL/SQL --> 存儲(chǔ)過(guò)程

??? PL/SQL --> 函數(shù)

??? PL/SQL --> 動(dòng)態(tài)SQL

??? PL/SQL --> 動(dòng)態(tài)SQL的常見(jiàn)錯(cuò)誤

?

有關(guān)ORACLE其它特性

??? Oracle 常用目錄結(jié)構(gòu)(10g)

??? 使用OEM,SQL*Plus,iSQL*Plus 管理Oracle實(shí)例

??? 日志記錄模式(LOGGING 、FORCE LOGGING 、NOLOGGING)

??? 表段、索引段上的LOGGING與NOLOGGING

??? Oralce OMF 功能詳解

??? Oracle 用戶、對(duì)象權(quán)限、系統(tǒng)權(quán)限 ?

??? Oracle 角色、配置文件

??? Oracle 分區(qū)表

??? Oracle 外部表

??? 使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

??? 簇表及簇表管理(Index clustered tables)

??? 數(shù)據(jù)泵 EXPDP 導(dǎo)出工具的使用

??? 數(shù)據(jù)泵 IMPDP 導(dǎo)入工具的使用

??? 導(dǎo)入導(dǎo)出 Oracle 分區(qū)表數(shù)據(jù)

??? SQL*Loader使用方法

??? 啟用用戶進(jìn)程跟蹤

??? 配置非默認(rèn)端口的動(dòng)態(tài)服務(wù)注冊(cè)

??? 配置ORACLE 客戶端連接到數(shù)據(jù)庫(kù)

??? system sys，sysoper sysdba 的區(qū)別

??? ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME

??? Oracle 補(bǔ)丁全集 (Oracle 9i 10g 11g Path)

??? Oracle 10.2.0.1 升級(jí)到 10.2.0.4

??? Oracle 徹底 kill session

ORA-02019 錯(cuò)誤處理