Friday, June 13, 2008

Resizing OEM database 10g

We noticed our Grid control (10.2.0.3.0) was not cleaning up his metrics. Because of this we are running out of disk space on the oem volume. I noticed the mgmt_tablespace was 27 gb. The table mgmt_metrics_raw had more then 410 million records. I also found out that almost 4 million records were older than sysdate -9. Oem should automaticly clean up these records after zeven days, so something has gone wrong...
I decided to run the emd_maintenance.partition_maintenance (after applying patches mentioned in note 456101.1)
Now the mgmt_tablespace is only 3 gb, so I was able to clean up 24 gb. Next step is to resize the tablespace, so I can reuse the 24 gb. But resizing the datafile didn't work. Oracle showed me the : 'ORA-03297: file contains used data beyond requested RESIZE value' error.

I think there are two ways of reclaiming the free space in this tablespace. Completly export the sysman scheme, drop repository and import it again (tested by Oracle), or (and I would like to do this one) export the mgmt_tablespace using expdp TABLESPACE=mgmt_tablespace, drop/create the tablespace end import the data again. This option is faster and eassier, but not tested by Oracle yet.

Now I wonder if anaybody has already solved an issue like this ?

4 comments:

Marco Gralike said...

rebuild via move using same tablespace?

Anonymous said...

Dont drop the sysman schema - its not that easy - there are many hidden things in there like VPD and policies and stuff.. Grab the script from Tom Kyte that shows you what tables/indexes are in those last few blocks, export them and then resize the tablespace.

Rob Meijer said...

dit gaat erg makkelijk met enterprise manager (reorganize objects)

Bas Klaassen said...

Rebuild via OEM...

Encountered Advanced Queue table SYSMAN.AQ$_MGMT_LOADER_QTABLE_G. Currently this type of object is not supported. Reorganization of this tablespace is not supported.