D 2システム・アップグレード方式に対してEnterprise Managerで実行されないジョブの特定

この付録では、既存のEnterprise Managerシステムおよびアップグレード後のEnterprise Managerシステムで実行されないジョブの特定方法を説明します。

新しい、アップグレード後のEnterprise Managerシステムで実行されないジョブの特定
既存のEnterprise Managerシステムで実行されないジョブの特定

D.1 新しい、アップグレード後のEnterprise Managerシステムで実行されないジョブの特定

次のSQL問合せは、対象のターゲットについて特定のOracle Management Agentが移行されている場合でも、現在、実行を妨げられているジョブの特定に役立ちます。問合せをEnterprise Manager Cloud Controlで実行します。問合せの最後に、移行が必要なOracle Management Agentのリストの提供に使用できる変更のための、オプションの問合せがあります。

アップグレード後または新規のEnterprise Managerシステムで実行されないジョブを特定する必要性の詳細は、第3章「作業開始前の予備知識」を参照してください。

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 既存のEnterprise Managerシステムで実行されないジョブの特定

既存のEnterprise Managerシステムで実行されないジョブを特定する必要性の詳細は、第3章「作業開始前の予備知識」を参照してください。

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.us.oracle.com'));
 
but DO NOT COMMIT
rollback when done.
*/