Oracle网络配置-listener tnsnames sqlnet

最新推荐文章于 2021-04-07 20:01:52 发布
最新推荐文章于 2021-04-07 20:01:52 发布
阅读量391 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lv941002/article/details/82956786
版权 
Oracle 监听程序
#easy connect 特点是不需要配置文件,需要服务器端监听启动,而且数据库OPEN
sqlplus sys/oracle@192.168.18.101:1521/LFN as sysdba
Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service name.


##########################################################################################################################
sqlnet.ora
#最常见的参数

SQLNET.AUTHENTICATION_SERVICES

#以下是官方文档的说明
#默认值是none 指的是登录的时候需要user name and password
none for no authentication methods, including Microsoft Windows native operating system authentication. 
When SQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database.

#本机测试在 $ORACLE_HOME/network/admin/ 下建立 sqlnet.ora的文件
添加 SQLNET.AUTHENTICATION_SERVICES=none
#然后通过sqlplus / as sysdba去连接 提示输入用户名密码
[oracle@oracle admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 4 12:49:55 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

#注释参数信息 不要user name and password
[oracle@oracle admin]$ more sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES=none


[oracle@oracle admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 4 12:51:27 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LFN>

#all指的是所有方式

all for all authentication methods.

#指的是允许本地登录
nts for Microsoft Windows native operating system authentication.

#第二个参数
NAMES.DIRECTORY_PATH=(tnsnames.ezconnect)
配置了tnsnames后就会去tnsnames找连接符后面的alias


#解析tnsnames.ora
tnsnames.ora

LFN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LFN)
    )
  )

#下面是官方文档关于service_name的说明
#位置Net Services Administrator's Guide ->8 Configuring Naming Methods->Table 8-1 Connect Identifier for Easy Connection Naming Method
Optional. Specify the service name of the database.
If a user specifies a service name, 
then the listener connects the user to that specific database. 
Otherwise, the listener connects to the database specified by the DEFAULT_SERVICE_listener_name parameter 
in the listener.ora file. If DEFAULT_SERVICE_listener_name 
is not configured for the listener and a service name 
is not explicitly specified by the user as part of the Easy Connect syntax, 
then the listener returns an error.
#当远程连接数据库使用tnsnames文件.
#比如使用了命令  sqlplus sys/oracle@LFN.首先会在sqlnet.ora中的检查 NAMES.DIRECTORY_PATH=(tnsnames.ezconnect)参数.
#然后去tnsnames.ora文件中找LFN的配置文件.Oracle会检查配置文件中的service_name.是否和默认的service_name匹配.
#当我们在服务器端没有配置listener.ora文件的时候也可以启动监听程序
[oracle@oracle admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2018 16:53:15

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                04-OCT-2018 16:51:58
Uptime                    0 days 0 hr. 1 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.cuug.com)(PORT=1521)))
Services Summary...
Service "LFN" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
Service "LFNXDB" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
The command completed successfully

#并没有listener.ora文件
[oracle@oracle admin]$ ll
total 16
drwxr-xr-x 2 oracle oinstall 4096 Jul 16  2015 samples
-rw-r--r-- 1 oracle oinstall  205 May 11  2011 shrept.lst
-rw-r--r-- 1 oracle oinstall   79 Oct  4 13:17 sqlnet.ora
-rw-r--r-- 1 oracle oinstall  117 Oct  4 13:13 tnsnames.ora


#解析listener.ora文件
如果你需要EM管理器name需要配置静态监听程序

官方文档位置:Net Services Reference -> 7 Oracle Net Listener Parameters (listener.ora) ->Example 7-1 listener.ora File

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=LFN)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=LFN))
    (SID_DESC=
      (SID_NAME=LFN)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)))
      
启动监听
[oracle@oracle admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2018 17:23:54

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                04-OCT-2018 17:23:54
Uptime                    0 days 0 hr. 0 min. 0 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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "LFN" has 1 instance(s).
  Instance "LFN", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully

查看状态
[oracle@oracle admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2018 17:24:17

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                04-OCT-2018 17:23:54
Uptime                    0 days 0 hr. 0 min. 23 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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "LFN" has 2 instance(s).
  Instance "LFN", status UNKNOWN, has 2 handler(s) for this service...
  Instance "LFN", status READY, has 1 handler(s) for this service...
Service "LFNXDB" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
The command completed successfully



#配置非默认端口的1526;
 LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=LFN)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=LFN))
    (SID_DESC=
      (SID_NAME=LFN)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)))


LSN2=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526))
      )
   )     

lsnrctl start LSN2
[oracle@oracle admin]$ lsnrctl status LSN2

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 04-OCT-2018 17:56:12

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LSN2
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                04-OCT-2018 17:55:42
Uptime                    0 days 0 hr. 0 min. 30 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/oracle/lsn2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
The listener supports no services
The command completed successfully

no services.没有注册到LSN2

使用非默认的端口注册监听的时候,需要修改local_listener.
alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526))'
SYS@LFN>alter system register; 

System altered.
[oracle@oracle admin]$ lsnrctl status LSN2

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-OCT-2018 10:42:13

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LSN2
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                07-OCT-2018 10:38:32
Uptime                    0 days 0 hr. 3 min. 40 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/oracle/lsn2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
Services Summary...
Service "LFN" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
Service "LFNXDB" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
The command completed successfully
#非默认端口LSN2已经注册到里面 当然也可以在tnsnames.ora里面添加一些配置
#添加LSN2
[oracle@oracle admin]$ more tnsnames.ora
LFN= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521))
   (CONNECT_DATA= 
     (SERVICE_NAME=LFN)
     )
  ) 

LSN2=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526))
  )
  
alter system set local_listener='lsn2';


[oracle@oracle admin]$ lsnrctl status LSN2

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 07-OCT-2018 10:55:16

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     LSN2
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                07-OCT-2018 10:46:16
Uptime                    0 days 0 hr. 9 min. 0 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/oracle/lsn2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.18.101)(PORT=1526)))
Services Summary...
Service "LFN" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
Service "LFNXDB" has 1 instance(s).
  Instance "LFN", status READY, has 1 handler(s) for this service...
The command completed successfully


#Oracle有默认的动态监听程序,动态监听需要Oracle启动到mount状态才可以.如果你要使用EM,或者需要远程启动Oracle.
#那么你需要配置静态监听程序.

 

lv941002
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle的diag文件可以删除,oracle11g rac diag/tnslsnr/pgis2/listener/alert 中的文件能删除吗...
weixin_28834169的博客
04-04 3229
满意答案yaoyangm2016.08.03采纳率：47%等级：12已帮助：27962人当然要对监听日志文件(listener.log)进行定期清理，如果不定期清理，会遇到下面一些麻烦：1：监听日志文件(listener.log)变得越来越大，占用额外的存储空间。(当然现在存储白菜价，不差那几G的空间。但是我们还是要本着工匠情怀，精益求精)2：监听日志文件(listener.log)变得太...
sqlnet.ora oracle,Oracle 网络配置用到的sqlnet.ora, tnsnames.ora, listener.ora文件 | 学步园...
weixin_42298382的博客
04-14 564
listener.ora、tnsnames.ora和sqlnet.ora这3个文件是关系oracle网络配置的3个主要文件，都是放在$ORACLE_HOME/network/admin目录下。其中listener.ora是和数据库服务器端相关，而tnsnames.ora和sqlnet.ora这2个文件不仅仅关系到服务器端，主要的还是和客户端关系紧密。一、客户端设置uAlR6D_9l ?C1oi01...
oracle中的listener.ora和tnsnames.ora及sqlnet.ora
qq_34412985的博客
02-20 323
一、oracle的客户端与服务器端 oracle在安装完成后服务器和客户端都需要进行网络配置才能实现网络连接。 服务器端配置监听器，客户端配置网络服务名 服务器端可配置一个或多个监听程序 。 二、oracle至少启动的服务 如果通过网络访问数据库服务器，至少要启动OracleOraDb11g_home1TNSListener和OracleServiceORCL服务。 如果本...
oracle的sqlnet.ora,tnsnames.ora,listener.ora的理解
lifeneedyou的专栏
03-31 99
1   oracle中3个比较常用的配置文件  tnsnames.ora  ，listener.ora ，sqlnet.ora  ，它们都位于$ORACLE_HOME/db_2\NETWORK\ADMIN 目录下面 ，我们可以手动修改它们，也可以使用ORACLE提供的图形界面来修改 如果使用图形界面，那么netca命令对应  tnsnames.ora 文件 ，net manager对应的...
Oracle配置（tnsnames，sqlnet，listener）
iteye_470的博客
09-10 137
listener.ora,sqlnet.ora,tnsnames.ora,都是放在$ORACLE_HOME/network/admin目录下。重点:三个文件的作用和使用　　　　sqlnet.ora-----作用类似于linux或者其他unix的nsswitch.conf文件，通过这个文件来决定怎么样找一个连接中出现的连接字符串，　　　　例如我们客户端输入　　　　sqlplus sys/orac...
oracle网络配置(listener_ora-sqlnet_ora-tnsnames_ora).mht
09-11
oracle网络配置(listener_ora-sqlnet_ora-tnsnames_ora).mht
认识oracle中的sqlnet.ora tnsnames.ora listener.ora三个文件
07-24
在Oracle安装过程中，我们常常会在安装目录下的`$HOME/network/admin`文件夹中发现三个重要的配置文件：`sqlnet.ora`、`tnsnames.ora`以及`listener.ora`。尽管`tnsnames.ora`的用途较为人所熟知，但`sqlnet.ora`与`...
oracle-10g客户端网络配置.doc
06-20
此时，Net Configuration Assistant将更新网络配置文件，如tnsnames.ora和listener.ora。 配置完成后，可以在ODBC数据源管理器中查看和测试新配置的服务名。选择“Test Connection”来验证连接是否有效。同时，你...
listener_ora--sqlnet_ora--tnsnames_ora的关系以及手工配置举例
10-18
在Oracle数据库系统中，`listener.ora`, `sqlnet.ora` 和 `tnsnames.ora` 是三个至关重要的配置文件，它们共同负责网络服务的管理和连接。理解它们之间的关系和如何手工配置对于管理和维护Oracle数据库是至关重要的...
oracle网络配置 tnsnames.ora
04-09
### Oracle网络配置详解 ...、`tnsnames.ora`和`listener.ora`这三个文件，可以确保Oracle数据库能够正常地在网络环境中被访问。理解这些配置文件的工作原理对于管理和维护Oracle数据库至关重要。
六、配置Oracle数据库的网络环境
weixin_30900589的博客
10-19 489
1. 连接建立的过程场景：oracle默认监听器1521,把数据库服务注册到监听器中,客户端才能通过监听器登录到数据库上操作, 数据库的名称是orcl,注册后,叫服务命名 [oracle@oracledemo ~]$ sqlplus scott/tiger@localhost:1521/orcl.example.com 建立连接注意：监听器只是在连接建立的过程中起作用,一旦连...
ORACLE之手动注册监听listener。alter system set local_listener="XXX"
weixin_30687587的博客
10-15 490
记录下刚刚做的一个为一个数据库（t02）配置多个监听（listener）的实验，过程有点小曲折。 （1）新增两个测试的监听，listener.ora的配置内容（可纯手动编辑该文件或使用netca）如下： （LISTENER为系统原有；L1、L2为测试用新增的监听） [sql]view plaincopy #listener.oraNetworkCon...
listener.ora和localhost
liuzhilong的博客
07-18 794
客户反馈连接不上数据库 查看监听状态和监听配置 listener.ora： SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = ex...
oracle 进1,oracle 进阶 （1）
weixin_39992312的博客
04-07 122
准备从零开始学习oracle。我说过2016年坚持每周写一篇技术博客。那么从现在开始。今天遇到一个弱爆了的问题：前几天换了一台笔记本，在新笔记本上面安装了oracle 11g R2。使用dbca安装完数据库没有报错，就关电脑睡觉了，今天想着开始学习oracle，实现OCP进阶，结果刚开始就犯了一个低级错误。我登陆服务器，[root@oracle~]#su-oracle[oracle@ora...
oracle安装tns错误,TNS-00583 错误解决案例一则
weixin_35224065的博客
04-04 632
TNS-00583 错误解决案例一则SQL> startup restrict;ORACLE instance started.Total System Global Area 535662592 bytesFixed Size 1384752 bytesVariable Size 268439248 bytesDatabase B...
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))说明监听extproc进程请求的地址
haiross的专栏
10-21 8458
这sql语句是什么意思？ 2010-01-03 23:15 提问者悬赏：10分 | whuie | 分类：数据库DB | 浏览1067次 CREATE OR REPLACE FUNCTION MYLIB."MIMA" (l string) RETURN string AS LANGUAGE C NAME "MD5passwd" LIBRARY MYLIB4 PARAMETERS (l
oracle外网监听端口,oracle 11g 修改默认监听端口1521
weixin_36019543的博客
04-03 190
OS:Oracle Linux Server release 5.7DB:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production1、查看监听状态：SQL> !lsnrctl statusLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 1...
local_listener 与 remote_listener 参数说明
小宝老豆的专栏
09-28 3191
一.  LOCAL_LISTENER 和 REMOTE_LISTENER说明 1.1  LOCAL_LISTENER 官网信息如下：        Property Description Parameter type
Oracle（XE）把listener.ora以及tnsnames.ora中host值改为本机IP之后出现的连环问题
qq_39526111的博客
11-09 1940
一、首先ip改为127.0.0.1时cmd连接出现     监听程序当前无法识别连接描述符中请求的服务   问题 网上查阅到https://www.cnblogs.com/shangshan/p/6359880.html 即：在listener中加入   (SID_DESC =       (SID_NAME = XE)       (ORACLE_HOME = C:\oraclexe\ap...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值