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.
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>
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>
COMPATIBLE
parameter must be set to 12.0.0 or higher.DB_CACH_SIZE
parameter to an appropriately large value. For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/12c/full-database-caching-mode-12cr1