Information Technology Blog

Scenario of The problem:

In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,

SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument

Reason of The problem:

The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.

Solution of The problem:

1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,

SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.

SQL> alter system set sga_target=1600M;
System altered.

SQL>shutdown

Now set ORACLE_SID and start the instance.

bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;

And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.

And start the database with the pfile.

SQL>STARTUP PFILE='pfile_name';

Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;

A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. With the dynamic SGA infrastructure, the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically, because portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters most affect SGA size:

DB_CACHE_SIZE: The size of the cache of standard blocks.

LOG_BUFFER: The number of bytes allocated for the redo log buffer cache.

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

LARGE_POOL_SIZE: The size of the large pool; the default is zero

JAVA_POOL_SIZE :- The size of the Java pool.

Dynamic SGA Granules

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Related post: