oracle数据库今天突然无法进入，ora-12514：TNS:listener dose... Oracle-12514错误发生的几种原因
\u6c42\u52a9\uff1a\u6570\u636e\u5e93ORA-12514\uff1aTNS\uff1alistener does not.\u9519\u8bef\u662f\u6570\u636e\u5e93\u76d1\u542c\u6ca1\u6709\u542f\u52a8\u3002
\u9020\u6210ORA-12560: TNS: \u534f\u8bae\u9002\u914d\u5668\u9519\u8bef\u7684\u95ee\u9898\u7684\u539f\u56e0\u6709\u4e09\u4e2a\uff1a
1.\u76d1\u542c\u670d\u52a1\u6ca1\u6709\u8d77\u8d77\u6765\u3002windows\u5e73\u53f0\u4e2a\u4e00\u5982\u4e0b\u64cd\u4f5c\uff1a\u5f00\u59cb---\u7a0b\u5e8f---\u7ba1\u7406\u5de5\u5177---\u670d\u52a1\uff0c\u6253\u5f00\u670d\u52a1\u9762\u677f\uff0c
\u542f\u52a8oraclehome92TNSlistener\u670d\u52a1\u3002
2.database instance\u6ca1\u6709\u8d77\u8d77\u6765\u3002windows\u5e73\u53f0\u5982\u4e0b\u64cd\u4f5c\uff1a\u5f00\u59cb---\u7a0b\u5e8f---\u7ba1\u7406\u5de5\u5177---\u670d\u52a1\uff0c\u6253\u5f00\u670d\u52a1
\u9762\u677f\uff0c\u542f\u52a8oracleserviceXXXX,XXXX\u5c31\u662f\u4f60\u7684database SID.
3.\u6ce8\u518c\u8868\u95ee\u9898\u3002regedit\uff0c\u7136\u540e\u8fdb\u5165HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\u5c06\u8be5\u73af\u5883\u53d8\u91cfORACLE_SI
D\u8bbe\u7f6e\u4e3aXXXX,XXXX\u5c31\u662f\u4f60\u7684database SID.\u6216\u8005\u53f3\u51e0\u6211\u7684\u7535\u8111\uff0c\u5c5e\u6027--\u9ad8\u7ea7--\u73af\u5883\u53d8\u91cf---\u7cfb\u7edf\u53d8\u91cf--\u65b0\u5efa
\uff0c\u53d8\u91cf\u540d=oracle_sid,\u53d8\u91cf\u503c=XXXX,XXXX\u5c31\u662f\u4f60\u7684database SID.\u6216\u8005\u8fdb\u5165sqlplus\u524d\uff0c\u5728command line\u4e0b
\u8f93set oracle_sid=XXXX,XXXX\u5c31\u662f\u4f60\u7684database SID.
\u7ecf\u8fc7\u4ee5\u4e0a\u6b65\u9aa4\uff0c\u5c31\u53ef\u4ee5\u89e3\u51b3\u95ee\u9898\u3002
ora-12514\u7684\u9519\u8bef\u7684\u539f\u56e0\u6709\u5f88\u591a\uff0c\u4f46\u65e0\u5916\u4e4e\u8fd9\u51e0\u79cd\uff1a
1.ora-12514: TNS: \u6ca1\u6709\u76d1\u542c\u5668
\u663e\u800c\u6613\u89c1\uff0c\u670d\u52a1\u5668\u7aef\u7684\u76d1\u542c\u5668\u6ca1\u6709\u542f\u52a8\uff0c\u53e6\u5916\u68c0\u67e5\u5ba2\u6237\u7aefIP\u5730\u5740\u6216\u7aef\u53e3\u586b\u5199\u662f\u5426\u6b63\u786e\u3002\u542f\u52a8\u76d1\u542c\u5668\uff1a
$ lsnrctl start\u6216C:lsnrctl start
2\u3001ora-12514: TNS: \u65e0\u6cd5\u5904\u7406\u670d\u52a1\u540d
\u68c0\u67e5\u8f93\u5165\u7684\u670d\u52a1\u540d\u4e0e\u914d\u7f6e\u7684\u670d\u52a1\u540d\u662f\u5426\u4e00\u81f4\u3002\u53e6\u5916\u6ce8\u610f\u751f\u6210\u7684\u672c\u5730\u670d\u52a1\u540d\u6587\u4ef6(Windows\u4e0b\u5982D:oracleora92networkadmin tnsnames.ora\uff0cLinux/Unix\u4e0b/network/admin/tnsnames.ora)\u91cc\u6bcf\u9879\u670d\u52a1\u7684\u9996\u884c\u670d\u52a1\u540d\u79f0\u524d\u4e0d\u80fd\u6709\u7a7a\u683c\u3002
3\u3001ora-12514: TNS: \u76d1\u542c\u8fdb\u7a0b\u4e0d\u80fd\u89e3\u6790\u5728\u8fde\u63a5\u63cf\u8ff0\u7b26\u4e2d\u7ed9\u51fa\u7684SERVICE_NAME
\u6253\u5f00NetManager\uff0c\u9009\u4e2d\u670d\u52a1\u540d\u79f0\uff0c\u68c0\u67e5\u670d\u52a1\u6807\u8bc6\u680f\u91cc\u7684\u670d\u52a1\u540d\u8f93\u5165\u662f\u5426\u6b63\u786e\u3002\u8be5\u670d\u52a1\u540d\u5fc5\u987b\u4e0e\u670d\u52a1\u5668\u7aef\u76d1\u542c\u5668\u914d\u7f6e\u7684\u5168\u5c40\u6570\u636e\u5e93\u540d\u4e00\u81f4\u3002\u540c\u65f6\u68c0\u67e5sqlnet.ora\uff0c\u4f8b\u5982\u5982\u679c\u60f3\u8981\u91c7\u7528\u7b80\u4fbf\u8fde\u63a5\u65b9\u5f0f\u8fde\u63a5\u5c31\u9700\u8981\u5728NAMES.DIRECTORY_PATH\u53c2\u6570\u4e2d\u6dfb\u52a0EZCONNECT\u3002
\u62d3\u5c55\u77e5\u8bc6\uff1a
ora-12514\uff1aTNS\uff1a\u76d1\u542c\u7a0b\u5e8f\u5f53\u524d\u65e0\u6cd5\u8bc6\u522b\u94fe\u63a5\u63cf\u8ff0\u7b26\u4e2d\u8bf7\u6c42\u7684\u670d\u52a1
\u8be5\u95ee\u9898\u662f\u7531\u4e8e\u7f3a\u5c11\u76d1\u542c\u5668\u7684SID_LIST\u63cf\u8ff0\u9879\u5f15\u8d77\u7684\uff0c\u91c7\u7528netca\u8fdb\u884c\u914d\u7f6e\u7684\u65f6\u5019\u7ecf\u5e38\u4f1a\u9047\u5230\u8be5\u95ee\u9898\uff0clistener.ora\u793a\u4f8b\u5982\u4e0b
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0.4)
(PROGRAM = extproc))
(SID_DESC =
(GLOBAL_DBNAME = SAMPLE.COM)
(ORACLE_HOME = /opt/oracle/product/9.2.0.4)
SID_NAME = SAMPLE)))LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tcy.com)(PORT = 1521)))
这种情况我遇到过几次，一般是因为用32位的客户端去链接64位的oracle服务端到账的不兼容问题，在这种情况下，我们需要在本机修改2处环境变量。
修改一：修改环境变量ORACLE_HOME得值配置，要指向oracle\product\10.2.0\client_1
修改二：
在监听文件listener.ora中添加：
(SID_DESC =
(GLOBAL_DBNAME = 实例名)
(ORACLE_HOME = oracle\product\10.2.0\client_1 )
(SID_NAME = 实例名)
)
以上问题，主要是oracle10g版本容易出现，11g版本目前暂时没遇到过。
看看你的 $ORACLE_HOME/network/admin 下的 tnsnames.ora文件配置情况，还有在问问题之前最好讲清楚你使用的平台和ORACLE版本
使用网络配置向导重新配置监听试一试
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_2)
(PROGRAM = extproc)
)
(SID_DESC =
(GOLBAL_DBNAME= orcl)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_2)
(SID_NAME=orcl)
)
)
这样试试
看样子你用的是动态注册侦听,如果真是这样,最简单的尝试是 先把侦听服务重启,然后再重启数据库服务, 再试一下
绛旓細鏈嶅姟娌℃湁寮璧锋潵鍛 鍒版帶鍒堕潰鏉-銆嬬鐞嗗伐鍏-銆嬫湇鍔￠噷闈紝鎶OracleserviceORCL鍜孫racleOraDb10g_home1TNSListener鍚姩鍗冲彲
绛旓細oracle redhat as 瑙ｅ喅鏂规硶:鏇存敼涓涓媗inux鐢ㄦ埛鐨勭幆澧冨彉閲廜RACLE_SID灏卞彲浠ヤ簡 閿欒 ORA : shared memory realm does not exist 鎴戣瘯鍥捐繛鎺ユ暟鎹簱鏃剁殑ORA 鍜 ORA 閿欒淇℃伅 瀹冩绘槸鏄剧ず杩欐牱鐨勪俊鎭 ORA Oracle not available 鍜 ORA shared memory realm does not exist 涓嶈兘杩炴帴鍒版垜鐨Oracle 鏁版嵁搴...
绛旓細1銆佽繘鍏MD锛屾墽琛宻et ORACLE_SID=fbms锛岀‘淇濊繛鎺ュ埌姝ｇ‘鐨凷ID锛2銆佽繍琛宻qlplus "/as sysdba"SQL>shutdown immediate 鍋滄鏈嶅姟 SQL>startup 鍚姩鏈嶅姟锛岃瀵熷惎鍔ㄦ椂鏈夋棤鏁版嵁鏂囦欢鍔犺浇鎶ラ敊锛屽苟璁颁綇鍑洪敊鏁版嵁鏂囦欢鏍囧彿 SQL>shutdown immediate 鍐嶆鍋滄鏈嶅姟 SQL>startup mount SQL> recover datafile 2 ...
绛旓細鏈嶅姟琚叧浜嗗惂 鍒縺鍔 鍘绘帶鍒堕潰鏉块噷鐪嬬湅
绛旓細浣犳妸鏉姣掕蒋浠跺拰闃茬伀澧欓兘閫鍑烘潵鐧婚檰涓涓嬭瘯涓璇曪紝oracle璺熸煇浜涙潃姣掕蒋浠跺啿绐
绛旓細2銆佸叾娆★紝瀹屾垚涓婅堪姝ラ鍚庯紝杞埌Orcale骞跺惎鍔ㄤ笌Oracle鐩稿叧鐨勬墍鏈夋湇鍔★紝濡備笅鍥炬墍绀恒3銆佹帴鐫锛屽畬鎴愪笂杩版楠ゅ悗锛岃浆鍒oracle11g瀹夎鐩綍骞舵壘鍒颁睛鍚枃浠垛 listener.ora鈥濓紝濡備笅鍥炬墍绀恒4銆佺劧鍚庯紝瀹屾垚涓婅堪姝ラ鍚庯紝鎵撳紑鈥 listener.ora鈥濇枃浠讹紝娣诲姞鎴栦慨鏀筍ID_NAME鍚嶇О涓鏁版嵁搴瀹炰緥鍚嶇О锛屽涓嬪浘鎵绀恒5銆佹渶鍚庯紝...
绛旓細鏌ヨ浠ヤ笅淇℃伅锛歝rsctl check cluster -all 浠ュ強濡備笅淇℃伅锛歴qlplus / as sysasm select NAME , STATE FROM V$ASM_DISKGROUP;--涓嶈繃濡傛灉浣犵殑鏁版嵁搴撲笉鏄疌luster鑰屾槸SingleNode鐨勮瘽锛屾鏌ワ細1銆佹暟鎹簱鏈嶅姟鍣ㄦ墍鍦ㄦ満鍣ㄥ悕鏄惁鏀硅繃锛2銆佸湪64Bit绯荤粺涓婅鐨32Bit鐨Oracle鏁版嵁搴锛熺増鏈淇濇寔涓鑷淬3銆侀棯鍥炲尯鏄惁宸叉弧...
绛旓細杩愯$ORACLE_HOME/rdbms/admin/utlrp.sql鍙互缂栬瘧鎵鏈夌殑鍖咃紝濡傛灉浣犵粡甯稿彂鐢熷寘澶辨晥鐨勪簨鎯咃紝鏈夊繀瑕佸畾鏃跺仛涓狣BA浣滀笟鏉ヨ繍琛岃繖涓紪璇戝伐浣滐紝鑷充簬澶辨晥鐨勫師鍥狅紝涓鑸潵璇村彧瑕佹煇涓畠鐢ㄥ埌鐨凮BJECT鐨勪慨鏀归兘浼氬紩璧峰畠澶辨晥鐨勶紝鎶婂璁″紑鍚幓鎵惧叿浣揝QL璇彞鍚э紝娌℃湁浠涔堝お濂界殑鍔炴硶銆
绛旓細oracle ORA-01033闂鍏蜂綋瑙ｅ喅姝ラ濡備笅锛1銆佷互sys鐧诲綍涓簊ysdbasqlplus /NOLOG鎴栬咃紙sqlplus sys/sys@localhost/orcl as sysdba锛寃indow绯荤粺涓嬮儴缃茬殑 oracle鏁版嵁搴鎬绘槸鍚勭bug锛屾垜鐨勫繀椤绘坊鍔爄p鍦板潃鎸囧悜鎵嶈兘杩炴帴锛夈2銆佺櫥褰曞埌sys鐢ㄦ埛鍚庯紝鍏抽棴鏁版嵁搴擄紝杈撳叆鍛戒护锛歴hutdown锛涘洖杞︼紝缁撴灉鎻愮ず鏁版嵁搴撴湭鎵撳紑銆3銆佸惎鍔...
绛旓細棣栧厛锛寀ndo琛ㄧ┖闂存弧鏄甯哥殑锛oracle鑷劧浼氶噸鐢ㄦ垨鑰呮墿灞曞畠锛屼竴鑸笉鐢ㄧ瀹冦傜劧鍚庯紝鐜板湪瑕佽В鍐崇殑璇濓紝闇瑕佸厛鎶妘ndo tablespace璁剧疆鎴愭墜鍔紝鍚姩鏁版嵁搴锛屽垱寤烘柊鐨剈ndo tablespace銆傛妸鏂扮殑璁剧疆鎴愰粯璁ょ殑銆傚亣璁句綘鐨勫簱鐜板湪鏄痬ounted鐘舵 1 鍒涘缓PFILE(濡傛灉宸叉湁灏辨槸鏇存柊)SQL>create pfile from spfile;2 鍏抽棴鏁版嵁搴 ...
