Oracle ORA-01017 when sys login by tns

記錄一下解決方式

事情是因為 Oracle 要與 NBU 對接,但 NBU 一直無法正常登入,因此測試了一下,發現有 ORA-01017 的錯誤

[oracle@test-db1 ~]$ sqlplus sys/oracle@test  as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 11 14:38:21 2021

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

ERROR:

ORA-01017: invalid username/password; logon denied

正常情況用本機登看看,發現可以

[oracle@test-db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 11 14:36:40 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

用 system 帳號透過 tns 登入可以

[oracle@test-db1 trace]$ sqlplus system/oracle@DEV

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 11 14:40:10 2021

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

Last Successful login time: Tue May 11 2021 14:34:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

嘗試改密碼重新登入,一樣不行

SQL> alter user sys identified by "oracle";

User altered.

SQL> exit
[oracle@test-db1 ~]$ sqlplus sys/oracle@test  as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 11 14:36:57 2021

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

ERROR:
ORA-01017: invalid username/password; logon denied

檢查了環境參數 remote_login_passwordfile 是設定 EXCLUSIVE

[oracle@test-db1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 11 14:40:55 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter remote_login_passwordfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile	     string	 EXCLUSIVE

檢查了 passwordfile 是否存在,發現是不存在

[oracle@test-db1 trace]$ ls -l $ORACLE_HOME/dbs/orapw*
total 0

重建 orapwfile 問題就解決

[oracle@test-db1 trace]$ orapwd file=$ORACLE_HOME/dbs/orapwtest password=oracle entries=10