No free buffers available and MRP terminated
MRP termination with no free buffers may not occur in normal database operations but in my case happened after the fresh restore of the database(standby). After performing restore i able to start MRP but unfortunately the background process is killed and errors are below.
Tue Aug 05 04:11:22 2014
Slave exiting with ORA-379 exception
Errors in file /u01/app/oracle/diag/rdbms/CRSTAN/crmtrn/trace/crmtrn_pr1f_68115.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K
Tue Aug 05 04:11:22 2014
Slave exiting with ORA-379 exception
Errors in file /u01/app/oracle/diag/rdbms/CRSTAN/crmtrn/trace/crmtrn_pr22_68221.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K
Recovery Slave PR1F previously exited with exception 379
Errors with log /u01/app/oracle/oradata/CRSTAN/archivelog/2_574_853968104.arc
MRP0: Background Media Recovery terminated with error 448
Errors in file /u01/app/oracle/diag/rdbms/CRSTAN/crmtrn/trace/crmtrn_pr00_67902.trc:
ORA-00448: normal completion of background process
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Also reviewed the trace file to locate more possible reason for recovery interruption.
Uninitialized buffer pool scanned.
In set=291, bsz=2048, wsi=0, poolid=4, pool_name=DEFAULT
Buffers originally scanned and found pinned = [0 0], evict=0, aged=0
Buffers rescanned on auxiliary list = 0
Buffers rescanned on main list = 0
Buffers in working set 0 (0), hbufs=0, hbmax=0
Buffers on repl list main=0, aux=0
Slave exiting with ORA-379 exception
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 2K
KCBR: Number of read descriptors = 591
KCBR: Buffer cache full = 1 times
KCBR: Influx buffers flushed = 2 times
From the above alert and trace files, it clears that database(source/primary) have tablespace with blocksize 2k apart from the default block size 8k. So let’s check what are the different block sizes is database using and which tablespaces.
Primary Database
SQL> select tablespace_name,block_size from dba_tablespaces where block_size!=8192;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
OLTP1_2K 2048
OLTP_LOB_32K 32768
SQL> select name,value from v$parameter where name in ('db_block_size','db_2k_cache_size','db_32k_cache_size');
NAME VALUE
-------------------- ----------
db_block_size 8192
db_2k_cache_size 67108864
db_32k_cache_size 67108864
From above output is clear that OLTP1_2K tablesapce is with 2k block size and OLTP_LOB_32k is with 32k. As a basic rule to have multiple block size tablespaces in database we must also have to configure the parameters db_nk_cache_size in order to create tablespaces. DB_nk_CACHE_SIZE specifies the size of the cache for the bueffers and probably that should be issue on standby, So beeter to check once what are the parameter values have configured.
SQL> select name,value from v$parameter where name in ('db_block_size','db_2k_cache_size','db_32k_cache_size');
NAME VALUE
-------------------- ----------
db_block_size 8192
db_2k_cache_size 0
db_32k_cache_size 335544320
From Standby, the value for db_2k_cache_size is NULL and hence there is no allocation to use the non-default block size tablespaces. Now you can use same size allocated in primary and update the PFILE/SPFILE. DB_nk_CACHE_SIZE parameter is dynamic so no bounce of instance is required neither.
SQL> ALTER SYSTEM SET db_2k_cache_size=67108864 SCOPE=BOTH;
Now, media recovery was able to continue working.
Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 2 680 680 0 3 679 679 0
Conclusion: This errors may happen either with non-standard blocksize tablespace is added on primary and not updated on standby after which media recovery on standby fails with ORA-00379 (or) wrong specifications in standby database while performing refresh. So ensure you have all parameters configured properly even in standby as well.
— Happy Reading —
