500
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"的監聽器。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
$ cat sqlnet.ora
NAMES.DIRECTORY_PATH=
(TNSNAMES, EZCONNECT)
ADR_BASE
= /u01/app/oracle
$ cat listener.ora
LISTENER
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = ora11g.example.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER
= /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON #
line added by Agent
$ cat tnsnames.ora
ORCL
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))
(CONNECT_DATA
=
(SERVICE_NAME
= orcl.example.com)
)
)
$
lsnrctl service
LSNRCTL for Linux:
Version 11.2.0.1.0 - Production on 11-SEP-2013 22:11:58
Copyright
(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
SERVER
Service "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
SERVER
Service "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進程，所以監聽必須在數據庫啟動之前啟動，否則動態注冊將失敗；在數據庫運行的過程中，如果重啟監聽也會造成動態注冊失敗。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
show parameter _listener
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
local_listener
string //默認為空值，則找默認的監聽器LISTENER
remote_listener
string //RAC中指定對端結點的監聽器
SQL>
show parameter service_names
NAME
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/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER
= ON
|
添加多個監聽器：
-
動態注冊
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
$ 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 invalid
ORA-00119:
invalid specification for system
parameter LOCAL_LISTENER
ORA-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
listener1
Version
TNSLSNR for Linux:
Version 11.2.0.1.0 - Production
Start
Date 11-SEP-2013 14:18:13
Uptime
0 days 0 hr. 0 min. 1 sec
Trace
Level off
Security
ON: Local OS Authentication
SNMP
OFF
Listener
Parameter File /u01/app/oracle/product/11.2.0/dbhome_1//network/admin/listener.ora
Listener
Log File /u01/app/oracle/diag/tnslsnr/ora11g/listener1/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1522)))
The
listener supports no services
The 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.
|
-
靜態注冊
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ vi $TNS_ADMIN/listener.ora
LISTENER2
=
(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
|
使用靜態注冊還可以在遠程開啟數據庫實例，而動態注冊由於數據庫實例未啟動無法注冊，所以在數據庫開啟之前無法通過動態注冊監聽程序連接到數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
$
sqlplus /nolog
SQL*Plus:
Release 11.2.0.1.0 Production on Wed Sep 11 16:30:14 2013
Copyright
(c) 1982, 2009, Oracle. All rights reserved.
SQL>
conn / as sysdba
Connected.
SQL> shutdown immediate
Database
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 connect
descriptor
SQL>
conn sys/oracle_4U@apear
as sysdba //成功連接靜態注冊的監聽，可實現遠程開啟數據庫
Connected
to an idle instance.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 459304960 bytes
Fixed
Size 2214336 bytes
Variable
Size 289408576 bytes
Database
Buffers 159383552 bytes
Redo
Buffers 8298496 bytes
Database
mounted.
Database
opened.
|
配置連接時客戶端故障轉移和負載均衡Failover和LoadBalance
可通過dbconsole進行配置，也可以直接修改tnsnames.ora文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ grep -A
11 ORCL tnsnames.ora
ORCL
=
(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添加監聽器組件時，需先關閉欲添加的監聽器，否則會報錯
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
$
. oraenv
ORACLE_SID
= [orcl] ? +ASM
The
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 -t
Name
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
|
最後更新：2017-04-03 15:21:46