Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘awr in db restricted mode’

AWR report in restricted mode

Posted by Pavan DBA on February 8, 2018


Today, I have learned new thing regarding AWR report when database is in restricted mode.

By default, if your database is in restricted mode, you cannot generate AWR report. It will throw below error in the alert log.

ORA-13516: AWR Operation failed

or

ORA-20200: Database/Instance nnnnnn does not exist in DBA_HIST_DATABASE_INSTANCE

This is because Oracle database will not allow to generate AWR report in restricted mode. To still continue with report generation, you need to set below hidden parameter in your spfile

_AWR_RESTRICT_MODE

Steps to perform:

  1. alter system set _awr_restrict_mode=TRUE scope=SPFILE; -> Default value is FALSE
  2. srvctl stop database -d <database_name> (for RAC, restart node by node)

But, there is a limitation for this parameter. When it is set to TRUE, AWR snapshots will not generate during OPEN mode and you need to change this parameter back to FALSE.

Advertisements

Posted in Perf Tuning | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: