ITPub博客

首页 > Linux操作系统 > Linux操作系统 > listener.ora/sqlnet.ora/tnsnames.ora配置文件详解

listener.ora/sqlnet.ora/tnsnames.ora配置文件详解

原创 Linux操作系统 作者:hynixm 时间:2011-06-29 13:31:56 0 删除 编辑

启动Net manager

运行netmgr启动Net manager(网络配置工具),配置$ORACLE_HOME/network.admin目录下的3个文件。

Profile条目配置sqlnet.ora

Service naming条目配置tnsnames.ora

Listeners条目配置listener.ora

 

连接本地实例:sqlplus test/pass(唯一不需要侦听器的连接方式)

建立了侦听器,然后侦听器注册实例(静态,动态注册)。实例关闭时会自动解除对侦听器的注册

查看实例名,服务名:

select name,value,isdefault from v$parameter where name in ('instance_name','db_domain','service_name')

 

侦听器控制实用程序(lsnrctl

oracle@linux-wc79:~/product/10.2/db_1/network/admin> lsnrctl status

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-JUN-2011 22:58:48

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux-wc79)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                30-JUN-2011 22:23:01

Uptime                    0 days 0 hr. 35 min. 47 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2/db_1/network/admin/listener.ora

Listener Log File         /home/oracle/product/10.2/db_1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-wc79.site)(PORT=1521)))

Services Summary...

Service "cmis" has 1 instance(s).

  Instance "cmis", status UNKNOWN, has 1 handler(s) for this service...

Service "infadb" has 1 instance(s).

  Instance "infadb", status UNKNOWN, has 1 handler(s) for this service...

Service "tesdb" has 1 instance(s).

  Instance "testdb", status UNKNOWN, has 1 handler(s) for this service...

Service "testdb" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

Service "testdb_XPT" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

The command completed successfully

 

 

oracle@linux-wc79:~/product/10.2/db_1/network/admin> lsnrctl status list2

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-JUN-2011 22:59:37

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux-wc79.site)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias                     LIST2

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                30-JUN-2011 22:29:02

Uptime                    0 days 0 hr. 30 min. 35 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2/db_1/network/admin/listener.ora

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-wc79.site)(PORT=1522)))

The listener supports no services

The command completed successfully

 

其中默认侦听器支持服务testdb,而list2侦听器不支持服务(supports no services

 

名称解析技术(sqlnet.ora指定)

sqlnet.ora文件配置中对应的两种解析:

NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES)

1.       easy connect(不许任何配置,只限于TCP

sqlplus test/pass@linux-wc79/testdb

向侦听注册的服务名和机器名linux-wc79一样可以:

sqlplus test/pass@linux-wc79

2.       本地名称解析方法

本地文件tnsnames.ora

3.       目录名称解析方法

4.       外部名称解析方法

 

配置动态服务注册:

1.       关掉侦听器LISTENER保持list2 工作

Lsnrctl stop listener

Lsnrctl status list2

2.       net manager配置一个和list2侦听端口一样的服务testdb_1522

 

3.oracle@linux-wc79:~/product/10.2/db_1/network/admin> lsnrctl status list2

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 01-JUL-2011 00:22:35

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux-wc79.site)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias                     list2

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                01-JUL-2011 00:22:23

Uptime                    0 days 0 hr. 0 min. 11 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2/db_1/network/admin/listener.ora

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-wc79.site)(PORT=1522)))

The listener supports no services

The command completed successfully

 

 

4.sqlplus / as sysdba

SQL> alter system set local_listener='testdb_1522';

SQL> alter system register;

 

5.oracle@linux-wc79:~/product/10.2/db_1/network/admin> lsnrctl status list2

 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 01-JUL-2011 00:22:55

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux-wc79.site)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias                     list2

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                01-JUL-2011 00:22:23

Uptime                    0 days 0 hr. 0 min. 31 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2/db_1/network/admin/listener.ora

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux-wc79.site)(PORT=1522)))

Services Summary...

Service "testdb" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

Service "testdb_XPT" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

The command completed successfully

 

6.1522端口登陆testdb_1522服务

oracle@linux-wc79:~> sqlplus test/pass@testdb_1522

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 1 09:14:43 2011

 

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

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL>

 

7.撤销动态注册(用sysdba登陆)

SQL> alter system set local_listener='';

静态注册

修改listener.ora文件,加上SID_LIST_LISTENER=….,listener侦听器静态注册服务

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = testdb)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = tesdb)

    )

    (SID_DESC =

      (SID_NAME = cmis)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = cmis)

    )

    (SID_DESC =

      (SID_NAME = infadb)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = infadb)

    )

  )

 

LIST2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79.site)(PORT = 1522))

      )

    )

  )

 

LOGGING_LIST2 = OFF

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79)(PORT = 1521))

  )

 

TRACE_LEVEL_LIST2 = OFF

 

 

listener.ora/sqlnet.ora/tnsnames.ora配置文件详解

 

oracle网络配置

三个配置文件listener.orasqlnet.oratnsnames.ora,都是放在$ORACLE_HOME/network/admin目录下。

1. sqlnet.ora

sqlnet.ora-----通过这个文件来决定怎么样找一个连接中出现的连接字符串。

例如我们客户端输入

sqlplus sys/oracle@testdb

假如我的sqlnet.ora是下面这个样子

 

SQLNET.AUTHENTICATION_SERVICES= (NTS)本地用户方式登陆需注释这一行(sqlplus / as sysdba

NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

 

那么,客户端就会首先在tnsnames.ora文件中找testdb的记录.如果没有相应的记录则尝试把testdb当作一个主机名,通过网络的途径去解析它的ip地址然后去连接这个ipGLOBAL_DBNAME=testdb这个实例,当然我这里testdb并不是一个主机名

 

如果我是这个样子

NAMES.DIRECTORY_PATH= (TNSNAMES)NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES)

那么客户端就只会从tnsnames.ora查找teestdb的记录,括号中还有其他选项,如LDAP(目录名称解析)等并不常用。

2. tnsnames.ora

tnsnames.ora------这个文件类似于unixhosts文件,提供的tnsname到主机名或者ip的对应。

只有当sqlnet.ora中类似NAMES.DIRECTORY_PATH= (TNSNAMES)这样,也就是客户端解析连接字符串的顺序中有TNSNAMES是,才会尝试使用这个文件。

例子:

TESTDB_1522 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testdb)

    )

  )

 

TESTDB =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testdb)

    )

  )

 

LINUX-WC79 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testdb)

    )

  )

 

ORA_TEST:客户端连接服务器端使用的服务别名。注意一定要顶行书写,否则会无法识别服务别名。

PROTOCOL:客户端与服务器端通讯的协议,一般为TCP,该内容一般不用改。

HOSTORACLE服务器端IP地址或者hostname。确保服务器端的监听启动正常。

PORT:数据库侦听正在侦听的端口,可以察看服务器端的listener.ora文件或在数据库侦听所在的机器的命令提示符下通过lnsrctl status [listener name]命令察看。此处Port的值一定要与数据库侦听正在侦听的端口一样。

SERVICE_NAME:在服务器端,用system用户登陆后,sqlplus> show parameter service_name命令查看。

3. listener.ora

listener.ora------listener监听器进程的配置文件

关于listener进程就不多说了,接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不是使用的远程的连接,listener进程就不是必需的,同样的如果关闭listener进程并不会影响已经存在的数据库连接。

例子:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = testdb)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = tesdb)

    )

    (SID_DESC =

      (SID_NAME = cmis)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = cmis)

    )

    (SID_DESC =

      (SID_NAME = infadb)

      (ORACLE_HOME = /home/oracle/product/10.2/db_1)

      (GLOBAL_DBNAME = infadb)

    )

  )

 

LIST2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79.site)(PORT = 1522))

      )

    )

  )

 

LOGGING_LIST2 = OFF

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = linux-wc79)(PORT = 1521))

  )

 

TRACE_LEVEL_LIST2 = OFF

LISTENER:监听名称,可以配置多个监听,多个监听的端口号要区分开来。

GLOBAL_DBNAME:全局数据库名。通过select * from global_name;查询得出

ORACLE_HOMEoracle软件的跟目录

SID_NAME:服务器端(本机)的SID

PROTOCOL:监听协议,一般都使用TCP

HOST:本机IP地址,双机时候使用浮动IP

PORT:监听的端口号,使用netstat an检查该端口不被占用。

 

当你输入sqlplus sys/oracle@orcl的时候

1 查询sqlnet.ora看看名称的解析方式,发现是TNSNAME

2 则查询tnsnames.ora文件,从里边找orcl的记录,并且找到主机名,端口和service_name

3 如果listener进程没有问题的话,建立与listener进程的连接。

4 根据不同的服务器模式如专用服务器模式或者共享服务器模式,listener采取接下去的动作。默认是专用服务器模式,没有问题的话客户端就连接上了数据库的server process

5 这时候网络连接已经建立,listener进程的历史使命也就完成了。

 

sql*plus运行基本机理:

在用户输入sqlplus system/manager@test后,sqlplus程序会自动到sqlnet.ora文件中找NAMES.DEFAULT_DOMAIN参数,假如该参数存在,则将该参数中的值取出,加到网络服务名的后面,即此例中你的输入由sqlplus system/manager@test自动变为sqlplus system/manager@test.server.com,然后再到tnsnames.ora文件中找test.server.com网络服务名,这当然找不到了,因为该文件中只有test网络服务名,所以报错。解决的办法就是将sqlnet.ora文件中的NAMES.DEFAULT_DOMAIN参数注释掉即可,如#NAMES.DEFAULT_DOMAIN = server.com。假如NAMES.DEFAULT_DOMAIN参数不存在,则sqlplus程序会直接到tnsnames.ora文件中找test网络服务名,然后取出其中的hostporttcpservice_name,利用这些信息将连接请求发送到正确的数据库服务器上。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25897089/viewspace-700962/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 正则表达式
下一篇: tput命令
请登录后发表评论 登录
全部评论

注册时间:2011-06-02

  • 博文量
    31
  • 访问量
    34837