sga_target設定大於100G遇到的一個bug ORA-00064: object is too large，sgatarget

sga_target設定大於100G遇到的一個bug ORA-00064: object is too large，sgatarget

主機記憶體256G，sga_target準備設定為160G，DBCA建立資料庫時一直報錯ORA-00064: object is too large to allocate on this O/S (1,15429280)

直接使用DBCA預設參數把庫建好後，再次使用命令來修改，報錯依舊。經查，參考文檔Database startup can fail with ORA-00064 Errors with huge sga_target of over 40Gig (文檔 ID 886312.1)，也就是與SGA Granule Size相關，設定_ksmg_granule_size=33554432解決此問題。

文檔部分內容：

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
<= 1GB	4MB	4MB	4MB
1GB - 4GB	16MB	16MB	16MB
4GB - 8GB	16MB	64MB	16MB
8GB - 16GB	16MB	64MB	32MB
16GB - 32GB	16MB	256MB	64MB
32GB - 64GB	16MB	256MB	128MB
64GB - 128GB	16MB	512MB	256MB
128GB - 256GB	16MB	512MB	512MB
> 256GB	16MB	512MB	512MB

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 32MB). 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.