前言:

????????近期處理了一起Oracle 19c集群sysbackup用戶登陸隨機(jī)報(bào)錯(cuò)ORA-01017: invalid username/password; logon denied的問題，后面分析發(fā)現(xiàn)是由于集群密碼文件沒有共享，兩個(gè)節(jié)點(diǎn)的密碼文件不一致，導(dǎo)致用戶通過(guò)scan ip隨機(jī)分發(fā)節(jié)點(diǎn)時(shí)，連接到了錯(cuò)誤密碼所在的節(jié)點(diǎn)，進(jìn)而產(chǎn)生了ORA-01017。

問題現(xiàn)象：

集群sysbackup用戶登陸節(jié)點(diǎn)隨機(jī)報(bào)錯(cuò)ORA-01017: invalid username/password; logon denied

問題原因:

????????用戶進(jìn)行配置sysbackup用戶的操作，但由于集群密碼文件沒有共享，導(dǎo)致兩個(gè)節(jié)點(diǎn)的密碼文件不一致，在用戶通過(guò)scan ip隨機(jī)分發(fā)節(jié)點(diǎn)時(shí)，連接到了錯(cuò)誤密碼所在的節(jié)點(diǎn)，進(jìn)而產(chǎn)生了ORA-01017。

問題解決：

方法一:在集群每個(gè)節(jié)點(diǎn)同時(shí)執(zhí)行配置sysbackup的操作，確保節(jié)點(diǎn)密碼文件一致

alter user testbackup identified by testbackup;
grant sysbackup to testbackup;

方法二:將正確節(jié)點(diǎn)的密碼文件拷貝到其他節(jié)點(diǎn)，確保集群節(jié)點(diǎn)密碼文件配置一致

scp $ORACLE_HOME/dbs/orapworcl1 node2@:$ORACLE_HOME/dbs/orapworcl2

方法三:將密碼文件設(shè)置為asm共享文件

orapwd input_file='/u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl1' file='+DATADG/ORCL/orapworcl' asm=y  force=y
---不需要重啟數(shù)據(jù)庫(kù)，密碼文件會(huì)自己切換到asm路徑下，可以通過(guò)GV$PASSWORDFILE_INFO進(jìn)行驗(yàn)證

問題分析:

在節(jié)點(diǎn)一node1創(chuàng)建備份用戶testbackup

create user testbackup identified by testbackup;
grant connect,resource,sysbackup to testbackup;

測(cè)試用戶登陸，會(huì)出現(xiàn)隨機(jī)登陸失敗的情況

---通過(guò)scan_ip,第一次登陸成功
[oracle@node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ sqlplus testbackup/testbackup@orcl as sysbackup
?
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 18 17:21:28 2022
Version 19.3.0.0.0
?
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
?
?
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
?
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
---通過(guò)scan_ip,第二次登陸失敗，報(bào)ORA-01017: invalid username/password; logon denied
[oracle@node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ sqlplus testbackup/testbackup@orcl as sysbackup
?
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 18 17:21:31 2022
Version 19.3.0.0.0
?
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
?
ERROR:
ORA-01017: invalid username/password; logon denied
?
Enter user-name:

查看sysbackup用戶密碼文件的配置

---會(huì)發(fā)現(xiàn)，只有節(jié)點(diǎn)一node1有寫入TESTBACKUP這個(gè)sysbackup的用戶，節(jié)點(diǎn)二node2沒有寫入sysbackup用戶
  1* select inst_id,username,sysbackup from gv$pwfile_users
?
   INST_ID USERNAME                                  SYSBA
---------- -------------------------------------------------------------------------------------------------------------------------------- -----
   1 SYS                                              FALSE
   1 TESTBACKUP                                       TRUE
   2 SYS                                              FALSE

查看密碼文件路徑，可以發(fā)現(xiàn)密碼文件都是節(jié)點(diǎn)本地存放

1* select * from GV$PASSWORDFILE_INFO
?
   INST_ID FILE_NAME              FORMAT IS_AS   CON_ID
---------- ------------------------------------------------------------ ------ ----- ----------
   1 /u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl1    12     FALSE        0
   2 /u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl2    12     FALSE        0

測(cè)試通過(guò)節(jié)點(diǎn)vip訪問登陸,節(jié)點(diǎn)一vip登陸沒有報(bào)錯(cuò)，節(jié)點(diǎn)二vip登陸有報(bào)錯(cuò)ora-01017，這進(jìn)一步驗(yàn)證了節(jié)點(diǎn)二密碼文件有問題

---通過(guò)節(jié)點(diǎn)一vip登陸沒有報(bào)錯(cuò)
[oracle@node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ rman target /
?
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jul 18 17:26:30 2022
Version 19.3.0.0.0
?
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
?
RMAN> connect target 'testbackup/testbackup@node1-vip:1521/orcl as sysbackup'
connected to target database: ORCL (DBID=1632283697)
RMAN> exit
?
---通過(guò)節(jié)點(diǎn)二vip登陸報(bào)錯(cuò)ora-01017
[oracle@node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ rman target /
?
RMAN>  connect target 'testbackup/testbackup@node2-vip:1521/orcl as sysbackup';
?
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01017: invalid username/password; logon denied
?
RMAN> exit
?

在節(jié)點(diǎn)二node2執(zhí)行節(jié)點(diǎn)一配置sysbackup的操作

SQL> alter user testbackup identified by testbackup;
SQL> grant sysbackup to testbackup;
?
Grant succeeded.
?
?
SQL>  select * from gv$pwfile_users;
?
   INST_ID USERNAME                                  SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS           PASSWORD_PROFILE
---------- -------------------------------------------------------------------------------------------------------------------------------- ----- ----- ----- ----- ----- ----- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
LAST_LOGIN                    LOCK_DATE EXPIRY_DA
--------------------------------------------------------------------------- --------- ---------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTHENTI COM   CON_ID
-------- --- ----------
   2 SYS                                    TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN
PASSWORD NO        0
   2 TESTBACKUP                                  FALSE FALSE FALSE TRUE  FALSE FALSE OPEN
PASSWORD NO        0
?
   1 SYS                                    TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN
PASSWORD NO        0
?
   1 TESTBACKUP                                  FALSE FALSE FALSE TRUE  FALSE FALSE OPEN
PASSWORD NO        0

再次通過(guò)節(jié)點(diǎn)二vip進(jìn)行登陸，連接成功

RMAN> connect target 'testbackup/testbackup@node2-vip:1521/orcl as sysbackup';
?
connected to target database: ORCL (DBID=1632283697)
?
RMAN>  select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;
?
using target database control file instead of recovery catalog
?
?
CURRENT_SCHEMA                                                                  
--------------------------------------------------------------------------------
SESSION_USER                                                                    
--------------------------------------------------------------------------------
?
SYS
SYSBACKUP
 
RMAN> exit

總結(jié):

???????sysbackup用戶出現(xiàn)隨機(jī)登陸失敗ora-01017的原因?yàn)橛脩暨M(jìn)行配置sysbackup用戶的操作，但由于集群密碼文件沒有共享，導(dǎo)致兩個(gè)節(jié)點(diǎn)的密碼文件不一致，在用戶通過(guò)scan ip隨機(jī)分發(fā)節(jié)點(diǎn)時(shí)，連接到了錯(cuò)誤密碼所在的節(jié)點(diǎn)，進(jìn)而產(chǎn)生了ORA-01017。
????????可以通過(guò)以下方法修復(fù)問題
????????方法一:在集群每個(gè)節(jié)點(diǎn)同時(shí)執(zhí)行配置sysbackup的操作，確保節(jié)點(diǎn)密碼文件一致。
????????方法二:將正確節(jié)點(diǎn)的密碼文件拷貝到其他節(jié)點(diǎn)，確保集群節(jié)點(diǎn)密碼文件配置一致。
????????方法三:將密碼文件設(shè)置為asm共享文件。