この付録では、既存のEnterprise Managerシステムおよびアップグレード後のEnterprise Managerシステムで実行されないジョブの特定方法を説明します。
次のSQL問合せは、対象のターゲットについて特定のOracle Management Agentが移行されている場合でも、現在、実行を妨げられているジョブの特定に役立ちます。問合せをEnterprise Manager Cloud Controlで実行します。問合せの最後に、移行が必要なOracle Management Agentのリストの提供に使用できる変更のための、オプションの問合せがあります。
アップグレード後または新規のEnterprise Managerシステムで実行されないジョブを特定する必要性の詳細は、第2章「作業開始前の予備知識」を参照してください。
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 */
次のSQL問合せは、対象のターゲットについて特定のOracle Management Agentが移行されている場合でも、現在、実行を妨げられているジョブの特定に役立ちます。問合せをEnterprise Manager Cloud Controlで実行します。問合せの最後に、移行が必要なOracle Management Agentのリストの提供に使用できる変更のための、オプションの問合せがあります。
既存のEnterprise Managerシステムで実行されないジョブを特定する必要性の詳細は、第2章「作業開始前の予備知識」を参照してください。
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. */