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 [+] >
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.
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
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.
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.
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