织梦CMS - 轻松建站从此开始！
高级搜索|网站地图|TAG标签RSS订阅[设为首页] [加入收藏]

abg欧博官网|登陆|游戏|

搜索

热门标签:

当前位置: abg欧博官网|登陆|游戏| > 欧博会员网站 > 文章页

28000: the account is locked的解决办法

时间:2024-09-03 23:26来源: 作者:admin 点击: 36 次
【DG】DG备库报ORA-28000: the account is locked的解决办法 测试用户为lhr现象：主备库的lhr用户的状态都是OPEN，但是，备库连接的时候报ORA-28000: the account is locked错误。

【DG】DG备库报ORA-28000: the account is locked的解决办法

2017-09-25 2023

版权

版权声明：

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 【DG】DG备库报ORA-28000: the account is locked的解决办法 测试用户为lhr现象：主备库的lhr用户的状态都是OPEN，但是，备库连接的时候报ORA-28000: the account is locked错误。

【DG】DG备库报ORA-28000: the account is locked的解决办法


测试用户为lhr
现象： 主备库的lhr用户的状态都是OPEN，但是，备库连接的时候报ORA-28000: the account is locked错误。在主库执行“alter user lhr identified by lhr account unlock;”同步到备库也不能解决，在备库该命令不能执行。
解决：重启DG环境的备库实例即可
参考：ORA-28000 On Active Data Guard (文档 ID 1922621.1)
用户密码请参考：

 

 主库

SYS@oradg11g > alter user lhr identified by lhr account unlock;


User altered.


SYS@oradg11g > 
SYS@oradg11g > 
SYS@oradg11g > 
SYS@oradg11g > conn lhr/lhr
Connected.
LHR@oradg11g >  alter user lhr identified by lhr account unlock;


User altered.

LHR@oradg11g > conn lhr/lhr
Connected.
LHR@oradg11g > select * from dba_users where USERNAME='LHR';


USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE         DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED       PROFILE                        INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME                                                                                                                             PASSWORD E AUTHENTI
------------------------------ ---------- ------------------------------ -------------------------------- ------------------- ------------------- ------------------------------ ------------------------------ ------------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- - --------
LHR                                    95                                OPEN                                                 2018-03-24 11:49:18 USERS                          TEMP                           2017-04-03 21:01:18 DEFAULT                          DEFAULT_CONSUMER_GROUP                                                                                                                                           10G 11G  N PASSWORD

 

 


备库：

 

LHR@oradgphy >  conn lhr/aa
ERROR:
ORA-01017: invalid username/password; logon denied




Warning: You are no longer connected to ORACLE.
@ >  conn lhr/aa
ERROR:
ORA-01017: invalid username/password; logon denied




@ >  conn lhr/aa
ERROR:
ORA-01017: invalid username/password; logon denied




@ >  conn lhr/aa
 conn lhr/aa
ERROR:
ORA-01017: invalid username/password; logon denied




@ > 
 conn lhr/aa




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




@ > @ >  conn lhr/aa




 conn lhr/aa




 conn lhr/aa




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




@ > @ > @ >  conn lhr/aa


 conn lhr/aa




 conn lhr/aa




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




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




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




@ > @ > @ >  conn lhr/aa






ERROR:
ORA-28000: the account is locked




@ > @ > ERROR:
ORA-28000: the account is locked




@ > @ > @ > ERROR:
ORA-28000: the account is locked




@ > @ > @ > ERROR:
ORA-28000: the account is locked




@ > @ > @ > @ >  conn lhr/aa
ERROR:
ORA-28000: the account is locked




@ > 
@ > 
@ > conn lhr/lhr
ERROR:
ORA-28000: the account is locked




@ >  conn lhr/lhr
ERROR:
ORA-28000: the account is locked




@ >  select * from dba_users where USERNAME='LHR';
SP2-0640: Not connected
@ > 
@ > conn  / as sysdba
Connected.
SYS@oradgphy >  select * from dba_users where USERNAME='LHR';


USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE         DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED       PROFILE                        INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME                                                                                                                             PASSWORD E AUTHENTI
------------------------------ ---------- ------------------------------ -------------------------------- ------------------- ------------------- ------------------------------ ------------------------------ ------------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- - --------
LHR                                    95                                OPEN                                                 2018-03-24 11:49:18 USERS                          TEMP                           2017-04-03 21:01:18 DEFAULT                          DEFAULT_CONSUMER_GROUP                                                                                                                                           10G 11G  N PASSWORD


SYS@oradgphy >  conn lhr/lhr
ERROR:
ORA-28000: the account is locked




Warning: You are no longer connected to ORACLE.
@ > conn / as sysdba
Connected.
SYS@oradgphy > startup force
ORACLE instance started.


Total System Global Area  242171904 bytes
Fixed Size                  2227256 bytes
Variable Size             197133256 bytes
Database Buffers           37748736 bytes
Redo Buffers                5062656 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/oradgphy/system01.dbf'




SYS@oradgphy > 
SYS@oradgphy > 
SYS@oradgphy > alter database recover managed standby database using current logfile disconnect from session;










Database altered.


SYS@oradgphy > SYS@oradgphy > SYS@oradgphy > SYS@oradgphy > SYS@oradgphy > 
SYS@oradgphy > 
SYS@oradgphy > alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress




SYS@oradgphy >  alter database recover managed standby database cancel;


Database altered.


SYS@oradgphy > alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/oradgphy/system01.dbf'




SYS@oradgphy > conn lhr/lhr
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0




Warning: You are no longer connected to ORACLE.
@ > conn / as sysdba
Connected.
SYS@oradgphy > select value from v$diag_info;


VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUE
/u01/app/oracle
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/alert
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/incident
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/cdump
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/hm
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_ora_8346.trc
0
0


11 rows selected.


SYS@oradgphy > 
SYS@oradgphy > 
SYS@oradgphy > alter database recover managed standby database using current logfile disconnect from session;






Database altered.


SYS@oradgphy > SYS@oradgphy > SYS@oradgphy > col name for a100
SYS@oradgphy > set linesize 9999  pagesize 9999
SYS@oradgphy > col NEXT_CHANGE# for 999999999999999
SYS@oradgphy > SELECT THREAD#,
  2         NAME,
  3         sequence#,
  4         archived,
  5         applied, 
  6         a.NEXT_CHANGE#
  7  FROM   v$archived_log a
  8  WHERE  a.sequence# >= (select max(b.sequence#)-3 from v$log b where b.THREAD#=a.THREAD# )
  9  AND    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
 10  ORDER  BY a.THREAD#,
 11            a.sequence#;


   THREAD# NAME                                                                                                  SEQUENCE# ARC APPLIED       NEXT_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ----------------
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_164_dwjyf088_.arc                164 YES YES                3218670
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_165_dwjydkp8_.arc                165 YES YES                3221768
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_166_dwjyf828_.arc                166 YES IN-MEMORY          3221806


SYS@oradgphy >  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     162
Next log sequence to archive   0
Current log sequence           167
SYS@oradgphy > 10046
SP2-0226: Invalid line number
SYS@oradgphy > col name for a100
SYS@oradgphy > set linesize 9999  pagesize 9999
SYS@oradgphy > col NEXT_CHANGE# for 999999999999999
SYS@oradgphy > SELECT THREAD#,
  2         NAME,
  3         sequence#,
  4         archived,
  5         applied, 
  6         a.NEXT_CHANGE#
  7  FROM   v$archived_log a
  8  WHERE  a.sequence# >= (select max(b.sequence#)-3 from v$log b where b.THREAD#=a.THREAD# )
  9  AND    resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
 10  ORDER  BY a.THREAD#,
 11            a.sequence#;


   THREAD# NAME                                                                                                  SEQUENCE# ARC APPLIED       NEXT_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ----------------
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_164_dwjyf088_.arc                164 YES YES                3218670
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_165_dwjydkp8_.arc                165 YES YES                3221768
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2017_09_25/o1_mf_1_166_dwjyf828_.arc                166 YES IN-MEMORY          3221806


SYS@oradgphy >  alter database recover managed standby database using current logfile disconnect from session;
 alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active




SYS@oradgphy >  alter database recover managed standby database cancel;
































[1]+  Stopped                 rlwrap sqlplus / as sysdba
[oracle@rhel6lhr ~]$ 
[oracle@rhel6lhr ~]$ 
[oracle@rhel6lhr ~]$ 
[oracle@rhel6lhr ~]$ 
[oracle@rhel6lhr ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 25 11:59:00 2017


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SYS@oradgphy > alter database open readonly;
alter database open readonly
                    *
ERROR at line 1:
ORA-02288: invalid OPEN mode




SYS@oradgphy > 
SYS@oradgphy > 
SYS@oradgphy > alter database open read only;


Database altered.


SYS@oradgphy > conn lhr/lhr
Connected.

 

 

 






ORA-28000 On Active Data Guard (文档 ID 1922621.1)  

In this Document

   
   
   
   


APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS

Noticed one user account was locked in primary and its Active Data Guard instances. It was fine Primary Database after unlocking the User, but at the Active Data Guard Standby Database, it was showing ORA-28000 that the account is still locked. Followed

Note 1600401.1: ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary

but it is still unlocked and user can not connect to standby database. Here is log:

 

SQL> select name,database_role , open_mode from v$database ;

NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ELFVRFI1 PHYSICAL STANDBY READ ONLY

SQL> select username,account_status from dba_users where username = 'A472033';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
A472033 LOCKED(TIMED)


SQL> alter user A472033 account unlock ;
SQL> alter user A472033 account unlock
*
ERROR at line 1:
ORA-28015: Account unlocked, but the database is open for read-only access

 

CAUSE

This issue is detailed in 
Bug 16345308: LOGGING INTO ADG STANDBY GETS ORA-28000: THE ACCOUT IS LOCKED. AND IS NOT

It is not a bug, but a limitation of an Active Data Guard READ ONLY Database 

 

SOLUTION

Regardless of how the account becomes locked once locked it will stay locked in the Active Data Guard standby that is open read only.
You may have an account profile that sets how many times an incorrect password can be used before the account is locked.
In this case the standby instance needs to be restarted to reflect the correct status of the account again in the primary.

Or the profile may lock the account after it times out depending on the settings.
You can then unlock the account in the primary and the data dictionary is updated, but that which locks the account in memory is not.
So again you must restart the ADG standby.  This is not a bug it is a limitation of a read only standby database.

The issues with applications that have Identity in the database and applications that have Identity in the front end app. 
The later is not a problem for this issues.  With Identity in the database it can be.

 

Per this note  Bug 17732353 - ORA-28000 "the account is locked" attempting to log in as a remote SYSDBA user if SYS account is locked (Doc ID 17732353.8)

it is fixed in 12.1 and 12.2

REFERENCES BUG:16345308  - LOGGING INTO ADG STANDBY GETS ORA-28000: THE ACCOUT IS LOCKED. AND IS NOT.
NOTE:1600401.1  - ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary.
NOTE:17732353.8  - Bug 17732353 - ORA-28000 "the account is locked" attempting to log in as a remote SYSDBA user if SYS account is locked

Bug 16345308 : LOGGING INTO ADG STANDBY GETS ORA-28000: THE ACCOUT IS LOCKED. AND IS NOT.  

 

 

 

 
 
 

 

       

Bug 属性

     
     

类型        
       
       
       
       
       
     

             
     

       
       

 >>                                                                                                                                                                                                                                                                                                        >>>>                                                                                                                                                                                                                                                                                                        >>                                           >>>>>>>>




&

    

  

img_e3029f287d989cd04bd75432ecc1c172.png


  

DBA笔试面试讲解群1


  

DBA笔试面试讲解群2


  

欢迎与我联系

(责任编辑：)
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规，严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 验证码:
最新评论 进入详细评论页>>
发布者资料
查看详细资料 发送留言 加为好友 用户等级: 注册时间:2025-05-18 23:05 最后登录:2025-05-18 23:05
栏目列表
推荐内容
热点内容

Powered by 欧博官网 © 2020

Copyright © 2023 abg欧博官网|登陆|游戏| 版权所有