1、tnsnames.ora 在listener的作用
数据库只是在启动的过程中会读到tnsnames.ora中的内容去解析LOCAL_LISTENER，之后tnsnames的变化和侦听无关
---设置了LOCAL_LISTENER，1522端口
SQL> show parameter list
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string LISTENER_1
remote_listener string
---listener.ora里面的内容
[oracle@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 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.10)(PORT = 1522))
)
ADR_BASE_LISTENER_1 = /oracle/app/oracle
---tnsnames.ora里面的内容
LISTENER_1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.10)(PORT = 1522))
)
)
目前数据库状态正常
现在删除tnsnames。ora，启动数据库
SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_1
数据库报错，说明数据库在启动的时候是通过tnsnames.ora去解析LOCAL_LISTENER的
step2：修改tnsnames。ora的内容给他一个错误的IP地址
[oracle@node1 admin]$ vi tnsnames.ora
LISTENER_1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.17)(PORT = 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=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.10)(PORT=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=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.10)(PORT=1522)))
The listener supports no services
The command completed successfully
SQL>
没有服务注册到listener
step3：修改tnsnames.ora的内容为正确的IP
重新启动listener
[oracle@node1 admin]$ lsnrctl stop LISTENER_1
[oracle@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@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=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.10)(PORT=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=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.10)(PORT=1522)))
The listener supports no services
The command completed successfully
还是不行
[oracle@node2 admin]$ sqlplus vic@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
客服端也无法连接，说明重启listner对不会读取tnsnames。ora的内容。
step4：重启数据库
SQL> shut immediate
startup
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
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.
alter system register;
Database opened.
SQL> SQL>
System altered.
oracle@node1 admin]$ lsnrctl status LISTENER_1
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-SEP-2013 21:45:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.10)(PORT=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. 3 min. 7 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=(ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.10)(PORT=1522)))
Services Summary...
Service "haha" has 1 instance(s).
Instance "vicdb", status READY, has 1 handler(s) for this service...
Service "hehe" has 1 instance(s).
Instance "vicdb", status READY, has 1 handler(s) for this service...
Service "vicdb" has 1 instance(s).
Instance "vicdb", status READY, has 1 handler(s) for this service...
Service "vicdbXDB" has 1 instance(s).
Instance "vicdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@node2 admin]$ sqlplus vic@vic2
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 30 21:45:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
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
SQL>
能正常启动，能正常连接,说明重启数据库时候能重新读取tnsnames。ora,是否有其它办法，可以再试，应该可以使用lsnrctl reload xxx来实现，今晚有活动，明天验证
