ORA-00600 solved....

The ORA-00600 from my previous post is solved. Also the notifications are being send again.
The 'ORA-00600 [kwqidrdq: loop], [0], [0], [0], [0], [], [], []' error was shown in the EMD_NOTIFICATION.QUEUE_READY.
Normally you receive this error wwhen records are deleted manually from queue tables, which is unsupported (also see note 1070715.6).
The problem was solved by executing the following statements in sql :

[oemprd01@amssys07 udump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 19 08:43:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter aq

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1

SQL> alter system set aq_tm_processes=0;
System altered.

SQL> conn sysman
Enter password:
Connected.
SQL> delete from AQ$_mgmt_notify_qtable_I i 2
where not exists (select t.msgid from mgmt_notify_qtable t where i.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_H h 2
where not exists (select t.msgid from mgmt_notify_qtable t where h.msgid = t.msgid);
1 row deleted.
SQL> delete from AQ$_mgmt_notify_qtable_T ti 2
where not exists (select t.msgid from mgmt_notify_qtable t where ti.msgid = t.msgid);
0 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system set aq_tm_processes=1;
System altered.

After this the ORA-00600 error was gone, trace files were no longer generated in the udump directory and OEM notifications were being send again.
Only thing that isn't clear for me....Did the export/import of the sysman schema cause this problem ?
I think it did, but what could I have done to prevent it. Because I followed all the steps mentioned by Metalink.
Next time, check the queue status before exporting sysman.
select owner,name,queue_table,queue_type,enqueue_enabled,dequeue_enabled
from dba_queues;
Then queues can be controlled by
dbms_aqadm.stop_queue
dbms_aqadm.start_queue

OEM after import sysman schema....

After the export/import of the sysman schema, we recoverd almost 23G of disk space. But yesterday the /oem partition again was 100% full ????
Analyzing the different diectories showed an udump directory with 23G of trace files !!!
So, something is wrong with OEM. I also noticed no notifications were being send to me. The trace files showed me an ORA-00600 [kwqidrdq: loop], [0], [0], [0], [0], [], [], [] error. Could this have something to do with the export/import ??
I check the repository for invalid sysman objects. No invalid objects. I bounced the database and after bouncing the database some emails were send to me with OEM notifications, but still a lot off notifications were waiting to be send.
Using the emdiag tool...

[oemprd01@amssys07 bin]$ ./repvfy verify notifications -level 9
Please enter the SYSMAN password:
---------------------------------------------------------------------------
EMDIAG: 2008.0321 Repository: 10.2.0.3.0 19-Jun-2008 07:47:08
---------------------------------------------------------------------------verifyNOTIFICATIONS
100. Notification backlog: 2
103. Stuck notification waiting to be delivered: 1090

I decided to tell Oracle about this and ask them if this could have anything to do with the wxport/import.
Still no answer, and my udump is filling up every day.

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');
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 !

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 ?

Pocket guides....

Sometimes when surfing the internet you find something interesting. I don't know exactly how, but I arrived at the following site with a lot of handy guides for the dba or EBS dba.
Check it out !

http://www.solutionbeacon.com/tool_pocket.htm