[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"