SYS用户遭遇登陆障碍，原来是这个环境变量在作祟

青年数据库学习互助会 2025-01-16

111

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

DataDocks 数据码头 💬

最近联合几个 ACE 开通了一个付费微信群，加群后会有一些会员福利（分享各类技术文档，干货资源，问题解答等等），更有特邀嘉宾会定期在群内直播，解读AWR，快问快答等！有兴趣联系微：ywu0613

正文开始

本文内容

主要介绍两个环境变量，适用于Oracle 19C及以后的版本（18C也支持，但是用的人应该不多）

适用于CDB&PDB环境，在不需要@TNS服务名的情况下，直接登陆PDB

ORACLE_PDB_SID ：设置该环境变量为对应的pdb name

TWO_TASK：设置该环境变量为对应的pdb name，可以使普通用户直接登陆PDB

当两个数同时配置时，sys用户不能直接登陆PDB，需要取消TWO_TASK

unset TWO_TASK

测试基础环境

[oracle@rac02 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB)
    )
  )
--当前环境变量
[oracle@rac02 ~]$ env |grep ORACLE
ORACLE_UNQNAME=orcl
ORACLE_SID=orcl2
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
--登陆查看当前数据库CDB&PDB情况
[oracle@rac02 ~]$ sqlplus  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:25:09 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
--当前监听状态
[oracle@rac01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JAN-2025 08:42:15

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-JAN-2025 20:24:07
Uptime                    0 days 1 hr. 18 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File         u01/app/grid/diag/tnslsnr/rac01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.90)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.92)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "0b74bb4121154089e0635a38a8c06ef6" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

变量ORACLE_PDB_SID使用

使用ORACLE_PDB_SID直接登陆PDB数据库

[oracle@rac02 ~]$ export ORACLE_PDB_SID=PDB
[oracle@rac02 ~]$ sqlplus  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:25:56 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO

TWO_TASK环境变量

创建一个普通用户

--在当前sys用户登陆到PDB时，创建scott用户
CREATE USER SCOTT IDENTIFIED BY TIGGER ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
--尝试切换到scott用户
SQL> conn SCOTT/TIGGER;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@rac02 ~]$ env |grep PDB
ORACLE_PDB_SID=PDB
--尝试直接登陆PDB,注意之前创建用户的SQL都是大写
[oracle@rac02 ~]$ sqlplus SCOTT/TIGGER

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:27:13 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

使用TWO_TASK环境变量

[oracle@rac02 ~]$ export  TWO_TASK=PDB
[oracle@rac02 ~]$ env |grep PDB
ORACLE_PDB_SID=PDB
TWO_TASK=PDB
[oracle@rac02 ~]$ sqlplus SCOTT/TIGGER

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:32:55 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 15 2025 08:32:28 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit

注意如果要再想使用sysdba登陆数据库，要取消two_task变量，否则无法登陆

[oracle@rac02 ~]$ env |grep PDB
ORACLE_PDB_SID=PDB
TWO_TASK=PDB
[oracle@rac02 ~]$ sqlplus  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:36:16 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
[oracle@rac02 ~]$ unset TWO_TASK
[oracle@rac02 ~]$ sqlplus  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 15 08:36:26 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>