Shulou(Shulou.com)06/01 Report--

AWR can not automatically generate snapshot solution

Problem description: after the RAC archive is full, AWR cannot automatically generate a snapshot. The principle should be caused by MMON HANG, so the problem was solved after trying to restart MMON. ; MMON HANG solution

1) restart the refresh of mmon

Alter system set "_ swrf_mmon_flush" = false

Alter system set "_ swrf_mmon_flush" = true; 2) or, find the mmon process and kill it, let the database restart a new mmon process automatically, or restart the next instance.

Ps-ef | grep mmon

Kill-9 xxxxxx Manageability Monitor Processes (MMON and MMNL)

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects. The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full. See Also:

"Automatic Workload Repository (AWR)" and "Active Session History (ASH)"

AWR and ASH background running processes

AWR = = > MMON

ASH = > MMNL MMON is responsible for performing tasks related to AWR. It includes collecting database statistics, collecting AWR snapshots, starting various automatic maintenance jobs JOB, and generating alarm information exceeding the threshold.

MMNL is responsible for performing tasks related to ASH.

# previous records of MOS expert consultation

1. Please provide the belowscript output on failed instance:

Conn / as sysdbaset pages 1000set num 20

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Alter session set nls_timestamp_format='yyyy-mm-dd hh34:mi:ss.ff'

Alter session set nls_timestamp_tz_format='yyyy-mm-dd hh34:mi:ss.ff tzh:tzm'

Set mark html onspool info.html

Select * from (select T. over, rank () over (partition by dbid, instance_number order by begin_interval_time desc) r from dba_hist_snapshot t) where r

< 100; select * from wrm$_wr_control where dbid = (select dbid from v$database); select * from gv$database; select * from gv$thread;show parameter statistics_level show parameter control_management_pack_accessspool off exit 2. Provide the "awrinfo" script output on instance 3: SQL>

Conn / as sysdbaSQL > @? / rdbms/admin/awrinfo.sql

3. Enable SQL tracing on MMON slaves to collect more information. (do this in all instances)

Begin

Dbms_monitor.serv_mod_act_trace_enable (service_name = > 'SYS$BACKGROUND'

, module_name = > 'MMON_SLAVE'

, action_name = > 'Auto-Flush Slave Action'

, waits = > true

, binds = > true)

End

/

Begin

Dbms_monitor.serv_mod_act_trace_enable (service_name = > 'SYS$BACKGROUND'

Module_name = > 'MMON_SLAVE'

, action_name = > 'Remote-Flush Slave Action'

Waits = > true,binds = > true)

End

/

Wait for 2 snapshots time then upload the MMON (xxxx_mmon_xxxx.trc) and the below mmon slave trace files.

Egrep-l "Auto-Flush Slave Action | Remote-Flush Slave Action" * .trc | xargs tar-cvf-| gzip > trace.tar.gzls-l trace.tar.gzTo disable the tracing later

Use "dbms_monitor.serv_mod_act_trace_disable" instead. For example:

Begin

Dbms_monitor.serv_mod_act_trace_disable (service_name = > 'SYS$BACKGROUND'

Module_name = > 'MMON_SLAVE'

Action_name = > 'Auto-Flush Slave Action')

End

/

Begin

Dbms_monitor.serv_mod_act_trace_disable (service_name = > 'SYS$BACKGROUND'

Module_name = > 'MMON_SLAVE'

Action_name = > 'Remote-Flush Slave Action')

End

/

#

All rights reserved, the article is allowed to be reprinted, but the source address must be indicated by link, otherwise legal liability will be investigated! [QQ Exchange Group: 53993419]

QQ:14040928 E-mail:dbadoudou@163.com

Link to this article: http://blog.itpub.net/26442936/viewspace-2125130/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.