Sessions or DataFeeds Stopped due to Oracle Database Shared Memory issues

Asset ID:	1-72-1912485.1
Update Date:	2017-12-08
Keywords:

Solution Type Problem Resolution Sure

Solution 1912485.1 : Sessions or DataFeeds Stopped due to Oracle Database Shared Memory issues

Applies to:

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

Symptoms

Sessions are stopped and/or DataFeeds are not exporting data.

For Sessions:

Sessions are not updating under ProTrace (End Date frozen).

For DataFeeds:

Under DataFeed application -> Concerned DataFeed -> statistics, no xDRs are exported (record count column always equal to 0 when issue appears) and the Delay increasing continuously.

Cause

At least one of the xDR storage servers (DWS) of the concerned subsystem is 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 ")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")

Solution

To quickly restore the service, restart the ORACLE database on the impacted xDR server (DWS). In some cases, a file system clean up is mandatory before restarting the database.

Depending on the installed ORACLE (10g or 11g), PIC (9.0.3 or earlier) and OS (32bits or 64 bits) releases, SGA or specifically "shared memory" can be extended in order to delay as much as possible the issue's occurrences. Before installing the appropriate workaround or patch, please verify if it is not already installed.

Final fix is provided in PIC9.0.3 with ORACLE11 combined to a 64bits OS.

Follow the below steps:

1- Restart the ORACLE service on the impacted xDR server (DWS):

1.1- If /usr/TKLC/oracle (or /usr/TKLC/oracle11 if having ORACLE 11g) partition is full, delete trc and trm files from the following locations depending on ORACLE installed release:

a- If ORACLE 10g: delete trc and trm files from /usr/TKLC/oracle/admin/IXP/bdump/,/usr/TKLC/oracle/admin/IXP/cdump/ and /usr/TKLC/oracle/admin/IXP/udump/.

b- If ORACLE 11g: delete trc and trm files from /opt/oracle11/oracle/diag/rdbms/ixp/IXP/trace

1.2- restart the ORACLE service. As root, run:

service TKLCoracledb restart

2- Verify if the WA or patch is already installed. As oracle user connect to the database of the impacted xDR server(DWS):

$ sqlplus / as sysdba

  2.1- If ORACLE 10g. Check if following parameters are defined as below. If yes, no need to apply the WA:

      a- Check USE_INDIRECT_DATA_BUFFERS parameter:

SQL> show parameter USE_INDIRECT_DATA_BUFFERS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
use_indirect_data_buffers boolean TRUE

b- Check DB_CACHE_SIZE parameter:

SQL> show parameter DB_CACHE_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0

c- Check sga_target parameter

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0

d- Check DB_BLOCK_BUFFERS parameter

SQL> show parameters DB_BLOCK_BUFFERS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 244241

e- Check the Maximum SGA Size parameter

SQL> SELECT * FROM V$SGAINFO;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1275200 No
Redo Buffers                       49053696 No
Buffer Cache Size                2000822272 Yes
Shared Pool Size                 1996488704 Yes
Large Pool Size                           0 Yes
Java Pool Size                     33554432 Yes
Streams Pool Size                         0 Yes
Granule Size                       16777216 No
Maximum SGA Size            4093640704 No
Startup overhead in Shared Pool   134217728 No
Free SGA Memory Available                 0

11 rows selected.

2-2 If ORACLE 11g with 32 bits OS, Check if both the ORACLE patch 15969429 and Workaround are installed on the impacted xDR server (DWS):

a- Verify if ORACLE patch 15969429 is installed. It it is the case, the following command result should be as below. As oracle user run:

$ /opt/oracle11/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (coffee) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/oracle11/oracle/product/11.2.0/dbhome_1
Central Inventory : /opt/oracle11/oraInventory
  from : /opt/oracle11/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.2.0
Log file location : /opt/oracle11/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2013-07-29_19-49-13P

Lsinventory Output file location : /opt/oracle11/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinv

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 15969429 : applied on Mon Jul 29 19:49:00 WIT 2013
Unique Patch ID: 15960295
  Created on 28 Jul 2013, 18:27:40 hrs PST8PDT
  Bugs fixed:
  15969429

b- Check if the workaround is installed or not. If it is the case, A log called /home/oracle/PERCENT_SHARED_POOL_FLUSH_JOB.log is available and the expected content is the following:

System altered.

System altered.

System altered.

System altered.

Procedure created.

Creating Job 'JOB_SHARED_POOL_FLUSH'

PL/SQL procedure successfully completed.

Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2510954496 bytes
Fixed Size 1345992 bytes
Variable Size 1090520632 bytes
Database Buffers 1375731712 bytes
Redo Buffers 43356160 bytes
Database mounted.
Database opened.

Variable Size 838862392 bytes

Database Buffers 1627389952 bytes

Redo Buffers 43356160 bytes

Database mounted.

Database opened.

3- If appropriate workaround or patch is not installed, please open an SR so that Support team can install it on impacted system.

Attachments

This solution has no attachment