[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.收尾:

--//还原配置.仅仅知道这些就足够了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2287143/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2287143/