Today, I got this message in my alert.log file:
Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 709 MBs bigger than current size.
When I look at the datafile sizes and compare them with the buffer cache size, it shows:
SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo; NAME MB -------------------------------------------------- ---------- Fixed SGA Size 2,80265045 Redo Buffers 13,1953125 Buffer Cache Size 3296 In-Memory Area Size 2048 Shared Pool Size 736 Large Pool Size 32 Java Pool Size 16 Streams Pool Size 0 Shared IO Pool Size 208 Data Transfer Cache Size 0 Granule Size 16 Maximum SGA Size 6144 Startup overhead in Shared Pool 181,258133 Free SGA Memory Available 0 14 rows selected. SYS@cloudcdb > select sum(bytes)/1024/1024 as mb from v$datafile; MB ---------- 3675
It is true, the database doesn’t fit completely into the buffer cache, missing roughly that amount of space mentioned. There is no such parameter as DEFAULT_CACHE_SIZE, though.
What we have instead is DB_CACHE_SIZE. In order to fix that issue, I was using this initialization parameter file to create a new spfile from:
[oracle@uhesse-service2 dbs]$ cat initCLOUDCDB.ora
*.audit_file_dest='/u02/app/oracle/admin/CLOUDCDB/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u02/app/oracle/oradata/CLOUDCDB/control01.ctl','/u03/app/oracle/fra/CLOUDCDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CLOUDCDB'
*.db_recovery_file_dest='/u03/app/oracle/fra'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLOUDCDBXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.sga_target=6g
*.pga_aggregate_target=2g
*.inmemory_size=1g
*.db_cache_size=4g
That reduced the size of the In-Memory Column Store to make room for the buffer cache. Now the database fits nicely into the buffer cache again:
SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo;
NAME MB
-------------------------------------------------- ----------
Fixed SGA Size 2,80265045
Redo Buffers 13,1953125
Buffer Cache Size 4256
In-Memory Area Size 1024
Shared Pool Size 800
Large Pool Size 32
Java Pool Size 16
Streams Pool Size 0
Shared IO Pool Size 0
Data Transfer Cache Size 0
Granule Size 16
Maximum SGA Size 6144
Startup overhead in Shared Pool 181,290176
Free SGA Memory Available 0
14 rows selected.
Accordingly the message in the alert.log now reads
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
Don’t get me wrong: I’m not arguing here against the In-Memory Option or in favor of Full Database Caching. Or whether it makes sense to use any of them or both. This post is just about clarifying the strange message in the alert.log that may confuse people.
And by the way, my demo database is running in the Oracle Cloud 🙂
Pickleball spielen 