当客户端通过网络以"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
(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
[FC BL.png]
可通过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 ] [-s] [-p "[TCP:][, ...][/IPC:][/NMP:][/TCPS:]
[/SDP:]"] [-o ]
-l Listener name (default name is LISTENER)
-o ORACLE_HOME path (default value is CRS_HOME)
-s Skip the checking of ports
-p "[TCP:][, ...][/IPC:][/NMP:][/TCPS:] [/SDP:]" 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
阅读(2985) | 评论(0) | 转发(2) |
