Resolution of ORA-00371: not enough shared pool memory
I inaugurate this blog on Oracle with an episode that happened to me just this morning as soon as I need to work.
Short preamble: at 08:30 the strain test of a replacement procedure that we bought here where I work had to start out. it’s a web-based application whose sites are served by two Tomcat application servers with Windows Server 2008 R2 OS, while the database is found on an Oracle RAC (2 Red Hat Linux nodes) connected to a SAN via glass fiber ( cool!).
The aim is to enhance the performance of the appliance in such how to allow users (accustomed to a client-server application practically with a pseudo-textual interface and thus in no time compared to a web-based procedure) to figure quickly. This tuning is reflected at the extent of Tomcat, the online pages it serves, and therefore the underlying Oracle DB. I, a minimum of partially, affect this last point.
Just this morning, therefore, the corporate that supplies us with the appliance strongly advised me to bring the processes parameter on each of the 2 instances of our RAC, relative to the database in question, from the worth 1000 to 3500. Now, this parameter which is within the spfile of your database indicates the utmost number of OS processes that will hook up with the Oracle database concurrently. Setting it to 3500, for every node, means during a two-node RAC, you’ve got up to 7000 possible processes that will access the database.
Since the spfile of an Oracle database may be a computer file, one shouldn’t even dream of opening and editing it with a text editor, while it’s possible to use the SQL command ALTER SYSTEM.
$sqlplus / nolog
SQL> connect sys @as sysdba
SQL> ALTER SYSTEM SET PROCESSES = 3500 SCOPE = SPFILE SID = '*'
The SCOPE parameter indicates to update the processes value in the spfile of the db, while the SID = ‘*’ parameter is used to instruct Oracle to perform the modification for both instances of the db, given that we are operating on a RAC.
Once this is done, the two instances are stopped using the srvctl command provided by Oracle, or with an immediate shutdown always performed as user SYS or SYSTEM from the SQL prompt and then restart the RAC instances to make the change made to the spfile file effective.
I expected everything to go smoothly, however, when launching the command to start the two instances of the RAC, I get this error:
ORA-00371: not enough shared pool memory, should be atleast N bytes
To fix this error, by googling a bit I find:
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value
Well, just increase the value of the shared memory assigned to Oracle, for each instance, in the spfile file, therefore always using an ALTER SYSTEM, to be launched as SYS user and … how can I access as SYS user if the database is down?
The first idea that came to me was to copy the contents of the spfile file into a pfile, edit it calmly, bringing the processes value back to 1000, and then recreate the spfile, but doing this I realized that the RAC that we use here at work already uses a pfile of its own that contains a string that reports the position of the spfile which then, in fact, is used to start the RAC Oracle instances.
At this point, the idea that was proposed to me is the following: take back the pfile used when the database was created and which fortunately had not been thrown away, start the two instances of the RAC with it, then launch RMAN (use always RMAN for Oracle backups right?) for spfile restore
$sqlplus / nolog
SQL> connect / as sysdba
(connected to a suspended instance)
SQL> startup pfile = 'path_of_pfile_ recovered' nomount
The two instances magically restart and in NOMOUNT mode, which is important to perform a correct restore via RMAN! Now we need to take care of restoring the spfile via RMAN. You can get the spfile file from the autobackup of the control file that you set up in RMAN with the syntax:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
TO '/ backup / CF_% F';
Now you can connect to RMAN, set the DBID (you can get it from a log executed by RMAN when the database was still working) and then start with the restore of the spfile:
$RMAN TARGET /
RMAN set DBID = (DBID)
RMAN> run {
2> set controlfile autobackup format
3> for device type disk to '/ backup / CF_% F';
4> restore spfile from autobackup;
5>}
command execution: SET CONTROLFILE AUTOBACKUP FORMAT
use of the target database control file instead of the
recovery catalog
Start of restore in 14-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 101 instance =devtype = DISK
channel ORA_DISK_1: search for automatic backup of the day: 20200768
channel ORA_DISK_1: search for automatic backup of the day: 20200769
channel ORA_DISK_1: automatic backup found: / backup / CF_c-1453447801-2020
channel ORA_DISK_1: restore of SPFILE from automatic backup completed
restore finished on 14-JUN-20
After the shutdown and restart of the two instances (you can always use srvctl, or RMAN, or the SQL commands shutdown and startup), the offending instances are back online and the stress test has started.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.