配置靜態監聽解決ORA-12514錯誤的案例

配置靜態監聽解決ORA-12514錯誤的案例
今天做Linux下DG配置的時候，遇到一個現象，tnsname.ora檔案配置都正常，tnsping也正常，監聽也正常，但是仍然報ORA-12514錯誤： SQL> set lin 130 pages 130 SQL> select dest_id,error from v$archive_dest;    DEST_ID ERROR---------- -----------------------------------------------------------------         1         2 ORA-12514: TNS:listener does not currently know of service           requested in connect descriptor          3         4         5         6         7         8         9        10 --查看主庫監聽SQL> !lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:31:46 Copyright (c) 1991, 2010, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd)(PORT=1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 10.2.0.5.0 - ProductionStart Date                08-OCT-2014 10:34:51Uptime                    0 days 1 hr. 56 min. 54 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))Services Summary...Service "PLSExtProc" has 1 instance(s).  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "prd" has 1 instance(s).  Instance "prd", status READY, has 1 handler(s) for this service...Service "prdXDB" has 1 instance(s).  Instance "prd", status READY, has 1 handler(s) for this service...Service "prd_XPT" has 1 instance(s).  Instance "prd", status READY, has 1 handler(s) for this service...The command completed successfully --tnsnames.ora檔案內容# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools. EXTPROC_CONNECTION_DATA =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))    )    (CONNECT_DATA =      (SID = PLSExtProc)      (PRESENTATION = RO)    )  ) PRD =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = prd)    )  ) STD =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = prd)    )  ) --主庫tnsping測試網路服務名是否正常SQL> !tnsping prd TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:35 Copyright (c) 1997,  2010, Oracle.  All rights reserved. Used parameter files:  Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))OK (10 msec) SQL> !tnsping std TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:32:43 Copyright (c) 1997,  2010, Oracle.  All rights reserved. Used parameter files:  Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))OK (20 msec) --查看備庫監聽[oracle@std ~]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:29:52 Copyright (c) 1991, 2010, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 10.2.0.5.0 - ProductionStart Date                08-OCT-2014 09:41:41Uptime                    0 days 2 hr. 48 min. 11 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))Services Summary...Service "PLSExtProc" has 1 instance(s).  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "std" has 1 instance(s).  Instance "prd", status READY, has 1 handler(s) for this service...Service "std_XPT" has 1 instance(s).  Instance "prd", status READY, has 1 handler(s) for this service...The command completed successfully --查看備庫遠程歸檔路徑SQL> set lin 130 pages 130 SQL> col error for a20
SQL> select dest_id,error,status from v$archive_dest;    DEST_ID ERROR                STATUS---------- -------------------- ---------         1                      VALID         2                      VALID         3                      INACTIVE         4                      INACTIVE         5                      INACTIVE         6                      INACTIVE         7                      INACTIVE         8                      INACTIVE         9                      INACTIVE        10                      INACTIVE        11                      VALID --備庫tnsping測試網路伺服器名是否正常SQL> !tnsping prd TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:39:40 Copyright (c) 1997,  2010, Oracle.  All rights reserved. Used parameter files:  Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))OK (20 msec) SQL> !tnsping std TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 08-OCT-2014 12:39:44 Copyright (c) 1997,  2010, Oracle.  All rights reserved. Used parameter files:  Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = std)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prd)))OK (10 msec) tnsping測試下來也都是通的，這就比較奇怪了，但是ORA-12514錯誤很明顯是指監聽方面的問題，導致服務名無法解析，由於主備庫採用的是同一個執行個體名，因此兩邊的instance_name用的都是prd，乍一看好像很正常，但是解析確實出現了問題，那麼怎麼辦呢？ 

Oracle資料庫監聽非常慢，基本hang住故障處理

Oracle監聽之動態監聽與靜態監聽特點

Oracle 11g RAC 環境下單一實例非預設監聽及連接埠配置

Oracle 監聽器日誌配置與管理

Oracle錯誤- ORA-12514：TNS：無監聽程式

ORA-12514 監聽錯誤解決

Oracle監聽器出現的6種串連問題及其解決方案

Oracle LISTENER 未監聽到Oracle執行個體問題解決

設定 Oracle 監聽器密碼(LISTENER)

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁