【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/，如需轉載，請註明出處，否則將追究法律責任。
