【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 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. |
|
|
|
类型 | B - Defect | 已在产品版本中修复 | |
严重性 | 2 - Severe Loss of Service | 产品版本 | 11.2.0.3 |
状态 | 92 - Closed, Not a Bug | 平台 | 212 - IBM AIX on POWER Systems (64-bit) |
创建时间 | 2013-2-22 | 平台版本 | 6.1 |
更新时间 | 2017-8-24 | 基本 Bug | N/A |
数据库版本 | 11.2.0.3 | 影响平台 | Generic |
产品源 | Oracle | 与此 Bug 相关的知识, 补丁程序和 Bug |
产品线 | Oracle Database Products | 系列 | Oracle Database Suite |
区域 | Oracle Database | 产品 | 5 - Oracle Database - Enterprise Edition |
Hdr: 16345308 11.2.0.3 RDBMS 11.2.0.3 DATAGUARD_ACTV PRODID-5 PORTID-212 ORA-28000
Abstract: LOGGING INTO ADG STANDBY GETS ORA-28000: THE ACCOUT IS LOCKED. AND IS NOT. *** 02/21/13 03:48 pm *** (ADD: Impact/Symptom->WRONG RESULT - INCOR...) *** 02/21/13 03:48 pm *** BUG TYPE CHOSEN
===============
Code
== Component: RDBMS ==
======================
DETAILED PROBLEM DESCRIPTION
============================
Customer has a user id they want to use in ADG standby.
CRMPTSI
DBA_USERS show in the primary and the standby the account is open.
The can log into the primary but logging into the standby gets
ORA-28000: the account is locked
This account shows in dba_users that it is open not locked.
Although the account shows as open in the standby customer states
"We have just restarted the standby DB and it did unlock the user." they mean
they were then able to log in with the account. "We do wish to follow through
you asked. Alert logs from both primary and standby and the Opatch
lsinventory list"
But per the diagnostics below, it does not show as locked.
DIAGNOSTIC ANALYSIS
===================
on the primary
SQL> select USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,
CREATED, PROFILE
2 from dba_users where username = 'CRMPTSI' ;
USERNAME USER_ID ACCOUNT_STATUS
LOCK_DATE EXPIRY_DATE CREATED PROFILE
------------------------------ ---------- --------------------------------
------------------ ------------------ ------------------
------------------------------
CRMPTSI 196 OPEN
14-may-13 17:20:23 11-jul-12 17:51:47 PROFILE_SOX
1 row selected.
SQL>
Connected.
SQL>
SQL> show user
USER is "CRMPTSI
On the standby
SQL> select USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,
CREATED, PROFILE
2 from dba_users where username = 'CRMPTSI' ;
USERNAME USER_ID ACCOUNT_STATUS
LOCK_DATE EXPIRY_DATE CREATED PROFILE
------------------------------ ---------- --------------------------------
------------------ ------------------ ------------------
------------------------------
CRMPTSI 196 OPEN
14-may-13 17:20:23 11-jul-12 17:51:47 PROFILE_SOX
1 row selected.
SQL>
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL>
I had them do this to get stack trace.
In the standby:
Do this in one session and the connect in another
session 1:
oradebug setmypid ;
oradebug unlimit ;
oradebug event 10046 trace name context forever,level ;
alter system set events '28000 trace name errorstack level 3';
exit
session 2
sqlplus
connect CRMPTSI/portugal_2013
exit
Standby profile
SELECT * FROM DBA_PROFILES where profile ='PROFILE_SOX' ;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------- -------------------------------- -------- ---------
WORKAROUND?
===========
No
TECHNICAL IMPACT
================
The customer cannot use this account for ADG
RELATED ISSUES (bugs, forums, RFAs)
===================================
I find on bug at all on this stack trace related to ADG standby.
HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
====================================================
Always
DOES THE ISSUE REPRODUCE INTERNALLY?
====================================
No
IS A TESTCASE AVAILABLE?
========================
No
Link to IPS Package:
====================
not available *** 02/21/13 03:49 pm *** *** 02/21/13 10:56 pm *** (CHG: Sta->10) *** 02/21/13 10:56 pm *** *** 02/21/13 10:56 pm *** *** 02/21/13 11:36 pm *** *** 02/22/13 07:15 am *** *** 02/22/13 07:18 am *** (CHG: RDBMS Ver.-> 11.2.0.2 -> 11.2.0.3) *** 02/22/13 09:00 am *** *** 02/22/13 09:55 am *** *** 03/06/13 09:12 am *** (CHG: Sta->16) *** 03/06/13 09:12 am *** *** 03/07/13 01:17 am *** *** 03/07/13 01:19 am *** (CHG: Sta->32) *** 03/07/13 01:19 am *** Key Symptoms/Summary/Rediscovery:
Account status remains OPEN after a user's account is locked on a standby
database.
Explain why this is not a bug:
The account status reflects that of the account on the primary database. *** 03/07/13 07:28 am *** *** 03/13/13 09:22 am *** (CHG: Sta->16) *** 03/13/13 09:22 am *** *** 03/13/13 09:36 am *** (CHG: Sta->32) *** 03/13/13 09:36 am *** *** 03/28/13 08:05 am *** *** 04/23/13 02:48 am *** *** 07/22/13 01:10 am *** (CHG: Sta->92) *** 08/13/13 07:59 pm *** *** 08/26/13 12:46 pm *** *** 12/23/13 12:41 am *** *** 02/26/14 01:52 am *** *** 03/03/14 03:33 pm *** *** 09/02/14 08:19 am *** *** 10/07/14 04:17 pm *** *** 07/27/15 11:08 pm *** *** 10/18/15 10:56 pm *** *** 10/27/15 11:34 am *** *** 08/22/16 12:19 am *** *** 10/02/16 11:28 pm *** *** 10/10/16 07:13 pm *** *** 10/16/16 02:47 pm *** *** 12/01/16 02:42 pm *** *** 12/09/16 05:13 am *** *** 02/09/17 04:08 pm *** *** 04/07/17 12:34 pm *** *** 08/12/17 06:34 pm *** *** 08/18/17 05:51 am *** *** 08/23/17 06:22 pm ***
About Me
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2145402/,如需转载,请注明出处,否则将追究法律责任。