active dataguard用户解锁

小荷 2014-07-17 15:18

有个用户,在备库尝试多次登录,都是密码错误登录不上,再去主库登录,还是登录不上。并且由于尝试过多次数的密码,账户被锁定了。

DBA帮助其在主库解锁后,在active dataguard却还是无法登陆。

在ADG端检查:

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端检查:

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)

==>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 useraccount unlock;即可。

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:

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>

[返回] [原文链接]