How to recreate the AWR Repository

last week we were upgrading our test databases from 11.2.0.2 to 11.2.0.3
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 11.2.0.2

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 11.2.0.3 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 =

sga_target=0

memory_target=0

statistics_level=basic

cluster_database=false

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>@/rdbms/admin/catnoawr.sqlSQL>alter system flush shared_pool;
SQl>@/rdbms/admin/catawrtb.sql
SQL>@/rdbms/admin/execsvrm.sql  # Only if database is 11g

recompile the invalid objects
SQL>@/rdbms/admin/utlrp.sql 

SQL>shutdown immediate;

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

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

That it.