Oracle 网络配置：
Oracle 网络配置用到的3个配置文件：listener.ora ，sqlnet.ora，tnsnames.ora
[oracle@test admin]$ cd $ORACLE_HOME/network/admin
1) listener.ora 接受远程对数据库的接入申请并转交给oracle的服务器进程。
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
当listener配置好后，我们可用tnsping来检查配置是否正确：
[oracle@test admin]$ tnsping testdb
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-MAY-2009 05:13:39
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (10 msec) --->结是是这样就表示是正确的。
当然还可以在命令行运行lsnrctl来查看listener的状态：
[oracle@test admin]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-MAY-2009 05:16:32
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-MAY-2009 05:46:58
Uptime 4 days 23 hr. 29 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" 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
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
关于listener的名称:
在配置listener的时候，有一个问题要注意，当你用lsnrctl stutus来查看listener的状态的时候，lsnrctl 默认只会检查listener的名字为listener,所以如果你的listener名字是不是listener,而是叫做其它名字的话，那么在运行那个命令的时候，后面要带上listner的名字，否则会出错。
如下例证：
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
# Generated by Oracle configuration tools.
)
LISTENER = ------->这里的listener名字就是默认的名字
(DESCRIPTION_LIST =
(DESCRIPTION =
(SID_NAME = testdb)
(ADDRESS = (PROTOCOL= IPC)(KEY= EXTPROCTEST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT =1521))
)
)
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 03:48:08
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias testdb
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 30-AUG-2009 02:52:04
Uptime 0 days 0 hr. 56 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/testdb.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(SID_NAME=testdb))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" 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@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
# Generated by Oracle configuration tools.
testdb = --------->这里的listener的名字不是默认的名字
(DESCRIPTION_LIST =
(DESCRIPTION =
(SID_NAME = testdb)
(ADDRESS = (PROTOCOL= IPC)(KEY= EXTPROCTEST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT =1521))
)
)
[oracle@test admin]$ lsnrctl status ----->如果不带listener名字的话，就出错。因为它默认还是去找名字为listener的listener.
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 03:48:30
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@test admin]$ lsnrctl status testdb ---->这样就是ok的。
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 03:48:34
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias testdb
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 30-AUG-2009 02:52:04
Uptime 0 days 0 hr. 56 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/testdb.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(SID_NAME=testdb))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" 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
关于listener的端口:
listener在默认情况下用1521，如果你用其它端口，会出现这样的问题：
当用默认端口时，lsnrctl staus结果正常，客户端连接正常。
[oracle@test admin]$ cat listener.ora
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SID_NAME = testdb)
(ADDRESS = (PROTOCOL= IPC)(KEY= EXTPROCTEST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT =1521))
[oracle@test admin]$ lsnrctl staus
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 04:06:40
Copyright (c) 1991, 2005, Oracle. All rights reserved.
NL-00853: undefined command "staus". Try "help"
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 04:06:42
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 30-AUG-2009 04:05:09
Uptime 0 days 0 hr. 1 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(SID_NAME=testdb))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", 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 "testdbXDB" 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
[rachel@test ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 30 04:07:11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter local_lis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 127.0.0.1)(PORT=1521))
当用其它非默认端口时，问题出现了。Instance staus 状态为unknow, 而且客户端连不上。
(当然，当listen.ora里的端口更改了，请别忘了将tnsname.ora里的端口作相应更改）。
[oracle@test admin]$ cat listener.ora
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SID_NAME = testdb)
(ADDRESS = (PROTOCOL= IPC)(KEY= EXTPROCTEST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT =1666))
)
)
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 04:11:20
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 30-AUG-2009 04:11:16
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1666))(SID_NAME=testdb))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@test admin]$
[rachel@test ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 30 04:11:48 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
Enter user-name:
[oracle@test admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 30 04:12:50 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn testdb as sysdba
Enter password:
Connected.
SQL> show parameter local_lis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 127.0.0.1)(PORT=1521))
我虽然在listener.ora & tnsname.ora里将listener改为了1666， 而且重启了listener,可是从数据字典里查到的local_listener的port还是1521，所以我们需要将local_lis的值改成与前面那两个文件一致的端口，才不会有报错。
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT=1666))';
System altered.
SQL> show parameter local_lis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 127.0.0.1)(PORT=1666))
这样改过来以后， lsnrctl staus 和客户端的连接就正常了
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-AUG-2009 04:16:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(SID_NAME=testdb)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCTEST)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 30-AUG-2009 04:11:16
Uptime 0 days 0 hr. 5 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_3/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCTEST))(SID_NAME=testdb))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1666))(SID_NAME=testdb))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", 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 "testdbXDB" 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@test admin]$
[rachel@test ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 30 04:17:07 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2) sqlnet.ora 通过这个文件来决定怎样找一个连接中出现的连接字符串
[oracle@test admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES,hostname,onames)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)----表示将首先利用tnsnames进行解析；如果tnsnames解析不到，将使用hostname解析；如果hostname解析不到，将采用onames进行解析；最后使用EZCONNECT解析。
3)tnsnames.ora 这个文件放在客户端机器上，记录客户端访问数据库的本地配置，其实就是定义网络服务
[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/db_3/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
最后，别忘了哦：
建议在配置的时候，尽量使用真实的IP 地址，不是使用127.0.0.1， 如果在本机工作，无所谓这两个哪个都行，但是从远程客户端连进来的时候，设成127.0.0.1就会访问不了。
