Doc ID: Note:373018.1 Type: PROBLEM
Last Revision Date: 01-SEP-2006 Status: MODERATED

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1.0
This problem can occur on any platform.

Symptoms

After upgrading the database to 10.2.0.1, initialization parameter LOG_BUFFER was increased to 6283264 which is not the value set in the spfile or pfile (3145728).

Cause

The log_buffer value is more than which set in spfile. This is not a bug, it is an expected behaviour of parameter log_buffer.

Research
------------

The below are from Oracle 9.2.0.6 (HP-UX PA-RISC (64-bit))

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 406810928
SQL> show sga

Total System Global Area 406810928 bytes
Fixed Size 737584 bytes
Variable Size 369098752 bytes
Database Buffers 33554432 bytes
Redo Buffers 3420160 bytes
SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 3145728
SQL> select name, value, isdefault from v$parameter where name = 'log_buffer';


NAME VALUE ISDEFAULT
------------------------------ ------------------------------ ---------
log_buffer 3145728 FALSE


SQL> select pool, name, bytes from v$sgastat where name = 'log_buffer';

POOL NAME BYTES
----------- -------------------------- ----------
log_buffer 3409920

SQL> select sid, name, value, isspecified from v$spparameter where name = 'log_buffer';

SID NAME VALUE ISSPEC
--- ---- ----- ------
* log_buffer 3145728 TRUE

SQL> show parameter pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 117440512
large_pool_size big integer 16777216
olap_page_pool_size integer 33554432
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 201326592



The below are from Oracle 10.2.0.2 (HP-UX PA-RISC (64-bit)) after upgrading from 9.2.0.6

SQL> show sga

Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes
SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 400M
SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 6230016
SQL> select name, value, isdefault from v$parameter where name = 'log_buffer';

NAME VALUE ISDEFAULT
------------------------------ ------------------------------ ---------
log_buffer 6230016 FALSE

SQL> select pool, name, bytes from v$sgastat where name = 'log_buffer';

POOL NAME BYTES
------------ ------------------------------ ----------
log_buffer 6328320


SQL> select sid, name, value, display_value, isspecified from v$spparameter where name =
'log_buffer';

SID NAME VALUE DISPLAY_VALUE ISSPEC
--- ---- ----- ------------- ------
* log_buffer 3145728 3145728 TRUE

SQL> alter system set sga_max_size=500M scope=spfile;

System altered.

SQL> show sga

Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 2058120 bytes
Variable Size 482347128 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.

SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 6230016
SQL> select name, value, isdefault from v$parameter where name = 'log_buffer';

NAME VALUE ISDEFAULT
------------------------------ ------------------------------ ---------
log_buffer 6230016 FALSE

SQL> select pool, name, bytes from v$sgastat where name = 'log_buffer';

POOL NAME BYTES
------------ ------------------------------ ----------
log_buffer 6328320

SQL> select sid, name, value, display_value, isspecified from v$spparameter where name =
'log_buffer';

SID NAME VALUE DISPLAY_VALUE ISSPEC
--- ---- ----- ------------- ------
* log_buffer 3145728 3145728 TRUE

SQL> sho parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 500M

SQL> show sga

Total System Global Area 524288000 bytes
Fixed Size 2058120 bytes
Variable Size 482347128 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes

SQL> alter system set sga_max_size=400M scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> show parameter pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 112M
large_pool_size big integer 16M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 184M
streams_pool_size big integer 48M

SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;

ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2060536 bytes
Variable Size 796921608 bytes
Database Buffers 33554432 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.
SQL> show parameter pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 112M
large_pool_size big integer 16M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 184M
streams_pool_size big integer 48M


SQL> exit

Solution

The change in the log_buffer value is expected behaviour.
The value of log_buffer from v$spparameter gives you the value which you have set in the spfile;

The value from show sga, sho parameter, v$sgastat, v$parameter are different than which you have set because this is not a minimum limit for redo buffer size, Oracle combines fixed SGA area and redo buffer together, if there is a free space after Oracle put combined buffer to fit in granule, that space is added to redo buffer. Thus you see redo buffer has more space as expected. This is an expected behavior.

For example if you see the output i have given in the research area,

In show SGA for sga_max_size of 400M the split up is as

show SGA
Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes

show parameter pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 112M
large_pool_size big integer 16M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 184M
streams_pool_size big integer 48M

db_cache_size is 32 M

This contains 3 components as
1. Fixed size + Redo Buffer
2. Variable Size - (shared_pool_size + large_pool_size + streams_pool_size +
java_pool_size+overhead)
3. Database buffers - (db_cache_size + db_nK_cache_size +..)

In the above example,
sga_max_size= (Fixed size + Redo Buffer ) + variable size + database buffers
400M= (2057256+6328320 (8 M)) + 377490392 (360 M) + 33554432 (32 M)
sga_max_size is 400M
db_cache_size is 32 M
Variable size as mentioned 360 M
Fixed size varies from Platform to Platform and Database to Database depending on other parameter values like shared_pool, large_pool, etc.,
In this case 2M is Fixed size, so 360+32+2 is 394 M.
left out is 6M and the log_buffer set is 3M.
Since sga_max_size is 400M which is > 128 M the granule size is 16M the value of redo buffer should be log_buffer + overhead (rounded off to 1 granule which is 16 M in this case) i.e. 3M+16M => 19M, but the sga_max_size is 400M it cannot exceed 400M,
so it will take 394 M + 3 M + 3M which is = 400, since total sga size should be <= sga_max_size (400 M in this case).

This is the reason you could see difference in values in dictionaries or views than which you have set in spfile or pfile.
This is an expected behaviour.