14 Replies Latest reply: Mar 22, 2012 10:38 AM by Joss Leaver RSS

    How to extract data from OEM ?

      Hi, All:

      I would like to get some data from OEM , the data the OEM used for it's report, I tried to search around emrep database , did not find anything meaningful, can someone share with me where I can find and how to extract the data from OEM repository?

        • 1. Re: How to extract data from OEM ?
          Talip Hakan Ozturk
          Check SYSMAN schema tables.

          Talip Hakan Ozturk
          • 2. Re: How to extract data from OEM ?
            Hi, Talip:

            Thanks for your advice, I checked sysman schema, most of the tables are empty.

            Any other ideas?



            Edited by: OISK on Mar 7, 2012 6:55 AM
            • 3. Re: How to extract data from OEM ?
              You didn't mention the version you're running. Grid Control 11g and Cloud Control 12c both include a function to display the SQL recently executed on your behalf by OEM. Try running the report or viewing the page that contains the data you're looking for, then check the SQL history for tables and column names that look relevant. Look into those tables. Try the 'Search SQL' function and query for SQL parsed in the SYSMAN schema on the repository database where the SQL text is like whatever you're looking for -- %target%, %credential%, %metric%, and so on.

              Be prepared to dig around a lot, the SYSMAN schema is complex. Review the OEM licensing documentation to make sure you don't directly query repository tables related to management packs for which you don't own a license.
              • 4. Re: How to extract data from OEM ?
                Hi, Brain:

                Thanks a lot for your advice, it is very helpful.....at least I know where to look for information now. However, when I tried an database availability report from OEM and come back to sysman schema to look for a clue....I am like a small fish in an ocean....totally lost....but I am still trying to see if I can identify any tables that may have that sql text....Do you happen to know which tables may have that information?

                By the way, I am using OEM 11g.

                Thank you so much for your help.

                • 5. Re: How to extract data from OEM ?
                  I'm using 12c and can't say what may have changed since 11g, but things are probably similar enough.

                  I ran the Availability History report for a database target, then tried the 'Cloud Control SQL History' and didn't find anything useful. So then I went to the repository database and did a 'Search SQL' looking for parsing schema = 'SYSMAN' and SQL text like '%avail%', and received 123 matches. I skimmed through the matches and found one starting out with "SELECT DECODE (availability_status,'Target Down'..." -- that one looked like it was related to the report I ran. Clicking that SQL ID and viewing the activity history showed it was parsed at the time when I ran the report, so it seems like the right one.

                  I'm not sure if it's legit for me to post the query or not, but I'll say that it queried the MGMT$AVAILABILITY_HISTORY view, the MGMT$TARGET view and the MGMT$ALERT_HISTORY view, all with some complex joins to deal with the GUIDs that OEM uses internally for most everything. That might be a good place to start. I would definitely spend some time with the Search SQL functionality to see what OEM does on your behalf.

                  Definitely note that the MGMT$AVAILABILITY_HISTORY and MGMT$ALERT_HISTORY views are explicitly noted as licensed as part of the diagnostics pack, so don't query them if you don't have that pack licensed.

                  I'll also add to my earlier suggestion and say that searching through the MGMT$ and EM$ views owned by SYSMAN in the repository database may make things slightly more clear than the base tables. But it will still be very difficult to write your own SQL unless you're much better at it than I am :)
                  • 6. Re: How to extract data from OEM ?
                    Hi, Brain:

                    Thank you so much for spending time to write up such a detailed explanation. I learned a lot from your post as to how to tackle the problem. I really appreciate your help.

                    We have the diagnose pack license. so don't worry about that.

                    I am not sure how to do 'Search SQL', so after I run the report, I went the DB , tried to query v$sql, v$sqlarea and v$sqltext, only v$sql and v$sqlarea has the parsing_schema_name column which allows me to define the schema as 'SYSMAN' , with that I got about 180 matches from v$sql or v$sqlarea, but if I put further condition like where sql_fulltext like '%aval%' in, then no records come back., even after I run the report from OEM several times, Maybe I am not looking at the right place?

                    if it's not very convenient to talk about it here, can you email me at oracle1961@gmail.com ?

                    Thank again for your patience, obviously I am not that good on oracle yet, your help has already helped me improved a lot .

                    • 7. Re: How to extract data from OEM ?
                      Hi, Brain:

                      After I posed the previous reply, I all of sudden realize that maybe I was not in the right place, for example, after I run a report of availability for my database my_db on OEM, I went to my_db to look into v$sql etc view with schema ='SYSMAN', but now I realize maybe I should try to query on emrep database, and I did see a huge lists of records back with condition of '%avail%'. and schema='SYSMAN'...not sure, I will be getting anything real from that yet, but it seems hopeful for now...will let you know...thanks

                      • 8. Re: How to extract data from OEM ?
                        One of the nice things about OEM is that it gives you a nice interface to search through SQL executed on a database, and it can pull the data from the current cursor cache and AWR snapshots, so you don't have to bother with querying through the v$sql% views on your own.

                        I'm trying to remember where this lives in 11g. But in 12c you would, in OEM, connect to the repository database target then access 'Search SQL' through the Performance menu, then SQL, then Search SQL. For 11g I think you need to go to the performance page (or maybe the 'top activity' page), and then somewhere down at the bottom of the page is a link you can click titled 'Search SQL' -- if that's not it, maybe one of the other folks here running 11g can correct me. Once you get to the right page it gives you a nice query interface that defaults to include parsing schema name, SQL text, and so on.

                        When you're querying V$SQL manually for this sort of thing, make sure you include UPPER around the parsing_schema_name and sql_text so you don't get hit by case-sensitive SQL: select sql_text, elapsed_time from v$sql where upper(parsing_schema_name) = 'SYSMAN' and upper(sql_text) like '%AVAIL%';. I just tried on my repository DB and leaving out the upper() check on sql_text gave me only 33 rows vs 128. That might help.

                        Edit after your followup -- yes, this absolutely comes from the repository database, not the monitored database.... you're on the right track now!

                        Edited by: BrianP on Mar 8, 2012 9:25 AM
                        • 9. Re: How to extract data from OEM ?
                          Courtney Llamas-Oracle
                          Not sure which report you are trying to get SQL for, but if "most" of the Information Publisher reports can be obtained by doing a Create Like on the report (say Availability for target), select the Elements or Parameters tab, and view the Table from SQL element by clicking the icon on the right.
                          • 10. Re: How to extract data from OEM ?
                            Hi, Brain:

                            Thanks again for the instruction, I got the statement from OEM-performacne-searach SQL and by querying V$sql and V$sqlarea , it was a good learning experience, really like it and appreciate your help.

                            I know I should not keep bothering you, but since you have been so nice and patient, I am going to stretch my luck a little bit, and asking more questions here. :-)

                            I real issue is like this: One of our client wants to view some of OEM reports or in similar fashion and be able to run it at his own will and pick his time frame , yet we can't give him the access of our OEM, and we don't have Web server to publish the report to web for him to view, and it's going to be extremely hard for me to write the code for those report since I know I am not good at that, we happen to have MicroStrategy, so I am thinking/planning to get the query from OEM and find out the related tables and export it to a mini data-mart, like a data warehouse, and then use the query I got from OEM , and hopefully be able to create report for him via MicroStrategy.....

                            Is it a good and doable plan or are there any better ways to do that?

                            Thank you so much.

                            • 11. Re: How to extract data from OEM ?
                              Hi, cllamas:

                              Very interesting suggestion, I went to OEM and got to the "element' part, but I don't see any icon's that I can click to get sql text , the only icon's there are 'set parameters'....can you give me more instruction ?

                              Thanks a lot for your help, learned new thing again.

                              • 12. Re: How to extract data from OEM ?
                                Courtney Llamas-Oracle
                                Yes, click on Set Elements, you should see the SQL there if it's a Table from SQL element.

                                As far as allowing access to the reports, you can use the Public option to make them available w/o OEM login if the customer has access to your network. Extracting information can be done, but can be tricky as there are many dependencies.
                                • 13. Re: How to extract data from OEM ?
                                  I think the ideas that cllamas has will probably get you where you want to go faster than poking around the repository, good luck!
                                  • 14. Re: How to extract data from OEM ?
                                    Joss Leaver

                                    you can find some useful info - and a walk-through - on how to extract OEM GC repository data to excel here: