advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment