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>;

EM12c Server Report

Ever wanted to see the following type of information you see on the host target homepage in EM12c? Now you can using the select statement I created below.



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

This SQL statement will give you server information from the EM repository tables.

HOST_NAME- server name
HARDWARE- hardware type
OS- os version
FREQ_IN_MHZ- speed of CPU
IMPL- implementation
CPU_CORES- number of cores
CPU_THREADS- number of threads
TOTAL_MEMORY_SIZE_GB- total physical RAM
TOTAL_SWAP_SPACE_IN_GB- total swap space
ROOT_FILESYSTEM_TYPE- type of root file system

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

SELECT DISTINCT
            a.target_guid,
            CASE
               WHEN host_name LIKE '%.%'
               THEN
                  LOWER (SUBSTR (host_name,
                                 1,
                                   INSTR (host_name,
                                          '.',
                                          2,
                                          1)
                                 - 1))
               ELSE
                  host_name
            END
               host_name,
            system_config hardware,
            OS_summary OS,
            c.freq_in_mhz,
            c.impl,
            cpu_count cpu_cores,
            logical_cpu_count cpu_threads,
            CEIL (mem / 1024) AS total_memory_size_in_gb,
            CEIL (max_swap_space_in_mb / 1024) AS total_swap_space_in_gb,
            d.TYPE AS root_filesystem_type
       FROM MGMT$OS_HW_SUMMARY a,
            MGMT$OS_SUMMARY b,
            (SELECT target_guid, freq_in_mhz, impl
               FROM (SELECT t1.target_guid,
                            T1.FREQ_IN_MHZ,
                            t1.impl,
                            ROW_NUMBER ()
                            OVER (PARTITION BY t1.target_guid ORDER BY t1.impl)
                               rn
                       FROM MGMT$HW_CPU_DETAILS T1)
              WHERE rn = 1) c,
            (SELECT cm_target_guid, TYPE
               FROM CM$MGMT_ECM_OS_FILESYSTEM
              WHERE mount_location IN ('c:\', '/', 'C:\')) d
      WHERE     a.target_guid = b.target_guid
            AND a.target_guid = c.target_guid
            AND a.target_guid = d.cm_target_guid
   ORDER BY 2 ASC;

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$OS_HW_SUMMARY to <username>;
grant select on MGMT$OS_SUMMARY to <username>;
grant select on MGMT$HW_CPU_DETAILS to <username>;
grant select on CM$MGMT_ECM_OS_FILESYSTEM to <username>;
grant exempt access policy to <username>;

EM12c Storage Report

Want a report with all your storage mounts as well as the total space allocated and used?

Below is a select statement you can use to select the data from the SYSMAN views.

select
target_name,
name,
ceil(sizeb/1024/1024/1024) as allocated_gb,
ceil(usedb/1024/1024/1024) as used_gb,
ceil(freeb/1024/1024/1024) as free_gb
from sysman.mgmt$storage_report_data
where entity_type='Mountpoint'
order by 1 asc

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 exempt access policy to <username>;

Oracle Home Property Missing for 12c Agent

1. Check that the Oracle home exist in the $AGENT_INST/sysman/emd/targets.xml

2. Check that the Oracle home exist in the oraInventory

Here is the resolution:

Missing home in oraInventory

1. Attach home to oraInventory

$cd $AGENT_HOME

$AGENT_HOME/oui/bin/runInstaller -silent -attachHome -invPtrLoc $AGENT_HOME/oraInst.loc ORACLE_HOME_NAME="agent1_home" oracle_home="$AGENT_HOME" "cluster_nodes={}"

2. Run emctl collection

$cd $AGENT_HOME

 $./emctl control agent runCollection <TARGET_ORACLE_HOME_NAME>:oracle_home oracle_home_config

Missing Home in Targets.xml

1. Run emctl collection

 $cd $AGENT_HOME

$./emctl control agent runCollection <TARGET_ORACLE_HOME_NAME>:oracle_home oracle_home_config

12c Agents not showing up on list for upgrade

If you are trying to find your agent to upgrade but it is not in the list you need to check the "Not Upgradable Agents".

1. Go to Setup>Manage Cloud Control>Upgrade Agents
2. Click "Not Upgradable Agents"
3. Following the recommendation to fix the issue
4. Once resolved try to upgrade the agent again.

Another way to check "Not Upgradable Agents"

1. Go to Setup>Manage Cloud Control>Agents
2. Click the link "Not Upgradable"
3. Review the reason 

Solaris EM12c Agent Unreachable with Error "peer not authenticated"

If you come across a Solaris EM12c agent with status unreachable with error "peer not authenticated" here is a solution.

Verify the Error:
1.$cd $AGENT_HOME/bin
2.$./emctl status agent
Example of error
Agent is Unreachable (REASON = unable to connect to http server at https://dbtest:3872/emd/main/. [peer not authenticated]) but the host is reachable.

Stop the Agent:
1.$./emctl stop agent
2.Verfiy the java process has stopped
$ps -ef |grep java
If the java process is still running you would see like the following still running
$AGENT_HOME/core/12.1.0.2.0/jdk/bin/sparcv9/java
3.$kill -9 <pid>

Update Config File:
1.$cd $AGENT_HOME/agent_inst/sysman/config
2.$cp s_jvm_options.opt s_jvm_options.opt.orig
3.Add the following line to s_jvm_options.opt
-Dsun.security.pkcs11.enable-solaris=false

Restart Agent and Test Upload:
1.$cd $AGENT_HOME/bin
2. $./emctl start agent
3. $./emctl upload agent

Reference:
EM12c Agent status Fails With "Peer Not Authenticated" Error. [ID 1510706.1]

My webpages
http://db12c.blogspot.com/
http://cloudcontrol12c.blogspot.com/

http://www.youtube.com/user/jfruiz11375

Follow me on Twitter

Resolving Agent12c Unreachable

In OEM if you see the your targets have a status of unreachable here are a few steps you can take to try and resolve the issue.

  

$cd $AGENT_HOME/bin/

$./emctl clearstate agent

$./emctl stop agent

$./emctl clearstate agent

If the agent will not stop you can search for the OS process and issue a kill -9

$ps -ef |grep agent12c/core/12.1.0.1.0/jdk

$kill -9 <pid>

$./emctl clearstate agent

$./emctl start agent

$./emctl upload agent

If the upload fails issue the upload command again

$./emctl upload agent

In OEM you should now see that all targets are back online

If the above steps do not work you can doing the following

$cd $AGENT_HOME/bin/

$./emctl clearstate agent

$./emctl stop agent

$./emctl clearstate agent

$cd AGENT_INST/sysman/log

$rm -rf *.*

$cd $AGENT_INST/sysman/emd

$rm -rf state upload

$cd $AGENT_HOME/bin/

$./emctl clearstate agent

$./emctl start agent

$./emctl clearstate agent

$./emctl status agent

$./emctl upload agent

In OEM you should now see that all targets are back online