A bug ORA-00064 encountered when sga_target is set to be greater than GB: object is too large, sgatarget

A bug ORA-00064 encountered when sga_target is set to be greater than GB: object is too large, sgatarget

Host memory 256 GB, sga_target ready to be set to 160 GB, DBCA always reports an error ORA-00064 when creating a database: object is too large to allocate on this O/S)

Directly use the default DBCA parameter to create the database and use the command again to modify the database. The error is still returned. After investigation, refer to the document Database startup can fail with ORA-00064 Errors with huge sga_target of over 40Gig (Document ID 886312.1), which is related to SGA Granule Size, set _ ksmg_granule_size = 33554432 to solve this problem.

Part of the document:

CAUSEThe cause of this problem has been identified in Bug: 5051962, which has been closed as not-a-bug. it is caused by the granule size needed for the new SGA size to be different than the one currently in use by the instance at the time the alter system set SGA_TARGET command is run. SOLUTIONThis is expected behaviour: the granule size is set based on the size of the SGA. if you set a SGA parameter (such as SGA_TARGET) to a value which exceeds the value of the granule size that was calulcated at instance startup, then you must set the _ ksmg_granule_size instance parameter as well to choose a larger granule size. as this parameter is not dynamically changeable, an instance restart is required.

The following table, shows granule sizes for specific SGA sizes and releases of Oracle:

SGA size	10g granule size	11g granule size	11g granule size With fix 8813366 installed
<= 1 GB	4 MB	4 MB	4 MB
1 GB-4 GB	16 MB	16 MB	16 MB
4 GB-8 GB	16 MB	64 MB	16 MB
8 GB-16 GB	16 MB	64 MB	32 MB
16 GB-32 GB	16 MB	256 MB	64 MB
32 GB-64 GB	16 MB	256 MB	128 MB
64 GB-128 GB	16 MB	512 MB	256 MB
128 Gbit/s-256 Gbit/s	16 MB	512 MB	512 MB
> 256 GB	16 MB	512 MB	512 MB

See also document: 947152.1 for details on the granule size and its impact.

The following SQL statement can be used to check the actual granule size in use by the instance:
SQL> select bytes from v$sgainfo where name like 'Granule Size';
So in order to implement the solution for the issue at hand, either:

1. Set SGA_TARGET to a value less than or equal to the previous one, or:
2. Set _ ksmg_granule_size to a higher value (in this case the value was set to 32 MB). When using a text parameter file, add the following parameter:
   _ksmg_granule_size=33554432
   When using a server parameter file, issue:
   alter system set "_ksmg_granule_size"=33554432 scope=spfile;
   In either case, restart the instance for the changes to take effect.