ORA-39700: database must be opened with UPGRADE option when upgrading standby instance

You might see error “ORA-39700: database must be opened with UPGRADE option “  in standby instance after Oracle version upgrade even if standby binary is also upgraded to same version as of primary

[oracle@admdbbk001:/orasw/app/oracle/product/11.2.0.4/db]# echo $ORACLE_HOME

/orasw/app/oracle/product/11.2.0.4/db

[oracle@admdbbk001:/orasw/app/oracle/product/11.2.0.4/db]# sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 9 10:14:01 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2248080 bytes

Variable Size             822084208 bytes

Database Buffers         1291845632 bytes

Redo Buffers               21708800 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 17039506

Session ID: 588 Serial number: 3

Fix is to start standby instance in mount mode, start listener and wait for logs to be transferred and applied to standby.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size                  2248080 bytes

Variable Size             822084208 bytes

Database Buffers         1291845632 bytes

Redo Buffers               21708800 bytes

Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select name,open_mode,database_role,switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

TEST11G   MOUNTED              PHYSICAL STANDBY NOT ALLOWED

After few minutes once it has applied all logs, we can shutdown and start database and error will disappear.

LOCKED vs LOCKED(TIMED) in ACCOUNT_STATUS in DBA_USERS

We use ACCOUNT_STATUS column value: LOCKED(TIMED) in DBA_USERS dictionary view to monitor cases where ACCOUNTS getting locked due to wrong password attempts.  When we deliberately lock a user account, status is set to LOCKED and it works for most of the cases.

I Started seeing a new behavior after 11g upgrade where ACCOUNT_STATUS for database users changed from LOCKED(TIMED) to LOCKED when user accounts is locked after too many wrong password attempts.

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_APP';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_APP                       LOCKED                           2015-02-03 12:18:28 APPLICATION_PROFILE

SQL> select username,account_status,LOCK_DATE,profile from dba_users where username='MAD_TOOL_ADM';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           PROFILE

------------------------------ -------------------------------- ------------------- ------------------------------

MAD_TOOL_ADM                  LOCKED                           2015-02-03 12:19:33 SCHEMA_PROFILE

SQL> alter user MAD_APP account unlock;

User altered.

SQL> alter user MAD_TOOL_ADM account unlock;

User altered.

After checking at DBA_PROFILES settings for these user profiles, noticed that  PASSWORD_LOCK_TIME  is set to UNLIMITED. With this value set to unlimited, it’s an expected behavior to see only LOCKED status instead of LOCKED(TIMED). As per our organization standards it should be set to 24855.

PROFILE                               RESOURCE_NAME               RESOURCE_TYPE        LIMIT

--------------------------------- ---------------------- ---------------------- -------------

APPLICATION_PROFILE                PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

SCHEMA_PROFILE                     PASSWORD_LOCK_TIME              PASSWORD        UNLIMITED

Expected behaviour is
1. Oracle release is <= 11.1.0.7.
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME =
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

Note that 10.2.0.5 displays the same behavior as 11.2, because the fix that changed the behavior in 11.2 was introduced in 10.2.0.5.

So finally resolved the issue by following command

SQL> alter profile system_profile limit PASSWORD_LOCK_TIME 24855;

SQL> alter profile application_profile limit PASSWORD_LOCK_TIME 24855;

Now I could see that status changed to LOCKED(TIMED)

SQL> select username,account_status from dba_users where username in ('MAD_APP','MAD_TOOL_ADM');

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MAD_APP                       LOCKED(TIMED)
MAD_TOOL_ADM                  LOCKED(TIMED)