Oracleawr Delete History Snapshot description

During the test, it was found that a new snapshot could not be generated and the system time was October 26, but the snapshot has been generated for December 1.

The workaround at this point is to delete the existing snapshot.

Turn http://itlab.idcquan.com/Oracle/backup/947118_2.html

One. AWR Overview

A document that has been previously collated with an AWR description:

Description of Oracle AWR (Automatic Workload Repository)

http://blog.csdn.net/tianlesoftware/article/details/4682300

Starting with Oracle 10g, Oracle has launched a tool: Awr (Automatic Workload Repository), and it is recommended to use AWR instead of 9i statspack.

AWR is essentially an Oracle built-in tool that collects performance-related statistics and derives energy from those statistics to track potential problems.

The Awr snapshot is made by a Mmon background process and automatically collected once per hour from the process. In order to save space, snapshots in Oracle 10g are retained for 7 days, 11g snaps are retained for 8 days, excess is automatically deleted, and snapshot frequency and retention time can be modified by the user.

AWR uses several tables to store collected statistics, all of which are stored in the SYS mode in a specific tablespace with the new name Sysaux, and are named in the format wrm$_* and wrh$_*:

(1) Wrm$_* Class table stores metadata information (such as checked databases and captured snapshots)

(2) The Wrh$_* class holds the actual collected statistics.

H stands for "Historical Data (historical)" and M for "metadata (metadata)".

Several views with the prefix Dba_hist_ are built on these tables, which can be used to write your own performance diagnostic tools. The name of the view is directly related to the table, for example, the view dba_hist_sysmetric_summary is built on the Wrh$_sysmetric_summary table.

Two. Removal of the AWR snapshot

2.1 Why should I delete an awr snapshot?

By default, the AWR report generated by the database itself is retained for 7 days or 8 days.

Sql> Select dbid, retention from Dba_hist_wr_control;

DBID RETENTION

-------------------------------------------------------------------------------------

879543530 +00008 00:00:00.0

This is my 11g library, which is displayed for 8 days, and if it is Oracle 10g, it will be retained for 7 days.

AWR snapshots can also be imported from other databases, and here we look at:

Sql> Select dbid, retention from Dba_hist_wr_control;

DBID RETENTION

-------------------------------------------------------------------------------

877621333 +40150 00:00:00.0

879543530 +00008 00:00:00.0

If you import a snapshot from another library, it will be saved for 110 years (40150/365) after 10g, and this data will not be permanently deleted.

--awr Export Script:

Sql> @?/rdbms/admin/awrextr.sql

--awr Import Script:

Sql> @?/rdbms/admin/awrload.sql

Another important reason is that sometimes automatic snapshots are not automatically collected, and manually created snapshots can be successful, and the solution to this problem is to erase the previous snapshot.

2.2 Deleting AWR snapshot data

2.2.1 Use Dbms_workload_repository package to remove

2.2.1.1 Remove the Awr snapshot for this machine

Sql> Select dbid, retention from Dba_hist_wr_control;

DBID RETENTION

---------- --------------------

879543530 +00008 00:00:00.0

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 879543530;

MIN (snap_id) MAX (snap_id)

------------------------

161 176

Sql> exec Dbms_workload_repository.drop_snapshot_range (161, 176, 879543530)

PL/SQL proceduresuccessfully completed.

Sql> Select *from dba_hist_snapshot where dbid = 879543530;

No rows selected

2.2.1.2 deleting snapshots of other instances

Sql> Select dbid, retention Fromdba_hist_wr_control;

DBID RETENTION

---------- ---------------------------------------------------------------------

877621333 +40150 00:00:00.0

879543530 +00008 00:00:00.0

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 877621333;

MIN (snap_id) MAX (snap_id)

------------ ------------

160 192

Sql> Execdbms_workload_repository.drop_snapshot_range (160, 170, 877621333)

PL/SQL procedure successfully completed.

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 877621333;

MIN (snap_id) MAX (snap_id)

------------ ------------

171 192

Sql>

Delete succeeded.

2.2.2 Use dbms_swrf_internal package to remove

2.2.2.1 Delete other database imported AWR

Sql> Select dbid, retention Fromdba_hist_wr_control;

DBID RETENTION

-------------------------------------------------------------------------------

877621333 +40150 00:00:00.0

879543530 +00008 00:00:00.0

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 877621333;

MIN (snap_id) MAX (snap_id)

------------ ------------

160 192

sql> exec dbms_swrf_internal.unregister_database (877621333)

PL/SQL procedure successfully completed.

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 877621333;

MIN (snap_id) MAX (snap_id)

------------ ------------

Sql> Select dbid, retention Fromdba_hist_wr_control;

DBID RETENTION

-------------------------------------------------------------------------------

879543530 +00008 00:00:00.0

Sql>

Dbms_swrf_internal.unregister_database will kill all the snapshots directly, and the deletion in our previous section is different.

2.2.2.2 Delete this machine's awr

We emptied the snapshot before, so there was no result:

Sql> SELECT * from Dba_hist_snapshotwhere dbid = 879543530;

No rows selected

Sql> Executedbms_workload_repository.create_snapshot ();

PL/SQL procedure successfully completed.

Sql> Executedbms_workload_repository.create_snapshot ();

PL/SQL procedure successfully completed.

Sql> Executedbms_workload_repository.create_snapshot ();

PL/SQL procedure successfully completed.

Sql> Executedbms_workload_repository.create_snapshot ();

PL/SQL procedure successfully completed.

-Now there's a snapshot again:

sql> Select min (snap_id), Max (snap_id) from dba_hist_snapshot where dbid = 879543530;

MIN (snap_id) MAX (snap_id)

------------ ------------

177 180

--Delete Snapshot:

sql> exec dbms_swrf_internal.unregister_database (879543530)

Begindbms_swrf_internal.unregister_database (879543530); END;

*

ERROR at line 1:

Ora-13521:unregister operation on LocalDatabase ID (879543530) not allowed

Ora-06512:at "SYS. Dbms_swrf_internal ", line 99

Ora-06512:at Line 1

This is a direct hint that dbms_swrf_internal.unregister_database cannot be used on a local database. So if the local database, you can only use the Dbms_workload_repository package.

2.2.3 Summary

Dbms_workload_repository:

You can delete snapshots of local and other databases, and you can select different snapshots to delete them.

Dbms_swrf_internal:

Only snapshots of other databases can be manipulated, and the entire snapshot will be unregister out.

Oracleawr Delete History Snapshot description