D Identifying the Jobs That Will Not Run in Enterprise Manager for 2-System Upgrade Approach

This appendix describes how you can identify the jobs that will not run in the existing Enterprise Manager system and in the upgraded Enterprise Manager system.

D.1 Identifying the Jobs That Will Not Run in the New, Upgraded Enterprise Manager System

The following SQL query will help identify any jobs that are currently blocked from running if only certain Oracle Management Agents for their targets are migrated. Run the query in Enterprise Manager Cloud Control.At the end of the query, is an optional query for modifications that can be used to give a list of Oracle Management Agents that need to be migrated.

For more information on the need for identifying jobs that will not run in the upgraded or new Enterprise Manager System, refer to Chapter 3, "Things to Know About Upgrading an Enterprise Manager System"

SET TRIMSPOOL ON
SET VERIFY   OFF
SET LINESIZE  80
SET PAGESIZE 500
 
 
PROMPT ======================
PROMPT Valid on "new" 12.1 EM
PROMPT ======================
PROMPT List jobs that will not be run on either system
PROMPT due to a partially migrated target list
PROMPT ======================
 
WITH
-- list of migrated targets
migrated_targets AS
(
SELECT   target_guid
FROM     EM_CURRENT_AVAILABILITY
WHERE    current_status     = 4 -- G_STATUS_UNREACHABLE
AND      current_sub_status = 1 -- G_SUB_STATUS_UNMIGRATED
),
-- list of job related migrated targets
migrate_job_targets AS
(
SELECT   job_id, execution_id, target_guid
FROM     MGMT$JOB_EXECUTION_HISTORY JOIN
         migrated_targets           USING(target_guid)
WHERE    STATE_CYCLE NOT IN ('FINISHED', 'RUNNING')
),
-- list of jobs against the migrate job targets
effected_jobs AS
(
SELECT  count(1) migrated_target_count, job_id, execution_id
FROM    migrate_job_targets
GROUP BY job_id, execution_id
),
-- list of jobs with some unmigrated targets and some migrate targets
partly_migrated_jobs AS
(
SELECT je.job_id,
       je.execution_id,
       je.job_name,
       je.job_owner,
       je.job_type,
       je.target_name,
       je.target_type,
       je.target_guid
FROM   MGMT$JOB_EXECUTION_HISTORY je,
       effected_jobs              ej
WHERE  je.job_id       = ej.job_id
AND    je.execution_id = ej.execution_id
AND    target_guid     NOT IN
                     ( SELECT target_guid
                       FROM   migrate_job_targets
                     )
)
-- list jobs, targets and agents
SELECT job_name, target_name, target_type
FROM   partly_migrated_jobs
ORDER BY job_name, target_type, target_name;
 
/*
 Could change the last select to
 SELECT DISTINCT job_name, job_owner
 or
 SELECT DISTINCT target_guid
 
 to get the distinct list of jobs or target guids.
 
 or
 SELECT DISTINCT emd_url
 FROM   partly_migrated_jobs JOIN
        MGMT_TARGETS         USING (target_guid)
 
 to get the distinct list of agents
*/

D.2 Identifying the Jobs That Will Not Run in the Existing Enterprise Manager System

The following SQL query will help identify any jobs that are currently blocked from running if only certain Oracle Management Agents for their targets are migrated. Run the query in Enterprise Manager Cloud Control.At the end of the query, is an optional query for modifications that can be used to give a list of Oracle Management Agents that need to be migrated.

For more information on the need for identifying jobs that will not run in the existing Enterprise Manager System, refer to Chapter 3, "Things to Know About Upgrading an Enterprise Manager System"

SET TRIMSPOOL ON
SET VERIFY   OFF
SET LINESIZE  80
SET PAGESIZE 500
 
 
PROMPT ======================
PROMPT Valid on "original" EM
PROMPT ======================
PROMPT List jobs that will not be run on either system
PROMPT due to a partially migrated target list
PROMPT ======================
PROMPT Enter a quoted, comma separated list of agent guids about to be
migrated
PROMPT OR any quoted character to list currently stuck jobs
PROMPT ======================
 
WITH
-- list of targets the user is about to migrate
migrating_targets AS
(
SELECT   target_name, target_type, target_guid
FROM     MGMT_TARGETS t
WHERE    t.emd_url IN ( SELECT emd_url
                        FROM   MGMT_TARGETS
                        WHERE  target_guid IN (&p_agent_guid_list)
                        AND    target_type = 'oracle_emd') --MGMT_GLOBAL.G_AGENT_TARGET_TYPE )
),
-- list of already migrated targets
migrated_targets AS
(
SELECT   target_name, target_type, target_guid
FROM     PRE_UPGC_TGT_SW
WHERE    STATUS  =  'AVAILABLE'
-- NOTE: neither system will monitor targets <> 'AVAILABLE'
--       How to treat them here?
--       For now, treat them as unmigrated
AND      emd_url IN ( SELECT emd_url
                      FROM   PRE_UPGC_AGT_STAT_MGMT JOIN
                             MGMT_TARGETS           USING(target_guid)
                      WHERE  SWITCH_STATUS='STATUS_SUCCESS'
                      OR     SWITCH_STATUS='STATUS_IN_PROGRESS')
),
-- list of job related targets (either migrating or already migrated)
migrate_job_targets AS
(
SELECT   -- use DISTINCT to cover target overlap case
DISTINCT job_id, execution_id, target_guid
FROM     MGMT$JOB_EXECUTION_HISTORY JOIN
       ( SELECT target_guid FROM migrating_targets
         UNION ALL
         SELECT target_guid FROM migrated_targets
       ) USING(target_guid)
WHERE    status  NOT IN ('Error',
                         'Failed',
                         'Succeeded',
                         'Skipped',
                         'Stopped')
),
-- list of jobs against the migrate job targets
effected_jobs AS
(
SELECT  count(1) migrated_target_count, job_id, execution_id
FROM    migrate_job_targets
GROUP BY job_id, execution_id
),
-- list of jobs with some unmigrated targets and some migrate targets
partly_migrated_jobs AS
(
SELECT je.job_id,
       je.execution_id,
       je.job_name,
       je.job_owner,
       je.job_type,
       je.target_name,
       je.target_type,
       je.target_guid
FROM   MGMT$JOB_EXECUTION_HISTORY je,
       effected_jobs              ej
WHERE  je.job_id       = ej.job_id
AND    je.execution_id = ej.execution_id
AND    target_guid     NOT IN
                     ( SELECT target_guid
                       FROM   migrate_job_targets
                     )
)
-- list jobs, targets and agents
SELECT job_name, target_name, target_type
FROM   partly_migrated_jobs
ORDER BY job_name, target_type, target_name;
 
/*
 Could change the last select to
 SELECT DISTINCT job_name, job_owner
 or
 SELECT DISTINCT target_guid
 
 to get the distinct list of jobs or target guids.
 
 or
 SELECT DISTINCT emd_url
 FROM   partly_migrated_jobs JOIN
        MGMT_TARGETS         USING (target_guid)
 
 to get the distinct list of agents
*/

/*
to test already migrated target part, do
update PRE_UPGC_AGT_STAT_MGMT set SWITCH_STATUS ='STATUS_SUCCESS' where target_guid = (select target_guid from mgmt_targets where target_type = 'oracle_emd' and emd_url = (select emd_url from mgmt_targets where target_name = 'db.example.com'));
 
but DO NOT COMMIT
rollback when done.
*/