Thursday, June 13, 2013

EM12c Database Inventory Report

With the select statement below you can have a database inventory report.

Below is a sample report I have in my apex application op top of my EM12c repository.








Instance name- SID
Host name- Host name where database lives
Version- database version
Total memory- This is the total memory SGA*PGA or if 11g total memory_target
Data Guard Status- Primary or Standby
Port- Listener port
Path- Oracle Home path

-----------SQL Statement-----------------------

     SELECT DISTINCT
            tbl_tar.target_guid,
            tbl_sid.sid AS instance_name,
            CASE
               WHEN tbl_tar.host_name LIKE '%.%'
               THEN
                  LOWER (SUBSTR (tbl_tar.host_name,
                                 1,
                                   INSTR (tbl_tar.host_name,
                                          '.',
                                          2,
                                          1)
                                 - 1))
               ELSE
                  tbl_tar.host_name
            END
               host_name,
            tbl_ver.version,
            CASE
               WHEN tbl_mem.mem_max > 0
               THEN
                  CEIL (tbl_mem.mem_max / 1024 / 1024)
               ELSE
                  CEIL (tbl_sga.sga / 1024 / 1024 + tbl_pga.pga / 1024 / 1024)
            END
               total_memory,
            tbl_dg.data_guard_status,
            tbl_port.port,
            tbl_home.PATH
       FROM (SELECT p.target_guid, p.property_value AS port
               FROM mgmt_target_properties p
              WHERE p.property_name = 'Port') tbl_port,
            (SELECT s.target_guid, UPPER (s.property_value) AS sid
               FROM mgmt_target_properties s
              WHERE s.property_name = 'SID') tbl_sid,
            (SELECT s.target_guid, s.property_value AS version
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('Version')) tbl_ver,
            (SELECT s.target_guid, s.property_value AS PATH
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('OracleHome')) tbl_home,
            (SELECT s.target_guid, s.property_value AS data_guard_status
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
            (SELECT s.target_guid, s.VALUE AS PGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'pga_aggregate_target') tbl_pga,
            (SELECT s.target_guid, s.VALUE AS SGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'sga_max_size') tbl_sga,
            (SELECT s.target_guid, s.VALUE AS mem_max
               FROM mgmt$db_init_params s
              WHERE s.name = 'memory_target') tbl_mem,
            mgmt_target_properties tbl_main,
            mgmt_targets tbl_tar
      WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
            AND tbl_main.target_guid = tbl_sid.target_guid(+)
            AND tbl_main.target_guid = tbl_tar.target_guid(+)
            AND tbl_main.target_guid = tbl_ver.target_guid(+)
            AND tbl_main.target_guid = tbl_home.target_guid(+)
            AND tbl_main.target_guid = tbl_dg.target_guid(+)
            AND tbl_main.target_guid = tbl_pga.target_guid(+)
            AND tbl_main.target_guid = tbl_sga.target_guid(+)
            AND tbl_main.target_guid = tbl_mem.target_guid(+)
            AND tbl_tar.target_type = 'oracle_database'
   GROUP BY tbl_tar.target_guid,
            tbl_port.port,
            tbl_sid.sid,
            tbl_tar.host_name,
            tbl_ver.version,
            tbl_home.PATH,
            tbl_dg.data_guard_status,
            tbl_pga.pga,
            tbl_sga.sga,
            tbl_mem.mem_max
   ORDER BY 2;

Note: If you are going to use another account other than SYSMAN to select you will need to have the following privilege.

grant select on mgmt$storage_report_data to <username>;
grant select on mgmt_target_properties to <username>;
grant select on mgmt_targets to <username>;
grant exempt access policy to <username>;

3 comments:

  1. Dear Javier

    Hello.
    Can you help me the below issue.

    In which repository table of em 12c store the saved configurations(property name with value)? actually we need to get automated notification report against any chages of host db etc..

    Enterprise --> configurtion --> saved
    Enterprise --> configurtion --> compare

    automated capture and notify any changes in host,db,os ... with cusomization

    Regards
    Q.A.Rab
    qarabus@yahoo.com

    ReplyDelete
  2. You can try reviewing any of the tables under the sysman schema that have the word snapshot those seem to contain compare data.

    ReplyDelete
  3. Hello,

    When i create a data_model with BI Publisher with your statement i have an error "ORA-00907: missing right parenthesis". If i try with sqlplus or sqldev i have no error. I try to modify this statement but i can't solve this error. Do you know a solution?
    thx,

    ReplyDelete

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).