[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

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)

--//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

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

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

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

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

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

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

--//還原配置.僅僅知道這些就足夠了.

[20190102]連線串不配置服務名能連線資料庫嗎.txt

相關文章