第三十一讲--网络原理及配置
来源：互联网 发布：免费手机扫描仪软件 编辑：程序博客网 时间：2024/05/16 07:10
Oracle的网络没有负载。
Oracle的监听要独立启动，监听跟数据库的没有太多关系。可以先起监听再起数据库，也可以先起数据库再起监听。数据库崩溃了监听可以存在，监听完蛋了数据库可以存在。虽然监听和数据库没有太多联系，但是它们之间也要互相知道对方在哪。监听知道数据库在什么地方，但是监听和数据库之间没有长连接。
用户通过sqlplus发起的连接连的是监听。Linux所谓的服务其实就是IP地址加端口。用户连接监听，监听把用户的连接请求转发给数据库实例，实例会为这个连接起一个server process，实例同时把server process的地址告诉监听，监听把server process的地址告诉客户端，然后客户端直接发起对server process的连接请求（此时绕过了监听），这时客户端和oracle的实例正式建立了联系（会话），接下来客户端把用户名和密码给server process，server process拿着用户名和密码进行验证，验证成功以后连接正式建立，此后客户端和数据库的操作全部通过会话进行，和监听没有关系。监听只是在建立连接的时候负责转发，此后就没有任何意义了，即使把监听关了，会话照样存在照样工作。
监听唯一会出问题的地方是短时间内有大量的连接请求，这时监听可能会产生延迟，用户感觉连数据库的时候比较慢。
Oracle的监听跟三个文件相关：listener.ora、tnsnames.ora、sqlnet.ora（几乎不用）。客户端应该放tnsnames.ora，监听应该放listener.ora。当然，在服务器端除了有listener.ora以外一般还有tnsnames.ora。listener.ora和tnsnames.ora有一些语法和对应关系。
配置xmanager：
1. Xmanager配置在哪个IP上：
[root@redhat4~]# xhost +access controldisabled, clients can connect from any host
2. 查看终端远程是哪个IP的哪个端口登上来的：
[root@redhat4~]# w08:54:35 up 54min, 3 users, load average: 0.09, 0.09, 0.03USER TTY FROM LOGIN@ IDLE JCPU PCPU WHATroot pts/1 192.168.128.185 08:05 48:49 0.17s 0.16s sqlplus as syroot :0 - 08:51 ?xdm? 14.65s 0.23s /usr/bin/gnome-root pts/2 <span style="color:#FF0000;">:0.0</span> 08:51 0.00s 0.03s 0.00s w
3. 把xmanager配置到相应端口：
[root@redhat4~]# export DISPLAY=:0.0
xmanager是图形界面，配好以后就可以打开图形化的netca工具了：
[root@redhat4~]# su - oracle[oracle@redhat4~]$ netca
Oracle NetServices 配置:
Warning: Cannotconvert string "-isas-song ti-medium-r-normal--*-140-*-*-c-*-gb2312.1980-0" to type FontStruct
Netca的第一项：监听程序配置，是在服务器端配置的。对监听程序配置进行的操作其实是生成了listener.ora文件。
Listener.ora文件的位置：
[oracle@redhat4~]$ cd $ORACLE_HOME[oracle@redhat4db_1]$ cd network/[oracle@redhat4network]$ cd admin[oracle@redhat4admin]$ lslistener1511088下午5629.bak shrept.lstlistener1511088下午5703.bak tnsnames1511088下午5629.baklistener1511088下午5910.bak tnsnames1511088下午5703.baklistener1511089下午0102.bak tnsnames1511088下午5910.baklistener1511089下午0310.bak tnsnames1511089下午0102.baklistener1511089下午0501.bak tnsnames1511089下午0310.baklistener1511089下午1026.bak tnsnames1511089下午0501.baklistener1511089下午1132.bak tnsnames1511089下午1026.baklistener1511089下午1305.bak tnsnames1511089下午1132.baklistener1512298上午5313.bak tnsnames1511089下午1305.baklistener1512298上午5422.bak tnsnames1512298上午5313.baklistener1512298上午5535.bak tnsnames1512298上午5422.baklistener.ora tnsnames1512298上午5535.baksamples tnsnames.ora
打开listener.ora文件：
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =redhat4)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC0)) ) )
这一段就是监听的配置信息，HOST代表主机名，PORT代表端口号
关闭监听：
[oracle@redhat4admin]$ lsnrctl stop LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 29-DEC-2015 09:14:32 Copyright (c)1991, 2005, Oracle. All rights reserved. Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))The commandcompleted successfully
启动监听：
[oracle@redhat4admin]$ lsnrctl start LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 29-DEC-2015 09:15:18 Copyright (c)1991, 2005, Oracle. All rights reserved. Starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR forLinux: Version 10.2.0.1.0 - ProductionSystem parameterfile is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraLog messageswritten to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of theLISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 09:15:18Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListeningEndpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))ServicesSummary...Service"PLSExtProc" has 1 instance(s). Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...The commandcompleted successfully
查看监听状态：
[oracle@redhat4admin]$ lsnrctl status LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 29-DEC-2015 09:16:26 Copyright (c)1991, 2005, Oracle. All rights reserved. Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of theLISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 09:15:18Uptime 0 days 0 hr. 1 min. 8 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListeningEndpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))ServicesSummary...Service "PLSExtProc"has 1 instance(s). Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...Service"jiagulun" has 1 instance(s). Instance "jiagulun", status READY,has 1 handler(s) for this service...Service"jiagulunXDB" has 1 instance(s). Instance "jiagulun", status READY,has 1 handler(s) for this service...Service"jiagulun_XPT" has 1 instance(s). Instance "jiagulun", status READY,has 1 handler(s) for this service...The commandcompleted successfully
配置完listener.ora以后就可以启动监听了，接下来就要配置客户端的tnsnames.ora了。
选择第三项，本地Net服务名配置，这是用来生成tnsnames.ora的。
选择添加
登录sqlplus，查看服务名：
[oracle@redhat4 admin]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 29 09:21:452015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bitProductionWith the Partitioning, OLAP and Data Mining options SQL> show parameter service NAME TYPE VALUE---------------------------- -------------------------------- ------------------------------service_names string jiagulun
选择TCP协议
Tnsnames.ora是客户端的，客户端要连接连接数据库，所以这里的IP地址应该是数据库服务器的监听的IP地址，也就是listener.ora里面配置的IP地址。
查看监听的IP地址;
[oracle@redhat4 admin]$ netstat -tulnp|grep 1521(Not all processes could be identified, non-owned process infowill not be shown, you would have to be root to see it all.)tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN 12419/tnslsnr
在实际生产中一个数据库服务器对应很多网卡。
网络服务名可以随便起，一般就和前面的服务名一样
打开tnsnames.ora
[oracle@redhat4 /]$ cd $ORACLE_HOME[oracle@redhat4 db_1]$ cd network/admin/[oracle@redhat4 admin]$ vi tnsnames.ora CGH = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.115)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cgh) ) )
CGH就是网络服务名，用netca配置时往往会转成大写，需要在tnsnames.ora中改为小写。
根据HOST和PORT可以找到监听，在Linux终端输入netstat –tulnp|grep 1521就可以获得HOST。
SERVICE_NAME必须和数据库的服务名一样，登录sqlplus，输入showparameter service 就可以获得SERVICE_NAME。
当连接失败时的排错流程：
1． 先看tnsnames.ora，里面有IP地址、端口号，这两个参数在服务器端用netstat –tulnp|grep 1521查看，看客户端的tnsnames.ora配置的对不对。Service_names是否等于oracle数据库的service_names，Service_names在服务器端登录sqlplus后用show parameterservice可以得到。如果IP地址、端口号、service_names都对的上，那么客户端是没有问题的。
以oracle用户登录Linux终端，输入tnsping cgh（网络服务名），看是否能ping的通服务器端的数据库实例。
[oracle@redhat4 admin]$ tnsping cgh TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on29-DEC-2015 14:52:27 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.115)(PORT =1521))) (CONNECT_DATA = (SERVICE_NAME = jiagulun)))TNS-12541: TNS:no listener
提示没有监听，这是由于在服务器端没有打开监听所致，回到服务器端，输入命令，打开监听：
[oracle@redhat4 admin]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 -Production on 29-DEC-2015 14:57:43 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 -ProductionSystem parameter file is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521))) Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 14:57:44Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))Services Summary...Service "PLSExtProc" has 1instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) forthis service...The command completed successfully
然后再在Linux终端以oracle用户tnsping cgh（网络服务名）：
[oracle@redhat4 admin]$ tnsping cgh TNS Ping Utility for Linux: Version10.2.0.1.0 - Production on 29-DEC-2015 15:06:58 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.115)(PORT =1521))) (CONNECT_DATA = (SERVICE_NAME = jiagulun)))OK (60 msec)
Tnsping能ping通不一定oracle能连上去。
客户端以sqlplus 用户名/密码@字符串的方式登录oracle，@的字符串就在tnsnames.ora里面，通过字符串就可以获得IP地址（HOST）、端口号（PORT）、数据库服务名（SERVICE_NAME）。通过tnsnames.ora文件中的IP地址和端口号就可以找到监听，监听一定认识tnsnames.ora中配置的service_name，因为这个service_name是数据库的服务名。怎么判断监听是否知道数据库的service_name？
判断监听是否知道数据库service_name的方法：
[oracle@redhat4 ~]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 -Production on 29-DEC-2015 15:33:13 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 14:57:44Uptime 0 days 0 hr. 35 min. 29 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))Services Summary...Service "PLSExtProc" has 1instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) forthis service...<span style="color:#FF0000;">Service"jiagulun" has 1 instance(s). Instance "jiagulun", status READY, has 1 handler(s)for this service...</span>Service "jiagulunXDB" has 1instance(s). Instance "jiagulun", status READY, has 1 handler(s) for thisservice...Service "jiagulun_XPT" has 1instance(s). Instance "jiagulun", status READY, has 1 handler(s) for thisservice...The command completed successfully
标红语句中jiagulun的status是ready，标红语句中Service的名字必须和tnsnames.ora中的service_name相同。
Tnsnames.ora有几个名字：
1. 字符串的名字：cgh，客户端sqlplus 用户名/密码@字符串，@的就是这个字符串
2. IP地址和端口号，找到监听
3. Service_name，监听（不是监听文件listener.ora）中的service和tnsnames.ora中的service_name是一样的。默认情况下oracle数据库会把自己的service_name注册到监听中。每次监听启动的时候oracle就会把自己的service_name注册一次，为了加快注册速度可以使用一下命令：
[oracle@redhat4 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Productionon Tue Dec 29 15:55:55 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database 10g Enterprise EditionRelease 10.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP and Data Miningoptions SQL> alter system register; System altered.
如果tnsnames.ora中的service_name和监听中的service对上口的话，客户端和监听就建立连接了。
数据库自己把服务名注册到监听和使用SQL语句注册监听都是自动注册方式，还有静态注册的方式，静态注册是为了解决动态注册不稳定的情况。
打开listener.ora，加上下面这几行语句：
SID_LIST_LISTENER= (SID_LIST =<span style="color:#FF0000;"> (SID_DESC = (GLOBAL_DBNAME = 数据库的服务名（service_name）) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1) (SID_NAME =数据库的服务名（instance_name）) )</span> )
查看数据库的服务名：
[oracle@redhat4~]$ sqlplus / as sysdba SQL*Plus:Release 10.2.0.1.0 - Production on Tue Dec 29 16:13:57 2015 Copyright (c)1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith thePartitioning, OLAP and Data Mining options SQL> showparameter service; NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------service_names string jiagulun
查看数据库的实例名：
[oracle@redhat4~]$ sqlplus / as sysdba SQL*Plus:Release 10.2.0.1.0 - Production on Tue Dec 29 16:15:17 2015 Copyright (c)1982, 2005, Oracle. All rights reserved. Connected to:Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith thePartitioning, OLAP and Data Mining options SQL> showparameter instance; NAME TYPE--------------------------------------------------------------------VALUE------------------------------active_instance_count integer cluster_database_instances integer1instance_groups string <span style="color:#FF0000;">instance_name stringjiagulun</span>instance_number integer NAME TYPE--------------------------------------------------------------------VALUE------------------------------0instance_type stringRDBMSopen_links_per_instance integer4parallel_instance_group string parallel_server_instances integer1
配置listener.ora为静态注册监听后，使用lsnrctlstop和lsnrtctl start重启监听，马上用lsnrctl status查看监听的状态：
[oracle@redhat4admin]$ lsnrctl status LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 29-DEC-2015 16:21:40 Copyright (c)1991, 2005, Oracle. All rights reserved. Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of theLISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 16:21:36Uptime 0 days 0 hr. 0 min. 4 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListeningEndpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))ServicesSummary...Service"PLSExtProc" has 1 instance(s). Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...<span style="color:#FF0000;">Service "jiagulun" has 1 instance(s). Instance "jiagulun",status UNKNOWN, has 1 handler(s) for this service...</span>The commandcompleted successfully
发现instance “jiagulun”的status是UNKNOWN
等一段时间之后，在查看监听状态：
[oracle@redhat4admin]$ lsnrctl status LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 29-DEC-2015 16:38:00 Copyright (c)1991, 2005, Oracle. All rights reserved. Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))STATUS of theLISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version10.2.0.1.0 - ProductionStart Date 29-DEC-2015 16:21:36Uptime 0 days 0 hr. 16 min. 24 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.oraListener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/listener.logListeningEndpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))ServicesSummary...Service"PLSExtProc" has 1 instance(s). Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...Service"jiagulun" has 2 instance(s).<span style="color:#3333FF;"> Instance "jiagulun", status UNKNOWN,has 1 handler(s) for this service...</span><span style="color:#FF0000;"> Instance"jiagulun", status READY, has 1 handler(s) for this service...</span>Service"jiagulunXDB" has 1 instance(s). Instance "jiagulun", status READY,has 1 handler(s) for this service...Service"jiagulun_XPT" has 1 instance(s). Instance "jiagulun", status READY,has 1 handler(s) for this service...The commandcompleted successfully
蓝色的是静态注册的监听，红色的是动态注册的监听。
Oracle的几个名字：
数据库名、实例名、服务名的区别。但实例环境中，实例名字就等于数据库名字
查看这几个名字：
SQL> showparameter name;NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------db_file_name_convert stringdb_name string jiagulundb_unique_name string jiagulunglobal_names Boolean FALSEinstance_name string jiagulunlock_name_space stringlog_file_name_convert stringservice_names string jiagulun
oracle的几种连接方式：
1. sqlplus hr/hr；不走监听（本地连接）
2. sqlplus / as sysdba；不走监听（本地连接）
3. slqplus hr/hr@jiagulun；走监听
4. sqlplus hr/hr@192.168.128.56:1521/jiagulun；走监听了，都是没有走tnsnames.ora
0 0
- 第三十一讲--网络原理及配置
- <<OCM实验选讲>> 第三课 网络配置 实验
- Java基础：第三十一讲 常量成员
- JavaSE第三十一讲：Java数组剖析
- Docker 配置Flannel网络过程及原理
- VMware虚拟网络配置及原理
- JavaSE第三十五讲：冒泡排序、交换排序及快速排序原理与实现
- DICOM标准及应用——第三讲 DICOM消息交换和网络通信
- 韩顺平网页设计第三十一三十二讲
- 第三十一讲 : DataSet和DataAdapter（上）
- 第三十一讲项目一 简单循环的流程图
- Linux学习笔记（九）网络原理及网络配置
- 个性化WinPE封装方法《第三讲---- 配置开始菜单及桌面环境》
- 第二十八讲至第三十一讲 编程项目 字符统计 字符串处理函数（１）
- Spring cloud系列十一 @Feign集成的Hystrix进行个性化配置及集成原理
- JavaSE第七十一讲：Target及ElementType详解
- 第三讲
- 第三讲
- pomelo命令行管理pomelo项目
- STRUCT 使用
- 蓝桥杯 基础练习 特殊的数字
- CocoaPods安装使用
- 欧拉函数的线筛模板
- 第三十一讲--网络原理及配置
- 【解决方法】ld: warning: directory not found for option
- win10 系统和压缩内存占用80端口解决
- HDU 2571 命运
- Codeforces 632D 暴力
- office word出现“错误！未找到引用源”的错误
- web 中保存头像功能
- C++作业2
- MySQL排序原理与案例分析
