ORA-04031: unable to allocate 2048024 bytes of shared memory: 11 g RAC Fix error

The following error is getting logged frequently in alert log and as per the metalink suggestion the SGA_TARGET has to be increased to fix the same. To increase the SGA_TARGET value it requires the database bounce. If you are running in Oracle RAC environment you can perform this change without full database outage. Please find the steps to change value instance by instance.
Error: ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

Step 1 Verify the current values
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
sga_max_size                         big integer 3G
sga_target                           big integer 2560M

Step 2 Change the sga_target value to less than or equal to sga_max_size by using alter system command.
SQL> alter system set sga_target=3g scope=spfile sid='*';

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
sga_max_size                         big integer 3G
sga_target                           big integer 2560M

Step 3 Check the instance status and bounce the first instance.
srvctl status database -d proddb1
Instance PRODDB11 is running on node diamond01
Instance PRODDB12 is running on node diamond02

$ srvctl stop instance -d PRODDB1 -i PRODDB11
$ srvctl status database -d proddb1
Instance PRODDB11 is not running on node diamond01
Instance PRODDB12 is running on node diamond02
$ srvctl start instance -d PRODDB1 -i PRODDB11

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
sga_max_size                         big integer 3G
sga_target                           big integer 3G

Step 4 Do the same steps for the second instance.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
sga_max_size                         big integer 3G
sga_target                           big integer 2560M
SQL> exit

$ srvctl status database -d PRODDB1
Instance PRODDB11 is running on node diamond01
Instance PRODDB12 is running on node diamond02
$ srvctl stop instance -d PRODDB1 -i PRODDB12
$ srvctl status database -d PRODDB1
Instance PRODDB11 is running on node diamond01
Instance PRODDB12 is not running on node diamond02

Step 5 Bounce second database instance.
$ srvctl start instance -d PRODDB1 -i PRODDB12
$ srvctl status database -d PRODDB1
Instance PRODDB11 is running on node diamond01
Instance PRODDB12 is running on node diamond02

[oracle@diamond02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 6 00:32:58 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
sga_max_size                         big integer 3G
sga_target                           big integer 3G
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google