配置靜態監聽時SID_NAME名字需要注意大小寫，同樣的名字不一樣的大小寫表示完全不同的資料庫例項。一旦靜態監聽的例項名字與對應的資料庫例項不一致時，便會出現使用連線串無法連線資料庫的問題。
將這個問題記錄在這裡，供大家參考。

1.檢視與靜態監聽相關的配置資訊
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat listener.ora
LISTENER=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROD)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = prod)
    )
)

特別注意，此處SID_NAME內容是小寫的“prod”，而資料庫例項的名字是大寫的“PROD”。

2.檢視tnsnames.ora中記錄的連線串
PROD@secdb1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cat tnsnames.ora
PROD =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = secdb1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
)

3.重啟監聽使上面錯誤的配置資訊生效
PROD@secdb1 /home/oracle$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-AUG-2011 21:44:43

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-AUG-2011 21:47:42
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD" has 1 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

特別注意這裡的提示資訊“Instance "prod"”，這裡清楚地顯示出是引號括起來的小寫prod例項名。

4.測試使用連線串連線資料庫時的故障現象
1）嘗試用SYS使用者以特權身份使用連線串登入資料庫
PROD@secdb1 /home/oracle$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:53:10 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

這裡提示的錯誤是“許可權不足”，潛在的含義便是沒有辦法登入資料庫。

2）嘗試用system普通使用者使用連線串登入資料庫
PROD@secdb1 /home/oracle$ sqlplus system/oracle@prod

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 11 21:48:22 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

這裡提示的錯誤是“Oracle資料庫不可用”，這個錯誤提示很直觀，因為靜態監聽中配置的是小寫的“prod”例項，而實際上資料庫例項名字是大寫的“PROD”，因此連線過程中無法找到對應的例項！

5.小結
本文給出故障現象的最根本的原因是：資料庫例項名本身是區分大小寫的，因此在配置靜態監聽配置SID_NAME時一定要注意大小寫。
當遇到資料庫無法連線的時候我們應該從多個角度去分析，不放過任何一個細節。

Good luck.

secooler
11.08.11

-- The End --

【LISTENER】配置靜態監聽時謹防SID_NAME大小寫問題導致資料庫無法連線

相關文章