oracle中tnsnames.ora的作用

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來實現，今晚有活動，明天驗證