配置Oracle網路環境

當用戶端通過網路以"connect 使用者名稱/密碼@連接字串"的形式串連oracle資料庫時，需要監聽程式的協助，當串連建立後，即使監聽程式掛掉也不會影響已經建立好的串連。

使用者串連oracle資料庫伺服器主要有兩種方式：專有伺服器串連和共用伺服器串連

• 在專用伺服器模式中，對於每個運行資料庫應用程式的使用者進程都由執行Oracle 資料庫伺服器代碼的專用伺服器處理序提供服務。

每個伺服器處理序都有自己專用的PGA，這個PGA 在伺服器處理序啟動時建立，對PGA 的存取權限僅限於該伺服器處理序，並且只能由代表該伺服器處理序的Oracle 代碼對PGA 進行讀寫。

• 在共用伺服器模式中，不必為每個串連都提供一個專用伺服器處理序。指派程式將多個傳入網路會話請求引到共用伺服器處理序池。共用伺服器處理序為所有客戶機請求提供服務。

多個用戶端使用者共用伺服器處理序，且UGA被轉移到SGA(如果配置了共用池或者大型池)，PGA將只剩下棧空間。

配置和管理Oracle Net 的工具主要有dbconsole、netca、netmgr、和命令列工具(vi)

Oracle Net支援多種串連解析方式：

• Easy Connect(EZCONNECT)

   使用簡便串連時，可提供Oracle Net 串連所需的所有資訊作為連接字串的一部分。簡便串連的連接字串採用以下形式：

   username/password@hostname[:port][/service_name]

   監聽程式連接埠和服務名為可選項。如果未提供監聽程式連接埠，Oracle Net 假定使用的是預設連接埠1521。如果未提供服務名，Oracle Net 假定連接字串中提供的資料庫服務名與主機名稱是相同的。

   SQL> conn hr/hr@192.168.0.90:1521/orcl.example.com

• 本地命名(TNSNAMES)

   使用本地命名時，使用者可提供Oracle Net 服務的別名。Oracle Net 會根據本地已知服務的列表來檢查別名，如果發現匹配名稱，會將別名轉換為主機、協議、連接埠和服務名。

   本地命名的一個優勢是，資料庫使用者僅需要記住簡便串連所需的短別名，而不必記住很長的連接字串。

   如果組織的Oracle Net 服務配置不經常更改，則適合使用本地命名。

   SQL> conn hr/hr@orcl

• 目錄命名和外部命名

Oracle 網路相關的檔案位於$TNS_ADMIN目錄下，其中主要有sqlnet.ora、listener.ora、tnsnames.ora三個檔案

sqlnet.ora    ：位於資料庫伺服器上，用於定義串連解析方式(預設串連方式為TNSNAMES, EZCONNECT)

listener.ora  ：位於資料庫伺服器上，用於監聽器的設定檔

tnsname.ora   ：位於客戶機上，包含網路伺服器名，並映射到連接字串

預設可能不存在sqlnet.ora、listener.ora兩個檔案，但監聽程式仍然可以以預設啟動。此時，Oracle將自動在解析地址為電腦主機名稱，連接埠為1521的地址上啟動一個名為"LISTENER"的監聽器。

$ cat sqlnet.oraNAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)ADR_BASE = /u01/app/oracle$ cat listener.oraLISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g.example.com)(PORT = 1521))    )  )ADR_BASE_LISTENER = /u01/app/oracleENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent$ cat tnsnames.oraORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))    (CONNECT_DATA =      (SERVICE_NAME = orcl.example.com)    )  )$ lsnrctl serviceLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-SEP-2013 22:11:58Copyright (c) 1991, 2009, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora11g.example.com)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM", status READY, has 1 handler(s) for this service...    Handler(s):      "DEDICATED" established:0 refused:0 state:ready         LOCAL SERVERService "orcl.example.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 SERVERService "orclXDB.example.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:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 5008>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=56668))The command completed successfully

向資料庫註冊執行個體的方法分為靜態註冊和動態註冊。

註冊就是將資料庫作為一個服務註冊到監聽程式。用戶端不需要知道資料庫名和執行個體名，只需要知道該資料庫對外提供的服務名就可以申請串連到資料庫。

在資料庫伺服器啟動過程中，資料庫伺服器會向監聽程式註冊相應的服務無論何時啟動一個資料庫，預設地都有兩條資訊註冊到監聽器中：資料庫伺服器對應的執行個體名和服務名）

• 靜態註冊就是執行個體啟動時讀取listener.ora檔案的配置，將執行個體和服務註冊到監聽程式。無論何時啟動一個資料庫，預設地都有兩條資訊註冊到監聽器中：資料庫伺服器對應的執行個體和服務。

   靜態註冊時，listener.ora中的GLOBAL_DBNAME向外提供服務名，listener.ora中的SID_NAME提供註冊的執行個體名。

• 動態註冊是在instance啟動的時候PMON進程根據init.ora中的instance_name,service_names兩個參數將執行個體和服務動態註冊到listener中。

   註冊到監聽器中的執行個體名從參數檔案中的instance_name參數取得。如果該參數沒有設定值，那麼它將取參數檔案中的db_name的值。

   註冊到監聽器中的服務名從參數檔案中的參數service_names取得。如果該參數沒有設定值，資料庫將拼接參數檔案中的 db_name和db_domain的值來註冊自己。

   由於動態註冊需要pmon進程，所以監聽必須在資料庫啟動之前啟動，否則動態註冊將失敗；在資料庫啟動並執行過程中，如果重啟監聽也會造成動態註冊失敗。

SQL> show parameter _listenerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------local_listener                       string                             //預設為空白值，則找預設的監聽器LISTENERremote_listener                      string                             //RAC中指定對端結點的監聽器SQL> show parameter service_namesNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------service_names                        string      orcl.example.com$ cat listener.ora# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11g.example.com)(PORT = 1521))    )  )ADR_BASE_LISTENER = /u01/app/oracleENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

添加多個監聽器：

• 動態註冊

$ vi $TNS_ADMIN/listener.ora                                            //添加監聽器"LISTENER1"的配置資訊LISTENER1 =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1522))    )$ vi $TNS_ADMIN/tnsnames.ora                                            //添加串連到監聽器"LISTENER1"的連接字串APPLE =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1522))  )SQL> alter system set local_listener='listener1';alter system set local_listener='listener1'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00132: syntax error or unresolved network name 'listener1'SQL> alter system set local_listener='apple';                                    //修改預設監聽程式(必須使用監聽程式對應的連接字串)System altered.$ lsnrctl start listener1                                               //啟動監聽程式"LISTENER1"---------------省略輸出---------------STATUS of the LISTENER------------------------Alias                     listener1Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date                11-SEP-2013 14:18:13Uptime                    0 days 0 hr. 0 min. 1 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1//network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/ora11g/listener1/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1522)))The listener supports no servicesThe command completed successfully$ lsnrctl status listener1                                              //查看"LISTENER1"監聽程式的狀態(動態註冊一般會有幾秒鐘的延遲)---------------省略輸出---------------Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1522)))Services Summary...Service "orcl.example.com" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...Service "orclXDB.example.com" has 1 instance(s).  Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully$ lsnrctl status                                                    //查看"LISTENER"監聽程式的狀態("LISTENER"監聽程式可省略指定)---------------省略輸出---------------Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM", status READY, has 1 handler(s) for this service...The command completed successfully$ sqlplus /nolog                 SQL> conn hr/hr@apple                                                //通過"LISTENER"監聽程式串連Connected.

• 靜態註冊
  

$ vi $TNS_ADMIN/listener.oraLISTENER2 =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1523))    )  )SID_LIST_LISTENER2 =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = orcl.example.com)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1/)      (SID_NAME = orcl)    )  )$ lsnrctl start listener2---------------省略輸出---------------Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1523)))Services Summary...Service "orcl.example.com" has 1 instance(s).  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully

使用靜態註冊還可以在遠程開啟資料庫執行個體，而動態註冊由於資料庫執行個體未啟動無法註冊，所以在資料庫開啟之前無法通過動態註冊監聽程式串連到資料庫

$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 16:30:14 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> conn sys/oracle_4U@apple as sysdba                              //無法串連通過動態註冊的監聽ERROR:ORA-12514: TNS:listener does not currently know of service requested in connectdescriptorSQL> conn sys/oracle_4U@apear as sysdba                              //成功串連靜態註冊的監聽，可實現遠程開啟資料庫Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  459304960 bytesFixed Size                  2214336 bytesVariable Size             289408576 bytesDatabase Buffers          159383552 bytesRedo Buffers                8298496 bytesDatabase mounted.Database opened.

配置串連時用戶端容錯移轉和負載平衡Failover和LoadBalance

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/214IM449-0.png" title="FC BL.png" />

可通過dbconsole進行配置，也可以直接修改tnsnames.ora檔案

$ grep -A 11 ORCL tnsnames.oraORCL =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1522))      (LOAD_BALANCE = yes)    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl.example.com)    )  )

其中只有第二項“隨機嘗試每個地址，直到有一個地址成功”才同時達到容錯移轉和負載平衡的作用。

第一項：只有容錯移轉的作用

ORCL =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1523))

   )

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orcl.example.com)

   )

 )

第三項：只有負載平衡的作用

ORCL =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1523))

     (LOAD_BALANCE = yes)

     (FAILOVER = false)

   )

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orcl.example.com)

   )

 )

第四項：只有源路由的作用

ORCL =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1523))

     (SOURCE_ROUTE = yes)

     (FAILOVER = false)

   )

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orcl.example.com)

   )

 )

第五項：只使用第一個地址

ORCL =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1523))

     (FAILOVER = false)

   )

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orcl.example.com)

   )

 )

添加監聽程式到OHASD：

註：使用srvctl添加監聽器組件時，需先關閉欲添加的監聽器，否則會報錯

$ . oraenvORACLE_SID = [orcl] ? +ASMThe Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle$ lsnrctl stop listener1$ lsnrctl stop listener2$ srvctl add listener -h Adds a listener configuration to be managed by Oracle Restart.Usage: srvctl add listener [-l <lsnr_name>] [-s] [-p "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>] [/SDP:<port>]"] [-o <oracle_home>]    -l <lsnr_name>           Listener name (default name is LISTENER)    -o <oracle_home>         ORACLE_HOME path (default value is CRS_HOME)    -s                       Skip the checking of ports    -p "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>] [/SDP:<port>]"       Comma separated tcp ports or listener endpoints    -h                       Print usage$ srvctl add listener -l listener1 -p 1522 -o /u01/app/oracle/product/11.2.0/dbhome_1/$ srvctl add listener -l listener2 -p 1523 -o /u01/app/oracle/product/11.2.0/dbhome_1/$ srvctl start listener -l listener1$ srvctl start listener -l listener2$ crs_stat -tName           Type           Target    State     Host   ------------------------------------------------------------ora.DATA.dg    ora....up.type ONLINE    ONLINE    ora11g ora.FRA.dg     ora....up.type ONLINE    ONLINE    ora11g ora....ER.lsnr ora....er.type ONLINE    ONLINE    ora11g ora....R1.lsnr ora....er.type ONLINE    ONLINE    ora11g ora....R2.lsnr ora....er.type ONLINE    ONLINE    ora11g ora.asm        ora.asm.type   ONLINE    ONLINE    ora11g ora.cssd       ora.cssd.type  ONLINE    ONLINE    ora11g ora.diskmon    ora....on.type ONLINE    ONLINE    ora11g ora.orcl.db    ora....se.type ONLINE    ONLINE    ora11g

本文出自 “ゞ煙花っ笑” 部落格，請務必保留此出處http://vnimos.blog.51cto.com/2014866/1294812