000035405 - How to modify the Oracle database Automatic Workload Repository (AWR) retention period in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Dec 19, 2019
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000035405
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Product/Service Type: Appliance
RSA Version/Condition: 7.x
Platform (Other): Oracle 12.x
 
IssueThe Oracle and RSA Identity Governance & Lifecycle default retention period for the Automatic Workload Repository (AWR) is eight days.This article describes how to modify the Automatic Workload Repository (AWR) retention period on an appliance. For a customer-supplied database, please contact the DBA.

For more information on the Automatic Workload Repository (AWR), please see RSA Knowledge Base Article 000037779 -- How to generate an Oracle Automatic Workload Repository (AWR) report for RSA Identity Governance & Lifecycle.
 
Tasks

Check the current retention period setting



The current retention period can be checked by running the below query as SYSDBA. In this example, the system shows a retention period of eight days.




$ sqlplus / as sysdba
SQL> SELECT RETENTION FROM DBA_HIST_WR_CONTROL;

RETENTION
--------------------------
+00008 00:00:00.0
Resolution

Modify the current retention period setting 



  1. Calculate the desired retention period.

The retention period parameter is defined in minutes and the minimum retention period is one day. To change the retention period to a different number of days, first determine how many minutes that would be. For example, to change the retention period to four days, calculate the value as 5760 minutes  (4 days x 24 hours x 60 minutes.)



  1. Execute the following command as SYSDBA:


$ sqlplus / as sysdba
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>5760);


  1. The above SQL may return the following error:


ORA-13541: system moving window baseline size (691200) greater than retention (345600)


If this error occurs, the window baseline size needs to be set lower than the retention size.


  1. Note that the units for the error, retention and window baseline size are different.

  • The ORA-13541 error reports both the window baseline size and the retention size in SECONDS.
  • The retention units are in MINUTES
  • The window baseline size units are in DAYS.

  1. Convert the numbers (SECONDS) from the above ORA-13541 error to the appropriate unit values:

  • Retention = 345600 seconds / 60 = 5760 minutes / 60 = 96 hours / 24 = 4 days
  • Window baseline size = 691200 seconds / 60 = 11520 minutes / 60 = 196 hours / 24 = 8  days

The window baseline size needs to be changed from 8 days to 4 days using the following sequence of commands as SYSDBA.


  1. Get the current window baseline size:


$ sqlplus / as sysdba
SQL> COL BASELINE_NAME FORMAT A22
SQL> SELECT DBID, BASELINE_NAME, BASELINE_TYPE, MOVING_WINDOW_SIZE
 
2  FROM DBA_HIST_BASELINE
 
3  WHERE BASELINE_NAME='SYSTEM_MOVING_WINDOW';

      DBID BASELINE_NAME          BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------- ------------- ------------------
1688764095 SYSTEM_MOVING_WINDOW   MOVING_WINDOW                  8


  1. Change the window baseline size to a value lower than or equal to the value of the AWR retention setting (where  window_size=>4 refers to four days):


SQL> BEGIN
 
2  DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(WINDOW_SIZE=>4);  
3  END;
 
4  /


  1. Check the window baseline size to ensure the window_size value was modified:


SQL> COL BASELINE_NAME FORMAT A22
SQL> SELECT DBID, BASELINE_NAME, BASELINE_TYPE, MOVING_WINDOW_SIZE
 
2  FROM DBA_HIST_BASELINE
 
3  WHERE BASELINE_NAME='SYSTEM_MOVING_WINDOW';

      DBID BASELINE_NAME          BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------- ------------- ------------------
1688764095 SYSTEM_MOVING_WINDOW   MOVING_WINDOW                  4


  1. Attempt to change the AWR retention to four days again (where retention=>5760 refers to four days in minutes);  


SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>5760);  


  1. Check that the AWR retention period has been modified:


SQL> SELECT RETENTION FROM DBA_HIST_WR_CONTROL;

RETENTION
--------------------------
+00004 00:00:00.0
NotesThe AWR data resides in tablespace SYSAUX. If you need to increase the AWR retention period significantly, the SYSAUX tablespace should be monitored by executing the below query as SYSDBA. 
 

$ sqlplus / as sysdba
SQL> COLUMN OCCUPANT_NAME FORMAT A15
SQL> SELECT OCCUPANT_NAME, OCCUPANT_DESC, SPACE_USAGE_KBYTES
SQL> FROM V$SYSAUX_OCCUPANTS
SQL> WHERE OCCUPANT_NAME LIKE '%AWR%';

OCCUPANT_NAME OCCUPANT_DESC                                    SPACE_USAGE_KBYTES
------------- ---------------------------------------------    ------------------
SM/AWR        Server Manageability - Automatic Workload Repository         214656


If you would like to purge the AWR data in the SYSAUX tablespace, please open a support case with RSA Identity Governance & Lifecycle Support and refer to this RSA Knowledge Base Article ID 35405 for reference. Please also attach the results of the above query to the case. The long term solution would be to periodically purge the space or reduce the retention period setting.
 

Attachments

    Outcomes