有个用户，在备库尝试多次登录，都是密码错误登录不上，再去主库登录，还是登录不上。并且由于尝试过多次数的密码，账户被锁定了。
DBA帮助其在主库解锁后，在active dataguard却还是无法登陆。
在ADG端检查：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select username ,account_status from dba_users where username='TEST'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- TEST OPEN SQL> SQL> SQL> SQL> conn test/test ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. SQL> SQL> |
在Primary端检查：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select username ,account_status from dba_users where username='TEST'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- TEST OPEN SQL> SQL> conn test/test Connected. SQL> |
这个问题，其实在MOS中Doc ID 1600401.1有说明：
ORA-28000 “the account is locked” in the standby database, even after the account was unlocked in the primary. (Doc ID 1600401.1)
|
1 2 3 4 5 6 7 8 9 |
==>CAUSE A privileged user (a database administrator) must unlock account in the standby database. It is not enough to unlock the same account in the primary database. This is because the standby is open read-only and cannot update any tables. When a user's account has to be locked on the standby database, it is locked only in memory there. ==>SOLUTION A privileged user (sysdba, the database administrator) must logon to the standby and unlock the account there. A message ORA-28015 "Account unlocked, but the database is open for read-only access" confirms that the account is now unlocked in the standby database. From then on, the user can logon to the standby database without getting any error. |
解决方式：
在ADG端再次执行一次alter user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> select username ,account_status from dba_users where username='TEST'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- TEST OPEN SQL> SQL> SQL> SQL> conn test/test ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. SQL> SQL> conn / as sysdba Connected. SQL> SQL> alter user test account unlock; alter user test account unlock * ERROR at line 1: ORA-28015: Account unlocked, but the database is open for read-only access SQL> SQL> conn test/test Connected. SQL> |
在某些版本中，如11.2.0.4，可能会遇到即使在ADG将用户unlock，报错ora-604，还是不能解锁，此时需要重启ADG：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
SQL> alter user test account unlock; alter user test account unlock * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access ORA-06512: at line 993 ORA-16000: database open for read-only access SQL> SQL> SQL> conn test/test ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. SQL> SQL> SQL> conn / as sysdba Connected. SQL> SQL> SQL> SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 356517912 bytes Database Buffers 159383552 bytes Redo Buffers 3780608 bytes Database mounted. Database opened. SQL> SQL> SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> conn test/test Connected. SQL> SQL> |