ORA-12514 TNS: 監聽器目前不知道連線描述區中要求的服務 之 Service & SID 傻傻分不清楚
RojerChen.2018.11.05
目前我 C# 的程式連結 Oracle 都是透過 Oracle.ManagedDataAccess 來連結的，這樣的好處在於不需要額外安裝肥美的 Oracle Client，只不過最近要連線另外一台 DB 的時候卻遇到了這樣的訊息：
ORA-12514: TNS: 監聽器目前不知道連線描述區中要求的服務
目前我手邊兩台 Oracle 的版本為 11g 與 12c，由於 12c 可以正常連線，但是為什麼 11g 就不行呢？是連線字串的問題？元件版本的問題？32位元還是64位元的問題嗎？嘗試了很多次，最後發現到原來是連線字串用錯了。
以下是我原本程式的連線字串
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));
User Id=myUsername;Password=myPassword;
最後成功的連線字串
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MyOracleSID)));
User Id=myUsername;Password=myPassword;
主要的問題在於 SERVICE_NAME=MyOracleSID 與 SID=MyOracleSID 的差異，會發生這樣的原因還是 Oracle 上面的設定所導致的，由於 Oracle 環境不是我裝的，所以我對於是否有使用 SERVICE 我當然就不知道囉。
SID & Service
SID
Oracle System Identifier (SID) The system identifier (SID) is a unique name for an Oracle database instance on a specific host. On UNIX and Linux, Oracle Database uses the SID and Oracle home values to create a key to shared memory. Also, the SID is used by default to locate the parameter file, which is used to locate relevant files such as the database control files.
On most platforms, the ORACLESID environment variable sets the SID, whereas the ORACLEHOME variable sets the Oracle home. When connecting to an instance, clients can specify the SID in an Oracle Net connection or use a net service name. Oracle Database converts a service name into an ORACLEHOME and ORACLESID.
Service Name
In the context of net services, a service is a set of one or more database instances. A service name is a logical representation of a service used for client connections.
When a client connects to a listener, it requests a connection to a service. When a database instance starts, it registers itself with a listener as providing one or more services by name. Thus, the listener acts as a mediator between the client and instances and routes the connection request to the right place.
A single service, as known by a listener, can identify one or more database instances. Also, a single database instance can register one or more services with a listener. Clients connecting to a service need not specify which instance they require.
心得
在尋找問題的過程中，發現到另外一種方式，就是不透過安裝 Oracle Client 也可以連線的方式，詳細可以參考下面這篇文章。
將這些相關的 DLL 放置在 BIN 目錄底下後，只需要參考 Oracle.DataAccess.DLL 後，就可以連資料庫了。
其他
查看 Oracle 版本
SELECT * FROM V$VERSION;
0 意見:
張貼留言