[20190102]连接串不配置服务名能连接数据库吗.txt

[20190102]连接串不配置服务名能连接数据库吗.txt --//如果连接串里面没有配置服务名或者sid能连接数据库吗? --//这个问题就像别人问我不启动监听远程能连接数据库吗? --//我的第一回答是不行,实际上不启动监听是可以的,参考连接: --//http://blog.itpub.net/267265/viewspace-1816211/ =>[20151023]不启动监听远程能连接数据库吗? --// 连接串不配置服务名也可以连接数据库, 通过例子说明问题: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//连接串在tnsnames.ora的配置如下: 78=   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))     )     (SDU = 32768)     (CONNECT_DATA =       (SERVER = DEDICATED) #      (SERVICE_NAME = book)     )   ) --//注解了服务名. 2.测试: d:\>tnsping  78 2 TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 02-JAN-2019 14:48:54 Copyright (c) 1997, 2016, Oracle.  All rights reserved. Used parameter files: E:\app\oracle\product\12.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))) (SDU = 32768) (CONNECT_DATA = (SERVER = DEDICATED))) OK (0 msec) OK (0 msec) --//tnsping没有问题,并不说明client段能连上.仅仅说明打开了1521端口. d:\>sqlplus -l scott/book@78 SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 2 14:49:55 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. ERROR: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus 3.实际上只要在服务器监听加入如下: DEFAULT_SERVICE_LISTENER=book --//再重启启动监听,客户端就可以连接数据库. $ grep DEFAULT_SERVICE_LISTENER listener.ora DEFAULT_SERVICE_LISTENER=book $ lsnrctl stop ;sleep 1 ; lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-JAN-2019 14:51:44 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK))) The command completed successfully LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-JAN-2019 14:51:50 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(ARGV0=LLLLLL)(HOST=0.0.0.0)(PORT=1521)(ARGV0=KKKKK))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date                02-JAN-2019 14:51:50 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Default Service           book Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "booK123" has 1 instance(s).   Instance "book", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully d:\>sqlplus -l scott/book@78 SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 2 14:52:10 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SCOTT@78> @ spid  SID    SERIAL# PROCESS                  SERVER    SPID  PID  P_SERIAL# C50 ---- ---------- ------------------------ --------- ----- --- ---------- ---------------------------------------------   41        299 9484:9912                DEDICATED 60928  27        108 alter system kill session '41,299' immediate; --//测试使用ezconnect方式看看: d:\>sqlplus scott/book@192.168.100.78:1521 SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 2 14:53:01 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SCOTT@192.168.100.78:1521> select sysdate from dual ; SYSDATE ------------------- 2019-01-02 14:53:07 SCOTT@192.168.100.78:1521> @ spid SID    SERIAL# PROCESS                  SERVER    SPID  PID  P_SERIAL# C50 --- ---------- ------------------------ --------- ----- --- ---------- --------------------------------------------- 262         71 9640:7508                SHARED    58907  20          1 alter system kill session '262,71' immediate; --//连接模式=SHARED.这个问题源于配置参数dispatchers,加入了服务名book. SCOTT@book> show parameter dispatchers NAME            TYPE     VALUE --------------- -------- ------------------------------------- dispatchers     string   (PROTOCOL=TCP) (SERVICE=book,bookXDB) max_dispatchers integer d:\>sqlplus scott/book@192.168.100.78:1521/:DEDICATED SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 2 14:54:47 2019 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SCOTT@192.168.100.78:1521/:DEDICATED> @ spid SID    SERIAL# PROCESS                  SERVER    SPID  PID  P_SERIAL# C50 --- ---------- ------------------------ --------- ----- --- ---------- ---------------------------------------------  41        297 9472:9520                DEDICATED 60916  27        107 alter system kill session '41,297' immediate; --//连接模式=DEDICATED. 4.收尾: --//还原配置.仅仅知道这些就足够了.