oracle环境变量TWO_TASK和LOCAL：一（TWO_TASK)

逃离我推掉我的手 2024-03-24 13:57 15阅读 0赞

我们平常为oracle设置的环境通常有ORACLE\_BASE, ORACLE\_HOME,ORACLE\_SID，

今天偶然看到了， oracle竟然还有个环境变量 TWO\_TASK ，LOCAL，仔细看了一下， 挺有意义， 测试了一把，做个记录。

### 官方说明 ###

先看看官方文档对这两个环境变量的说明

**1. TWO\_TASK:**

You can set the connect string to an empty connect string (“”), and then set the TWO\_TASK environment variable to one of the following values:

A direct address, as described under “Specifying a Connection by Using the Easy Connect Naming Method”

Oracle Net keyword-value pair

A tnsnames.ora entry and TNS\_ADMIN is set to the location of tnsnames.ora

This method allows the applications to specify internally a connection string if the application code itself uses an empty connection string. The benefit of an empty connect string is that the application itself does not have to specify the tnsnames.ora entry. Instead, when a user starts the application, the location of the database is determined by a script or the environment, depending on where you have set the TWO\_TASK environment variable. The disadvantage of using empty strings is that you must configure this additional information in order for the application to connect to the database.

您可以将连接字符串设置为空连接字符串（“”），然后将TWO\_TASK环境变量设置为以下值之一：

直接地址，如“使用简易连接命名方法指定连接”中所述

Oracle Net关键字值对

tnsnames.ora条目，TNS\_ADMIN设置为tnsnames.orga的位置

如果应用程序代码本身使用空的连接字符串，则此方法允许应用程序在内部指定连接字符串。空连接字符串的好处是应用程序本身不必指定tnsnames.ora条目。相反，当用户启动应用程序时，数据库的位置由脚本或环境决定，具体取决于您在哪里设置了TWO\_TASK环境变量。使用空字符串的缺点是，必须配置这些附加信息，应用程序才能连接到数据库。

oracle环境变量TWO\_TASK和LOCAL  
原创2023-03-30 10:03·贺浦力特  
我们平常为oracle设置的环境通常有ORACLE\_BASE, ORACLE\_HOME,ORACLE\_SID，

今天偶然看到了， oracle竟然还有个环境变量 TWO\_TASK ，LOCAL，仔细看了一下， 挺有意义， 测试了一把，做个记录。

官方说明  
先看看官方文档对这两个环境变量的说明

1.  TWO\_TASK:

You can set the connect string to an empty connect string (“”), and then set the TWO\_TASK environment variable to one of the following values:

A direct address, as described under “Specifying a Connection by Using the Easy Connect Naming Method”

Oracle Net keyword-value pair

A tnsnames.ora entry and TNS\_ADMIN is set to the location of tnsnames.ora

您可以将连接字符串设置为空连接字符串（“”），然后将TWO\_TASK环境变量设置为以下值之一：

直接地址，如“使用简易连接命名方法指定连接”中所述

Oracle Net关键字值对

tnsnames.ora条目，TNS\_ADMIN设置为tnsnames.orga的位置

**2. LOCAL**

Describes how to specify a connection to an empty connect string and set the LOCAL environment variable.

You can set the connect string to an empty connect string (“”), and then set the LOCAL environment variable to one of the following values:

A direct address

Oracle Net keyword-value pair

A tnsnames.ora entry and TNS\_ADMIN is set to the location of tnsnames.ora

A tnsnames.ora entry and the following:

tnsnames.ora file located in ORACLE\_HOME/network/admin

The ORACLE\_HOME environment variable set to this Oracle home

This method allows your applications to specify internally a connection string if the application code uses an empty connection string. The benefit of an empty connect string is that the application does not need to specify the tnsnames.ora entry. Instead, when a user invokes the application, the location of the database is determined by a script or the environment, depending on where you have set the LOCAL environment variable. The disadvantage of using empty strings is that you must configure this additional information for your application to connect to the database.

描述如何指定到空连接字符串的连接并设置LOCAL环境变量。

您可以将连接字符串设置为空连接字符串（“”），然后将LOCAL环境变量设置为以下值之一：

直接地址

Oracle Net关键字值对

tnsnames.ora条目，TNS\_ADMIN设置为tnsnames.orga的位置

tnsnames.ora条目和以下内容：

位于ORACLE\_HOME/network/admin中的tnsnames.ora文件

设置为此ORACLE主页的ORACLE\_HOME环境变量

如果应用程序代码使用空的连接字符串，则此方法允许应用程序在内部指定连接字符串。空连接字符串的好处是应用程序不需要指定tnsnames.ora条目。相反，当用户调用应用程序时，数据库的位置由脚本或环境决定，具体取决于您在哪里设置了LOCAL环境变量。使用空字符串的缺点是，必须为应用程序配置这些附加信息才能连接到数据库。

### 实验环境 ###

环境是 oracle linux 7.9 + oracle database 21c

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

cat > test.sql<<EOF
    show user;
    exit;
    EOF

**查看下tns配置**

[oracle@localhost ~]$ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
    # tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    ABC =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
      )

**测试命令**

分别是  
a方式: 不输入账号密码以sysdba登录  
b方式: 账号/密码@tnsname登录  
c方式: 账号/密码登录  
这3种情况，然后执行准备的脚本(很简单，显示当前用户并退出)

sqlplus / as sysdba @test.sql
    sqlplus system/manager@abc @test.sql
    sqlplus system/manager @test.sql

### 监听器状态，目前是开启 ###

[oracle@localhost ~]$ lsnrctl status
    
    LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 30-MAR-2023 08:58:35
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
    Start Date                30-MAR-2023 08:56:00
    Uptime                    0 days 0 hr. 2 min. 34 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "cc3ffc024d48791ce055000000000001" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclpdb1" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully

**第一种情况**

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

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

[oracle@localhost ~]$ echo $TWO_TASK
    
    [oracle@localhost ~]$ echo $LOCAL
    
    [oracle@localhost ~]$ echo $ORACLE_SID
    orcl
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 08:59:09 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYS"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 08:59:09 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Mar 29 2023 17:24:29 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 08:59:09 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 08:59:09 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0

**第二种情况 只设置了 TWO\_TASK**

实验结果: a方式登录失败，提示账号密码错误

[oracle@localhost ~]$ export TWO_TASK=abc
    [oracle@localhost ~]$ unset LOCAL
    [oracle@localhost ~]$ unset ORACLE_SID
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ echo $TWO_TASK
    abc
    [oracle@localhost ~]$ echo $LOCAL
    
    [oracle@localhost ~]$ echo $ORACLE_SID
    
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:00:06 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 08:59:09 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:00:06 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 09:00:06 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:00:07 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Enter user-name: 
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Enter user-name: 
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

**第三种情况 只设置了 LOCAL**

实验结果： b方式登录成功

[oracle@localhost ~]$ unset TWO_TASK
    [oracle@localhost ~]$ export LOCAL=abc
    [oracle@localhost ~]$ unset ORACLE_SID
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ echo $TWO_TASK
    
    [oracle@localhost ~]$ echo $LOCAL
    abc
    [oracle@localhost ~]$ echo $ORACLE_SID
    
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:00:50 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 09:00:06 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:00:50 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    SP2-0306: Invalid option.
    Usage: CONN[ECT] [{
        logon|/|proxy} [AS {
        SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
    where <logon> ::= <username>[/<password>][@<connect_identifier>]
          <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:01:11 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

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

### 监听器状态 关闭 ###

[oracle@localhost ~]$ lsnrctl stop
    
    LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 30-MAR-2023 09:01:37
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
    The command completed successfully
    [oracle@localhost ~]$ lsnrctl status
    
    LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 30-MAR-2023 09:01:37
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       Linux Error: 111: Connection refused
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
     TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener
       Linux Error: 2: No such file or directory

**第一种情况**

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

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

[oracle@localhost ~]$ echo $TWO_TASK
    
    [oracle@localhost ~]$ echo $LOCAL
    
    [oracle@localhost ~]$ echo $ORACLE_SID
    orcl
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:02:17 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYS"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:02:17 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    SP2-0306: Invalid option.
    Usage: CONN[ECT] [{
        logon|/|proxy} [AS {
        SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
    where <logon> ::= <username>[/<password>][@<connect_identifier>]
          <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
    Enter user-name: 
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:02:37 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 09:00:50 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0

**第二种情况 只设置了 TWO\_TASK**

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

[oracle@localhost ~]$ export TWO_TASK=abc
    [oracle@localhost ~]$ unset LOCAL
    [oracle@localhost ~]$ unset ORACLE_SID
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ echo $TWO_TASK
    abc
    [oracle@localhost ~]$ echo $LOCAL
    
    [oracle@localhost ~]$ echo $ORACLE_SID
    
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:03:21 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:03:32 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:03:42 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12541: TNS:no listener
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

**第三种情况 只设置了 LOCAL**

实验结果： abc全部失败

[oracle@localhost ~]$ unset TWO_TASK
    [oracle@localhost ~]$ export LOCAL=abc
    [oracle@localhost ~]$ unset ORACLE_SID
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ echo $TWO_TASK
    
    [oracle@localhost ~]$ echo $LOCAL
    abc
    [oracle@localhost ~]$ echo $ORACLE_SID
    
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:04:14 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12541: TNS:no listener
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ 
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:04:21 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:04:28 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    Enter user-name: 
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

### 监听器开启，同时存在 ORACLE\_SID 和 TWO\_TASK ###

实验结果： sysdba不能连接

[oracle@localhost ~]$ lsnrctl status
    
    LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 30-MAR-2023 09:38:21
    
    Copyright (c) 1991, 2021, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
    Start Date                30-MAR-2023 09:36:37
    Uptime                    0 days 0 hr. 1 min. 43 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "c8209f27c6b16005e053362ee80ae60e" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "cc3ffc024d48791ce055000000000001" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclpdb1" has 1 instance(s).
      Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully
    [oracle@localhost ~]$ export TWO_TASK=abc
    [oracle@localhost ~]$ echo $TWO_TASK
    abc
    [oracle@localhost ~]$ echo $LOCAL
    
    [oracle@localhost ~]$ echo $ORACLE_SID
    orcl
    [oracle@localhost ~]$ sqlplus system/system@abc @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:38:34 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 09:02:37 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus system/system @test.sql
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:38:34 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    Last Successful login time: Thu Mar 30 2023 09:38:34 +08:00
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    
    USER is "SYSTEM"
    Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.3.0.0.0
    [oracle@localhost ~]$ sqlplus / as sysdba @test.sql 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 30 09:38:38 2023
    Version 21.3.0.0.0
    
    Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Enter user-name: 
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    Enter user-name: 
    ERROR:
    ORA-01017: invalid username/password; logon denied
    
    
    SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

### 总结 ###

<table> 
 <thead> 
  <tr> 
   <th>监听器状态</th> 
   <th>ORACLE_SID</th> 
   <th>TWO_TASK</th> 
   <th>LOCAL</th> 
   <th>/ as sysdba</th> 
   <th>system/manager@abc</th> 
   <th>system/manager</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>running</td> 
   <td>orcl</td> 
   <td></td> 
   <td></td> 
   <td>Y(2)</td> 
   <td>Y(1)</td> 
   <td>Y(2)</td> 
  </tr> 
  <tr> 
   <td>running</td> 
   <td></td> 
   <td>abc</td> 
   <td></td> 
   <td></td> 
   <td>Y(1)</td> 
   <td>Y(4)</td> 
  </tr> 
  <tr> 
   <td>running</td> 
   <td></td> 
   <td></td> 
   <td>abc</td> 
   <td></td> 
   <td>Y(1)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>stop</td> 
   <td>orcl</td> 
   <td></td> 
   <td></td> 
   <td>Y(2)</td> 
   <td></td> 
   <td>Y(2)</td> 
  </tr> 
  <tr> 
   <td>stop</td> 
   <td></td> 
   <td>abc</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>stop</td> 
   <td></td> 
   <td></td> 
   <td>abc</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>running</td> 
   <td>orcl</td> 
   <td>abc</td> 
   <td></td> 
   <td></td> 
   <td>Y(1)</td> 
   <td>Y(3)</td> 
  </tr> 
 </tbody> 
</table>

1.  system/manager@abc 任何时候都是网络连接，需要监听器开启。
2.  不论监听器开启还是关闭，只存在 ORACLE\_SID 环境变量时， / as sysdba 和 system/manager 这两种都可以连接。/ as sysdba 使用操作系统验证，说明是本地连接。
3.  当监听器开启且存在 ORACLE\_SID 和 TWO\_TASK 环境变量时，system/manager 可以连接， / as sysdba不能使用操作系统验证，需要密码验证说明是网络连接。也说明 TWO\_TASK 环境变量优先级 大于 ORACLE\_SID。
4.  当监听器开启, 只存在 TWO\_TASK 环境变量时 system/manager 可以连接。当监听器关闭, 只存在 TWO\_TASK 环境变量时则不能连接，说明 TWO\_TASK 环境变量是依赖监听器的， 其实就是指向 tnsname.ora 文件里面的别名，设置了这个环境变量，就不需要写 @abc 了 。TWO\_TASK 环境变量的值必须在 tnsnames.ora 中存在。
5.  LOCAL这个变量没有用途，LOCAL是windows里面环境变量， 请看下一次实验