Oracle 12c FULL Database Caching – part 1

With the introduction of Oracle 12c, 12.1.0.2 a few new features are introduced.
One of those features is FULL Database Caching. No additional licensing is required when using this feature

This feature is available in two flavours.
– Database Caching (when possible)
– Database Caching with force option.

In this blog we look at the database caching with force option.

Lets start by executing the statement, which will result in an error when the database is not running in mount mode, which means downtime is required to use the FORCE option.

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

After mounting the database and execute again we can check if  database caching is forced.

SQL> alter database force full database caching;


SQL> select force_full_db_caching from v$database;


FOR
---
YES

Now what will happen when the Buffer_cache is to small to host the total DB size into memory?
Lets change the SGA size and validate.

SQL> alter system set sga_max_size=500M scope=spfile;
System altered.


SQL> alter system set sga_target=500M scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 268437776 bytes
Database Buffers 247463936 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.

As you can see the database is still opened with success. Lets check the alert.log

.......

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Buffer Cache Force Full DB Caching mode on when DB does not fit in cache. Turning off Force Full DB Caching advisable
.......

According to the alert.log database is not using Force FULL DB caching mode anymore.
But checking the force_full_db_caching column still displays the value of YES, which in this case is misleading, a search on error or warning
will also not result in find.

SQL> select force_full_db_caching from v$database;


FOR
---
YES

Set the proper memory settings again:

SQL> alter system set sga_max_size=2500M scope=spfile;
System altered.


SQL> alter system set sga_target=2500M scope=spfile;
System altered.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size 2927864 bytes
Variable Size 687866632 bytes
Database Buffers 1929379840 bytes
Redo Buffers 13848576 bytes
Database mounted.

Disable force full database caching just execute:

SQL> alter database no force full database caching;
Database altered.


SQL> alter database open;
Database altered.

SQL> select force_full_db_caching from v$database;
FOR
---
NO

Conclusion: Using force database mode is nice, but you need to validate yourself if it still fit into the memory allocated. Although the demo
above we change the sga_target, it is all about the size of the buffer_cache.

Read the next part when not using the force option, check out this blog.

16 oktober 2015|12c, Multitenancy, rdbms|

Leave A Comment