Thursday, 10 August 2017

Full Database Caching Mode in Oracle Database 12cR1

Under normal running the Oracle database decides what data to cache in the buffer cache. If there is not enough room, data can be aged out of the cache. In addition, Oracle may choose to bypass the buffer cache for some operations to prevent useful information being artificially aged out by a large read operation.

Oracle 12cR1 (12.1.0.2) introduces the concept of full database caching. If Oracle determines that the buffer cache is big enough to hold the entire database it will cache all blocks. In addition, full database caching mode can be forced.

  • Enable Force Full Database Caching Mode


Rather than letting Oracle determine if full database caching is appropriate, you can force the decision using the ALTER DATABASE command.

If the database is open in this or any other instance you get an error message.

SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;
ALTER DATABASE FORCE FULL DATABASE CACHING
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

SQL>

To force full database caching, you will need to do the following.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FORCE FULL DATABASE CACHING;
ALTER DATABASE OPEN;

After that, the change will be visible in the V$DATABASE view.

SELECT force_full_db_caching FROM v$database;

FOR
---
YES

SQL>

  • Disable Force Full Database Caching Mode


Disabling force full database caching mode follows a similar format.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NO FORCE FULL DATABASE CACHING;
ALTER DATABASE OPEN;

The change is reflected in the V$DATABASE view.

SELECT force_full_db_caching FROM v$database;

FOR
---
NO

SQL>

  • Caveats


  1. The COMPATIBLE parameter must be set to 12.0.0 or higher.
  2. If you are using AMM (MEMORY_TARGET) or ASMM (SGA_TARGET) it is possible the buffer cache size will alter, making the cache too small to hold the entire database. Either size the memory parameters appropriately, or better still set the minimum size of the buffer cache by setting the DB_CACH_SIZE parameter to an appropriately large value.
  3. There is no pre-emptive loading of objects. Instead, objects are cached as they are accessed.
  4. LOBs defined as NOCACHE can be cached when force full database cache mode is enabled. Under normal running they are not.
  5. Enabling force full database cache mode applies to the CDB and all PDBs when using the multitenant option.
  6. If you need to recover your controlfile, you should check that force full database cache mode is still enabled.