Pavan DBA's Blog

The DBA Knowledge Store

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: