How to Apply Very Large Memory (VLM) Workaround to Resolve Oracle Database Shared Memory (ORA-04031)

Asset ID:	1-72-1941703.1
Update Date:	2018-03-01
Keywords:

Solution Type Problem Resolution Sure

Solution 1941703.1 : How to Apply Very Large Memory (VLM) Workaround to Resolve Oracle Database Shared Memory (ORA-04031)

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 6.6.3 and later
Information in this document applies to any platform.

Symptoms

Some or all Oracle databases of a given IXP subsystem become unreachable.

On ProTrace, when querying a given session stored under one of the impacted databases, the following error message is raising: Unable to connect to Storage server/s X.X.X.X

Cause

The impacted xDR storage servers (DWS) are having "shared memory" issues.

When issue happens, ORA-04031 errors raise under alert_IXP.log file of the impacted xDR server (DWS):

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")

Solution

This solution must be executed only on xDR storage servers (DWS) installed with ORACLE database 10.2.0.5 release.

The solution consists on starting up the database using the attached pfile (pfile_10G_VLM.zip)

System clean up: Check if /usr/TKLC/oracle/ partition is full. If it is the case, delete .trc and .trm files located in /usr/TKLC/oracle/admin/IXP/bdump/,/usr/TKLC/oracle/admin/IXP/cdump/ and /usr/TKLC/oracle/admin/IXP/udump/
Restart the TKLCoracledb service. As root:
service TKLCoracledb restart
Implement VLM
1. Save pfile_10G_VLM.zip (attached to this document) in /home/oracle as oracle user.
2. Unzip the pfile_10G_VLM.zip file. As oracle user:
  
  $ unzip pfile_10G_VLM.zip
  Archive: pfile_10G_VLM.zip
  inflating: pfile_10G_VLM.ora
3. Log to oracle DB using sqlplus. As oracle user:
  $ sqlplus / as sysdba
4. Backup running spfile:
  
  SQL> create pfile='/home/oracle/pfile_backup.ora' from spfile;
  File created.
5. Shutdown oracle database:
  
  SQL> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
6. Start up the database with the new pfile:
  
  SQL> startup pfile='/home/oracle/pfile_10G_VLM.ora'
  ORACLE instance started.
  
  Total System Global Area 4093640704 bytes
  Fixed Size 1274572 bytes
  Variable Size 2042490164 bytes
  Database Buffers 2000822272 bytes
  Redo Buffers 49053696 bytes
  Database mounted.
  Database opened.
  
  In some cases the server may have slightly different memory causing the following error:
  ORA-27103: internal error
  Linux Error: 12: Cannot allocate memory
  In such case edit the pfile_10G_VLM.ora and change shared_pool_size = 1996488704 to shared_pool_size = 1820M and start the DB with the modified pfile.
7. Create spfile from pfile to permanently modify startup options:
  
  SQL> create spfile from pfile='/home/oracle/pfile_10G_VLM.ora';
  File created.
Restart oracle service. As root user:
service TKLCoracledb restart

Attachments

This solution has no attachment