[20181128]toad连接数据库的问题(补充).txt

[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

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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" 

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

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