I was facing below error while starting oracle database and because of that database was unable to start.
SQL> startup
ORA-04031: unable to allocate 2072 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","parameter table block")
SQL> exit
Disconnected
When I checked my database memory allocation then found that sga_target was set to 2GB and pga_aggregate_target was set to 1GB. Also checked available server memory using free command. But there was lots of free memory available on the server.
Cause: SGA is not able to allocate required amount of memory to its components. More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either reduce your use of shared memory, or increase the sga_target value to larger than allocated.
STEP 1:
To increase the sga_target first you need to create pfile from spfile because db is down.
SQL> create pfile='/home/oracle/db12c.ora' from spfile;
File created.
STEP 2:
Edit pfile and change the value of sga_target large enough than the current settings using vi editor.
STEP 3:
$ sqlplus / as sysdba
SQL> startup
ORA-04031: unable to allocate 2072 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","parameter table block")
SQL> exit
Disconnected
When I checked my database memory allocation then found that sga_target was set to 2GB and pga_aggregate_target was set to 1GB. Also checked available server memory using free command. But there was lots of free memory available on the server.
Cause: SGA is not able to allocate required amount of memory to its components. More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either reduce your use of shared memory, or increase the sga_target value to larger than allocated.
STEP 1:
To increase the sga_target first you need to create pfile from spfile because db is down.
SQL> create pfile='/home/oracle/db12c.ora' from spfile;
File created.
STEP 2:
Edit pfile and change the value of sga_target large enough than the current settings using vi editor.
STEP 3:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 15:19:47 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/home/oracle/db12c.ora' ;
SQL>create spfile from pfile;
SQL> shut immediate;
SQL> startup;
SQL>create spfile from pfile;
SQL> shut immediate;
SQL> startup;
Very helpful
ReplyDeleteVery helpfull
ReplyDelete