oracle环境变量TWO_TASK和LOCAL

云烟 • 2023年 12月 17日上午11:23 • 未分类 • 阅读 185

欢迎大家来到IT世界,在知识的湖畔探索吧!

我们平常为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环境变量。使用空字符串的缺点是，必须配置这些附加信息，应用程序才能连接到数据库。

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

欢迎大家来到IT世界,在知识的湖畔探索吧!

查看下tns配置

欢迎大家来到IT世界,在知识的湖畔探索吧![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

监听器状态，目前是开启

欢迎大家来到IT世界,在知识的湖畔探索吧![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

总结

监听器状态	ORACLE_SID	TWO_TASK	LOCAL	/ as sysdba	system/manager@abc	system/manager
runing	orcl			Y(2)	Y(1)	Y(2)
runing		abc			Y(1)	Y(4)
runing			abc		Y(1)
stop	orcl			Y(2)		Y(2)
stop		abc
stop			abc
runing	orcl	abc			Y(1)	Y(3)

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

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://itzsg.com/35776.html