【DG】DG备库报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)
用户密码请参考：http://blog.itpub.net/26736162/viewspace-2129595/

主库

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

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

@ > @ > @ > conn lhr/aa

conn lhr/aa

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

@ > @ > @ > @ > 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';

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#;

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

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

Symptoms

Cause

Solution

References

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 属性

类型

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