ANBOB™

DBCA 图形建库内存分配界面 未选用AMM，而是指定了SGA(ASMM)和PGA的大小，随后重启库发现 没有使用Hugepage,hugepage配置没有问题, 如果Hugepage偏小， 10G SGA全部使用Hugepage以外的空间， 11g 起默认配置系统参数use_large_pages =true， SGA会尽可能使用hugepage， hugepage不足时再把剩余部分分配新空间，从alert 日志中会记录Hagepage的相关信息如下， (如果使用了AMM 情况下 alert不会记录hugepage信息）

************************ Large Pages Information *******************
Per process system memlock (soft) limit = 30 GB

Total Shared Global Region in Large Pages = 2800 MB (91%)

Large Pages used by this instance: 1400 (2800 MB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 1400 (2800 MB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total System Global Area size is 3074 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by
 at least 137 (page size 2048 KB, total size 274 MB) system wide to
  get 100% of the System Global Area allocated with large pages
********************************************************************

接着描述前面的问题，DBCA时未使用AMM,并且Alert日志也没有hugepage使用信息，ipcs 查看共享内存段, 发现并未使用system v-style shared memory， 而是使用了Posix-Style shared memory management, 进数据库查看

SQL> show parameter mem
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga     boolean     FALSE
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3824M
memory_target                        big integer 0
shared_memory_address                integer     0

SQL> alter system set memory_max_target=0 scope=spfile;
System altered.

shutdown immediate
startup

SQL> show parameter target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 3824M
memory_target                        big integer 0
parallel_servers_target              integer     128
pga_aggregate_target                 big integer 798M
sga_target                           big integer 3008M

NOTE:
memory_max_target并未修改

SQL> create pfile from spfile;

vi $ORACLE_HOME/dbs/init<instance_name>.ora 

...
*.memory_max_target=0
*.memory_target=0
...

SQL> alter system set sga_target=0;

SQL> alter system set shared_pool_size=800m;
SQL> alter system set db_cache_size=2g;
SQL> alter system set sga_max_size=3g scope=spfile;

SQL> shutdown immediate

SQL> startup nomount
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 3221225472 cannot be set to more than MEMORY_MAX_TARGET 0.

Note:
终于出错了, 有错误就有解决的方向，从实例系统参数中删除了MEMORY_MAX_TARGET=0 、MEMORY_TARGET=0 的记录最终解决, refer  MOS ID 1397761.1

Cause
The problem is caused by the MEMORY_MAX_TARGET parameter explicitly being set to 0. In case AMM should not be used, MEMORY_MAX_TARGET should not be set at all.

Solution

create a PFILE from the SPFILE being used and remove the MEMORY_MAX_TARGET=0 and MEMORY_TARGET=0 lines. After that, use the modified PFILE to create a new SPFILE and start the instance with this new setup.

SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

打赏

微信扫一扫，打赏作者吧～