Thursday, December 27, 2012

How to recreate the AWR Repository

last week we were upgrading our test databases from to
All most all of them went fine but one wouldn't complete the upgrade. It didn't show
an error it just hung and that was it.
We stop the upgrade and downgrade the database back to

The name of database got me thinking as this was a database where awr reports
took forever or couldn't even be started. Also purging the awr repository cause the
database to hang.
Oracle suggested to upgrade to as this would fix te problem but we got the problem that
the database hung during upgrade.

The real solution was to recreate the awr reporsitory.
and this is how it goes in an Rac cluster.

First diabled AWR statistics gathering parameter
SQL> alter system set statistics_level=BASIC scope=both sid='*';
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

Yep you have to set sga_target or memory_target to 0.
so create a pfile from spfile and adjust the following parameters
SQL>create pfile from spfile;
this create as pfile in $ORACLE_HOME/dbs

set the following parameters to a appropriate value
shared_pool_size =
db_cache_size =
java_pool_size =
large_pool_size =

shutdown database and startup in resticted mode and with the pfile in $ORACLE_HOME/dbs
SQL>shutdown immediate;
SQL>startup restrict;

Now drop an recreate the awr objects.

SQL>alter system flush shared_pool;
SQL>@/rdbms/admin/execsvrm.sql  # Only if database is 11g

recompile the invalid objects

SQL>shutdown immediate;

Now start the database with the spfile with the old values;

After that we started the upgrade to and that went fine without any problem.

That it.

No comments:

Post a Comment