Oracle靜態監聽中SID_NAME區分大小寫嗎？

潇湘隐者發表於2024-09-19
Oracle

如果靜態監聽中ORACLE_SID不注意大小寫的話，可能導致資料庫連線不上的問題。如下案例所示:

$ sqlplus system/***@GPSUAT

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 18 15:48:37 2024
Version 19.24.0.0.0

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

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4480
Additional information: -1989017509
Process ID: 0
Session ID: 0 Serial number: 0


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

檢查資料庫例項正常，監聽正常。使用tnsping檢查服務的網路是否正常，如下所示：

$ tnsping GPSUAT

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-NOV-2023 08:35:42

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.40)(PORT = 15021)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GPSUAT)))
OK (10 msec)

資料庫中tnsnames.ora的配置如下所示：

GPSUAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.40)(PORT = 15021))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GPSUAT)
    )
  )

此時，監聽的配置如下

$ more listener.ora 
GPS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = GPS))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.40)(PORT = 15021))
      (ADDRESS = (PROTOCOL = TCPS)(HOST =192.168.9.40)(PORT = 15022))
    )
  )

SID_LIST_GPS =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = GPS)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GPSUAT)
      (SID_NAME = GPS)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GPSUAT2)
      (SID_NAME = GPS)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    ) 
  )
SECURE_REGISTER_GPS = (IPC)
SECURE_CONTROL_GPS =(TCPS,IPC)
ADMIN_RESTRICTIONS_GPS = ON
DIAG_ADR_ENABLED_GPS = OFF

正確的配置如下（SID_NAME必須使用小寫，跟例項的大小寫一致。）

$ more listener.ora 
GPS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = GPS))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.40)(PORT = 15021))
      (ADDRESS = (PROTOCOL = TCPS)(HOST =192.168.9.40)(PORT = 15022))
    )
  )

SID_LIST_GPS =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = gps)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GPSUAT)
      (SID_NAME = gps)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GPSUAT2)
      (SID_NAME = gps)
      (ORACLE_HOME=/opt/oracle19c/product/19.3.0/db_1)
    ) 
  )
SECURE_REGISTER_GPS = (IPC)
SECURE_CONTROL_GPS =(TCPS,IPC)
ADMIN_RESTRICTIONS_GPS = ON

其實這篇部落格配置靜態監聽時謹防SID_NAME大小寫問題導致資料庫無法連線[1]中已經對這個問題進行了詳細的描述，這裡不做重複展開討論了，直接應用它的結論。如下所示：

在監聽靜態註冊的環境中，listener.ora檔案中寫的例項名，要區分大小寫，否則雖然遠端telnet 監聽埠是通的，但依舊會報出資料庫不可用的問題。

之所以會報出資料庫不可用問題，是由於遠端連線透過監聽連到的是另外一個庫，而該庫沒有啟動或者不存在。。。

總結：以後在處理問題中，注意區分例項名大小寫問題，在監聽層面，動態監聽和靜態監聽很容易混淆例項名問題，尤其當伺服器上存在大小寫不同的資料庫時候。

參考資料
[1]

1: http://blog.itpub.net/519536/viewspace-704704/

相關文章