Month: April 2012

SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled – Oracle 11.2

Published on by Advait2 Comments

Its been observed that after upgrading the database to 11gR2 (11.2.0.2 onwards) SGA components are getting resized even when automatic memory management (MEMORY_TARGET=0) and automatic shared memory management (SGA_TARGET=0) is disabled. Resize are happening to an extent that the component sizes are shrinking below the minimum size set by DBAs.

This typically appears as growth in the __SHARED_POOL_SIZE value and a reduction in the __DB_CACHE_SIZE value being used in the instance, such that __DB_CACHE_SIZE may be shrunk below the DB_CACHE_SIZE value specified in the init.ora/spfile.

(In case if you are not aware __<param name> are the dynamic value of the component after database start up)

Checking V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS shows an IMMEDIATE re-size having occurred

SQL>select COMPONENT, PARAMETER, INITIAL_SIZE/1024/1024 "INITIAL_SIZE",FINAL_SIZE/1024/1024 "FINAL_SIZE", STATUS from v$SGA_RESIZE_OPS where component in ('DEFAULT buffer cache','shared pool') order by component, start_time;

COMPONENT PARAMETER INITIAL_SIZE FINAL_SIZE STATUS
---------------------------------------- ------------------------------ ------------ ---------- ---------
DEFAULT buffer cache db_cache_size 3280 3264 COMPLETE
DEFAULT buffer cache db_cache_size 3280 3264 COMPLETE
DEFAULT buffer cache db_cache_size 0 3280 COMPLETE
DEFAULT buffer cache db_cache_size 3264 3248 COMPLETE
DEFAULT buffer cache db_cache_size 3248 3232 COMPLETE
DEFAULT buffer cache db_cache_size 3232 3216 COMPLETE
DEFAULT buffer cache db_cache_size 3216 3200 COMPLETE
DEFAULT buffer cache db_cache_size 3200 3184 COMPLETE
DEFAULT buffer cache db_cache_size 3184 3168 COMPLETE
shared pool shared_pool_size 0 1024 COMPLETE
shared pool shared_pool_size 1024 1040 COMPLETE

COMPONENT PARAMETER INITIAL_SIZE FINAL_SIZE STATUS
---------------------------------------- ------------------------------ ------------ ---------- ---------
shared pool shared_pool_size 1040 1056 COMPLETE
shared pool shared_pool_size 1056 1072 COMPLETE
shared pool shared_pool_size 1072 1088 COMPLETE
shared pool shared_pool_size 1088 1104 COMPLETE
shared pool shared_pool_size 1104 1120 COMPLETE
shared pool shared_pool_size 1120 1136 COMPLETE

17 rows selected.

SQL>

You might think this is a bug, but this is an expected behavior. This is done to prevent an ORA-4031 error from being raised. So if shared_pool falls short of memory, Oracle extracts memory from db_buffer_cache and allocates the same to shared_pool.

But, enhancement Bug:13340694 has been logged to provide a warning message in the alertlog when such allocations occur to provide the DBA with an indication of a memory problem, as otherwise this can result in the buffer cache being shrunk below the specified minimum value, and hence lead to performance degradation

Is it possible to disable this expected behavior ?

Yes, this feature can be disabled by setting _MEMORY_IMM_MODE_WITHOUT_AUTOSGA=false. This can be done using alter system command.


connect / as sysdba

alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;

exit

References:

1269139.1 – https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1269139.1

Hope this helps !!

Advertisement

Removing targets from Grid Control – Oracle 11g

Published on by Advait1 Comment

Many times we have to cleanup some old targets from grid control. This happens because of host getting deprecated.

Example if you have database which was running on a host and is registered in grid. Because of scaling requirement you moved the database to bigger box. In that case you have to have agent installation done on new box and you have to remove old target from the grid.

Not doing this will make your grid stale with old data.

We can clean up grid from the Grid UI. Only problem with this approach is that this is time consuming.

When we have a database running on a host, its not just 1 target that agent discovers. We have multiple targets like host, database listeners etc. Deleting each target takes time.

Easiest approach for deleting the target is from backend database. We have grid database which stores complete grid information.

We can follow below steps to remove target from backend without using grid UI

1) Stop the management agent on the target database

% <AGENT_HOME>/bin/emctl stop agent

2) Log onto grid database (also called repository database) and search for target

SQL> select target_name from mgmt_targets where target_type = ‘oracle_emd’ and target_name like ‘<search_string>’;

This shows registered agents as per search string

3) Once you identify the target name, you below command to delete the target

exec mgmt_admin.cleanup_agent(‘<target_name>’);

Check the grid UI now for this target and you will not find it.

Hope this helps !!