配置ORACLE 客户端连接到数据库
??????
--=================================
-- 配置ORACLE 客戶端連接到數(shù)據(jù)庫
--=================================
?
??? Oracle 客戶端連接到Oracle 數(shù)據(jù)庫服務(wù)器貌似不同于SQL serve中的網(wǎng)絡(luò)配置，其實(shí)不然，只不過所有的SQL server 運(yùn)行于Windows平臺，故很多配置直接集成到了操作系統(tǒng)之中。所以無需配置客戶端即可連接到服務(wù)器。Oracle 客戶端連接到數(shù)據(jù)庫依賴于Oracle Net。Oracle提供了很多基于客戶端或服務(wù)器的配置工具，需要搞清的是Oracle Net 中的相關(guān)術(shù)語。術(shù)語明白了，其實(shí)配置方法與SQL server 大同小異。
?
一、什么是Oracle Net
??? Oracle Net 用于客戶端和服務(wù)器之間創(chuàng)建一個連接會話，并負(fù)責(zé)維護(hù)該會話。
??? Oracle Net 在客戶端作為應(yīng)用程序的一個后臺進(jìn)程組件，而在服務(wù)器端則包含稱為偵聽器的活動進(jìn)程，
負(fù)責(zé)客戶端和服務(wù)器之間的交互。
??? Oracle Net 同時也支持異構(gòu)數(shù)據(jù)庫的連接，如連接到Sybase，Informix，DB2，SQL Server等
??? 通常基于下列配置來實(shí)現(xiàn)
??????? 網(wǎng)絡(luò)配置(網(wǎng)絡(luò)必須是聯(lián)通的)
??????? 節(jié)點(diǎn)所在的位置(IP/Hostname)
??????? 應(yīng)用程序
??????? 所使用的協(xié)議(TCP/IP、/TCP/IP with SSL、SDP、Named Pipes)
???????
??? Oracle Net支持的連接類型
??????? 客戶端-服務(wù)器模式(如SQLPlus)
??????? Java 應(yīng)用程序(JDBC等)
??????? Web 客戶端應(yīng)用程序
使用基于Web的應(yīng)用程序(App Server)作為中間件來實(shí)現(xiàn)，可以配置JDBC Oracle Call Interface (OCI) driver 或thin JDBC driver
??????????? 通過HTTP直接連接到Oracle 服務(wù)器，如OEM
???????????
???
??? Oracle Net實(shí)現(xiàn)機(jī)制
??????? 用戶發(fā)出連接請求實(shí)現(xiàn)與用戶進(jìn)程進(jìn)行交互
??????? 用戶進(jìn)程通過Oracle Net 來與服務(wù)器進(jìn)程交互
??????? 服務(wù)器進(jìn)程則與實(shí)例進(jìn)行交互(由Oracle Net維護(hù))
??????? 實(shí)例通過后臺進(jìn)程交互來完成數(shù)據(jù)庫的讀寫操作
???
??? Oracle Net 連接方式
??????? 本地客戶端連接的數(shù)據(jù)庫(即客戶端與數(shù)據(jù)庫位于同一臺服務(wù)器)。
??????????? 本地連接同樣使用到了Oracle Net，因?yàn)槿魏闻c數(shù)據(jù)的交互都通過Oracle Net來完成。
不需要使用到Listener，因?yàn)楸镜剡B接使用的是IPC協(xié)議，而IPC協(xié)議允許主機(jī)內(nèi)進(jìn)程間相互通信，由操作系統(tǒng)提供。
??????????? 本地連接不需要進(jìn)行任何配置，且本地服務(wù)器上可以運(yùn)行若干個實(shí)例。
??????????? 本地連接是唯一一個不需要偵聽器的連接類型。
??????????? 連接方法：CONNECT username/password
???????????
??????? 所有非本地客戶端連接到數(shù)據(jù)庫
??????????? 服務(wù)器端啟用Listener進(jìn)程
??????????? 客戶端通過TNSNAME或Easy connect或LDAP等名稱解析方式來解析連接字符串
??????????? 一旦連接成功，即是Listener服務(wù)停止，不影響該會話。
???????????????
??? Oracle Net Listener(偵聽器)
??????? 運(yùn)行于服務(wù)器端，用于偵聽所有來自客戶端的連接請求，并提供處理數(shù)據(jù)庫服務(wù)方面的請求
??????????? 位置：$ORACLE_HOME/network/admin/listerer.ora
?--對應(yīng)于netmgr中(GUI界面)的Listeners項(xiàng)下建立的缺省偵聽器(Listener)
??????????????? ? $ORACLE_HOME/network/admin/sqlnet.ora??
?--對應(yīng)于netmgr中(GUI界面)的Profile
??????? 服務(wù)器端配置tnsnames
??????????? 位置：ORACLE_HOME/network/admin/tnsnames.ora
--對應(yīng)于netmgr中(GUI界面)的Service Naming
??????? Listener提供的信息包括，IP地址、端口號、協(xié)議、服務(wù)名(service_name)等
??????? 一個Listener能夠?yàn)槎鄠€數(shù)據(jù)庫實(shí)例提供服務(wù)
?
二、客戶端到服務(wù)器建立會話的過程及相關(guān)術(shù)語
??? 執(zhí)行如下的命令來建立連接
??????? SQL> CONNECT username/password@net_service_name??
???????
??????? 連接字符串(connect string)：username/password@net_service_name
包含了用戶名、密碼、連接標(biāo)識符
??????????? 如：SQL> conn scott/tiger@orcl
??????????? 字符串分解
??????????????? 用戶名scott
??????????????? 密碼為tiger
??????????????? "/"??? 用于分割用戶與密碼
??????????????? "@"?? 指示網(wǎng)絡(luò)連接所需的用戶進(jìn)程
??????????????? orcl? 連接標(biāo)識符
???????
??????? 連接標(biāo)識符(connect identifier)
??????????? @后面的即為連接標(biāo)識符，如上例中的orcl。
??????????? 連接標(biāo)識符的名字是一個映射到連接描述符的簡化
??????????? 連接標(biāo)識符可以為網(wǎng)絡(luò)服務(wù)名/別名、數(shù)據(jù)庫服務(wù)名等
(net service name, database service name, or net service alias.)
???????????
??????? 連接描述符(connect discriptor)
??????????? 使用一種特殊格式來描述連接的具體信息，信息包含了、數(shù)據(jù)庫所在的位置(IP地址)、
服務(wù)名、端口號等
??????????? 如tnsnames中的：
??????????? orcl =? --網(wǎng)絡(luò)服務(wù)名,數(shù)據(jù)庫別名,等同于連接標(biāo)識符(屏蔽客戶端如何連接到服務(wù)器端的細(xì)節(jié))
??????????? ? (DESCRIPTION =???????????
??????????????? (ADDRESS_LIST =
??????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
--IP地址,端口號,協(xié)議
??????????????? )
??????????????? (CONNECT_DATA =
??????????????? ? (SERVICE_NAME = orcl.robinson.com)? --服務(wù)名
??????????????? )
??????????? ? )
??????????? 以上所有DESCRIPTION內(nèi)的信息即為連接描述信息
??????????? 描述符之上等于號左邊的可以為網(wǎng)絡(luò)服務(wù)名，網(wǎng)絡(luò)服務(wù)別名等，此等同于連接標(biāo)識符。
???????????
??????? 服務(wù)名(service_name)
客戶端連接到實(shí)例的服務(wù)名，可以為該參數(shù)指定一個或多個服務(wù)名。該參數(shù)從i引入，service_name通常可以使用SID代替。
9i之后，Oracle推薦使用service_name而不是SID。可以通過定義多不不同的服務(wù)名來區(qū)分不同的用戶連接，該參數(shù)缺省的格式為db_name.db_name。
實(shí)例將所定義的服務(wù)名注冊到偵聽器，當(dāng)客戶端請求服務(wù)時，偵聽器根據(jù)服務(wù)名決定將使用哪個實(shí)例提供所請求的服務(wù)并與之建立連接
??????????? 基于相同的數(shù)據(jù)庫可以定義多不不同的服務(wù)名來區(qū)分不同的使用情況
??????????? 如定義service_name為
??????????????? service_names = sales.robinson.com,hr.robinsn.com
??????????? 則sales部門通過sales服務(wù)名來建立連接，hr部門通過hr服務(wù)名來建立連接，
而無須關(guān)心連接的是哪一個數(shù)據(jù)庫。
???????
??????? 演示連接過程(非本地連接)
??????????? 環(huán)境：服務(wù)器為RHEL 5 + Oracle 10g ，客戶端Winxp + 10g Client
??????????? 查看服務(wù)器listener是否啟動
??????????? [oracle@robinson admin]$ lsnrctl?? --啟動lsnrctl 偵聽器程序
?
??????????? LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-SEP-2010 13:31:06
?
??????? ??? Copyright (c) 1991, 2005, Oracle.? All rights reserved.
?
??????????? Welcome to LSNRCTL, type "help" for information.
?
??????????? LSNRCTL> status????? --查看偵聽器狀態(tài)，偵聽器沒有啟動
??????????? Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
??????????? TNS-12541: TNS:no listener
??????????? ?TNS-12560: TNS:protocol adapter error
??????????? ? TNS-00511: No listener
??????????? ?? Linux Error: 111: Connection refused
??????????? ??
??????????? --啟動缺省的偵聽器
??????????? --在下面列出的信息中沒有偵聽器的路徑及listener.ora文件，此處listener.ora 不存在
??????????? LSNRCTL> start????
??????????? Starting /u01/app/oracle/10g/bin/tnslsnr: please wait...
?
??????????? TNSLSNR for Linux: Version 10.2.0.1.0 - Production
??????????? Log messages written to /u01/app/oracle/10g/network/log/listener.log
??????????? Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=robinson.com)(PORT=1521)))
???????????????????????????????
??????????? Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
??????????? STATUS of the LISTENER
??????????? ------------------------
??????????? Alias???????????????????? LISTENER
??????????? Version?????????????????? TNSLSNR for Linux: Version 10.2.0.1.0 - Production
??????????? Start Date??????????????? 03-SEP-2010 13:31:14
??????????? Uptime??????????????????? 0 days 0 hr. 0 min. 0 sec
??????????? Trace Level?????????????? off
??????????? Security????????????????? ON: Local OS Authentication
??????????? SNMP????????????????????? OFF
??????????? Listener Log File???????? /u01/app/oracle/10g/network/log/listener.log
??????????? Listening Endpoints Summary...
??????????? ? (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=robinson.com)(PORT=1521)))
??????????? The listener supports no services
??????????? The command completed successfully
?
??????????? LSNRCTL> service? --已經(jīng)看到了已注冊的實(shí)例信息，因?yàn)?span lang="en-us">g支持動態(tài)注冊
??????????? Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
??????????? Services Summary...
??????????? Service "orcl.robinson.com" has 1 instance(s).
??????????? ? Instance "orcl", status READY, has 1 handler(s) for this service...
??????????????? Handler(s):
??????????????? ? "DEDICATED" established:0 refused:0 state:ready
??????????????????? ?LOCAL SERVER
??????????? Service "orclXDB.robinson.com" has 1 instance(s).
??????????? ? Instance "orcl", status READY, has 1 handler(s) for this service...
??????????????? Handler(s):
??????????????? ? "D000" established:0 refused:0 current:0 max:1022 state:ready
??????????????????? ?DISPATCHER <machine: robinson, pid: 3246>
??????????????????? ?(ADDRESS=(PROTOCOL=tcp)(HOST=robinson.com)(PORT=51473))
??????????? Service "orcl_XPT.robinson.com" has 1 instance(s).
??????????? ? Instance "orcl", status READY, has 1 handler(s) for this service...
??????? ??????? Handler(s):
??????????????? ? "DEDICATED" established:0 refused:0 state:ready
??????????????????? ?LOCAL SERVER
??????????? The command completed successfully?
?
??????????? --查看客戶端tnsnames.ora的信息
??????????? C:/>type F:/oracle/product/10.2.0/client_1/NETWORK/ADMIN/tnsnames.ora
??????????? # tnsnames.ora Network Configuration File: F:/oracle/product/10.2.0/client_1/NET
??????????? WORK/ADMIN/tnsnames.ora
??????????? # Generated by Oracle configuration tools.
??????????? orcl =
??????????? ? (DESCRIPTION =
??????????????? (ADDRESS_LIST =
??????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????? )
??????????? ??? (CONNECT_DATA =
??????????????? ? (SERVICE_NAME = orcl.robinson.com)
??????????????? )
??????????? ? )
??????????? C:/>sqlplus /nolog
?
??????????? SQL*Plus: Release 10.2.0.1.0 - Production on 星期五9月3 13:42:02 2010
?
??????????? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??????????? SQL> conn scott/tiger@orcl
??????????? Connected.
??????????? --將tnsnames.ora中服務(wù)別名改為oral后再連接，可以正常連接，即網(wǎng)絡(luò)服務(wù)名可以隨便取。
??????????? SQL> conn system/redhat@oral
??????????? Connected.
???????????
??????????? --查看數(shù)據(jù)庫中的服務(wù)名?????????
??????????? SQL> show parameter service_names;
?
??????????? NAME???????????????????????????????? TYPE??????? VALUE
??????????? ------------------------------------ ----------- --------------------------
??????????? service_names??????????????????????? string????? orcl.robinson.com
???????????
三、服務(wù)注冊與名稱解析????? ???
???
??? 服務(wù)注冊(service registration)
??????? 實(shí)例將數(shù)據(jù)庫將所提供的服務(wù)名及相關(guān)信息告知listener的過程稱之為服務(wù)注冊
??????? 注冊通常包含下列信息
??????????? 數(shù)據(jù)庫的服務(wù)名
??????????? 實(shí)例名
??????????? 可用的服務(wù)處理程序(service handlers)，用于調(diào)度(共享模式)和派生子程序(專用模式)
??????????? 端口號等
???????
??????? 注冊方法
??????????? 靜態(tài)注冊: 指將實(shí)例的相關(guān)信息手動告知listener偵聽器，可以使用netmgr,netca,oem以及直接vi listener.ora文件來實(shí)現(xiàn)
??????????????? --下面是通過netmgr配置后產(chǎn)生的listener.ora文件的內(nèi)容
??????????????? [oracle@robinson ~]$ cat $ORACLE_HOME/network/admin/listener.ora
??????????????? # listener.ora Network Configuration File: /u01/app/oracle/10g/network/admin/listener.ora
??????????????? # Generated by Oracle configuration tools.
?
??????????????? SID_LIST_LISTENER =?????? -->這里定義LISTENER進(jìn)程提供的數(shù)據(jù)庫服務(wù)列表
??????????????? ? (SID_LIST =
??????????????????? (SID_DESC =
??????????????????? ? (GLOBAL_DBNAME = orcl.robinson.com)
??????????????????? ? (ORACLE_HOME = /u01/app/oracle/10g)
??????????????????? ? (SID_NAME = ORCL)
??????????????????? )
??????????????? ? )
?
??????????????? LISTENER =?????????????? -->監(jiān)聽器的名字，一臺數(shù)據(jù)庫可以有多個監(jiān)聽器
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS = (PROTOCOL = TCP)(HOST = robinson)(PORT = 1521))
??????????????? ? )
??????????????? ?
動態(tài)注冊：實(shí)例在啟動時使用PMON進(jìn)程自動將instance_name和service_names等信息注冊到已啟動的缺省偵聽器listener.ora,在9i之后,不需要任何配置即可實(shí)現(xiàn)動態(tài)注冊.實(shí)例啟動后會自動在本地主機(jī)默認(rèn)的號端口上查找偵聽器,并在查找到時進(jìn)行注冊
??? ??????????? 可以在實(shí)例啟動后的任何時間段執(zhí)行下面命令實(shí)現(xiàn)重新注冊
??????????????????? alter system register;
???????????????????
??????????????????? 如果偵聽器在非默認(rèn)端口上運(yùn)行，可以通過設(shè)置local_listener參數(shù)來制定偵聽器所在的位置并手動進(jìn)行注冊
??????????????????? SQL> alter system set local_listener = 'list2';
??????????????????? SQL> alter system register;
???????????????????
??????????????? 建議配置兩個參數(shù)保證成功注冊：service_names,instance_name
??????????????????? service_names缺省為global database name
??????????????????? alter system set service_names = 'testdb.robinson.com' scope =spfile;
??????????????????? alter system set instance_name = 'testdb' scope = spfile;
???????????
??????????????? --下面啟動缺省的偵聽器來實(shí)現(xiàn)動態(tài)注冊
??????????????? [oracle@robinson ~]$ lsnrctl start
?
??? ??????????? LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-SEP-2010 16:30:04
?
??????????????? Copyright (c) 1991, 2005, Oracle.? All rights reserved.
?
??????????????? Starting /u01/app/oracle/10g/bin/tnslsnr: please wait...
?
??????????????? TNSLSNR for Linux: Version 10.2.0.1.0 - Production
??????????????? System parameter file is /u01/app/oracle/10g/network/admin/listener.ora
??????????????? Log messages written to /u01/app/oracle/10g/network/log/listener.log
??????????????? Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=robinson.com)(PORT=1521)))
?
??????????????? Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=robinson)(PORT=1521)))
??????????????? STATUS of the LISTENER
??????????????? -----------------------------部分信息省略---------------------------------------
???????????????
??????????????? Listener Parameter File?? /u01/app/oracle/10g/network/admin/listener.ora
??????????? ??? Listener Log File???????? /u01/app/oracle/10g/network/log/listener.log
??????????????? Listening Endpoints Summary...
??????????????? ? (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=robinson.com)(PORT=1521)))
??????????????? Services Summary...
??????????????? Service "orcl.robinson.com" has 1 instance(s).
--啟動了orcl.robinson.com服務(wù)且為靜態(tài)注冊
??????????????? ? Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
??????????????? The command completed successfully
?
??????????????? [oracle@robinson ~]$ export ORACLE_SID=testdb? --設(shè)置ORACLE_SID并啟動testdb
??????????????? [oracle@robinson ~]$ sqlplus /nolog
?
??????????????? SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 3 16:33:00 2010
?
??????????????? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??????????????? SQL> conn / as sysdba
??????????????? Connected to an idle instance.
??????????????? SQL> startup
??????????????? ORACLE instance started.
?
??????????????? Total System Global Area? 469762048 bytes
??????????????? Fixed Size????????????????? 1220048 bytes
??????????????? Variable Size???????????? 138412592 bytes
??????????????? Database Buffers????????? 327155712 bytes
??????????????? Redo Buffers??????????????? 2973696 bytes
??????????????? Database mounted.
??????????????? Database opened.
?
??????????????? SQL> ho lsnrctl status???? --再次查看偵聽器的狀態(tài)
??????????????????????????????????????? ?? --可以看到testdb已經(jīng)被動態(tài)注冊
??????????????? LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-SEP-2010 16:34:06
?
??????????????? Copyright (c) 1991, 2005, Oracle.? All rights reserved.
?
??????????????? Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=robinson)(PORT=1521)))
??????????????? STATUS of the LISTENER
??????????????? -----------------------------部分信息省略---------------------------------------
??????????????? Services Summary...
??????????????? Service "orcl.robinson.com" has 2 instance(s).
??????????????? ? Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
??????????????? Service "orclXDB.robinson.com" has 1 instance(s).
??????????????? Service "orcl_XPT.robinson.com" has 1 instance(s).
??????????????? Service "testdb" has 1 instance(s).??? --下面是和testdb相關(guān)的服務(wù)名，實(shí)例名
??????????????? ? Instance "testdb", status READY, has 1 handler(s) for this service...
??????????????? Service "testdb.robinson.com" has 1 instance(s).
??????????????? ? Instance "testdb", status READY, has 1 handler(s) for this service...
??????????????? Service "testdbXDB" has 1 instance(s).
??????????????? ? Instance "testdb", status READY, has 1 handler(s) for this service...
??????????????? Service "testdb_XPT" has 1 instance(s).
??????????????? ? Instance "testdb", status READY, has 1 handler(s) for this service...
??????????????? The command completed successfully?????????????
?
??????????????? SQL> conn scott/tiger@192.168.1.128/testdb.robinson.com? ?????????????? ??????????????? ??? Connected.? --動態(tài)注冊testdb,Win客戶端可以正常連接
??????????????? SQL> conn system/redhat@orcl ????????--原來靜態(tài)注冊的orcl也可以正常連接
??????????????? Connected.
???????????????
??????????????? --注意服務(wù)名中有些狀態(tài)為UNKNOWN，有些為READY
??????????????? --對于動態(tài)注冊的服務(wù)名，因?yàn)楸O(jiān)聽器知道實(shí)例的狀態(tài)，所以正常狀態(tài)通常顯示為READY。
??????????????? --對于靜態(tài)注冊的服務(wù)名，通常顯示為UNKNOWN。
???????????????
??????? 當(dāng)客戶端的請求到達(dá)listener時，listener選擇一個合適的service handler為之服務(wù)。
之后listener就不再參與相關(guān)處理。
???????
??????? 對于受限模式的實(shí)例，PMON阻塞所有到實(shí)例的連接。當(dāng)客戶端嘗試連接將收到如下錯誤信息：
??????????? ORA-12526：TNS：listener: all appropriate instances are in restricted mode
??????????? ORA-12527: TNS：listener: all appropriate instances are in restricted mode or blocking new connections
???????
??????????? C:/Documents and Settings/Robinson Cheng>sqlplus scott/tiger@orcl
?
??????????? SQL*Plus: Release 10.2.0.1.0 - Production on 星期三9月1 17:14:41 2010
?
??????????? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??????????? ERROR:
??????????? ORA-12526: TNS:listener: all appropriate instances are in restricted mode
???????
??????? 動態(tài)注冊時的幾個常用參數(shù)以及需要$ORACLE_HOME目錄
??????????? SQL> select name,value,isdefault from v$parameter
??????????? ? 2? where name in ('instance_name','db_domain','service_names');
?
??????????? NAME????????????????????? VALUE????????????????????????? ISDEFAULT
??????????? ------------------------- ------------------------------ ---------
??????????? db_domain???????????????? robinson.com?????????????????? FALSE
??????????? instance_name???????????? orcl??????????? ???????????????FALSE
??????????? service_names???????????? orcl.robinson.com????????????? FALSE
????????
??????? 更多關(guān)于動態(tài)注冊的更多描述：配置非默認(rèn)端口的動態(tài)服務(wù)注冊
??? 名稱解析方式(Naming Method)
??????? 用于客戶端連接到數(shù)據(jù)庫服務(wù)時如何解析連接字符串。連接字符串形式：c:/sqlplus scott/tiger@orcl
??????? 常用的字符串解析方法
??????????? Easy connect
??????????????? 缺省的連接方式為Easy connect ，僅僅限制于使用TCP協(xié)議，不支持負(fù)載均衡
??????????????? 不支持connect-time failover、source routing、load balancing
??????????????? SQL> connect username/password@host[:port][/service_name]
??????????????? C:/>sqlplus scott/tiger@192.168.192.128:1521/orcl
???????????????
??????????? Local naming(本地名稱解析)
??????????????? 使用本地配置文件tnsnames.ora
??????????????? 支持所有的Oracle Net 協(xié)議
??????????????? 支持高級連接選項(xiàng)：connect-time failover、source routing、load balancing
??????????????? 在服務(wù)器端缺省路徑為$ORACLE_HOME/network/admin
??????????????? tnsnames.ora可以使用TNS_ADMIN環(huán)境變量設(shè)定到任意位置
???????????????
??????????? Directory naming(目錄名稱解析)
??????????????? 目錄名稱解析技術(shù)使得解析實(shí)現(xiàn)集中化管理，用戶會被指向一個解析別名的LDAP目錄服務(wù)器
??????????????? 支持所有的Oracle Net協(xié)議及高級選項(xiàng)
???????????????
??????????? External Naming Method(外部名稱解析方式)
??????????????? 第三方名稱解析方法如SUN的NIS等。
???????
四、常用的配置文件
??? ldqp.ora???????? -->用于配置LDAP目錄名稱解析
??? cman.ora???????? -->用于配置Oracle 連接管理參數(shù)
??? tnsnames.ora???? -->用于配置本地客戶端或遠(yuǎn)程客戶端的本地名稱解析
??? listener.ora???? -->用于配置偵聽器的相關(guān)注冊信息
??? sqlnet.ora?????? -->用于配置服務(wù)器或客戶端所支持的解析方式
?
??? netmgr(Net Manager)中的幾個配置模塊
??????? Profile 分支?????? -->對應(yīng)于sqlnet.ora文件，可以設(shè)置客戶端和服務(wù)器端同時應(yīng)用的Oracle選項(xiàng)
??????? Sevice Naming 分支 -->用于配置客戶端的名稱解析，對應(yīng)于tnsnames.ora文件
??????? Listener 分支????? -->用于配置服務(wù)器端的數(shù)據(jù)庫偵聽器，對應(yīng)于listener.ora文件
?
五、偵聽器配置程序(lsnrctl) 及TNSPING命令??
??? [oracle@robinson admin]$ lsnrctl
?
??? LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-SEP-2010 21:30:52
?
??? Copyright (c) 1991, 2005, Oracle.? All rights reserved.
?
??? Welcome to LSNRCTL, type "help" for information.
?
??? LSNRCTL> help?? --使用help 查看lsnrctl 的擴(kuò)展命令
??? The following operations are available
??? An asterisk (*) denotes a modifier or extended command:
?
??? start?????????????? stop??????????????? status??? -->停止、啟動、查看狀態(tài)等(常用)?????????
??? services??????????? version???????????? reload?????????????
??? save_config???????? trace?????????????? spawn??????????????
??? change_password???? quit??????????????? exit????? ??????????
??? set*??????????????? show*?????????????? ???
???
??? lsnrctl命令用法
??????? LSNRCTL> command [listener_name]
???????
??? lsnrctl命令演示參照前面例子中的使用
???
??? tnsping命令用于測試網(wǎng)絡(luò)的可聯(lián)通性
??????? C:/>tnsping orcl
?
??????? TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-9月-
??? ??? 2010 21:52:45
?
??????? Copyright (c) 1997, 2005, Oracle.? All rights reserved.
?
??????? Used parameter files:
??????? F:/oracle/product/10.2.0/client_1/network/admin/sqlnet.ora
?
??????? Used TNSNAMES adapter to resolve the alias
??????? Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
??????? (HOST = 192.168.1.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl.robin
??????? son.com)))
??????? OK (0 msec)
???????
六、總結(jié)???????
??? 1.客戶端連接時的五個重要參數(shù)：用戶名、密碼、IP地址、端口號、服務(wù)名
??? 2.從客戶端成功連接到數(shù)據(jù)庫服務(wù)器
??????? 客戶端要求
??????????? 需要安裝適當(dāng)?shù)目蛻舳塑浖?span style="color:#808080;" lang="en-us">(Oracle Client)
??????????? 正確配置sqlnet.ora??
??????????????? NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT ….)??????
??????????????? SQLNET.AUTHENTICATION_SERVICES = (NTS)?? 關(guān)于認(rèn)證方式，請參考：Oracle 密碼文件
??????????????? NAMES.DEFAULT_DOMAIN=robinson.com
--表示客戶端在請求連接時將設(shè)定的缺省值追加網(wǎng)絡(luò)服務(wù)名(net_service_name)之后
??????????????????? --如果db_domain為robinson.com,使用scott/tiger@orcl連接,則等同于使用scott@orcl.robinson.com進(jìn)行連接
??????????? 正確配置tnsnames.ora
??????? 服務(wù)器端要求
??????????? listener已經(jīng)啟動
??????????? 數(shù)據(jù)庫已經(jīng)啟動
??
?
??? 3.手動配置listener.ora和tnsnames.ora
??????? 配置tnsname.ora文件：
??????????? 可用的配置工具
??????????????? oracle Net Configuration Assistant(GUI)?????? -->實(shí)際上修改tnsnames.ora文件
??????????????? oracle Net Manager(GUI)?????????????????????? -->實(shí)際上修改tnsnames.ora文件
???????????????
??? ??? 給出下面的示例：
??????????? orcl =?????????????????????????????????????????? -->網(wǎng)絡(luò)服務(wù)別名，可以修改，對應(yīng)于連接標(biāo)識符
??????????? ? (DESCRIPTION =
??????????????? (ADDRESS_LIST =
??????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????? )
??????????????? (CONNECT_DATA =
??????????????? ? (SERVICE_NAME = orcl.robinson.com)
??????????????? )
??????????? ? )
??????????? 紅色的內(nèi)容為根據(jù)實(shí)際需要進(jìn)行修改的內(nèi)容，現(xiàn)描述如下：
??????????? PROTOCOL：客戶端與服務(wù)器端通訊的協(xié)議，一般為TCP，該內(nèi)容一般不用改。
HOST：數(shù)據(jù)庫偵聽所在的主機(jī)的主機(jī)機(jī)名或IP地址，通常數(shù)據(jù)庫偵聽與數(shù)據(jù)庫位于同一個機(jī)器上。建議使用IP地址。在UNIX或WINDOWS下可以使用hostname來獲得主機(jī)的機(jī)器名或使用ifconfig(UNIX)、ipconfgi(Windows)來獲得主機(jī)的IP地址。
??????????? PORT：數(shù)據(jù)庫偵聽使用的端口，缺省為，可以查看服務(wù)器端的listener.ora文件獲得端口號
??????????? ????? 或在數(shù)據(jù)庫服務(wù)器命令提示符下使用nsrctl status [listener name]命令查看獲得端口號。
????????????????? 此處Port的值一定要與數(shù)據(jù)庫偵聽正在偵聽的端口一樣。
??????????? SERVICE_NAME：在服務(wù)器端，使用sqlplus> show parameter service_name命令查看。
???????????
??????????? 上述配置時應(yīng)保證客戶機(jī)能夠ping通服務(wù)器，之后可以使用
tnsping orcl(網(wǎng)絡(luò)服務(wù)名)來測試網(wǎng)絡(luò)的聯(lián)通情況
??????????????? SQL> conn scott/tiger@orcl
??????????????? Connected.
??????????? 使用上面的連接方式進(jìn)行連接時，則連接標(biāo)識符orcl被解釋為tnsnames.ora中orcl對應(yīng)
的描述信息,根據(jù)描述信息來解析連接服務(wù)器
???????????
??????? 配置listener.ora? -->同樣可以使用tnsnames.ora配置時所使用的GUI工具來完成
??????????? 給出下面的示例System parameter file is /u01/app/oracle/10g/network/admin/listener.ora
??????????? LISTENER =
??????????? ? (DESCRIPTION =
??????????????? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????? ? )?? --LISTENER部分描述了主機(jī)地址、端口及協(xié)議
???????????
??????????? SID_LIST_LISTENER =
??????????? ? (SID_LIST =
??????????????? (SID_DESC =
??????????????? ? (GLOBAL_DBNAME = orcl1)
??????????????? ? (ORACLE_HOME = /u01/app/oracle/10g)
??????????????? ? (SID_NAME = ORCL)
??????????????? )
??????????????? (SID_DESC =
??????????????? ? (GLOBAL_DBNAME = orcl2)
??????????????? ? (ORACLE_HOME = /u01/app/oracle/10g)
??????????????? ? (SID_NAME = ORCL)
??????????????? )
??????????? ? )
???????????
??????????? a. LISTENER部分描述了主機(jī)地址、端口及協(xié)議
??????????? b. SID_LIST_LISTENER描述對外提供數(shù)據(jù)庫服務(wù)的列表
??????????? ?? 兩個SID_DESC是對同一個SID數(shù)據(jù)庫設(shè)置的兩個service_names所設(shè)置的監(jiān)聽服務(wù)
??????????? 啟動該Listener后的信息如下
??????????????? Services Summary...
??????????????? Service "orcl1" has 1 instance(s).
??????????????? ? Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
??????????????? Service "orcl2" has 1 instance(s).
??????????????? ? Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
??????????????? The command completed successfully
??????????? 配置客戶端的tnsnames.ora如下
??????????????? orcl1 =
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS_LIST =
??????????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????????? )
??????????????????? (CONNECT_DATA =
??????????????????? ? (SERVICE_NAME = orcl1)
??????????????? ??? )
??????????????? ? )
??????????????? ?
??????????????? orcl2 =
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS_LIST =
??????????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????????? )
??????????????????? (CONNECT_DATA =
??????????????????? ? (SERVICE_NAME = orcl2)
??????????????????? )
??????????????? ? )
??????????????? ?
??????????? 從客戶端進(jìn)行連接??? ?
??????????????? C:/>sqlplus /nolog
?
??????????????? SQL*Plus: Release 10.2.0.1.0 - Production on 星期六9月4 09:47:22 2010
?
??????????????? Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
??????????????? SQL> conn scott/tiger@orcl1
??????????????? Connected.
??????????????? SQL> conn scott/tiger@orcl2
??????????????? Connected.?????
???????????????
??????????? 注意：
??????????????? 筆者首次連接時收到如下錯誤提示,將listener.ora中的SID_NAME的值改為小寫,即orcl,問題解決
??????????????? 所以盡可能的統(tǒng)一大小寫。
??????????????????? SQL> conn scott/tiger@orcl1;??? --下面是未統(tǒng)一大小寫時的錯誤提示
??????????????????? ERROR:
??????????????????? ORA-01034: ORACLE not available
??????????????????? ORA-27101: shared memory realm does not exis
??????????????????? Linux Error: 2: No such file or directory
???????????????????
??? 4.關(guān)于GLOBAL_DBNAME
??????? 下面是GLOBAL_DBNAME的官方描述
??????????? GLOBAL_DBNAME
??????????????? Purpose????????
??? Use the parameter GLOBAL_DBNAME to identify the database service.
???????????????
??????????????? While processing a client connection request, the listener tries to match the value of
??????????????? this parameter with the value of the SERVICE_NAME parameter in the client connect
??????????????? descriptor. If the client connect descriptor uses the SID parameter, then the listener
??????????????? does not attempt to map the values. This parameter is primarily intended for
??????????????? configurations with Oracle8 release 8.0 or Oracle7 databases (where dynamic service
??????????????? registration is not supported for dedicated servers). This parameter may also be
??????????????? required for use with Oracle9i and Oracle8 database services by some configurations
??????????????? and management tools.
???????????????
??????????????? The value for this parameter is typically obtained from the combination of the DB_
??????????????? NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization
??????????????? parameter file, but the value can also contain any valid name used by clients to
??????????????? identify the service.
???????????????
??????????????? Embed this parameter under the SID_DESC parameter.
???????????????
??????? 大致意思如下：
??????????? 當(dāng)客戶端發(fā)出連接請求時，監(jiān)聽器首先使用客戶端連接描述符中的SERVICE_NAME相匹配。
??????????? 如果客戶端連接描述符中使用的是SID,則不再嘗試匹配該值,而是對監(jiān)聽器中設(shè)置的SID_NAME進(jìn)行匹配
??????????? 該參數(shù)的值主要從初始化參數(shù)文件中的db_name和db_domian中組合得到。
???????
??????? 演示GOLBAL_DBNAME使用
??????????? 假定服務(wù)器listener.ora配置如下：
??????????????? SID_LIST_LISTENER =
??????????????? ? (SID_LIST =
??????????????????? (SID_DESC =
??????????????????? ? (GLOBAL_DBNAME = orcl.robinson.com)
??????????????????? ? (ORACLE_HOME = /u01/app/oracle/10g)
??????????????????? ? (SID_NAME = orcl)
??????????????????? )
??????????????? ??)
?
??????????????? LISTENER =
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????? ? )
??????????????? ?
??????????? 客戶端的tnsnames.ora配置如下：
??????????????? orcl =
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS_LIST =
??????????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????????? )
??????????????????? (CONNECT_DATA =
??????????????????? ? (SERVICE_NAME = orcl.robinson.com)
??????????????????? )
??????????????? ? )
??????????? 且spfileorcl中db_domain為空值
??????????????? [oracle@robinson ~]$ strings $ORACLE_HOME/dbs/spfileorcl.ora | grep db_domain
??????????????? *.db_domain=''
???????????
??????????? 數(shù)據(jù)庫中的相關(guān)設(shè)置為空值
??????????????? SQL> col name format a20
??????????????? SQL> col value format a35
??????????????? SQL> select name,value,isdefault from v$parameter
??????????????? ? 2? where name in ('instance_name','db_domain','service_names');
?
??????????????? NAME???????????????? VALUE?????????????????????????????? ISDEFAULT
??????????????? -------------------- ----------------------------------- ---------
??????????????? db_domain??????????????????????????????????????????????? FALSE
??????????????? instance_name??????????????????????????????????????????? FALSE
??????????????? service_names?????????????????????????????????????? ?????FALSE?
???????????????
??????????? 能夠成功連接
??????????????? SQL> conn system/redhat@orcl
??????????????? Connected.
??????????? ------------------------------------------------------------------------------------------
??????????? 修改客戶端tnsnames.ora中的service_name項(xiàng)
??????????????? 即去掉SERVICE_NAME = orcl.robinson.com這項(xiàng)內(nèi)容且添加為SID = ORCL
??????????? 修改后的內(nèi)容如下：
??????????????? ORCL =
??????????????? ? (DESCRIPTION =
??????????????????? (ADDRESS_LIST =
??????????????????? ? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
??????????????????? )
??????????????????? (CONNECT_DATA =
??????????????????? ? (SID = ORCL)
??????????????????? )
??????????????? ? )
???????????????
??????????? SQL> conn scott/tiger@orcl?? --能夠成功連接
??????????? Connected.
???????????????????
??????? 關(guān)于GOLBAL_DBNAME總結(jié)：
當(dāng)客戶端使用的是SID連接參數(shù)，則匹配偵聽器配置文件中的SID_NAME項(xiàng)??? (主要用于兼容以前的老版本,8i或更早版本)
否則當(dāng)使用的是SERVICE_NAME，則匹配偵聽器配置文件中的SERVICE_NAME項(xiàng) (現(xiàn)在多用SERVICE_NAME項(xiàng))
???
七、更多???
?
Oracle 冷備份
?
SPFILE錯誤導(dǎo)致數(shù)據(jù)庫無法啟動
?
Oracle 用戶、對象權(quán)限、系統(tǒng)權(quán)限
?
Oracle 角色、配置文件
?
? Oracle 聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)
?
? Oracle 控制文件(CONTROLFILE)
?
? Oracle 表空間與數(shù)據(jù)文件
?
Oracle 歸檔日志
?
?
?????
???????
總結(jié)
以上是生活随笔為你收集整理的配置ORACLE 客户端连接到数据库的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： CentOS 5.5升级内核到2.6.3
- 下一篇： 网交会引发的新交易机会