[20181107]低版本toad连接18c数据库问题.txt

[20181107]低版本toad连接18c数据库问题.txt

--//同事使用低版本toad连接18c遇到的问题,无法连接.实际上该版本toad使用10.2.0的oracle client.

根据MOS文档 (ID 755605.1)，ORA-28040的错误需要在Oracle 用户(非grid用户)的sqlnet.ora 文件中添加：

SQLNET.ALLOWED_LOGON_VERSION=8

或者使用更高版本的客户端。

但实际上，根据MOS文档(ID 2111876.1)， 在Oracle 12c 以后的版本，

SQLNET.ALLOWED_LOGON_VERSION 参数已经弃用了，应该使用以下2个参数代替：

SQLNET.ALLOWED_LOGON_VERSION_SERVER = n

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = n

--//修改数据库的sqlnet.ora文件加入:

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10

--//尝试连接报ora-1017错误,密码肯定没有问题,检查视图发现:

XXXX> select username,password_versions from dba_users;

USERNAME                       PASSWORD_VERSIONS

------------------------------ ----------------------------------

SYS                            11G 12C

SYSTEM                         11G 12C

...

--//可以发现PASSWORD_VERSIONS没有包括10g.

XXXX> alter user system identified by xxxx;

alter user system identified by xxxx

*

ERROR at line 1:

ORA-65066: The specified changes must apply to all containers

--//自己终于知道为什么修改system密码为什么必须应用全部containers.链接如下:https://community.oracle.com/thread/3786063

All Oracle-supplied administrative user accounts, such as SYS and SYSTEM, are common users and can navigate across the

CDB. Common users can have different privileges in different PDBs. For example, the common user SYSTEM can switch

between PDBs and use the privileges that are granted to SYSTEM in the current PDB.

. . .

If you plug a PDB that contains a common user into a CDB, then the following actions take place:

The common user accounts in this PDB lose commonly granted privileges that they may have had, including the SET

CONTAINER privilege.

If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB, then the new common

user is merged with the target CDB common user. The password of the target CDB common user takes precedence.

See that last sentence? As others, and the doc, say a common user is COMMON. The standard users SYS and SYSTEM are

common users so only have one password.

--//登录cdb数据库,执行如下ok.

alter user SYSTEM identified by xxxx container=all;

--//不过有点奇怪的是

CDB> select username,password_versions from dba_users;

USERNAME                       PASSWORD_VERSIONS

------------------------------ ----------------------------------

SYS                            11G 12C

SYSTEM                         10G 11G 12C

--//而PDB下显示的依旧不包括10g.

CDB> select username,password_versions from dba_users;

USERNAME                       PASSWORD_VERSIONS

------------------------------ ----------------------------------

SYS                            11G 12C

SYSTEM                         11G 12C

--//另外注意的问题,比如我当前的client端是12c版本.如果我执行alter user SYSTEM identified by xxxx container=all;,口令版本

--//一样无效,不会包括10g,必须修改我的sqlnet.ora文件加入:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

--//再登录数据库修改口令才会生效.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2219005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2219005/