上一篇 oracle环境变量TWO_TASK和LOCAL测试了linux环境的 TWO_TASK 变量，

这次来测试一下windows环境的LOCAL 变量

实验环境

环境是 WINDOWS 2016 + oracle database 19c

注意： 默认环境变量没有设置 ORACLE_SID， 但注册表中有 ORACLE_SID 会生效， 因此为了测试需要， 我们要先把注册表中 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB19Home1\ORACLE_SID 删除， 才好测后面的环境变量设置。

创建一个sql文件，显示当前用户， 待会要用到

C:\>type abc.sqlshow user;exit;EOF

查看下tns配置

ABC =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.55.168)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = test)    )  )

测试命令

分别是

a方式: 不输入账号密码以sysdba登录

b方式: 账号/密码@tnsname登录

c方式: 账号/密码登录

这3种情况，然后执行准备的脚本(很简单，显示当前用户并退出)

监听器状态，目前是开启

C:\Users\Administrator>lsnrctl statusLSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 29-MAR-2023 20:22:18Copyright (c) 1991, 2019, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.55.168)(PORT=1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - ProductionStart Date                29-MAR-2023 20:18:45Uptime                    0 days 0 hr. 3 min. 33 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   C:\app\administrator\product\19.3.0\dbhome_1\network\admin\listener.oraListener Log File         C:\app\Administrator\diag\tnslsnr\WIN-BRVSSISQILC\listener\alert\log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.55.168)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))Services Summary...Service "CLRExtProc" has 1 instance(s).  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "test" has 1 instance(s).  Instance "test", status READY, has 1 handler(s) for this service...Service "testXDB" has 1 instance(s).  Instance "test", status READY, has 1 handler(s) for this service...The command completed successfully

第一种情况

只设置了 ORACLE_SID , 也是99.99%的环境，通常大家环境都是只设置了ORACLE_SID环境变量

实验结果：abc方式 都是可以登录的

C:\Users\Administrator>echo %TWO_TASK%%TWO_TASK%C:\Users\Administrator>echo %LOCAL%%LOCAL%C:\Users\Administrator>echo %ORACLE_SID%testC:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:26:10 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYS"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:26:11 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:26:14 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:26:11 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

第二种情况 只设置了 TWO_TASK

实验结果: b方式可以登录的

C:\Users\Administrator>echo %TWO_TASK%ABCC:\Users\Administrator>echo %LOCAL%%LOCAL%C:\Users\Administrator>echo %ORACLE_SID%%ORACLE_SID%C:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:33:35 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:33:40 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:32:48 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:33:45 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

第三种情况 只设置了 LOCAL

实验结果：abc方式 都是可以登录的

C:\Users\Administrator>echo %TWO_TASK%%TWO_TASK%C:\Users\Administrator>echo %LOCAL%ABCC:\Users\Administrator>echo %ORACLE_SID%%ORACLE_SID%C:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:32:35 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYS"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:32:42 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:29:44 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:32:48 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:32:42 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

再实验下关闭监听器的情况

监听器状态 关闭

C:\Users\Administrator>lsnrctl stopLSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 29-MAR-2023 20:34:34Copyright (c) 1991, 2019, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.55.168)(PORT=1521)))The command completed successfullyC:\Users\Administrator>lsnrctl statusLSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 29-MAR-2023 20:34:40Copyright (c) 1991, 2019, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.55.168)(PORT=1521)))TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error  TNS-00511: No listener   64-bit Windows Error: 61: Unknown errorConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error  TNS-00511: No listener   64-bit Windows Error: 2: No such file or directory

第一种情况

只设置了 ORACLE_SID , 也是99.99%的环境，通常大家环境都是只设置了ORACLE_SID环境变量

实验结果： b方式登录失败，因为它依赖监听器， ac方式登录成功不需要监听器

C:\Users\Administrator>echo %TWO_TASK%%TWO_TASK%C:\Users\Administrator>echo %LOCAL%%LOCAL%C:\Users\Administrator>echo %ORACLE_SID%testC:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:40:14 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYS"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:40:21 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name:ERROR:ORA-01017: invalid username/password; logon deniedSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:40:26 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:33:41 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

第二种情况 只设置了 TWO_TASK

实验结果: abc方式全部失败

C:\Users\Administrator>echo %TWO_TASK%ABCC:\Users\Administrator>echo %LOCAL%%LOCAL%C:\Users\Administrator>echo %ORACLE_SID%%ORACLE_SID%C:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:38:07 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:38:16 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:38:23 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorEnter user-name:ERROR:ORA-12560: TNS:protocol adapter errorSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

第三种情况 只设置了 LOCAL

实验结果： abc全部失败

C:\Users\Administrator>echo %TWO_TASK%%TWO_TASK%C:\Users\Administrator>echo %LOCAL%ABCC:\Users\Administrator>echo %ORACLE_SID%%ORACLE_SID%C:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:39:03 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:39:12 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*PlusC:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:39:25 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerEnter user-name:ERROR:ORA-12541: TNS:no listenerSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

实验结果： abc方式都能登录

C:\Users\Administrator>echo %TWO_TASK%%TWO_TASK%C:\Users\Administrator>echo %LOCAL%ABCC:\Users\Administrator>echo %ORACLE_SID%testC:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:48:10 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYS"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:48:14 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:40:26 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:48:19 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:48:15 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

实验结果： abc方式都能登录

C:\Users\Administrator>echo %TWO_TASK%ABCC:\Users\Administrator>echo %LOCAL%%LOCAL%C:\Users\Administrator>echo %ORACLE_SID%testC:\Users\Administrator>sqlplus / as sysdba @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:49:49 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYS"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager@abc @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:49:54 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:48:19 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0C:\Users\Administrator>sqlplus system/manager @abc.sqlSQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 29 20:49:58 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Last Successful login time: Wed Mar 29 2023 20:49:54 -07:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0USER is "SYSTEM"Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0

总结

监听器

ORACLE_SID

TWO_TASK

LOCAL

/ as sysdba

system/manager@abc

system/manager

runing

TEST

Y(2)

Y(1)

Y(2)

runing

ABC

Y(1)

runing

ABC

Y(4)

Y(1)

Y(4)

stop

TEST

Y(2)

Y(2)

stop

ABC

stop

ABC

runing

TEST

ABC

Y(3)

Y(1)

Y(3)

runing

TEST

ABC

Y(3)

Y(1)

Y(3)

system/manager@abc 任何时候都是网络连接，需要监听器开启。不论监听器开启还是关闭，只存在 ORACLE_SID 环境变量时， / as sysdba 和 system/manager 这两种都可以连接。/ as sysdba使用操作系统验证，说明是本地连接。当监听器开启且存在 ORACLE_SID 和 (TWO_TASK 或 LOCAL) 环境变量时， / as sysdba 和 system/manager 都可以连接， / as sysdba使用操作系统验证，说明是本地连接。ORACLE_SID 优先级比 LOCAL 高。这和 linux 中不同。当监听器开启, 只存在 LOCAL 环境变量时 / as sysdba 和 system/manager 可以连接， / as sysdba使用操作系统验证，不需要密码验证，说明是本地连接。TWO_TASK这个变量没有用途。