ORA-13516: AWR Operation failed: database in restricted mode

   

Error while creating AWR snaps in Oracle database. Error: SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); BEGIN DBMS_WORKLOAD_REPOSITORY.create_snapshot(); END; * ERROR at line 1: ORA-13516: AWR Operation failed: database in restricted mode ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 145 ORA-06512: at line 1 Solution: This is because database instance is open in RESTRICTED mode. By default, AWR snapshots cannot be created for a database in the restricted session mode. If you try to create AWR snapshots in RESTRICTED mode then, you will face error message "ORA-13516: AWR Operation failed: database in restricted mode" which is expected.  To fix this error, you have to execute below procedure to create AWR snaps in RESTRICTED mode. DBMS_WORKLOAD_REPOSITORY.CONTROL_RESTRICTED_SNAPSHOT(TRUE); This procedure controls the AWR snapshot creation for a database in the restricted session mode. To enable AWR snapshot creation for an Oracle RAC in the restricted session mode, this procedure must be executed on every database instance of Oracle RAC database. #Now execute below procedure to enable AWR snap creation in restricted mode. SQL> exec DBMS_WORKLOAD_REPOSITORY.CONTROL_RESTRICTED_SNAPSHOT(TRUE); PL/SQL procedure successfully completed. #Now try to create AWR snap in restricted mode. SQL> exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); PL/SQL procedure successfully completed.

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !