RE: Why alter table move can repair the problem awr not generated ?
Date: Mon, 6 Jan 2014 11:53:03 +0000
Message-ID: <EC65ECF8123FEE4D8FC5B212637C304001664D0972DE_at_EXCH1.morrisonsplc.co.uk>
Without going into too much detail on a non-specific question my knowledge (and I keep meaning to blog about this) is that a scenario as fllows might have happened
- There is an automated job which runs each night by default to manage the AWR and optimizer stats - this is run from the MMON process
- That job - dbms_stats.purge_stats utility has 5 minutes to do its work and then it stops wherever it has got to
- If the SYSAUX table is full it cannot purge data and stops without giving providing alerting. This might happen for consecutive 2 days lets say on a database that does not have alerting on
- The SYSAUX tablespace then has space added
- The next time dbms_stats.purge_stats runs it starts to move the outstanding snapshots around and has too many to do in the time window provided therefore it stops
- This process then continues every day and continues to fail
Generally the management of both AWR data and historic optimizer stats is not well handled within Oracle up to 12c. I have noticed that there is a fix in 11.2.0.4 and in 12.1 that manages the purging much better and a patch 14373728 is available to address the issue.
My best advice however is to copy the AWR history out elsewhere using the pre-supplied package SYS.DBMS_SWRF_INTERNAL and then drop the AWR repository and re-create.
John
www.jhdba.wordpress.com
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: 06 January 2014 06:41
To: luolee.me_at_gmail.com
Cc: ORACLE-L
Subject: Re: Why alter table move can repair the problem awr not generated ?
We'll need the error encountered by the AWR snapshot job to determine a suitable answer. (which may of course be that it was a coincidence). You can find this (if my memory is good) in dba_scheduler_job_run_details. On Jan 6, 2014 1:59 AM, "LuoLee.me" <luolee.me_at_gmail.com<mailto:luolee.me_at_gmail.com>> wrote: Dear list,
I meet a problem that awr snapshot didn't generate correctly last week. I attempt to truncate the table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY, but it didn't take effect. The other way, I use the "alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move" in restricted mode, it became effect. I know the way that truncate the table could do it sometimes, but this time, it fails.
We all know that "alter table move" could lower the highest watermark, but why it can repair the awr not generated problem, and why truncate could not here.
Anyone know the truth ?
Thanks in advance.
Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.
If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email. If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.
This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.
Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop
Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility, and it is the responsibility of recipients to carry out their own virus checks.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 06 2014 - 12:53:03 CET