Tuesday, June 17, 2008

Resizing OEM database part II

So, today I actually resized the OEM database. I resized the database from almost 40gb to 17 gb. In my recent post I already mentioned the emd_maintenance.partition_maintenance I used to cleanup old records in the mgmt_metrics_raw table (mgmt_tablespace). Problem was, I could not resize the datafile because of an ORA-03297: file contains used data beyond requested RESIZE value error. Two options remained, one export of the sysman schema and drop the excisting repository and import again. Second option, export tablespace mgmt_tablespace and drop the tablespace, recreatre it and import.
Because Oracle did not test the second option, I decided to go for that one.
The tablespace export went well, I used the expdp with the TABLESPACES=MGMT_TABLESPACE option. After the export, I tried to drop the tablespace. Because of materialized views I did not succeed.
I decided not to try any futher and also made an sysman schema export. This time using the SCHEMAS option instead of the tablespaces...
After succesfully exported the sysman schema, I dropped the repository using the RepManger in $OMS_HOME/sysman/admin/emdrep/bin directory

After dropping the repository I had to bounce the database to free up the disk space on linux. Even when I removed the big datafile (27gb), linux still showed a 100% full.
After reclaiming the disk space on linux, I returned to the Oracle documentation again. Next step, Import the sysman export ? No need for a recreate of the repository ? Or maybe only the rep users, tablespaces, dba_roles ?? In the $OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin I found some scripts, for example admin_pre_import.sql and admin_create_tablespaces.sql. I decided to run those sql's before the import. The documentation continues after the import with the post import steps, but the pre import is just as important. Because if your import is not succesfull, you don't even have to perform any post import steps :)

To summerize this post, I will show the steps I have taken...

1. export sysman schema (expdp SCHEMAS=SYSMAN...)
2. drop the repository using RepManager
3. Create repository tablespaces (mgmt_tablespace and mgmt_emc_depot_ts)
4. Create repository users (sysman, mgmt_user and mgmt_view (see admin_pre_import.sql))
5. import the sysman export (impdp)
6. compile any invalid objects (admin_recompile_invalid.sql)
7. run admin_create_synonyms.sql (same directory)
8. run admin_post_import.sql
9. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_TASK_Q');
11. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_PAF_REQUEST_Q');
12. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_LOADER_Q');
13. Check sysman context using select * from dba_context where SCHEMA='SYSMAN';
14. exec emd_maintenance.analyze_emd_schema('SYSMAN');
15. run admin_submit_dbms_jobs.sql

If all these steps went oke, startup the OMS and you are back in business !


Eric Moe said...

Marco's comment in your previous post should have worked.
I just recently had the SYSAUX tablespace in one instance grow out of control to 22GB. I was able to purge the unneeded data. Then I used alter table move, alter index rebuild, etc, using the same tablespace. This lowered the high water mark for each object. I was then able to resize the datafile from 22Gb to 4GB.

Bas Klaassen said...

I tried the move offcourse, but I didn't clean up the tablespace.
I noticed that there were some materialized views at the end of the tablespace. Import/export did work also.

Anonymous said...

[...]You can go to the court to seek security (freezing bank account etc.) before commencement of proceedings but you cannot obtain any order to stop the breaching party.[...]