SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database a while ago:

New SPFILE parameters in Oracle Database

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

  • You must set the SGA_MIN_SIZE value to a value that meets these requirements:
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
    • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
    • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

    When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4


New SPFILE parameters in Oracle Database with July 2016 (and newer) PSU/BP

New Parameters in Oracle Database with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database manual:

The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory on UNIX platforms.

The default value is FALSE, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (, database shared memory was created with owner and group access.

When this parameter is set to TRUE, database shared memory is created with owner and group access. This behavior grants permissions to DBAs to manage shared memory outside the database, but also allows DBAs to read and write to shared memory, which may not be desirable for certain installations.

So there’s a tiny correction required:
It should say “prior to Oracle Database July 2016 PSU/BP”.

The ENCRYPT_NEW_TABLESPACES parameter came in for the cloud deployments and is documented in the description
of the July Proactive BP
: 21281607E Transparently encrypt tablespace at creation in Cloud (adds “encrypt_new_tablespaces”)


Why does ALLOW_GROUP_ACCESS_TO_SGA appear in Oracle Database

Simple reasons: it will make Oracle Database more secure. Default for this parameter is FALSE – which actually changes behavior but may not affect you at first sight. And that’s why I blog about it.

You will recognize that the Oracle executable runs now with permission “600” – whereas it was “640” before. See my example of an database with the January 2017 BP in place:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 23298063   oracle     600        2932736    76                      
0x00000000 23330832   oracle     600        1040187392 38                      
0x00000000 23363601   oracle     600        5455872    38                      
0xc8969114 23396370   oracle     600        20480      38       

whereas my database runs with different permissions:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status     
0x00000000 22872084   oracle     640        12582912   21                      
0x00000000 22904853   oracle     640        721420288  21                      
0xd41b1c5c 22937622   oracle     640        2097152    21 

Does this effect the connection of your applications to the database?
No, of course not.

It has only an effect if you try to access the SGA from the OS level, i.e. attaching to the shared memory segment. In the old behavior an OS user being in the same group can attach and read from the SGA. With the new “600” protection only the OWNER can attach to it – and read out the SGA.

This is the standard behavior in Oracle Database and onward. And it has been backported to the July 2016 PSU and Proactive Bundle Patches which are cumulative, i.e it is in all following PSUs and BPs included as well.


New SPFILE parameters in Oracle Database

This is the 1st posting in my series about init.ora/SPFILE parameters in Oracle Database

Oracle Database is available now in the Oracle Cloud.

And this is the list of 46 new init.ora/spfile parameters compared to Oracle Database – including the links (where possible) to the Oracle Database 12.2 Reference documentation.




allow_global_dblinks LDAP lookup for DBLINKS
allow_group_access_to_sga Allow read access for SGA to users of Oracle owner group
approx_for_aggregation Replace exact aggregation with approximate aggregation
approx_for_count_distinct Replace count distinct with approx_count_distinct
approx_for_percentile Replace percentile_* with approx_percentile
asm_io_processes number of I/O processes per domain in the ASM IOSERVER
autotask_max_active_pdbs Setting for Autotask Maximum Maintenance PDBs
awr_pdb_autoflush_enabled Enable/Disable AWR automatic PDB flushing
cdb_cluster [undocumented if TRUE startup in CDB Cluster mode
cdb_cluster_name [undocumented] CDB Cluster name
clonedb_dir CloneDB Directory
containers_parallel_degree Parallel degree for a CONTAINERS() query
cursor_invalidation default for DDL cursor invalidation semantics
data_guard_sync_latency Data Guard SYNC latency
data_transfer_cache_size Size of data transfer cache
default_sharing Default sharing clause
disable_pdb_feature [undocumented] Disable features
enable_automatic_maintenance_pdb Enable/Disable Automated Maintenance for Non-Root PDB
enable_dnfs_dispatcher Enable DNFS Dispatcher
enabled_PDBs_on_standby List of Enabled PDB patterns
encrypt_new_tablespaces whether to encrypt newly created tablespaces
exafusion_enabled Enable Exafusion
external_keystore_credential_location external keystore credential location
inmemory_adg_enabled Enable IMC support on ADG
inmemory_expressions_usage Controls which In-Memory Expressions are populated
inmemory_virtual_columns Controls which user-defined virtual columns are stored
instance_abort_delay_time time to delay an internal initiated abort (in seconds)
instance_mode indicates whether the instance read-only or read-write
or read-mostly
long_module_action Use longer module and action
max_datapump_jobs_per_pdb maximum number of concurrent Data Pump Jobs per PDB
max_idle_time maximum session idle time in minutes
max_iops MAX IO per second
max_mbps MAX MB per second
max_pdbs max number of pdbs allowed in CDB or Application ROOT
ofs_threads Number of OFS threads
one_step_plugin_for_pdb_with_tde [undocumented] Facilitate one-step plugin for PDB with TDE encrypted
optimizer_adaptive_plans controls all types of adaptive plans
optimizer_adaptive_statistics controls all types of adaptive statistics
outbound_dblink_protocols Outbound DBLINK Protocols allowed
remote_recovery_file_dest default remote database recovery file location for
resource_manage_goldengate goldengate resource manager enabled
sga_min_size Minimum, guaranteed size of PDB’s SGA
shrd_dupl_table_refresh_rate duplicated table refresh rate (in seconds)
standby_db_preserve_states Preserve state cross standby role transition
target_pdbs [undocumented] Parameter is a hint to adjust certain attributes of the
uniform_log_timestamp_format use uniform timestamp formats vs pre-12.2 formats