Oracle 21c PDB远程OS认证失败问题求助

问题描述 #

创建OS用户orasync，期望通过sqlplus /或sqlplus /@iqlink2登录Oracle 21c可插拔数据库（PDB）iqlink2，但登录失败。

环境信息 #

• 操作系统：Rocky Linux release 8.8 (Green Obsidian)
• Oracle版本：Oracle 21c
• CDB名称：iqlink2c
• PDB名称：iqlink2

操作步骤与错误现象 #

1. 创建OS用户orasync并配置.cshrc：

setenv ORACLE_BASE /opt/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/21c/dbhome_1
setenv ORACLE_SID iqlink2
setenv ORACLE_TERM xsun5
setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
setenv PATH ${PATH}:${ORACLE_HOME}/bin
setenv EDITOR /bin/vi

2. 在PDB iqlink2中创建外部认证用户并授权：

create user orasync
identified externally
default tablespace COMPANY
temporary tablespace TEMP
quota unlimited on COMPANY
quota unlimited on COMPANY_INDX
/

GRANT dba TO orasync
/

3. 查看CDB参数：

SQL> SHOW PARAMETERS os_authent_prefix;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix            string  ops$
SQL> SHOW PARAMETERS remote_os;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
remote_os_roles              boolean     FALSE

注：Oracle 21c已移除remote_os_authent参数，疑问PDB是否支持远程OS登录

4. 清空os_authent_prefix并重启数据库：

[oracle@company02 ~]$ sqlplus sys@iqlink2c as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 00:52:45 2023
Version 21.3.0.0.0

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

Enter password: 

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter system set os_authent_prefix='' scope=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup; 
ORACLE instance started.

Total System Global Area 4294967152 bytes
Fixed Size          9695088 bytes
Variable Size        3388997632 bytes
Database Buffers      872415232 bytes
Redo Buffers           23859200 bytes
Database mounted.
Database opened.
SQL> show parameter os_authent_prefix;               

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix            string

5. 登录失败现象：

• 执行sqlplus /报错：

[orasync@company02 ~]$ sqlplus /

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 01:04:49 2023
Version 21.3.0.0.0

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

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4775
Additional information: 1962365431
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

• 执行sqlplus /@iqlink2报错：

[orasync@company02 ~]$ sqlplus /@iqlink2

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 00:47:53 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:

• 验证：tnsping iqlink2正常，且该OS用户可通过其他数据库用户（如sys1）登录PDB：

[orasync@company02 ~]$ tnsping iqlink2

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 11-NOV-2023 01:08:00

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = iqlinkxg02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = iqlink2)))
OK (0 msec)

[orasync@company02 ~]$ sqlplus sys1@iqlink2

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 01:09:18 2023
Version 21.3.0.0.0

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

Enter password: 
Last Successful login time: Sat Nov 11 2023 01:03:42 +01:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>

已尝试的解决方法 #

• 清空os_authent_prefix的ops$值，仍出现ORA-01017错误；
• 配置Oracle 19c客户端并修改orasync用户的.cshrc，其他用户可登录但问题依旧；
• 添加setenv TWO_TASK $ORACLE_SID参数到.cshrc，无效果。

解决建议 #

1. 处理sqlplus /的ORA-01034错误
   ORACLE_SID设置为PDB名称iqlink2是错误的，因为共享内存属于CDB（iqlink2c），PDB没有独立的实例。修改orasync用户的.cshrc：

setenv ORACLE_SID iqlink2c

之后执行sqlplus /会登录到CDB，若要直接登录PDB，需设置TWO_TASK指向PDB的服务名：

setenv TWO_TASK iqlink2

此时执行sqlplus /即可通过外部认证登录PDB。

2. 处理sqlplus /@iqlink2的ORA-01017错误
   外部认证通过网络连接时，需确保sqlnet.ora中配置了SQLNET.AUTHENTICATION_SERVICES=(ALL)或包含NTS（Linux下为KERBEROS5,TCPS,NTS），同时检查：

• 数据库用户orasync的外部认证配置是否正确，需确保OS用户名与数据库用户名完全匹配（注意大小写，Oracle默认用户名大写，若OS用户是小写，创建数据库用户时需用双引号：create user "orasync" identified externally;）
• 确认os_authent_prefix已在CDB级别设置为空，且PDB继承该参数（可在PDB中执行show parameter os_authent_prefix验证）
• 检查remote_os_roles参数，若需要通过OS角色认证，需设置为TRUE，但外部认证用户登录本身不需要该参数，主要影响角色授权。

3. Oracle 21c外部认证说明
   Oracle 21c移除了remote_os_authent参数，外部认证通过网络连接时，依赖sqlnet.ora的SQLNET.AUTHENTICATION_SERVICES配置，同时要求客户端与服务器端的认证方式一致。Linux下默认支持NTS（操作系统认证），确保该选项在sqlnet.ora中启用。

内容的提问来源于stack exchange，提问作者Mike