Skip navigation.

Arun Bavera

Syndicate content
Few of my technology tips. http://www.linkedin.com/in/arunbavera
Updated: 5 hours 3 min ago

Installing Agent using Pull mechanism on Windows-without cygwin or CURL

Tue, 2014-03-18 15:49

Installing Agent using Pull mechanism on Windows
               1)Get the Latest Agent software using below URL format:
https://my.emservername.net/em/install/getAgentImage?user=AGENT_DOWNLOAD&password=oracle123&version=12.1.0.3.0&script=download&host=myhostname&type=agtimage&platform="Microsoft Windows (32-bit)"
You can use CURL on Linux if you don't have the browser installed:
curl --data @pswd.txt "https://%omsHost%:%omsPort%/em/install/getAgentImage?user=%username%&version=%version%&script=download&host=%hostname%&type=%type%&platform=%platform%" --insecure -o "%dest%"\agent.zip -S
Platforms                       VersionLinux x86                                          12.1.0.3.0 Linux x86-64                                    12.1.0.3.0   Microsoft Windows (32-bit)              12.1.0.3.0Microsoft Windows x64 (64-bit)       12.1.0.3.0
2) Save it as “getAgentImage.zip”
3)  Login as administrator, Unzip it to a stage directory
 For Example:unzip D:\tmp/getAgentImage.zip -d  D:\agtImg12c
4) Get the correct hostname from nslookup
5) Deploy the Agent  with correct hostname highlighted below For Example:
D:/agtImg12c/agentDeploy.bat AGENT_BASE_DIR=D:/Oracle/agent12c AGENT_INSTANCE_HOME=D:/Oracle/agent12c/agent_instOMS_HOST=my.emservername.net EM_UPLOAD_PORT=4900 AGENT_REGISTRATION_PASSWORD=oracle123   AGENT_PORT=3875  ORACLE_HOSTNAME=myhostname
Note: forward “/” in the path---------------------------------------------------------------------------------
Refer:EM 12c: How to Install EM 12c Agent in Silent Mode using a Response File (Doc ID 1360083.1)http://docs.oracle.com/cd/E24628_01/install.121/e24089/install_agent_usng_rsp.htm#CEGGACJE
Categories: Development

Troubleshooting the Discovery of Targets on OEM12c

Fri, 2014-03-14 13:41
Troubleshooting the Discovery of Targets on OEM12c

NOTE : Please enable perl trace to debug mode and then after discovery please send perl log file to OracleSupport for further observation.

  1. Go to /agent_inst/sysman/config
  2. Open emd.properties file and
  3. Change this line EMAGENT_PERL_TRACE_LEVEL=INFO to EMAGENT_PERL_TRACE_LEVEL=DEBUG
  4. Restart agent
  5. Run discovery again and send us /agent_inst/sysman/log/emagent_perl.trc file.
Categories: Development

Useful Queries in OEM12c PART-3

Wed, 2014-03-05 23:00
SOA11g:

FROM OEM12c Views

Total BPEL Instances in Last 1 Hour
SELECT  max(value) as  TotalInvocations , sysdate-2/24  Start_Time,sysdate-1/24  End_Time --((??EMIP_BIND_END_DATE??)-2/24)  Start_Time,  ((??EMIP_BIND_END_DATE??)-1/24)  End_Time
FROM mgmt$metric_details
WHERE target_name like '/PROD%'
AND collection_timestamp >= sysdate-2/24
AND collection_timestamp <= sysdate-1/24
AND metric_column = 'compositeInstances.delta'

SOA SERVICES PERFORMANCE MONITOR - PRD - HOURLY
select Process_Name,to_number(Invocations,'9999999999999') as NUM_INSTANCES,round(to_number(Invocations,'9999999999999')/60,4) as THRUPUT_PER_MIN from (
SELECT SUBSTR(target_name, INSTR(target_name,'/', -1, 1)+1) AS Process_Name,
       max(value) as Invocations
FROM mgmt$metric_details
WHERE target_name like '/PROD%'
AND collection_timestamp >= sysdate-2/24 --TO_DATE('3/5/2014 1:00:03 PM','MM/DD/YYYY HH:MI:SS AM') --((??EMIP_BIND_END_DATE??)-2/24)
AND collection_timestamp <= sysdate-1/24 --TO_DATE('3/5/2014 2:00:03 PM','MM/DD/YYYY HH:MI:SS AM')--((??EMIP_BIND_END_DATE??)-1/24)
AND metric_column = 'compositeInstances.delta'
GROUP BY SUBSTR(target_name, INSTR(target_name,'/', -1, 1)+1)
ORDER BY to_number(Invocations,'9999999999999') desc
)
where to_number(Invocations,'9999999999999')>0

ORDER BY to_number(Invocations,'9999999999999') desc


FROM Direct Database link or Metric Extension:
Total SOA11g Composite Instances in Last 1 Hour

SELECT   COUNT(*) AS TOTAL_NUM_INSTANCES,((sysdate)-2/24)  Start_Time,  ((sysdate)-1/24)  End_Time
FROM     MY_soainfra.CUBE_INSTANCE@MY_SOA_DBLINK
WHERE     MY_soainfra.CUBE_INSTANCE.CREATION_DATE >= (sysdate)-2/24 
          AND MY_soainfra.CUBE_INSTANCE.CREATION_DATE <= (sysdate)-1/24

      AND MY_soainfra.CUBE_INSTANCE.state in (5,6)

SOA11g-SERVICES PERFORMANCE MONITOR - PRD - HOURLYSELECT PROCESS_NAME,
       DOMAIN_NAME,
/*         TO_CHAR (MIN (INSTANCE_START), 'MM/DD/YYYY HH24:MI')
            AS PROCESS_LOAD_START,
         TO_CHAR (MAX (ACTIVITY_END), 'MM/DD/YYYY HH24:MI') AS PROCESS_LOAD_END, */
         COUNT (*) AS NUM_INSTANCES,
                 COUNT (*) * 60
            / (EXTRACT (
                  SECOND FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
               + EXTRACT (
                    MINUTE FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 60
               + EXTRACT (
                    HOUR FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 3600
               + EXTRACT (
                    DAY FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 86400),
            2)
            AS THRUPUT_PER_MIN_ANOTHER, 
         ROUND (
            AVG (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_AVG,
         ROUND (
            MEDIAN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MEDIAN,
         ROUND (
            STDDEV (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_STDDEV,
         ROUND (
            MIN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MIN,
         ROUND (
            MAX (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MAX,
         ROUND (
                 EXTRACT (SECOND FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START))
               + EXTRACT (MINUTE FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 60
               + EXTRACT (HOUR FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 3600
               + EXTRACT (DAY FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 86400,
            2)
            AS LOAD_DURATION,
         SUM(GT_2SEC) AS GT2SEC,
         ROUND(100 - 100*SUM(GT_2SEC)/COUNT(*),2) AS PER_LT2SEC,
         SUM(GT_5SEC) AS GT5SEC,
         ROUND(100 - 100*SUM(GT_5SEC)/COUNT(*),2) AS PER_LT5SEC,
         SUM(GT_15SEC) AS GT15SEC,
         ROUND(100 - 100*SUM(GT_15SEC)/COUNT(*),3) AS PER_LT15SEC,
         SUM(GT_30SEC) AS GT30SEC,
         ROUND(100 - 100*SUM(GT_30SEC)/COUNT(*),3) AS PER_LT30SEC,
         ROUND(100*SUM(SUCCESS_STATE)/COUNT(*),2) AS PER_SUCCESS,
         ROUND(100*SUM(FAULTED_STATE)/COUNT(*),2) AS PER_FAULT
    FROM (  SELECT CUBE_INSTANCE.TITLE TITLE,
                   CUBE_INSTANCE.DOMAIN_NAME,
                   CUBE_INSTANCE.component_name PROCESS_NAME, 
                   CUBE_INSTANCE.CIKEY CIKEY,
                   CUBE_INSTANCE.CREATION_DATE INSTANCE_START,
                   CUBE_INSTANCE.MODIFY_DATE ACTIVITY_END,
                   DECODE(CUBE_INSTANCE.STATE,5,1,0) AS SUCCESS_STATE,
                   CASE WHEN CUBE_INSTANCE.STATE = 6 THEN 1 ELSE 0 END AS FAULTED_STATE,
                   CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 00:00:02.000000' THEN 1 ELSE 0 END AS GT_2SEC,
                 CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:05.000000' THEN 1 ELSE 0 END AS GT_5SEC,
                 CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:15.000000' THEN 1 ELSE 0 END AS GT_15SEC,
                 CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:30.000000' THEN 1 ELSE 0 END AS GT_30SEC
               FROM MY_soainfra.CUBE_INSTANCE@MY_SOA_DBLINK
             WHERE  CUBE_INSTANCE.CREATION_DATE >= ((??EMIP_BIND_END_DATE??)-2/24)
         AND CUBE_INSTANCE.CREATION_DATE <=((??EMIP_BIND_END_DATE??)-1/24)
        -- and component_name='SalesOrderEBS'
          AND CUBE_INSTANCE.state in (5,6)
          ORDER BY CUBE_INSTANCE.CREATION_DATE ASC)
GROUP BY PROCESS_NAME, DOMAIN_NAME

ORDER BY NUM_INSTANCES desc

List all metric names for SOA Composite targets
select distinct metric_name from mgmt$metric_hourly where target_type='oracle_soa_composite';


List the names of all of the SOA Composite targets
select distinct target_name from mgmt$metric_hourly where target_type='oracle_soa_composite';

List the metric columns for the soainfra_composite_label_rollup_by_revision metric
select distinct metric_column from mgmt$metric_hourly where target_type='oracle_soa_composite' and metric_name='soainfra_composite_label_rollup_by_revision';

select distinct metric_column from mgmt$metric_hourly where target_type='soa_composite_component'
select distinct metric_column from mgmt$metric_details where target_type='soa_composite_component'
select distinct metric_column from mgmt$metric_hourly where target_type='soa_composite_service'

select distinct metric_column,target_type  from mgmt$metric_hourly where target_type like '%soa%'; --and metric_name='soainfra_composite_label_rollup_by_revision';

select distinct target_type from mgmt$metric_hourly where target_type like '%soa%';

Categories: Development

Useful Queries in OEM12c PART-2

Thu, 2014-02-20 16:15
SOA/AIA 10g

From Management Views:

Total BPEL Instances in Last 1 Hour
SELECT sum(value) as TOTAL_NUM_INSTANCES, to_char(sysdate -2/24,'dd-mm-yyyy HH:MI:ss') as START_TIME ,to_char(sysdate -1/24,'dd-mm-yyyy HH:MI:ss') AS END_TIME
FROM  MGMT$METRIC_CURRENT
WHERE
  (target_name ='bpeltarget.myfullhostname1_bpel' or target_name ='bpeltarget.myfullhostname2.net_bpel')
  and metric_column ='closedInstances.load'
  and collection_timestamp > sysdate -2/24
  and collection_timestamp

Closed_Instances_Since_Last_Collection

SELECT target_name,column_label,to_char(collection_timestamp,'dd-mm-yyyy HH:MI:ss') Collected,key_value Domain,key_value2 BPEL_Process,value ClosedInstances,value/3600 "Avg Time Taken(Seconds)"
FROM  MGMT$METRIC_CURRENT
WHERE 
  (target_name ='bpeltarget.myfullhostname1_bpel' or target_name ='bpeltarget.myfullhostname2_bpel')
  and metric_column ='closedInstances.load'
  and collection_timestamp > sysdate -2/24   
  and collection_timestamp
  and to_number(value) >0
 ORDER BY to_number(value) DESC



Directly From  Database using DBLink in OEM12c to SOA/AIA Database

Total BPEL Instances in Last 1 Hour
SELECT   COUNT(*) AS TOTAL_NUM_INSTANCES,((??EMIP_BIND_END_DATE??)-2/24)  Start_Time,  ((??EMIP_BIND_END_DATE??)-1/24)  End_Time
FROM     ORABPEL.CUBE_INSTANCE@mydblink
WHERE  ORABPEL.CUBE_INSTANCE.PROCESS_ID NOT IN
                      ('AIAAsyncErrorHandlingBPELProcess',  
                       'TaskActionHandler',  
                      'TaskManager',  
                      '__QuoteAccountHoldService',  
                      '__QuoteGlobalSalesTaxService',  
                      '__QuoteInputValidationService',  
                      '__QuotePricingService',  
                      '__QuotePromiseDatesService',  
                      '__QuoteStandardizeAddressesService',  
                      '__QuoteValidateShippingService')  
AND ORABPEL.CUBE_INSTANCE.CREATION_DATE >(??EMIP_BIND_END_DATE??)-2/24 
AND ORABPEL.CUBE_INSTANCE.CREATION_DATE <(??EMIP_BIND_END_DATE??)-1/24 

AND ORABPEL.CUBE_INSTANCE.state in (5,6)


SOA SERVICES PERFORMANCE MONITOR - PRD - HOURLY
SELECT PROCESS_NAME AS PROCESS,
       OPERATION,
/*         TO_CHAR (MIN (INSTANCE_START), 'MM/DD/YYYY HH24:MI')
            AS PROCESS_LOAD_START,
         TO_CHAR (MAX (ACTIVITY_END), 'MM/DD/YYYY HH24:MI') AS PROCESS_LOAD_END, */
         COUNT (*) AS NUM_INSTANCES,
         ROUND (
            COUNT (*) * 60
            / (EXTRACT (
                  SECOND FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
               + EXTRACT (
                    MINUTE FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 60
               + EXTRACT (
                    HOUR FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 3600
               + EXTRACT (
                    DAY FROM (MAX (ACTIVITY_END) - MIN (INSTANCE_START)))
                 * 86400),
            2)
            AS THRUPUT_PER_MIN,
         ROUND (
            AVG (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_AVG,
         ROUND (
            MEDIAN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MEDIAN,
         ROUND (
            STDDEV (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_STDDEV,
         ROUND (
            MIN (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MIN,
         ROUND (
            MAX (
                 EXTRACT (SECOND FROM ACTIVITY_END - INSTANCE_START)
               + EXTRACT (MINUTE FROM ACTIVITY_END - INSTANCE_START) * 60
               + EXTRACT (HOUR FROM ACTIVITY_END - INSTANCE_START) * 3600),
            2)
            AS DURATION_MAX,
         ROUND (
                 EXTRACT (SECOND FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START))
               + EXTRACT (MINUTE FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 60
               + EXTRACT (HOUR FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 3600
               + EXTRACT (DAY FROM MAX (ACTIVITY_END) - MIN (INSTANCE_START)) * 86400,
            2)
            AS LOAD_DURATION,
         SUM(GT_2SEC) AS GT2SEC,
         ROUND(100 - 100*SUM(GT_2SEC)/COUNT(*),2) AS PER_LT2SEC,
         SUM(GT_5SEC) AS GT5SEC,
         ROUND(100 - 100*SUM(GT_5SEC)/COUNT(*),2) AS PER_LT5SEC,
         SUM(GT_15SEC) AS GT15SEC,
         ROUND(100 - 100*SUM(GT_15SEC)/COUNT(*),3) AS PER_LT15SEC,
         SUM(GT_30SEC) AS GT30SEC,
         ROUND(100 - 100*SUM(GT_30SEC)/COUNT(*),3) AS PER_LT30SEC,
         ROUND(100*SUM(SUCCESS_STATE)/COUNT(*),2) AS PER_SUCCESS,
         ROUND(100*SUM(FAULTED_STATE)/COUNT(*),2) AS PER_FAULT
    FROM (  SELECT CUBE_INSTANCE.TITLE TITLE,
                   CASE CUBE_INSTANCE.PROCESS_ID 
                        WHEN 'RDLUCommandProcess' THEN SUBSTR(CUBE_INSTANCE.TITLE, 0, INSTR(CUBE_INSTANCE.TITLE, ' ', 1, 1) - 1)
                        WHEN 'RDLUResponseProcess' THEN SUBSTR(CUBE_INSTANCE.TITLE, 0, COALESCE(NULLIF(INSTR(CUBE_INSTANCE.TITLE, ':', 1, 1),0), INSTR(CUBE_INSTANCE.TITLE, ' ', 1, 1)) - 1) 
                        ELSE 'N/A' 
                    END
                        OPERATION,
                   CUBE_INSTANCE.CIKEY CIKEY,
                   CUBE_INSTANCE.PROCESS_ID PROCESS_NAME,
                   CUBE_INSTANCE.CREATION_DATE INSTANCE_START,
                   CUBE_INSTANCE.MODIFY_DATE ACTIVITY_END,
                   CASE WHEN CUBE_INSTANCE.STATE = 5 THEN 1 ELSE 0 END AS SUCCESS_STATE,
                   CASE WHEN CUBE_INSTANCE.STATE = 6 THEN 1 ELSE 0 END AS FAULTED_STATE,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:02.000' THEN 1 ELSE 0 END AS GT_2SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:05.000' THEN 1 ELSE 0 END AS GT_5SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:15.000' THEN 1 ELSE 0 END AS GT_15SEC,
                   CASE WHEN CUBE_INSTANCE.MODIFY_DATE - CUBE_INSTANCE.CREATION_DATE > '0 0:0:30.000' THEN 1 ELSE 0 END AS GT_30SEC
              FROM ORABPEL.CUBE_INSTANCE@mydblink
             WHERE CUBE_INSTANCE.PROCESS_ID NOT IN
                      ('AIAAsyncErrorHandlingBPELProcess',  
                       'TaskActionHandler',  
                      'TaskManager',  
                      '__QuoteAccountHoldService',  
                      '__QuoteGlobalSalesTaxService',  
                      '__QuoteInputValidationService',  
                      '__QuotePricingService',  
                      '__QuotePromiseDatesService',  
                      '__QuoteStandardizeAddressesService',  
                      '__QuoteValidateShippingService')
         AND CUBE_INSTANCE.CREATION_DATE >(??EMIP_BIND_END_DATE??)-2/24 
         AND CUBE_INSTANCE.CREATION_DATE< (??EMIP_BIND_END_DATE??)-1/24 
          AND CUBE_INSTANCE.state in (5,6)
          ORDER BY CUBE_INSTANCE.CREATION_DATE ASC)
GROUP BY PROCESS_NAME, OPERATION

ORDER BY NUM_INSTANCES desc



Other Queries:
select  state, count(*) Count from ORABPEL.dlv_message@dblink  where receive_date >= sysdate -1/24  and receive_date <= sysdate  group by state  order by Count

select count(*),process_id 
from ORABPEL.invoke_message@dblink
where 
receive_Date >= sysdate -1/24   and 
receive_Date <= sysdate   and 
state in (0,1) 
Group By process_id

SELECT  PROCESS_ID,
        COUNT(*) as "count",
        STATE
FROM ORABPEL.CUBE_INSTANCE@dblink
WHERE 
CREATION_DATE >=  sysdate -1/24 AND CREATION_DATE <= sysdate
AND MODIFY_DATE - CREATION_DATE > '0 0:5:00.000000000' GROUP BY PROCESS_ID, STATE ORDER BY PROCESS_ID


SELECT W.CIKEY,W.CLUSTER_NODE_ID, W.EXP_DATE, W.CREATION_DATE,W.MODIFY_DATE,W.COUNT_ID,W.STATE, (W.MODIFY_DATE - W.CREATION_DATE) AS PROCESSING_TIME, P.PROCESS_ID 
FROM ORABPEL.WORK_ITEM@dblink W,  ORABPEL.PROCESS@dblink P
WHERE P.PROCESS_GUID = W.PROCESS_GUID
AND W.STATE       NOT IN (6,1)
AND W.CREATION_DATE >= sysdate -1/24  
AND W.CREATION_DATE <=sysdate


SELECT 
(CASE state
 WHEN 0 THEN 'INITIATED' 
 WHEN 1 THEN 'OPEN and RUNNING' 
 WHEN 2 THEN 'OPEN and SUSPENDED'  
 WHEN 3 THEN 'OPEN and FAULTED'  
 WHEN 4 THEN 'CLOSED and PENDING_CANCEL'  
 WHEN 5 THEN 'CLOSED and COMPLETED' 
 WHEN 6 THEN 'CLOSED and FAULTED' 
 WHEN 7 THEN 'CLOSED and CANCELLED' 
 WHEN 8 THEN 'CLOSED and ABORTED' 
 WHEN 9 THEN 'CLOSED and STALE' 
 ELSE 'UNKNOWN State='||state
END) as BPELstate,
  COUNT(*) COUNT,
  process_id,
  TRUNC(MAX(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) MaxTime,
  TRUNC(MIN(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) MinTime,
  TRUNC(AVG(extract(DAY FROM (modify_date-creation_date))*24*60*60 + extract(hour FROM (modify_date-creation_date))*60*60 + extract(minute FROM (modify_date-creation_date))*60 + extract(second FROM (modify_date-creation_date))),4) AvgTime
FROM ORABPEL.CUBE_INSTANCE@dblink
WHERE creation_date >= sysdate-1/24
AND creation_date   <= sysdate
AND process_id NOT IN ('VehicleOneShotNonCallProcess')
GROUP BY state,
  process_id

ORDER BY MaxTime DESC



Total Active Sessions on MW DB Servers

select count(*) Total_DB_sessions 
from v$session@mydblink
where machine in ('myfullhostname1','myfullhostname2')

Total DB Sessions

select username,machine,to_char(sysdate,'MM-DD-RRRR HH:MI:SS') currentTime, count(*) Total_DB_sessions 
from v$session@mydblink   group by username,machine order by Total_DB_sessions desc





Categories: Development

Useful Queries in OEM12c PART-1

Thu, 2014-02-20 15:59
Weblogic Info
select "MGMT$WEBLOGIC_SERVER"."CM_TARGET_NAME" as "TARGET NAME",
"MGMT$WEBLOGIC_SERVER"."JAVAVMVENDOR" as "JVM VENDOR",
"MGMT$WEBLOGIC_SERVER"."JAVAVERSION" as "JAVA VERSION",
"MGMT$WEBLOGIC_SERVER"."BINARY_WEBLOGICHOME" as "WEBLOGIC HOME",
"MGMT$WEBLOGIC_SERVER"."BINARY_HOST" as "HOST"
 from "MGMT_VIEW"."MGMT$WEBLOGIC_SERVER" "MGMT$WEBLOGIC_SERVER"
 where "MGMT$WEBLOGIC_SERVER"."BINARY_WEBLOGICHOME" is not null
   and "MGMT$WEBLOGIC_SERVER"."BINARY_HOST" is not null

CPU Utilization Daily
select "MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."METRIC_NAME" as "METRIC_NAME",
"MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP" as "ROLLUP_TIMESTAMP",
"MGMT$METRIC_DAILY"."SAMPLE_COUNT" as "SAMPLE_COUNT",
"MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
"MGMT$METRIC_DAILY"."MINIMUM" as "MINIMUM",
"MGMT$METRIC_DAILY"."MAXIMUM" as "MAXIMUM",
"MGMT$METRIC_DAILY"."STANDARD_DEVIATION" as "STANDARD_DEVIATION"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
where metric_name = 'Load'
 and column_label like '%CPU Utilization%'
 and rownum <= 20
 ORDER BY MGMT$METRIC_DAILY.MAXIMUM desc
 
CPU Utilization Current
select "MGMT$METRIC_CURRENT"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_CURRENT"."METRIC_NAME" as "METRIC_NAME",
"MGMT$METRIC_CURRENT".column_label,
   to_char("MGMT$METRIC_CURRENT".collection_timestamp,'dd-mm-yyyy HH:MI:ss') Collected,
   "MGMT$METRIC_CURRENT".key_value key_value,
   "MGMT$METRIC_CURRENT".key_value2 key_value2,
   ROUND("MGMT$METRIC_CURRENT".value,2) as "CPU%Load"
 from "MGMT_VIEW"."MGMT$METRIC_CURRENT" "MGMT$METRIC_CURRENT"
where metric_name = 'Load'
 and column_label like '%CPU Utilization%'
 and collection_timestamp > sysdate - 1/24
 and rownum <= 20

 --ORDER BY MGMT$METRIC_CURRENT.MAXIMUM desc

20 Most Common Alerts by Alert Count
       SELECT * from (
          SELECT
    metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
             count(alert_state) NEW_ID
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND target_guid = ??EMIP_BIND_TARGET_GUID??
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
         GROUP BY metric_name,column_label,target_type,decode(ALERT_STATE,'Critical',25,'Warning',20)
         ORDER BY 5 DESC
       ) WHERE ROWNUM <21 p="">

Alerts History (Target)
Displays alerts for a target over the last 24 hours.

SELECT A.metric_label METRIC_GRP,A.column_label METRIC_ID,decode(A.alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID,
(
ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp ),'009'))||'d'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24 ),24),'09'))||'h'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60 ),60),'09'))||'m'
||':'||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60*60 ),60),'09'))||'s'
) DURATION_ID, A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B
       WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID??  AND A.alert_state in ('Critical','Warning')
       AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and
            DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)
            ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
            OR
            (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)
              or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION))
            )
          )
       ORDER BY A.collection_timestamp      
     
   
Top 10 LUNs by Capacity

(SELECT nameT.value "Name", TO_NUMBER(sizeT.val) "Size" FROM
(SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name= 'LunDetails' AND metric_column= 'Name') nameT,
(SELECT key_value, TO_NUMBER(value) AS val FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='LunDetails' AND metric_column='AddressableCapacity' ORDER BY val DESC) sizeT
WHERE nameT.key_value = sizeT.key_value)

       PIE Chart:
       (SELECT nameT.value "Name", TO_NUMBER(sizeT.value) "Size" FROM (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name= 'LunDetails' AND metric_column= 'Name') nameT, (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='LunDetails' AND metric_column='AddressableCapacity') sizeT WHERE nameT.key_value = sizeT.key_value) UNION (SELECT remainingNameT.value "Name", TO_NUMBER(remainingSpaceT.value) "Size" FROM (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='SystemDetails' AND metric_column='SystemName') remainingNameT, (SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name='SystemDetails' AND metric_column='TotalRemainingSpace_GB') remainingSpaceT WHERE remainingNameT.key_value = remainingSpaceT.key_value)
     

Top 20 Heap Usage
(SELECT nameT.value "Name", TO_NUMBER(sizeT.val) "Size" FROM
(SELECT key_value, value FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name like = 'Heap Usage%' AND metric_column= 'Name') nameT,
(SELECT key_value, TO_NUMBER(value) AS val FROM mgmt$metric_current WHERE target_guid=??EMIP_BIND_TARGET_GUID?? AND metric_name like = 'Heap Usage%' AND metric_column='AddressableCapacity' ORDER BY val DESC) sizeT
WHERE nameT.key_value = sizeT.key_value)


Select * from mgmt$metric_current where metric_name like  '%Heap%'

select distinct(metric_column),metric_name from mgmt$metric_current where metric_column like  '%Heap%'
and target_type='weblogic_j2eeserver'



Heap Usage
SELECT * from (
          SELECT
    target_name,metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
           alert_state,key_Value
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
            AND  target_type='weblogic_j2eeserver'
            AND  column_label like 'Heap Usage (%)'
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">
SELECT * from (
          SELECT
    target_name,metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,round(Value,2) AS "HeapUsage%"
          FROM MGMT$METRIC_DETAILS
          WHERE target_type='weblogic_j2eeserver'
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
            AND  column_label like 'Heap Usage (%)'
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">

metric_name=jvm_memory_usage
metric_column=Java Heap Used (MB)
--and target_name like '%EMGC_OMS1%'


Weblogic 100 Top Alerts:

SELECT * from (
          SELECT
    metric_name METRIC_GRP, column_label METRIC_ID,target_type TARGET_TYPE_ID,
             decode(alert_state,'Critical',25,'Warning',20) SEVERITY_ID,
             count(alert_state) NEW_ID
          FROM mgmt$alert_history
          WHERE alert_state in('Critical','Warning')
            AND collection_timestamp >= ??EMIP_BIND_START_DATE??
            AND collection_timestamp <= ??EMIP_BIND_END_DATE??
           AND  target_type='weblogic_j2eeserver'
         GROUP BY metric_name,column_label,target_type,decode(ALERT_STATE,'Critical',25,'Warning',20)
         ORDER BY 5 DESC
       ) WHERE ROWNUM <100 p="">
jvm Heap Usage (%) weblogic_j2eeserver 20 8




OutStanding Alerts: ALL
Summary
=======
SELECT 'METRIC_ALERTS_TXT_ID' ALERT_TYPE_ID,
        NVL(SUM(decode(ALERT_STATE,'Critical',1,0)),0) CRITICAL_ID,
NVL(SUM(decode(ALERT_STATE,'Warning',1,0)),0) WARNING_ID,
        'NA_TXT_ID' INFORMATIONAL_ID,
COUNT(UNIQUE target_guid) TARGETS_AFFECTED_ID from MGMT$ALERT_CURRENT B
where B.violation_type in('Resource','Threshold Violation')
AND B.ALERT_STATE in ('Critical','Warning')

Metric Alerts

SELECT A.TARGET_NAME TARGET_NAME_ID,A.TYPE_DISPLAY_NAME TARGET_TYPE_ID,
A.METRIC_LABEL METRIC_GRP, A.COLUMN_LABEL METRIC_ID,decode(A.ALERT_STATE,'Critical',25,'Warning',20) SEVERITY_ID,
MGMT_VIEW_UTIL.ADJUST_TZ(A.COLLECTION_TIMESTAMP,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) OPEN_SINCE_ID,
A.MESSAGE MESSAGE_ID from MGMT$ALERT_CURRENT A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND
A.violation_type in('Resource','Threshold Violation') and A.ALERT_STATE in ('Critical','Warning')
order by collection_timestamp



Weblogic Metrics for BI-Pug Reports

select "MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
"MGMT$METRIC_DAILY"."METRIC_LABEL" as "METRIC_LABEL",
"MGMT$METRIC_DAILY"."COLUMN_LABEL" as "COLUMN_LABEL",
"MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP" as "ROLLUP_TIMESTAMP"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
 where "MGMT$METRIC_DAILY"."METRIC_LABEL" like '%Server Servlet%JSP Metrics%'
   and "MGMT$METRIC_DAILY"."COLUMN_LABEL" like '%Request Processing Time %'
   and "MGMT$METRIC_DAILY"."TARGET_NAME" like '%MDA%Managed%'
   and "MGMT$METRIC_DAILY"."TARGET_TYPE" like 'weblogic_j2eeserver'
   and "MGMT$METRIC_DAILY"."TARGET_GUID" =:list_of_wls_param
 
   list_of_wls_lov
   select "MGMT$TARGET"."TARGET_NAME" as "TARGET_NAME",
"MGMT$TARGET"."TARGET_GUID" as "TARGET_GUID"
 from "MGMT_VIEW"."MGMT$TARGET" "MGMT$TARGET"
 where "MGMT$TARGET"."TARGET_TYPE" like 'weblogic_j2eeserver'


 list_of_wls_param

 select "MGMT$METRIC_DAILY"."TARGET_NAME" as "TARGET_NAME",
"MGMT$METRIC_DAILY"."TARGET_TYPE" as "TARGET_TYPE",
"MGMT$METRIC_DAILY"."METRIC_LABEL" as "METRIC_LABEL",
"MGMT$METRIC_DAILY"."COLUMN_LABEL" as "COLUMN_LABEL",
"MGMT$METRIC_DAILY"."AVERAGE" as "AVERAGE",
         "MGMT$METRIC_DAILY"."MINIMUM" as "MINIMUM",
"MGMT$METRIC_DAILY"."MAXIMUM" as "MAXIMUM",
"MGMT$METRIC_DAILY"."STANDARD_DEVIATION",
         TO_CHAR("MGMT$METRIC_DAILY"."ROLLUP_TIMESTAMP", 'DD-MON-YYYY') as "ROLLUP_TIMESTAMP"
 from "MGMT_VIEW"."MGMT$METRIC_DAILY" "MGMT$METRIC_DAILY"
 where "MGMT$METRIC_DAILY"."TARGET_TYPE" like 'weblogic_j2eeserver'
   and "MGMT$METRIC_DAILY"."METRIC_LABEL" like 'Server Servlet%JSP Metrics%'
   and "MGMT$METRIC_DAILY"."COLUMN_LABEL" like 'Request Processing Time%'
   and "MGMT$METRIC_DAILY"."TARGET_GUID" =:wls_server_param
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7
ORDER BY ROLLUP_TIMESTAMP


select "MGMT$TARGET"."TARGET_NAME" as "TARGET_NAME",
"MGMT$TARGET"."TARGET_GUID" as "TARGET_GUID"
 from "MGMT_VIEW"."MGMT$TARGET" "MGMT$TARGET"
 where "MGMT$TARGET"."TARGET_TYPE" like 'weblogic_j2eeserver'


 =================================================

 select "MGMT$WEBLOGIC_DOMAIN"."NAME" as "NAME",
"MGMT$J2EE_APPLICATION"."CM_TARGET_NAME" as "CM_TARGET_NAME",
"MGMT$J2EE_APPLICATION"."CM_TARGET_TYPE" as "CM_TARGET_TYPE"
 from "MGMT_VIEW"."MGMT$J2EE_APPLICATION" "MGMT$J2EE_APPLICATION",
"MGMT_VIEW"."MGMT$WEBLOGIC_DOMAIN" "MGMT$WEBLOGIC_DOMAIN"



select "MGMT$WEBLOGIC_DOMAIN"."NAME" as "NAME",
"MGMT$WEBLOGIC_DOMAIN"."CM_TARGET_GUID" as "CM_TARGET_GUID",
"MGMT$WEBLOGIC_DOMAIN"."CM_TARGET_NAME" as "CM_TARGET_NAME"
 from "MGMT_VIEW"."MGMT$WEBLOGIC_DOMAIN" "MGMT$WEBLOGIC_DOMAIN"


 ==========================================================

 OSB Error Metrics:
 SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


==================OSB METRICS=================================
OSB Proxy Service Response:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) AVG_RSP_TM,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'avgStat'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Proxy Service Err:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Proxy Service Message:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_proxy_service'
AND METRIC_NAME              = 'ProxyServices_TC'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Business Service Response:
SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) AVG_RESP_TM,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'avgStat'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Business Service Err:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Business Service Message:

SELECT rawtohex(TARGET_GUID) target_guid, TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE like 'osb_business_service'
AND METRIC_NAME              = 'BusinessServices_TC'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP


OSB Proxy Service:
select rawtohex(entity_guid) pxy_svc_guid, entity_name pxy_svc_nm, parent_me_name osb_name_pxy from mgmt$manageable_entities where entity_type = 'osb_proxy_service'

OSB Business Service:
select rawtohex(entity_guid) bus_svc_guid, entity_name bus_svc_nm, parent_me_name osb_name from mgmt$manageable_entities where entity_type = 'osb_business_service'

OSB Error Per Minute:
SELECT TARGET_NAME,
  ROUND(AVERAGE, 2) ERR_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE = 'bea_alsb'
AND METRIC_NAME              = 'OSB_ProxyServices_rollup'
AND METRIC_COLUMN            = 'errorStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

OSB Req Per Min:
SELECT TARGET_NAME,
  ROUND(AVERAGE, 2) MSG_PER_MIN,
  TO_CHAR(ROLLUP_TIMESTAMP, 'DD/MM/YYYY') METRIC_COLLECTION_DATE
FROM MGMT$METRIC_DAILY
WHERE TARGET_TYPE = 'bea_alsb'
AND METRIC_NAME              = 'OSB_ProxyServices_rollup'
AND METRIC_COLUMN            = 'countStat.rate'
AND ((:P_TIME_PERIOD         = '7'
AND ROLLUP_TIMESTAMP        >= SYSDATE - 7)
OR (:P_TIME_PERIOD           = 31
AND ROLLUP_TIMESTAMP        >= SYSDATE - 31)
OR (:P_TIME_PERIOD           = 90
AND ROLLUP_TIMESTAMP        >= SYSDATE - 90))
ORDER BY ROLLUP_TIMESTAMP

Categories: Development

OEM12c: Searching a Target without a LifeCycle status property

Mon, 2014-02-10 11:16
Steps:

Enterprise->Configurations->Search

Create->
Target Type->Choose Host
Click Properties to add a  property and choose
PropertyName  "Contains"  LifeCycleStatus

Click "advanced Options"  Choose "NOT EXISTS"

Save as " Host_LifeCycle_Null"

SQL for searching Production hosts with Null LifecycleStatus:
-------------------------------------------------------------------------------
SELECT
        s1.target_name
FROM
        MGMT$ECM_CURRENT_SNAPSHOTS s1gen1,
        MGMT$TARGET s1
WHERE
        (
                s1gen1.TARGET_GUID = s1.TARGET_GUID
            AND
                (
                        NOT EXISTS
                        (
                                SELECT
                                        s2.PROPERTY_NAME
                                FROM
                                        CM$EM_TPROPS_ECM_VIEW s2
                                WHERE
                                        (
                                                s1gen1.ECM_SNAPSHOT_ID = s2.ECM_SNAPSHOT_ID
                                            AND
                                                (
                                                        (
                                                                NLS_UPPER( s2.PROPERTY_NAME) LIKE NLS_UPPER('%'
                                                                ||'orcl_gtp_lifecycle_status'
                                                                ||'%')
                                                        )
                                                )
                                        )
                        )
                )
            AND s1.TARGET_TYPE       = 'host'
            AND s1gen1.SNAPSHOT_TYPE = 'orcl_tp_config'
            AND UPPER(s1.target_name) LIKE '%P.DOMAINNAME%'
        )
-------------------------------------------------------------------------------
Categories: Development

Identifying the Java Thread which is consuming high CPU

Thu, 2014-01-30 11:31
In Summary:

  1. Run Top on Linux or Use Process Explorer on Windows to identify the Top CPU Process
  2. Press Shift -H to enable the Thread view or use "top -H -p ". Watch for several minutes to make sure the particular thread is always consuming top CPU%
  3. Convert the top Thread PID to HEX, you can use online tool or Calculator:         http://www.binaryhexconverter.com/decimal-to-hex-converter
  4. Take the several  Thread Dump for the Java PID using jstack or kill -3 when the thread consumes top % CPU to check which method consumes the CPU cycle
  5. Match the HEX Thread in the Jstack thread dump



Here are some of the articles which helps in identifying the High CPU consuming Java threads:

http://code.nomad-labs.com/2010/11/18/identifying-which-java-thread-is-consuming-most-cpu/
https://bitbucket.org/pjtr/topthreads/overview

http://java.dzone.com/articles/which-java-thread-consumes-my

https://code.google.com/p/jvmtop/

http://javaeesupportpatterns.blogspot.com/2011/12/prstat-solaris-pinpoint-high-cpu-java.html

You can also use other tools like Jconsole with plugins, visualVM, Oracle EM JVMD

Categories: Development