Oracle集群sysbackup用户登陆随机报错ORA01017

前言:

        近期处理了一起Oracle 19c集群sysbackup用户登陆随机报错ORA-01017: invalid username/password; logon denied的问题&＃xff0c;后面分析发现是由于集群密码文件没有共享&＃xff0c;两个节点的密码文件不一致&＃xff0c;导致用户通过scan ip随机分发节点时&＃xff0c;连接到了错误密码所在的节点&＃xff0c;进而产生了ORA-01017。

问题现象&＃xff1a;

集群sysbackup用户登陆节点随机报错ORA-01017: invalid username/password; logon denied

问题原因:

        用户进行配置sysbackup用户的操作&＃xff0c;但由于集群密码文件没有共享&＃xff0c;导致两个节点的密码文件不一致&＃xff0c;在用户通过scan ip随机分发节点时&＃xff0c;连接到了错误密码所在的节点&＃xff0c;进而产生了ORA-01017。

问题解决&＃xff1a;

方法一:在集群每个节点同时执行配置sysbackup的操作&＃xff0c;确保节点密码文件一致

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

方法二:将正确节点的密码文件拷贝到其他节点&＃xff0c;确保集群节点密码文件配置一致

scp $ORACLE_HOME/dbs/orapworcl1 node2&＃64;:$ORACLE_HOME/dbs/orapworcl2

方法三:将密码文件设置为asm共享文件

orapwd input_file&＃61;&＃39;/u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl1&＃39; file&＃61;&＃39;&＃43;DATADG/ORCL/orapworcl&＃39; asm&＃61;y force&＃61;y
---不需要重启数据库&＃xff0c;密码文件会自己切换到asm路径下&＃xff0c;可以通过GV$PASSWORDFILE_INFO进行验证

问题分析:

在节点一node1创建备份用户testbackup

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

测试用户登陆&＃xff0c;会出现随机登陆失败的情况

---通过scan_ip,第一次登陆成功
[oracle&＃64;node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ sqlplus testbackup/testbackup&＃64;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
---通过scan_ip,第二次登陆失败&＃xff0c;报ORA-01017: invalid username/password; logon denied
[oracle&＃64;node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ sqlplus testbackup/testbackup&＃64;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用户密码文件的配置

---会发现&＃xff0c;只有节点一node1有写入TESTBACKUP这个sysbackup的用户&＃xff0c;节点二node2没有写入sysbackup用户1* select inst_id,username,sysbackup from gv$pwfile_users
​INST_ID USERNAME SYSBA
---------- -------------------------------------------------------------------------------------------------------------------------------- -----1 SYS FALSE1 TESTBACKUP TRUE2 SYS FALSE

查看密码文件路径&＃xff0c;可以发现密码文件都是节点本地存放

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 02 /u01/app/oracle/product/19.0.0/db_1/dbs/orapworcl2 12 FALSE 0

测试通过节点vip访问登陆,节点一vip登陆没有报错&＃xff0c;节点二vip登陆有报错ora-01017&＃xff0c;这进一步验证了节点二密码文件有问题

---通过节点一vip登陆没有报错
[oracle&＃64;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 &＃39;testbackup/testbackup&＃64;node1-vip:1521/orcl as sysbackup&＃39;
connected to target database: ORCL (DBID&＃61;1632283697)
RMAN> exit
​
---通过节点二vip登陆报错ora-01017
[oracle&＃64;node1 /u01/app/oracle/product/19.0.0/db_1/network/admin ]$ rman target /
​
RMAN> connect target &＃39;testbackup/testbackup&＃64;node2-vip:1521/orcl as sysbackup&＃39;;
​
RMAN-00571: &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
RMAN-00569: &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61; ERROR MESSAGE STACK FOLLOWS &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
RMAN-00571: &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
ORA-01017: invalid username/password; logon denied
​
RMAN> exit
​

在节点二node2执行节点一配置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 02 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

再次通过节点二vip进行登陆&＃xff0c;连接成功

RMAN> connect target &＃39;testbackup/testbackup&＃64;node2-vip:1521/orcl as sysbackup&＃39;;
​
connected to target database: ORCL (DBID&＃61;1632283697)
​
RMAN> select sys_context(&＃39;USERENV&＃39;, &＃39;CURRENT_SCHEMA&＃39;) current_schema, sys_context(&＃39;USERENV&＃39;, &＃39;SESSION_USER&＃39;) session_user from dual;
​
using target database control file instead of recovery catalog
​
​
CURRENT_SCHEMA
--------------------------------------------------------------------------------
SESSION_USER
--------------------------------------------------------------------------------
​
SYS
SYSBACKUPRMAN> exit

总结:

       sysbackup用户出现随机登陆失败ora-01017的原因为用户进行配置sysbackup用户的操作&＃xff0c;但由于集群密码文件没有共享&＃xff0c;导致两个节点的密码文件不一致&＃xff0c;在用户通过scan ip随机分发节点时&＃xff0c;连接到了错误密码所在的节点&＃xff0c;进而产生了ORA-01017。
        可以通过以下方法修复问题
        方法一:在集群每个节点同时执行配置sysbackup的操作&＃xff0c;确保节点密码文件一致。
        方法二:将正确节点的密码文件拷贝到其他节点&＃xff0c;确保集群节点密码文件配置一致。
        方法三:将密码文件设置为asm共享文件。