Oracle中tnsnames.ora的作用和配置方法

tnsnames.ora 在listener的作用&＃xff0c;数据库只是在启动的过程中会读到tnsnames.ora中的内容去解析LOCAL_LISTENER&＃xff0c;之后tnsnames的变化

1、tnsnames.ora 在listener的作用

数据库只是在启动的过程中会读到tnsnames.ora中的内容去解析LOCAL_LISTENER&＃xff0c;之后tnsnames的变化和侦听无关

---设置了LOCAL_LISTENER&＃xff0c;1522端口

SQL> show parameter list

NAME TYPE VALUE

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

listener_networks string

local_listener string LISTENER_1

remote_listener string

---listener.ora里面的内容

[Oracle&＃64;node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/db/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_1 &＃61;

(DESCRIPTION &＃61;

(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 1.1.1.10)(PORT &＃61; 1522))

)

ADR_BASE_LISTENER_1 &＃61; /oracle/app/oracle

---tnsnames.ora里面的内容

LISTENER_1 &＃61;

(DESCRIPTION &＃61;

(ADDRESS_LIST &＃61;

(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 1.1.1.10)(PORT &＃61; 1522))

)

)

目前数据库状态正常

现在删除tnsnames。ora&＃xff0c;启动数据库

SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name &＃39;LISTENER_1

数据库报错&＃xff0c;说明数据库在启动的时候是通过tnsnames.ora去解析LOCAL_LISTENER的

step2&＃xff1a;修改tnsnames。ora的内容给他一个错误的IP地址

[oracle&＃64;node1 admin]$ vi tnsnames.ora

LISTENER_1 &＃61;

(DESCRIPTION &＃61;

(ADDRESS_LIST &＃61;

(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 1.1.1.17)(PORT &＃61; 1522))

)

)

1.1.1.17为错误的IP

SQL> startup

ORACLE instance started.

Total System Global Area 1054593024 bytes

Fixed Size 1349616 bytes

Variable Size 507512848 bytes

Database Buffers 541065216 bytes

Redo Buffers 4665344 bytes

Database mounted.

Database opened.

SQL>

数据库能正常启动

SQL> !ps -ef | grep tns

oracle 3219 1 0 21:26 ? 00:00:00 /oracle/app/oracle/db/bin/tnslsnr LISTENER_1 -inherit

oracle 4125 3881 0 21:40 pts/1 00:00:00 /bin/bash -c ps -ef | grep tns

oracle 4127 4125 0 21:40 pts/1 00:00:00 /bin/bash -c ps -ef | grep tns

SQL> !lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-SEP-2013 21:40:25

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

Connecting to (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;TCP)(HOST&＃61;1.1.1.10)(PORT&＃61;1522)))

STATUS of the LISTENER

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

Alias LISTENER_1

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 30-SEP-2013 21:26:50

Uptime 0 days 0 hr. 13 min. 34 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/app/oracle/db/network/admin/listener.ora

Listener Log File /oracle/app/oracle/diag/tnslsnr/node1/listener_1/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;tcp)(HOST&＃61;1.1.1.10)(PORT&＃61;1522)))

The listener supports no services

The command completed successfully

SQL>

没有服务注册到listener

step3&＃xff1a;修改tnsnames.ora的内容为正确的IP

重新启动listener

[oracle&＃64;node1 admin]$ lsnrctl stop LISTENER_1

[oracle&＃64;node1 admin]$ lsnrctl start LISTENER_1

SQL> alter system register;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle&＃64;node1 admin]$ lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-SEP-2013 21:43:03

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

Connecting to (DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;TCP)(HOST&＃61;1.1.1.10)(PORT&＃61;1522)))

STATUS of the LISTENER

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

Alias LISTENER_1

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 30-SEP-2013 21:42:30

Uptime 0 days 0 hr. 0 min. 32 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/app/oracle/db/network/admin/listener.ora

Listener Log File /oracle/app/oracle/diag/tnslsnr/node1/listener_1/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;tcp)(HOST&＃61;1.1.1.10)(PORT&＃61;1522)))

The listener supports no services

The command completed successfully

还是不行

[oracle&＃64;node2 admin]$ sqlplus vic&＃64;vic2

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 30 21:43:32 2013

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

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

客服端也无法连接&＃xff0c;说明重启listner对不会读取tnsnames。ora的内容。

接下来请看第2页精彩内容&＃xff1a;

相关阅读&＃xff1a;

Oracle数据库中listener.ora sqlnet.ora tnsnames.ora的区别

Oracle RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)

Oracle RAC 监听配置 (listener.ora tnsnames.ora)

Oracle: listener.ora 、sqlnet.ora 、tnsnames.ora的配置及例子

Oracle本地命名服务tnsnames.ora配置

&＃xff0c;