Monday, May 23, 2016

reduce AWR retention but hit ORA-13541

Need to reduce AWR retention due to no disk space in this development database.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from dba_hist_wr_control;

      DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL        CON_ID
---------- ----------
2723810418
+00000 01:00:00.0
+00042 00:00:00.0
DEFAULT             0




SQL> begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
  2
  3  /
begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (3628800) greater than retention
(691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 198
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 246
ORA-06512: at line 1


SQL> SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';


MOVING_WINDOW_SIZE
------------------
                42

SQL>
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

PL/SQL procedure successfully completed.

SQL> SELECT moving_window_size
FROM   dba_hist_baseline
  2    3  WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                 7

SQL>
SQL> begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(11520,60); end;
  2  /

PL/SQL procedure successfully completed.

SQL>  select * from dba_hist_wr_control;

      DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL        CON_ID
---------- ----------
2723810418
+00000 01:00:00.0
+00008 00:00:00.0
DEFAULT             0


References:
http://www.sqlpanda.com/2013/02/ora-13541-system-moving-window-baseline.html