Re: 10g/AIX: Database Buffers problem

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 9 Jun 2004 07:17:22 +1000
Message-ID: <40c62ccc$0$3036$afc38c87@news.optusnet.com.au>

On further investigation, Alvaro, it is impossible to get this error using the DB_CACHE_SIZE parameter, because if that is set to zero, Oracle will silently round it up to at least one granule's worth of buffers (in your case, 16MB).

The only way to get Buffers of zero displayed is to be using the old-fashioned init.ora parameter db_block_buffers. That can be set to zero. Though even there, mysterious things happen:

SQL> show sga

Total System Global Area 83886080 bytes

Fixed Size                   787548 bytes
Variable Size              82836388 bytes
Database Buffers                  0 bytes
Redo Buffers                 262144 bytes

SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_block_buffers                     integer     512
db_block_checking                    boolean     FALSE
db_block_checksum                    boolean     TRUE
db_block_size                        integer     8192

So I have no buffers in one display, 512 in the other, zero set explicitly in my init.ora for db_block_buffers... it gets a bit of a mess! Oh, and my database remains perfectly functional at all times, and hasn't yet crashed out like you reported yours doing.

Anyway, starting off with an init.ora requesting zero db_block_buffers, I correct it as follows, connected as SYS AS SYSDBA:

SQL> create spfile from pfile;
File created.
SQL> startup force
ORACLE instance started.

Total System Global Area 83886080 bytes

Fixed Size                   787548 bytes
Variable Size              82836388 bytes
Database Buffers                  0 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.

SQL> alter system reset db_block_buffers scope=spfile sid='*';

(Removes db_block_buffers from the spfile)

SQL> startup force
ORACLE instance started.

Total System Global Area 130023424 bytes

Fixed Size                   787788 bytes
Variable Size              78641844 bytes
Database Buffers           50331648 bytes
Redo Buffers                 262144 bytes

Database mounted.
Database opened.

SQL> show parameter db_block_buffers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_block_buffers                     integer     0

SQL> show parameters db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_cache_size                        big integer 48M

(48M happens to be the default).

SQL> alter system set db_cache_size=16M; System altered.

SQL> show sga

Total System Global Area 130023424 bytes

Fixed Size                   787788 bytes
Variable Size             112196276 bytes
Database Buffers           16777216 bytes
Redo Buffers                 262144 bytes

...and now I have 16M.

Regards
HJR "Alvaro Fuentes" <alvarof2_at_hotmail.com> wrote in message news:ca55ob$i14$1_at_ausnews.austin.ibm.com...
>
> Fellow Oracle Users:
>
>
> I am running Oracle 10g Server on AIX 5.2
>
> When I try to start my instance, it comes with:
>
>
> SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jun 8 14:43:29 2004
>
> Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
> SQL> Connected to an idle instance.
> SQL> ORACLE instance started.
>
> Total System Global Area 2550136832 bytes
> Fixed Size 1328064 bytes
> Variable Size 2523642944 bytes
> Database Buffers 0 bytes
> Redo Buffers 25165824 bytes
> Database mounted.
> Database opened.
> SQL> Disconnected from Oracle Database 10g Release 10.1.0.2.0 - 64bit
> Production
>
>
> What parameter in my pfile should I set to allocate
> Database_buffers?
>
>
> Thanks in advance,
>
> A. Fuentes
> 512-297-9937
>
>
>
Received on Tue Jun 08 2004 - 16:17:22 CDT