[20181128]toad連線資料庫的問題(補充).txt

--//昨天下午,使用toad連線資料庫,出現一些奇怪的問題,我感覺有必要對以前遇到的問題做一個總結:

--//連結:http://blog.itpub.net/267265/viewspace-2221915/

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

SCOTT@book> show parameter dispatchers

NAME TYPE VALUE

--------------- ------- -------------------------------------

dispatchers string (PROTOCOL=TCP) (SERVICE=book,bookXDB)

max_dispatchers integer

SCOTT@book> show parameter service

NAME TYPE VALUE

------------- ------ ---------------

service_names string BOOK, BOOKSHARE

--//首先一點,如果應用使用共享伺服器模式,最好單獨配置服務名專用於共享伺服器模式.而不是與專用伺服器模式同名.

--//一般單例項,會自動建立一個與sid同名的service_names,建立共享伺服器不要在使用該名字.

2.tnsnames.ora配置連線別名,最好顯式定義連線模式,即引數SERVER = DEDICATED or SHARED ).

78S =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))

)

(SDU = 32768)

(CONNECT_DATA =

# (SERVER = SHARED)

# (SERVER = DEDICATED)

(SERVICE_NAME = book)

# (SID = book)

)

d:\tools\sqltemp>sqlplus scott/book@78s

SCOTT@78s> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

261 1 9948:9592 SHARED 27396 20 1 alter system kill session '261,1' immediate;

--//可以發現如果使用引數SERVICE_NAME = book的情況下,沒有指定SERVER,預設連線是共享伺服器模式.

78S =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))

)

(SDU = 32768)

(CONNECT_DATA =

# (SERVER = SHARED)

# (SERVER = DEDICATED)

# (SERVICE_NAME = book)

(SID = book)

)

d:\tools\sqltemp>sqlplus scott/book@78s

SCOTT@78s> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

28 7 11024:9208 DEDICATED 27438 26 3 alter system kill session '28,7' immediate;

--//可以發現如果使用引數SID = book的情況下,沒有指定SERVER,預設連線是專用伺服器模式.

--//所以建議配置連線串最好顯式的指定SERVER引數.

3.ezconnect連線模式,如果服務名可以執行在共享或者專用伺服器模式下,沒有指定的情況下,優先使用共享伺服器模式:

d:\tools\sqltemp>sqlplus scott/book@192.168.100.78:1521/book

SCOTT@192.168.100.78:1521/book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

261 3 7232:9504 SHARED 27396 20 1 alter system kill session '261,3' immediate;

--//沒有指定共享伺服器模式,優先使用共享伺服器模式.

d:\tools\sqltemp>sqlplus scott/book@192.168.100.78:1521/book:DEDICATED

SCOTT@192.168.100.78:1521/book:DEDICATED> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

54 3 9648:10524 DEDICATED 27464 28 2 alter system kill session '54,3' immediate;

--//如果使用ezconnect時,指定server=DEDICATED,就不會存在歧義性.

4.sys使用者連線問題:

$ sqlplus sys/oracle@127.0.0.1:1521/book as sysdba

SYS@127.0.0.1:1521/book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

261 9 27497 SHARED 27396 20 1 alter system kill session '261,9' immediate;

$ sqlplus sys/oracle@127.0.0.1:1521/book:DEDICATED as sysdba

SYS@127.0.0.1:1521/book:DEDICATED> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

274 5 27498 DEDICATED 27500 21 3 alter system kill session '274,5' immediate;

d:\tools\sqltemp>sqlplus sys/oracle@192.168.100.78:1521/book as sysdba

SYS@192.168.100.78:1521/book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------

261 17 7448:9696 SHARED 27396 20 1 alter system kill session '261,17' immediate;

--//但是toad如果使用sys使用者選擇ezconnect連線,會卡死...

5.toad連線問題:

--//toad命令列連線模式裡面的引數-c,實際上如果你使用ezconnect連線(我個人這不是,裡面的引數實際上sid而不是服務名).

--//也就是這樣的方式不是真正意義的ezconnect連線.

--//比如執行:

%toadzws% -min -max -c "scott/book@192.168.100.78:1521/noexist"

--//報的錯誤如下:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

--//說明裡面的noexist是指sid.

--//注toadzws定義如下:

d:\tools\sqltemp>set toadzws

toadzws="E:\Program Files\Dell\Toad for Oracle 12.6\Toad.exe"

--//還有toad的tns指定的連線模式(類似ezconnect)實際上與前面的-c引數一樣裡面的引數也是sid.

--//這也就是為什麼我執行

%toadzws% -min -max -c "sys/oracle@192.168.100.78:1521/book as sysdba"

--//可以成功.而在toad下執行使用ezconnect模式報錯.連線模式是專用連線模式.

--//但是toad如果使用sys使用者選擇ezconnect連線使用服務名的情況下,會很慢...(不知道是否是bug),有時候會報錯,

ORA-28547: connection to server failed, probable Oracle Net admin error

---//如果你不等報錯,在命令列執行:

sqlplus sys/oracle@192.168.100.78:1521/book as sysdba

sqlplus scott/book@192.168.100.78:1521/book

--//這個連線就會掛起!!最終報ora-28547錯誤.ORA-28547: connection to server failed, probable Oracle Net admin error

--//也就是我以前遇到的問題.,以前遇到的問題,連結:http://blog.itpub.net/267265/viewspace-2137526/

6.僅僅rman要求使用專用模式,不過一般rman都是在服務端執行,很少會遇到這個問題.

$ rman target sys/oracle@127.0.0.1:1521/book

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 28 15:53:39 2018

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-28547: connection to server failed, probable Oracle Net admin error

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$ rman target sys/oracle@127.0.0.1:1521/book:DEDICATED

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 28 15:56:27 2018

connected to target database: BOOK (DBID=1337401710)

7.toad連線rac例項問題.

--//有時候我喜歡登入連線2個例項,在命名行模式最好使用sid.(至少連線快一些)例子:

%toadzws% -min -max -c "system/xxx@scan_ip:1521/orcl2" "system/xxx@scan_ip/orcl1"

[20181128]toad連線資料庫的問題(補充).txt

相關文章