28 апреля 2008 г.

Error Creating Snapshot in AWR

When trying to run an ADDM Report in Enterprise Manager Grid Control 10g, I got the following error:

“Insufficient Data in Interval. For displaying data on this page, two historical snapshots are needed. Make sure that two snapshots are present in the target database instance. In addition modify the interval so that it is contained within two available snapshots”

I looked at Automatic Workload Repository, it wasn’t configured automatically.
Then I tried to manually create snapshot in Automatic Workload Repository:

“Are you sure you want to create a manual snapshot?
Snapshots are created automatically by the database. Creating one manually may affect the results of the automatic snapshot immediately following.”


I answered Yes, but snapshot couldn’t be created:

ORA-13516: SWRF Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31
ORA-06512: at line 1


Then I tried the same thing to manually create snapshot using the package:

SQL> exec dbms_workload_repository.create_snapshot();

begin dbms_workload_repository.create_snapshot(); end;

ORA-13516: SWRF Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31
ORA-06512: at line 1
The metalink Note:287818.1:
Error: ORA-13516 (ORA-13516)
Text: SWRF Operation failed: %s
---------------------------------------------------------------------------
Cause: The operation failed because SWRF is not available. The possible
causes are: SWRF schema not yet created; SWRF not enabled; SWRF
schema not initialized; or database not open or is running in
READONLY or STANDBY mode.
Action: check the above conditions and retry the operation.


Note:459887.1:

Cause
These errors would be caused because of wrong or invalid objects with respect to AWR

Solution
In order to resolve this issue it is recommended to drop and recreate the AWR objects , which can be done using CATNOAWR.SQL and CATAWR.SQL.

But from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .

Dropping and recreating the AWR objects and bouncing the database:
SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sql
SQL> @$ORACLE_HOME/rdbms/admin/catawr.sql

1 коммент.:

John комментирует...

The WRM$_DATABASE_INSTANCE gets populated with instance information when the instance is rebooted. So, If you have recreated AWR using catnoawr.sql , catawrtb.sql , then you need to bounce the Instance . On restart , the table will be populated with the requisite information. Hence , better option for recreating AWR is to use startup restrict method. After the recreation activity is completed , shutdown the database and open it normally.