ORA-01017/ORA-02063DbLink建立錯誤問題分析及解決

故障介紹：

我在oracle10.2.0.4中建立連線到11.2.0.3的DBLINK驗證的時候出現如下錯誤：

ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from

分析錯誤：

      從錯誤資訊來看，是使用者名稱和密碼的錯誤，但是使用者名稱和密碼我確定沒有問題，能夠透過密碼進行訪問資料庫。

詳細回顧及出現問題及排錯思路如下:

建立dblink使用的使用者/密碼是正確的。

SQL> conn prudent/woo@woo
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as woo
建立dblink的資料庫版本：

SQL> select * from V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 -Production
NLSRTL Version 10.2.0.4.0 - Production

ORACLE 10.2.0.4中建立到 Oracle 11.2.0.3.0 的DBLINK，建立成功

SQL>  create database link woo_100
  2    connect to prudent
  3      identified by woo
  4    using '(DESCRIPTION =
  5      (ADDRESS_LIST =
  6        (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.0.100)(PORT = 1521))
  7      )
  8      (CONNECT_DATA =
  9        (SERVER = DEDICATED)
 10        (SID = woo)
 11      )
 12    )';
 

但使用dblink去訪問目標資料庫時出錯
QL> select sysdate from dual@woo;
select sysdate from dual@woo

ORA-01017: invalid username/password; logondenied
ORA-02063: preceding line from woo_100

應該是11g密碼區分大小寫的緣故，需要在11g中重新設定大寫的密碼。實際上可以不用透過修改密碼的方式來解決，只需要加上“”號即可。

嘗試在建立dblink時用“”把密碼引起來。

SQL>  create database link woo_100
  2    connect to prudent
  3      identified by "woo"
  4    using '(DESCRIPTION =
  5      (ADDRESS_LIST =
  6        (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.0.100)(PORT = 1521))
  7      )
  8      (CONNECT_DATA =
  9        (SERVER = DEDICATED)
 10        (SID = woo)
 11      )
 12    )';

Database link created

SQL> alter session setnls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered

SQL> col sysdate for  a20;
SQL> select sysdate from dual@woo_100;

SYSDATE
---------------------------
2013-1-28 22:48:52

測試OK.

參考文獻：

: ORA-01017 ORA-02063 WHILE CONNECTING FROM10G TO 11G VIA PUBLIC DBLINK

Cause
The following Bug 6738104 was logged for this issue which was closed as not abug saying the cause being introduction of password case sensitivity feature in11g
When one creates a database link connection, a user name and password for theconnection needs to be defined. When the database link is created, the passwordis case sensitive. Before a user can connect from a pre-release 11g database toa 11g release database and as the password case sensitivity is enabled bydefault, you must re-create the password for this database link using alluppercase letters.
The reason you need to re-create the password using all uppercase letters is sothat it will match how Oracle Database stores database link passwords. OracleDatabase always stores this type of password in uppercase letters, even if thepassword had originally been created using lower or mixed case letters. If casesensitivity is disabled, the user can enter the password using the case thepassword was created in.