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');
10. exec DBMS_AQADM.START_QUEUE( queue_name=> 'MGMT_PAF_RESPONSE_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 !