Home » RDBMS Server » Server Administration » changing the size of log_buffer
changing the size of log_buffer [message #209451] Thu, 14 December 2006 23:25 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
I am using 10g on win2000 for testing purpose
and i am changing the log_buffer

SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_buffer integer 11158528

SQL> alter system set log_buffer=5242880 scope=spfile;

System altered.

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

Total System Global Area 138412032 bytes
Fixed Size 1247756 bytes
Variable Size 75498996 bytes
Database Buffers 54525952 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_buffer integer 7028736

I have changed log_buffer from 11158528 to 5242880
But when i restart the DB then i found log_buffer=7028736 instead of specified value(524288)
granule_size=4MB
What could be the reason?
Can anybody help me in this regard?

thanx in advance
Re: changing the size of log_buffer [message #209452 is a reply to message #209451] Thu, 14 December 2006 23:33 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,
For oracle 9i, log_buffer is static parameter and its size cannot be modified after instance startup.
Open pfile (initsid.ora) & change value of log_buffer.
Start database using pfile.
Check value of log_buffer.
Create spfile from pfile.
shutdown database.
start database using spfile.
For oracle 10g,with Oracle 10g the log_buffer parameter is not to be directly set anymore. Instead Oracle automatically calculates internally how big this buffer should be.
Check this link
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-08-19.0090015270

[Updated on: Thu, 14 December 2006 23:46]

Report message to a moderator

Re: changing the size of log_buffer [message #209463 is a reply to message #209451] Fri, 15 December 2006 00:31 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
M.Shakeel Azeem wrote on Thu, 14 December 2006 23:25
I am using 10g on win2000 for testing purpose
and i am changing the log_buffer

SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_buffer integer 11158528

SQL> alter system set log_buffer=5242880 scope=spfile;

System altered.

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

Total System Global Area 138412032 bytes
Fixed Size 1247756 bytes
Variable Size 75498996 bytes
Database Buffers 54525952 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- -----------
log_buffer integer 7028736

I have changed log_buffer from 11158528 to 5242880
But when i restart the DB then i found log_buffer=7028736 instead of specified value(524288)
granule_size=4MB
What could be the reason?
Can anybody help me in this regard?

thanx in advance



Oh Dear
I am quiet clear about log_buffer is static but please look at my query
SQL> alter system set log_buffer=5242880 scope=spfile;

This will change the log_buffer size in spfile and this change will only afftected by restrating the DB

But in 10g this parameter is still static and should be manually hadled by DBA because oracle server still not handle this automatically
Please read the relevant documents in depth before making any
suggestions

Best Regards,
M.Shakeel Azeem
Re: changing the size of log_buffer [message #209492 is a reply to message #209463] Fri, 15 December 2006 03:02 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,

LOG_BUFFER
10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.
Check this site:
1> http://www.adp-gmbh.ch/ora/admin/init_params/sga.html






Re: changing the size of log_buffer [message #209497 is a reply to message #209492] Fri, 15 December 2006 03:29 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
seema.taunk wrote on Fri, 15 December 2006 03:02
Hi,

LOG_BUFFER
10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.
Check this site:
1> http://www.adp-gmbh.ch/ora/admin/init_params/sga.html









From the link you provided ,i got the following

Manual SGA parameters
db_keep_cache_size
db_recycle_cache_size
db_NNk_cache_size
NN being one of 2, 4, 8, 16, 32
log_buffer
streams_pool_size

So you should read the complete document first and then give me any suggestions

OK dear ,have a nice weekend

Re: changing the size of log_buffer [message #209507 is a reply to message #209497] Fri, 15 December 2006 04:32 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,

I have read the document & I am not giving u any suggestions.
It is just a discussion.
If u have metalink login check it.
In metalink site
Doc ID: Note:351857.1

Subject: The Log_buffer Cannot be Changed In 10g R2
Bug 4930608 - Unable To Change Log_Buffer In 10g R2
Solution : Log_buffer can no longer be modified and will be set by default by Oracle.
Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.


Re: changing the size of log_buffer [message #209531 is a reply to message #209497] Fri, 15 December 2006 06:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The OP is repeatedly stating similar statements
>>Please read the relevant documents in depth before making any
>>So you should read the complete document first and then give me any suggestions

THis is NOT A PAID SERVICE.
If you want something like that, better post your queries in metalink.oracle.com.
NOT HERE. THis is really unfair.
Someone is trying to invest time and effort and this is not way to show the appreciation even
if the response was COMPLETELY WRONG (not in this case though). Sure you/anyone can correct and criticize the posting.
But you cannot show disrespect.


Quote:

From the link you provided ,i got the following

Manual SGA parameters
db_keep_cache_size
db_recycle_cache_size
db_NNk_cache_size
NN being one of 2, 4, 8, 16, 32
log_buffer
streams_pool_size

So you should read the complete document first and then give me any suggestions


Did you read the complete document or failed to understand the selective parts of it?
Quoting the same URL as before Seema.Taunk has quoted
Quote:

10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.


If you are so wise ( and after 80+ posting here in orafaq),
You should have spent atleast some time in searching google/this orafaq.
http://www.orafaq.com/forum/m/167503/42800/?srch=log_buffer#msg_167401

[Updated on: Fri, 15 December 2006 07:51]

Report message to a moderator

Re: changing the size of log_buffer [message #209619 is a reply to message #209507] Fri, 15 December 2006 22:47 Go to previous message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
seema.taunk wrote on Fri, 15 December 2006 04:32
Hi,

I have read the document & I am not giving u any suggestions.
It is just a discussion.
If u have metalink login check it.
In metalink site
Doc ID: Note:351857.1

Subject: The Log_buffer Cannot be Changed In 10g R2
Bug 4930608 - Unable To Change Log_Buffer In 10g R2
Solution : Log_buffer can no longer be modified and will be set by default by Oracle.
Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.





Just look closely at the following and then decide either u r wrong or right?

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 16 09:37:59 2006

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

Enter password: *******

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ----------------
log_buffer integer 7028736

SQL> alter system set log_buffer=8388608;
alter system set log_buffer=8388608
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set log_buffer=8388608 scope=spfile;

System altered.

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

Total System Global Area 138412032 bytes
Fixed Size 1247756 bytes
Variable Size 75498996 bytes
Database Buffers 50331648 bytes
Redo Buffers 11333632 bytes
Database mounted.
Database opened.
SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ---------------
log_buffer integer 11158528

Oracle will only round this to appropriate figure but it will not be handled by the oracle server completely and you still have to control this parameter by urself
i think it should be too much for now and we should close this chapter
anyhow thanx a lot for such a greate interest
Previous Topic: Export error while run the export command
Next Topic: INSTANCE_NAME change
Goto Forum:
  


Current Time: Thu Dec 08 08:15:53 CST 2016

Total time taken to generate the page: 0.05449 seconds