首页 > 代码库 > 一些name的连接 学习总结
一些name的连接 学习总结
主要实现下，oracle中instance_name ,db_name等对连接的关系（动态监听）
我们查看监听
[oracle@localhost ~]$ lsnrctl status lsnr2
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-NOV-2014 19:59:47
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias lsnr2
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 17-NOV-2014 19:53:45
Uptime 0 days 0 hr. 6 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/11g/db_1/network/admin/listener.ora
Listener Log File /app/oracle/product/11g/db_1/log/diag/tnslsnr/localhost/lsnr2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
可以看到监听中的serice是orcl，则在你的tns中的配置中的
132test=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.132)(PORT = 1522))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
这样一些客户端能连接上。
1.当我们修改db_domain
SQL> alter system set db_domain=‘xxx‘ scope= spfile;System altered.
需要重启数据库。
在执行lsnrctl status ，会发现，现在的service name为
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))STATUS of the LISTENER------------------------Alias lsnr2Version TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date 17-NOV-2014 19:53:45Uptime 0 days 0 hr. 13 min. 36 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /app/oracle/product/11g/db_1/network/admin/listener.oraListener Log File /app/oracle/product/11g/db_1/log/diag/tnslsnr/localhost/lsnr2/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1)))Services Summary...Service "orcl.xxx" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...Service "orclXDB.xxx" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully
发现server 为 "orcl.xxx" ，此时的oracle 客户端连接工具，如pl/sql 等工具将连接不上。需要重新配置TNS。
2.如果修改service_names,则情况如下，一个实例下的数据库能对应多个服务
先将原来设置的db_domain 还原
SQL> alter system reset db_domain; System altered.重启SQL> show parameter db_domainNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_domain string
修改service_names
SQL> alter system set service_names=app1,app2;System altered.SQL> show parameter serviceNAME TYPE VALUE------------------------------------ ----------- ------------------------------service_names string APP1, APP2
在查看监听内容，发现
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1)))Services Summary...Service "APP1" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...Service "APP2" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully
此时，我这里使用PL/SQL 可以看到连个都能连上
tns如下
132testapp1= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.132)(PORT = 1522)) (CONNECT_DATA = http://www.mamicode.com/(SERVER = DEDICATED)>
3.db_name是写入到文件头里面的，一般不能修改
4.db_unique_name
SQL> alter system set db_unique_name=‘aaaa‘ scope=spfile;System altered.
重启，观察lsnrctl
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1)))
Services Summary...
Service "APP1" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "APP2" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "aaaa" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
这样的实验我们可以看书使用tns连接的时候，oracle的一些配置对于tns连接的。可以去排错。
一些name的连接 学习总结