Connect Oracle Server 12c from Windows Client SQLplus 11c

Question

我有一个Oracle Server 12c，并希望从Windows机器与SQLplus连接。

服务器版本为 12.2.0.1SQLPlus 是 11.2.0.1

当我连接到"SQLPLUS 用户/pass@ORCL"之类的服务器时，出现错误

ORA-01017: invalid username/password; logon denied

凭据正确。使用 SQLPlus 12.2 它可以正常工作。

通过检查支持矩阵，它也应该适用于 11.2.0

所以这是我来自Windowsclient的tnsnames.ora

ORCL =
      (DESCRIPTION=
        (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=10.162.6.52)
            (PORT=1521)
        )
        (CONNECT_DATA=
            (SERVER = DEDICATED)
            (SID=orcltok)
        )
    )

用户的PASSWORD_VERSION是11g和12c。我已经从dba_users检查过了。

我希望有人给我一个提示。

愿你安好奥利弗

Answer 1

我对你的任务非常感兴趣，我在oracle XE 11g和即时客户端11上做了一个实验。

 1. USER SCOTT password  Tiger 
 2. USER Scott password  TigeR
C:apporacleproduct11.2.0client_1>sqlplus.exe system/password@aws
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 19 22:05:00 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter user SCOTT identified by Tiger;
SQL> select * from all_users;
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
XS$NULL                        2147483638 28-AUG-11
SCOTT                                  48 19-JAN-18
APEX_040000                            47 28-AUG-11
APEX_PUBLIC_USER                       45 28-AUG-11
FLOWS_FILES                            44 28-AUG-11
HR                                     43 28-AUG-11
MDSYS                                  42 28-AUG-11
ANONYMOUS                              35 28-AUG-11
XDB                                    34 28-AUG-11
CTXSYS                                 32 28-AUG-11
OUTLN                                   9 28-AUG-11
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 28-AUG-11
SYS                                     0 28-AUG-11
13 rows selected.
SQL> create user "Scott" identified by TigeR ;
User created.
SQL> grant connect to "Scott";
Grant succeeded.
SQL> select * from all_users;
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
XS$NULL                        2147483638 28-AUG-11
Scott                                  51 19-JAN-18
SCOTT                                  48 19-JAN-18
APEX_040000                            47 28-AUG-11
APEX_PUBLIC_USER                       45 28-AUG-11
FLOWS_FILES                            44 28-AUG-11
HR                                     43 28-AUG-11
MDSYS                                  42 28-AUG-11
ANONYMOUS                              35 28-AUG-11
XDB                                    34 28-AUG-11
CTXSYS                                 32 28-AUG-11
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
OUTLN                                   9 28-AUG-11
SYSTEM                                  5 28-AUG-11
SYS                                     0 28-AUG-11
14 rows selected.
C:apporacleproduct11.2.0client_1>sqlplus.exe "Scott"/Tiger@aws
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 19 22:10:31 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> show user
USER is "SCOTT"
SQL>
C:apporacleproduct11.2.0client_1>sqlplus.exe "Scott"/TigeR@aws
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 19 22:14:41 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
C:apporacleproduct11.2.0client_1>sqlplus.exe /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 19 22:15:22 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> connect Scott/TigeR@aws
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect "Scott"/TigeR@aws
Connected.
SQL> show user
USER is "Scott"
SQL>

示例 2. sqlplus 12.2.0

即时客户端 12.2.0.1

C:oracleinstantclient_12_2>sqlplus.exe Scott/Tiger@aws
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 22 09:42:08 2018
Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> show user
USER is "SCOTT"
SQL>

C:oracleinstantclient_12_2>sqlplus.exe "Scott"/"Tiger"@aws
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 22 09:44:46 2018
Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
C:oracleinstantclient_12_2>sqlplus.exe /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 22 09:45:48 2018
Copyright (c) 1982, 2017, Oracle.  All rights reserved.
SQL> conn Scott/TigeR@aws
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn "Scott"/TigeR@aws
Connected.
SQL> show user
USER is "Scott"
SQL>

如果用户名在不同的寄存器中包含字母，则 sqlplus 11 和 12 始终将用户名转换为大写。如果你像这样运行它 sqlpus "Scott"/TigeR@aws这相当于sqlpus "SCOTT"/TigeR@aws.如果首先运行 sqlplus /nolog 命令然后connect "Scott"/TigeR@aws.这一切都是相互联系的。