Oracle: Fixing RMAN‑11003 with ORA‑00379 “no free buffers available”

 

Fixing RMAN‑11003 with ORA‑00379 “no free buffers available”

This error means RMAN cannot allocate the required block buffers in the default buffer pool during the ALTER DATABASE RECOVER LOGFILE command, usually because the DB_16K_CACHE_SIZE (or other block buffer sizes) is set too low. databasewithsanga.blogspot.com

Why it happens

  • ORA‑00379 indicates the database has no free buffers in the default buffer pool for the requested block size (16K here) databasewithsanga.blogspot.com.

  • RMAN needs these buffers to read and write recovery logfiles. If the parameter is zero or too small, the operation fails databasewithsanga.blogspot.com.

  • This can occur after a restore/recovery in a new environment, or if parameters were reset.

How to fix it

You must allocate memory for the block buffers before retrying the recovery.

Step‑by‑step (from Oracle docs and community guidance) databasewithsanga.blogspot.com

  1. Log in to the database as a privileged user (e.g., sys).

  2. Check the current block buffer size:

    Sql
    SQL> show parameter DB_16K_CACHE_SIZE;

    If it shows 0 or a very small value, that’s the cause.

  3. Set a sufficient size (e.g., 10M or more, depending on available memory):

    Sql
    SQL> alter system set DB_16K_CACHE_SIZE=10M scope=spfile;

    You can also set DB_8K_CACHE_SIZE and DB_32K_CACHE_SIZE if needed.

  4. Restart the database:

    Sql
    SQL> shutdown immediate;
    SQL> startup mount;

  5. Verify the change:

    Sql
    SQL> show parameter DB_16K_CACHE_SIZE;

  6. Retry the RMAN recovery:

    Bash
    rman target db








oracle-psfststdb602.tmw.com:fscnv1:/usr/opt/app/oracle/admin/fscnv1/scripts>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 6 17:26:14 2026
Version 19.32.0.0.0

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8657043456 bytes
Database Buffers         1912602624 bytes
Redo Buffers               19869696 bytes
SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
0
SQL> alter system set DB_16K_CACHE_SIZE=10M scope=spfile;

System altered.

SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
0
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8589934592 bytes
Database Buffers         1979711488 bytes
Redo Buffers               19869696 bytes
SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
192M
SQL> alter system set DB_16K_CACHE_SIZE=10032M scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 18528M
ORA-01078: failure in processing system parameters
SQL> startup nomount pfile=/usr/local/oracle/fscnv1init.ora
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8657043456 bytes
Database Buffers         1912602624 bytes
Redo Buffers               19869696 bytes
SQL> alter system set DB_16K_CACHE_SIZE=1032M scope=both;
alter system set DB_16K_CACHE_SIZE=1032M scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use


SQL> alter system set DB_16K_CACHE_SIZE=1032M scope=pfile;
alter system set DB_16K_CACHE_SIZE=1032M scope=pfile
                                               *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter system set DB_16K_CACHE_SIZE=1032M;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount pfile=/usr/local/oracle/fscnv1init.ora
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8657043456 bytes
Database Buffers         1912602624 bytes
Redo Buffers               19869696 bytes
SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
0
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount pfile=/usr/local/oracle/fscnv1init.ora
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8589934592 bytes
Database Buffers         1979711488 bytes
Redo Buffers               19869696 bytes
SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
1056M
SQL> create spfile='/usr/local/oracle/fscnv1spfile.ora' from pfile='/usr/local/oracle/fscnv1init.ora';

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0603E+10 bytes
Fixed Size                 13683736 bytes
Variable Size            8589934592 bytes
Database Buffers         1979711488 bytes
Redo Buffers               19869696 bytes
SQL> show parameter DB_16K_CACHE_SIZE;

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_16k_cache_size                    big integer
1056M
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Comments

Post a Comment

Popular posts from this blog

Postgres: To list table owners

SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY tableowner, schemaname, tablename; ---- SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname <> tableowner AND  schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY tableowner, schemaname, tablename;
Read more

PeopleSoft: Clean Up PUM

  Manual Cleanup steps for WINDOWS Before carrying out the advanced steps in this section: Run the command:  psft-dpk-setup.bat --cleanup. If you deployed the environment using the use_alternate_puppet_install option, use this command:   psft-dpk-setup.bat --cleanup --use_alternate_puppet_install If the script displays a FAILED message, run it again. If it succeeds, check the BASE_DIR folders to be sure everything has been deleted. If the BASE_DIR folders are not clear, or if a subsequent deployment is not successful, carry out the steps below. For the  ADVANCED CLEAN UP PROCESS  on Microsoft Windows, please see the following steps. NOTE: These should be performed by someone familiar with modifying the Microsoft Windows registry. Depending upon where the cleanup process failed, some of the items mentioned in these steps may have already been removed. The user should remove whatever remains in this order: Start the Microsoft Windows Services utilit...
Read more

Postgres: To check the locale / Character set settings

  To check the locale settings in PostgreSQL, you can use the following SQL query: SHOW lc_collate; SHOW lc_ctype; SHOW lc_messages; SHOW lc_monetary; SHOW lc_numeric; SHOW lc_time; Explanation: lc_collate : Defines the collation order (sorting rules) for strings. lc_ctype : Specifies the character classification (e.g., upper/lower case). lc_messages : Determines the language for system messages. lc_monetary : Sets the locale for monetary formatting. lc_numeric : Configures the locale for numeric formatting. lc_time : Specifies the locale for date and time formatting. You can run these commands in your PostgreSQL client (e.g., psql ) to view the current locale settings. If you need to change them, you can configure them in the postgresql.conf file or during database initialization.
Read more