I knew that failing to have a db_Nk_cache_size setting for your database could cause a statement to crash when it tried to address an object (or tablespace) using a non-standard block size, reporting errors like:
ORA-29339: tablespace block size 16384 does not match configured block sizes”
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K
Here’s an interesting variation on the theme, reported in a note on the OTN database forum. Note particularly the ORA-603 and ORA-604 that wrap the ORA-379; and that the user states that the problem cache is the standard block size for the database. Unfortunately we never saw a resolution to this thread – perhaps it was simply a case of a cache that was too small when the database got very busy.
Footnote: a database can fail to open if it needs to do recovery in a tablespace for which there is no buffer set. Of course this is only likely to happen if you’re running with an init.ora file and have created a non-standard cache with ‘alter system’ calls while the database was previously up. Here’s an extract from an alert log showing the type of report you get:
Fri May 20 17:58:38 2011 ALTER DATABASE OPEN Beginning crash recovery of 1 threads parallel recovery started with 2 processes Started redo scan Completed redo scan 374 redo blocks read, 98 data blocks need recovery Fri May 20 17:58:40 2011 Slave exiting with ORA-379 exception Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_p000_2056.trc: ORA-00379: no free buffers available in buffer pool for block size 16K Aborting crash recovery due to slave death, attempting serial crash recovery Beginning crash recovery of 1 threads Started redo scan Completed redo scan 374 redo blocks read, 98 data blocks need recovery Aborting crash recovery due to error 379 Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_ora_3536.trc: ORA-00379: no free buffers available in buffer pool for block size 16K ORA-379 signalled during: ALTER DATABASE OPEN...