Thursday, August 28, 2014

Error while accessing ASM Instance using asmcmd -p => ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch") (DBD ERROR: error possibly near <*> indicator at char 33 in '/* ASMCMD */ select version from <*>v$instance')

Today, We have faced issue while accessing asmcmd prompt,

Issue:

From 1st Node of Exadata RAC,
[nodedbadm01.tajhotels.com -> oracle:/home/oracle] :asmcmd -p
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch") (DBD ERROR: error possibly near <*> indicator at char 33 in '/* ASMCMD */ select version from <*>v$instance')
ASMCMD [+] >

From 2nd Node of Exadata RAC,
ASMCMD [+RECO_DC/SIEBPRD/ARCHIVELOG] > rm -rf 2014_08_27
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

From our all Cell Nodes, ASM Disks were showing status UNKNOWN,

[root@inmumdcceladm01 ~]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Mon Sep 01 18:10:11 GMT+05:30 2014

Copyright (c) 2007, 2012, Oracle.  All rights reserved.
Cell Efficiency Ratio: 1,842

CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
         DATA_DC_CD_00_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_01_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_02_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_03_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_04_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_05_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_06_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_07_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_08_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_09_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_10_inmumdcceladm01   UNKNOWN
         DATA_DC_CD_11_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_02_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_03_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_04_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_05_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_06_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_07_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_08_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_09_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_10_inmumdcceladm01   UNKNOWN
         DBFS_DG_CD_11_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_00_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_01_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_02_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_03_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_04_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_05_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_06_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_07_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_08_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_09_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_10_inmumdcceladm01   UNKNOWN
         RECO_DC_CD_11_inmumdcceladm01   UNKNOWN

CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=flashdisk
         FLASH_1_0       FL00444G        normal
         FLASH_1_1       FL003YY8        normal
         FLASH_1_2       FL0044EV        normal
         FLASH_1_3       FL0045NR        normal
         FLASH_2_0       FL0044MF        normal
         FLASH_2_1       FL0044ME        normal
         FLASH_2_2       FL0043YZ        normal
         FLASH_2_3       FL0043WL        normal
         FLASH_4_0       FL0044KD        normal
         FLASH_4_1       FL0044EZ        normal
         FLASH_4_2       FL0045F9        normal
         FLASH_4_3       FL0043RF        normal
         FLASH_5_0       FL0044LC        normal
         FLASH_5_1       FL0045K3        normal
         FLASH_5_2       FL0044F6        normal
         FLASH_5_3       FL0045MD        normal

CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=harddisk
         20:0    K8L20N  normal
         20:1    K851NN  normal
         20:2    K8KPXN  normal
         20:3    K8565N  normal
         20:4    K5U4WN  normal
         20:5    K8L29N  normal
         20:6    K860YN  normal
         20:7    K8536N  normal
         20:8    K8KV4N  normal
         20:9    K8L3WN  normal
         20:10   K8L2GN  normal
         20:11   K8KWLN  normal



Reason:
More shared memory is needed than was allocated in the shared pool.

This is identified as Bug 13951456 - ASMCMD does not use bind variables in its SQL - affects shared pool (Doc ID 13951456.8)

Versions confirmed as being affected : 11.2.0.3, 11.2.0.4

Reason for showing ASM Disks UNKNOWN, is basically ASM Instance couldn't able to communicate with Cell Nodes.

Workaround:

1. SQL> Alter system flush shared_pool; in each ASM Instance.

2. Increase the sga_target and shared_pool size at ASM level from other instance.
SQL> alter system set sga_target=1536M sid='*' scope=spfile;
SQL> alter system set shared_pool_size=500M sid='*' scope=spfile;

and restart all the instances in rolling fashion to implement this changes.

3.limiting ASMCMD usage for the time being would help. So, reduce the usage of asmcmd level commands for time being.

4. Bounce the ASM instance in rolling fashion.

Bug Fix List: the 11.2.0.4 Patch Bundles for Oracle Exadata Database Machine (Doc ID 1601749.1)

QUARTERLY DATABASE PATCH FOR EXADATA (JUL2014 - 11.2.0.4.9) - Patch 18840215

Included Content - The following patch bundles were included in 11.2.0.4 BP9 for Exadata

Patch 18522509 - DATABASE PATCH SET UPDATE 11.2.0.4.3 (INCLUDES CPUJUL2014)
Patch 18522515 - OCW PATCH SET UPDATE 11.2.0.4.3
Patch 18825509 - EXADATA DATABASE BUNDLE PATCH 11.2.0.4.9
Patch 18835772 - QUARTERLY DATABASE PATCH FOR EXADATA (JUL 2014 - 11.2.0.3.24)
Patch 18632316 - MONTHLY DATABASE PATCH FOR EXADATA (JUN2014 - 11.2.0.4.8)


We have 11.2.0.3 Exadata Environment, so we are going to apply 18835772 patch for sure. :)

Update after 2 days,

We had to restart ASM Instance in rolling fashion, after increasing SGA_TARGET Parameter after which issue is resolved, also ASM Disks Status come to ONLINE.

1) Stopping All RAC DB Instances on 1st Node using srvctl / sqlplus
2) Stop ASM Instance
3) Create spfile from pfile for ASM Instance (where *.SGA_TARGET=3G from earlier 500M)
4) Startup ASM Instance
5) Startup All RAC DB Instances
6) Perform Same Steps on rest RAC Nodes.

Oracle SR Suggested below Plan of Action (but before that only, our issue was resolved)

FIX: 
=== 
I have verified that the one off patch 13951456 available on base 11.2.0.3 release does not conflict with you current version. Apply the patch 13951456 to fix the issue. 

About the UNKNOWN status of the asmmodestatus, I would recommend restarting the cellsrv and check if the status reflects correctly. 

No comments:

Post a Comment