[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.連結如下:

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

--//再登入資料庫修改口令才會生效.