ORA-04031: unable to allocate bytes of shared memory ("","","","")

Problem Description:

===============

Archive log has been shipped from primary but not able to apply in standby.

Error in DB Alert log:

================

Completed Media Recovery

Checking to start in-flux buffer recovery from SCN 1504.3738248416 to SCN  (non-inclusive) 1504.3738248416

ORA-00283: recovery session canceled due to errors

ORA-01237: cannot extend datafile 113

ORA-01110: data file 113: '+DATA/doyensys_p/datafile/sysaux.408.874229253'

ORA-17505: ksfdrsz:1 Failed to resize file to size 1126400 blocks

ORA-00569: Failed to acquire global enqueue.

Slave exiting with ORA-283 exception

ORA-00283: recovery session canceled due to errors

ORA-01237: cannot extend datafile 113

ORA-01110: data file 113: '+DATA/doyensys_p/datafile/sysaux.408.874229253'

ORA-17505: ksfdrsz:1 Failed to resize file to size 1126400 blocks

ORA-00569: Failed to acquire global enqueue.

When we checked for the size, We have enough size in the Disk. But still it shows the above error. So we checked for the asm log.

Asm alert log :

===========

Wed May 20 19:55:29 2015

Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_95265.trc  (incident=353797):

ORA-04031: unable to allocate  bytes of shared memory ("","","","")

Incident details in: /u01/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_353797/+ASM1_ora_95265_i353797.trc

Wed May 20 19:55:30 2015

Solution:

========

There was some memory issue happened in the asm instance though it has the enough memory.So we have just bounced the asm instance.

1. Recovery needs to be cancelled, But it has already been done.

2. shut down the database

3. connect to asm instance

4. srvctl stop asm -n nodename

5. srvctl start asm -n nodename

6. Now start the database in mount and started the recovery.

ORA-28000 "the account is locked" in the standby database

Scenario:

=======

The user attempts to logon and gets an error ORA-28000 "the account is locked" in the primary database which is configure with Dataguard.  The database administrator unlock this user's account in the primary database.  Still, the user cannot connect to the standby, getting the same error ORA-28000 for the particular user, But the account status shows open in the standby.

In Standby :

=========

SYS@DOYEN > conn SARAVANA/saravana123123

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SYS@DOYEN> select username,account_status from dba_users where username='SARAVANA';

USERNAME                                                                                   ACCOUNT_STATUS

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

SARAVANA                                                                                               OPEN

SYS@DOYEN > @check_db

                                                                                                                 Remote

       DbId Name      Log Mode     Open Mode            Database Role    Created             Resetlogs           Archive

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

  603299473 DOYEN   ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY 11/16/2013 20:16:17 07/22/2014 03:54:21 ENABLED

Solution :

=======

 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.

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.

SYS@DOYEN > ALTER USER SARAVANA ACCOUNT UNLOCK;

ALTER USER SARAVANA ACCOUNT UNLOCK

*

ERROR at line 1:

ORA-28015: Account unlocked, but the database is open for read-only access

SYS@DOYEN > conn SARAVANA/saravana123123

Connected.

Reference :
========

ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary. (Doc ID 1600401.1)