Rem Rem $Header: cpf_policy_pkgbodys.sql 09-sep-2007.09:43:28 rtakeish Exp $ Rem Rem cpf_policy_pkgbody.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem cpf_policy_pkgbody.sql Rem Rem DESCRIPTION Rem PL/SQL for checking status of critical job Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rtakeish 09/05/07 - fixing performance bug6379434 Rem abhalla 08/02/07 - fixing performance bug 6193428 Rem abhalla 05/22/07 - bug fix 5468724 - handling host blackout case, Rem modifying procedure GET_ADV_HOME_COUNT Rem abhalla 09/19/06 - adding index as a hint in count query for Rem improving performance Rem abhalla 08/07/06 - Backport abhalla_coretxn4 from main Rem abhalla 04/13/06 - Backport abhalla_bug-5151832 from main Rem abhalla 11/23/05 - Backport abhalla_cpfbugs from main Rem pdasika 11/21/05 - Backport pdasika_bug-4690073 from main Rem abhalla 07/06/06 - fixing bug 5174330, inserting timestamp of Rem RFM into mgmt_parameters as part of RFM job Rem abhalla 04/11/06 - Bug Fix 5151832, Adding a Table cast in call Rem to CPF_QUERY Rem abhalla 10/19/05 - taking care of Patch id Duplication Rem pdasika 10/05/05 - bugfix 4648361 Rem abhalla 09/08/05 - bug fix 4580045 Rem pdasika 09/11/05 - Changes for improving RFM Rem abhalla 09/02/05 - bugfix - 4587611 Rem pdasika 09/04/05 - Bug 4569993 Rem abhalla 08/24/05 - modifying MGMT_CPF_METRIC_SOURCE, adding code Rem for a column - IS_VALID Rem abhalla 08/27/05 - Adding new functions Rem get_exclusive_lock and release_lock Rem abhalla 08/15/05 - Improving performance of Rem SETUP_CPF_ADV_HOME_PATCH, bug fix-4536162 Rem abhalla 08/03/05 - bug fix- 4466468, modifying Rem SETUP_CPF_ADV_HOME_PATCH-taking care of Rem different home target types Rem abhalla 07/13/05 - adding three columns to Rem mgmt_bug_adv_home_patch: patch_id, Rem platform_id,release_id Rem abhalla 07/07/05 - Added code for SETUP_CPF_METRIC_SOURCE, Rem to populate MGMT_CPF_METRIC_SOURCE table Rem gsbhatia 06/26/05 - New repmgr header impl Rem abhalla 06/14/05 - bug fix 4260363 Rem apbharga 05/05/05 - prereq field is getting wrongly populated Rem apbharga 03/13/05 - remove check for type meta version Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ktlaw 01/11/05 - add repmgr header Rem mningomb 01/03/05 - Changes made in call to run_collection to (i) not store metric (ii) do bulk policy evaluation Rem mningomb 12/15/04 - mningomb_cpf_policy Rem mningomb 12/07/04 Created Rem CREATE OR REPLACE PACKAGE BODY CPF_POLICY AS -------------------------------------------------------------------------------- -- Does critical patch policy evaluation for all hosts -- PROCEDURE RUN_CPF_POLICY_EVALUATION IS l_targets mgmt_target_array := mgmt_target_array(); l_metric_name varchar2(40) := 'critcal_patch_advisories_metric'; MODULE_NAME VARCHAR2(20) := 'CPF_POLICY'; startTime timeStamp; BEGIN startTime := sysdate; CPF_POLICY.RUN_SETUP_CPF_ADV_HOME_PATCH; IF emdw_log.p_is_debug_set THEN emdw_log.debug('Time taken to run CPF_POLICY.SETUP_CPF_ADV_HOME_PATCH = ' || (sysdate - startTime), MODULE_NAME); END IF ; startTime := sysdate; CPF_POLICY.SETUP_CPF_METRIC_SOURCE; IF emdw_log.p_is_debug_set THEN emdw_log.debug('Time taken to run CPF_POLICY.SETUP_CPF_METRIC_SOURCE = ' || (sysdate - startTime), MODULE_NAME); END IF ; startTime := sysdate; SELECT mgmt_target_obj.new(TARGET_NAME,TARGET_TYPE) bulk collect into l_targets FROM MGMT_TARGETS WHERE TARGET_TYPE = MGMT_GLOBAL.G_HOST_TARGET_TYPE; IF emdw_log.p_is_debug_set THEN emdw_log.debug('Time taken to bulk collect the targets = ' || (sysdate - startTime), MODULE_NAME); END IF ; startTime := sysdate; MGMT_COLLECTION.RUN_COLLECTION( p_targets_list => l_targets, p_metric_name => l_metric_name, p_coll_name => l_metric_name, p_store_metric => MGMT_GLOBAL.G_FALSE); IF emdw_log.p_is_debug_set THEN emdw_log.debug('Time taken to do buld CPF policy violation = ' || (sysdate - startTime), MODULE_NAME); END IF ; END; -------------------------------------------------------------------------------- -- This method is specific to the critical patch advisory functionality -- policy. It is the function that is called by that policy to get violations. -- -- Returns a MGMT_BUG_ADVISORY_VIOLATIONS table. -- FUNCTION CPF_QUERY(p_targetGuid in RAW := NULL) RETURN MGMT_BUG_ADVISORY_VIOLATIONS IS l_cpf_table MGMT_BUG_ADVISORY_VIOLATIONS; l_cpf_obj MGMT_BUG_ADVISORY_VIOLATION; CURSOR C1 IS select unique a.advisory_name as ADVISORY_NAME, a.impact as IMPACT, case when ap.patch_type = 'Patchset' then ap.patch_id || '(' || ap.patch_type || ' ' || r.release_name || ')' when nt.prereq_release is not null then ap.patch_id || '(Requires patchset ' || r.release_name || ')' else to_char(ap.patch_id) end AS PATCH, a.abstract as ADVISORY_ABSTRACT, nt.HOME_LOCATION_DISPLAY, nt.HOME_LOCATION, nt.HOST_NAME, a.URL as ADVISORY_URL, nt.PATCH_GUID, nt.TARGET_GUID, nt.CONTAINER_GUID, nt.BUG_NUMBER, nt.PATCH_VALID_STATUS from mgmt_bug_adv_home_patch nt, mgmt_bug_patch_platform pp, mgmt_bug_available_patch ap, mgmt_bug_advisory a, mgmt_aru_releases r where pp.patch_guid = nt.patch_guid and pp.ap_guid = ap.ap_guid and a.advisory_name = nt.advisory_name and r.release_id = ap.release_id order by --nt.HOST_NAME,nt.home_location,a.advisory_name; nt.container_guid, a.advisory_name, nt.PATCH_GUID; CURSOR C2(p_targetGuid in RAW) IS select unique a.advisory_name as ADVISORY_NAME, a.impact as IMPACT, case when ap.patch_type = 'Patchset' then ap.patch_id || '(' || ap.patch_type || ' ' || r.release_name || ')' when nt.prereq_release is not null then ap.patch_id || '(Requires patchset ' || r.release_name || ')' else to_char(ap.patch_id) end AS PATCH, a.abstract as ADVISORY_ABSTRACT, nt.HOME_LOCATION_DISPLAY, nt.HOME_LOCATION, nt.HOST_NAME, a.URL as ADVISORY_URL, nt.PATCH_GUID, nt.TARGET_GUID, nt.CONTAINER_GUID, nt.BUG_NUMBER, nt.PATCH_VALID_STATUS from mgmt_bug_adv_home_patch nt, mgmt_bug_patch_platform pp, mgmt_bug_available_patch ap, mgmt_bug_advisory a, mgmt_aru_releases r where pp.patch_guid = nt.patch_guid and pp.ap_guid = ap.ap_guid and a.advisory_name = nt.advisory_name and r.release_id = ap.release_id and nt.target_guid = p_targetGuid order by --nt.HOST_NAME,nt.home_location,a.advisory_name; nt.container_guid, a.advisory_name, nt.PATCH_GUID; c1Rec C1%ROWTYPE; c2Rec C2%ROWTYPE; BEGIN l_cpf_table := MGMT_BUG_ADVISORY_VIOLATIONS(); IF p_targetGuid IS NULL THEN OPEN C1; LOOP FETCH C1 INTO c1Rec; EXIT WHEN c1%NOTFOUND; IF l_cpf_table.LAST IS NULL OR l_cpf_table(l_cpf_table.LAST).CONTAINER_GUID <> c1Rec.CONTAINER_GUID OR l_cpf_table(l_cpf_table.LAST).ADVISORY_NAME <> c1Rec.ADVISORY_NAME THEN l_cpf_table.EXTEND; l_cpf_table(l_cpf_table.LAST) := MGMT_BUG_ADVISORY_VIOLATION(c1Rec.ADVISORY_NAME, c1Rec.IMPACT, c1Rec.PATCH, c1Rec.ADVISORY_ABSTRACT, c1Rec.HOME_LOCATION_DISPLAY, c1Rec.HOME_LOCATION, c1Rec.HOST_NAME, c1Rec.ADVISORY_URL, c1Rec.PATCH_GUID||':'||c1Rec.BUG_NUMBER, c1Rec.PATCH_GUID, c1Rec.TARGET_GUID, c1Rec.CONTAINER_GUID, c1Rec.PATCH_VALID_STATUS); ELSE IF l_cpf_table(l_cpf_table.LAST).REF_PATCH_GUID = c1Rec.PATCH_GUID THEN l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS := l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS || ':' || c1Rec.BUG_NUMBER; ELSE l_cpf_table(l_cpf_table.LAST).PATCHES := l_cpf_table(l_cpf_table.LAST).PATCHES || ',' || c1Rec.PATCH; l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS := l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS || ',' || c1Rec.PATCH_GUID || ':' || c1Rec.BUG_NUMBER; l_cpf_table(l_cpf_table.LAST).REF_PATCH_GUID := c1Rec.PATCH_GUID; END IF; IF c1Rec.PATCH_VALID_STATUS = 'Y' AND l_cpf_table(l_cpf_table.LAST).PATCH_VALID_STATUS <> 'Y' THEN l_cpf_table(l_cpf_table.LAST).PATCH_VALID_STATUS := 'Y'; END IF; END IF; END LOOP; CLOSE C1; ELSE OPEN C2(p_targetGuid); LOOP FETCH C2 INTO c2Rec; EXIT WHEN c2%NOTFOUND; IF l_cpf_table.LAST IS NULL OR l_cpf_table(l_cpf_table.LAST).CONTAINER_GUID <> c2Rec.CONTAINER_GUID OR l_cpf_table(l_cpf_table.LAST).ADVISORY_NAME <> c2Rec.ADVISORY_NAME THEN l_cpf_table.EXTEND; l_cpf_table(l_cpf_table.LAST) := MGMT_BUG_ADVISORY_VIOLATION(c2Rec.ADVISORY_NAME, c2Rec.IMPACT, c2Rec.PATCH, c2Rec.ADVISORY_ABSTRACT, c2Rec.HOME_LOCATION_DISPLAY, c2Rec.HOME_LOCATION, c2Rec.HOST_NAME, c2Rec.ADVISORY_URL, c2Rec.PATCH_GUID||':'||c2Rec.BUG_NUMBER, c2Rec.PATCH_GUID, c2Rec.TARGET_GUID, c2Rec.CONTAINER_GUID, c2Rec.PATCH_VALID_STATUS); ELSE IF l_cpf_table(l_cpf_table.LAST).REF_PATCH_GUID = c2Rec.PATCH_GUID THEN l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS := l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS || ':' || c2Rec.BUG_NUMBER; ELSE l_cpf_table(l_cpf_table.LAST).PATCHES := l_cpf_table(l_cpf_table.LAST).PATCHES || ',' || c2Rec.PATCH; l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS := l_cpf_table(l_cpf_table.LAST).PATCH_GUIDS || ',' || c2Rec.PATCH_GUID || ':' || c2Rec.BUG_NUMBER; l_cpf_table(l_cpf_table.LAST).REF_PATCH_GUID := c2Rec.PATCH_GUID; END IF; IF c2Rec.PATCH_VALID_STATUS = 'Y' AND l_cpf_table(l_cpf_table.LAST).PATCH_VALID_STATUS <> 'Y' THEN l_cpf_table(l_cpf_table.LAST).PATCH_VALID_STATUS := 'Y'; END IF; END IF; END LOOP; CLOSE C2; END IF; RETURN l_cpf_table; END CPF_QUERY; ------------------------------------------------------------------------------------------ -- This method is used to do a preanalysis of the critical patches for homes. -- It stores the information in a table called MGMT_BUG_ADV_HOME_PATCH. It should -- be called prior to doing the policy evaluation for critical patches. The -- critical patch policy actually queries the MGMT_BUG_ADV_HOME_PATCH table. -- PROCEDURE SETUP_CPF_ADV_HOME_PATCH( p_homeRecords in CPF_HOME_ARRAY, p_deleteFlag in NUMBER) IS BEGIN -- Delete the rows only if it is the first time it is entering the procedure IF p_deleteFlag = 1 THEN DELETE FROM MGMT_BUG_ADV_HOME_PATCH; END IF; -- First insert all (if p_targetguid is null) or all for that targetguid. -- Hint is due to poor performance in 9015. Don't really want to do this but -- performance gurus suggest doing so. Works fine in 9.2 -- -- Platform decode table: -- Operating System OUIid ARUid -- ========================================================= -- AIX5L Based Systems (64-bit) 610 212 -- HP Tru64 UNIX 87 87 -- HP-UX Itaninum 197 197 -- HP-UX PA-RISC (64-bit) 2 59 -- IBM zSeries Based Linux 211 209 -- Linux Itaninum 110 214 -- Linux x86 46 46 -- Microsoft Windows (32-bit) 912 912 -- Microsoft Windows (64-bit) 208 208 -- Solaris Operating System (SPARC 32-bit) 453 453 -- Solaris Operating System (SPARC 64-bit) 23 23 -- Solaris Operating System (x86) 173 173 -- INSERT INTO MGMT_BUG_ADV_HOME_PATCH ( ADVISORY_NAME, BUG_NUMBER, HOST_NAME, HOME_LOCATION, HOME_LOCATION_DISPLAY, PATCH_GUID, PREREQ_RELEASE, TARGET_GUID, PATCH_ID, PATCH_RELEASE_ID, PATCH_PLATFORM_ID, CONTAINER_GUID, PATCH_VALID_STATUS) SELECT advisories.ADVISORY_NAME as ADVISORY_NAME, advisories.BUG_NUMBER as BUG_NUMBER, homes.HOME_HOST as HOST_NAME, homes.HOME_DIRECTORY as HOME_LOCATION, homes.HOME_NAME as HOME_LOCATION_DISPLAY, patches.PATCH_GUID as PATCH_GUID, case when ((patches.PATCH_TYPE = 'Patchset') or (instr(homes.HOME_VERSION, aru_rel.RELEASE_NAME) <> 1)) then aru_rel.RELEASE_NAME else null end as PREREQ_RELEASE, homes.HOST_TARGET_GUID as TARGET_GUID, patches.PATCH_ID as PATCH_ID, patches.PATCH_RELEASE_ID as PATCH_RELEASE_ID, patches.PATCH_PLATFORM_ID as PATCH_PLATFORM_ID, homes.HOME_ID as CONTAINER_GUID, patches.PATCH_VALID_STATUS as PATCH_VALID_STATUS FROM mgmt_aru_releases aru_rel, mgmt_aru_products aru_products, mgmt_bug_advisory_bug advisories, mgmt$cpf_patch_info patches, TABLE(CAST(p_homeRecords as CPF_HOME_ARRAY)) homes WHERE patches.patch_fixes_bug = advisories.bug_number and (patches.patch_platform_id = 2000 or (homes.home_platform_type = '0' and homes.home_aru_platform = patches.patch_platform_id) or (homes.home_platform_type = '1' and patches.patch_platform_id in (453, 23))) and aru_products.em_target_type = homes.HOME_TARGET_TYPE and patches.patch_product_id = aru_products.product_id and aru_rel.release_id = patches.patch_release_id and exists( select 1 from mgmt_bug_fix_applic_comp_list facl where facl.ap_guid = patches.patch_ap_guid and facl.bug_number = patches.patch_fixes_bug and not exists (select 1 from mgmt_aru_oui_components p, mgmt_bug_fix_applicable_comp fac where fac.component_list_guid = facl.component_list_guid and fac.oui_component_release_id = p.component_id and not exists (select 1 from mgmt_inv_component c, mgmt_inv_versioned_patch patch where c.container_guid = homes.home_id and c.component_guid = patch.component_guid(+) and p.component_name = c.name and p.component_release = decode(patch.version, NULL, c.version, patch.version) and rownum = 1 ) and rownum = 1 ) and not exists( select 1 from mgmt_inv_patch ip, mgmt_inv_patch_fixed_bug ibf where ip.container_guid = homes.home_id and ip.patch_guid = ibf.patch_guid and ibf.bug_number = facl.bug_number and rownum = 1) and rownum = 1); END SETUP_CPF_ADV_HOME_PATCH; ------------------------------------------------------------------------------------------ -- This method is used to do a preanalysis of the critical patches for homes. -- It stores the information in a table called MGMT_BUG_ADV_HOME_PATCH. It should -- be called prior to doing the policy evaluation for critical patches. The -- critical patch policy actually queries the MGMT_BUG_ADV_HOME_PATCH table. -- PROCEDURE SETUP_CPF_FOR_NAMED_HOST( p_targetName VARCHAR2 ) IS CURSOR C1 (p_targetGuid in RAW) IS SELECT HOME_HOST, HOME_DIRECTORY, HOME_NAME, HOME_VERSION, HOST_TARGET_GUID, HOME_ID, HOME_PLATFORM_TYPE, HOME_HOST_BIT_LENGTH, HOME_TARGET_TYPE, HOME_ARU_PLATFORM FROM MGMT$CPF_HOMES_INFO WHERE HOST_TARGET_GUID = p_targetGuid; p_homeRecords CPF_HOME_ARRAY := CPF_HOME_ARRAY(); p_home_record CPF_HOME_RECORD; c1Rec C1%ROWTYPE; is64bitSolarisFlag varchar2(1) := '0'; p_index number := 1; p_batchSize number := 5000; p_deleteFlag number := 1; p_targetGuid RAW(16); p_homeName varchar2(320); BEGIN select target_guid into p_targetGuid from mgmt_targets where target_name = p_targetName and target_type = 'host'; DELETE FROM MGMT_BUG_ADV_HOME_PATCH WHERE TARGET_GUID = p_targetGuid; OPEN C1(p_targetGuid); LOOP FETCH C1 INTO c1Rec; EXIT when C1%NOTFOUND; IF c1Rec.HOME_PLATFORM_TYPE = 'OUI' THEN IF c1Rec.HOME_ARU_PLATFORM = 453 OR c1Rec.HOME_ARU_PLATFORM = 23 THEN IF c1Rec.HOME_HOST_BIT_LENGTH = 64 THEN -- Home is solaris and host is 64 bit is64bitSolarisFlag := '1'; ELSE -- Home is solaris and host is 32- bit is64bitSolarisFlag := '0'; c1Rec.HOME_ARU_PLATFORM := 453; -- Making sure that the ARU platform becomes that oof solaris 32-bit END IF; ELSE -- This is the case when home plat type is 'OUI' but not solaris is64bitSolarisFlag := '0'; END IF; ELSE -- This is the case when home platform type is ARU is64bitSolarisFlag := '0'; END IF; IF c1Rec.HOME_NAME IS NULL THEN p_homeName := c1Rec.HOME_DIRECTORY; ELSE p_homeName := c1Rec.HOME_DIRECTORY || '(' || c1Rec.HOME_NAME || ')'; END IF; p_home_record := CPF_HOME_RECORD(c1Rec.HOME_HOST, c1Rec.HOME_DIRECTORY, p_homeName, c1Rec.HOME_VERSION, c1Rec.HOST_TARGET_GUID, c1Rec.HOME_ID, is64bitSolarisFlag,c1Rec.HOME_HOST_BIT_LENGTH, c1Rec.HOME_TARGET_TYPE, c1Rec.HOME_ARU_PLATFORM); p_homeRecords.EXTEND; p_homeRecords(p_index) := p_home_record; p_index := p_index + 1; IF p_index = (p_batchSize+1) THEN SETUP_CPF_ADV_HOME_PATCH(p_homeRecords,0); p_index := 1; p_homeRecords := CPF_HOME_ARRAY(); -- empty the record here END IF; END LOOP; CLOSE C1; IF p_homeRecords.count <> 0 THEN SETUP_CPF_ADV_HOME_PATCH(p_homeRecords,0); -- Run it for the final batch of homes END IF; CPF_POLICY.SETUP_CPF_METRIC_SOURCE( p_targetGuid); END SETUP_CPF_FOR_NAMED_HOST; PROCEDURE RUN_SETUP_CPF_ADV_HOME_PATCH IS CURSOR C1 IS SELECT HOME_HOST, HOME_DIRECTORY, HOME_NAME, HOME_VERSION, HOST_TARGET_GUID, HOME_ID, HOME_PLATFORM_TYPE, HOME_HOST_BIT_LENGTH, HOME_TARGET_TYPE, HOME_ARU_PLATFORM FROM MGMT$CPF_HOMES_INFO ORDER BY HOME_ID,HOME_TARGET_TYPE,HOME_VERSION DESC; p_homeRecords CPF_HOME_ARRAY := CPF_HOME_ARRAY(); p_home_record CPF_HOME_RECORD; p_currentTime timeStamp := sysdate; c1Rec C1%ROWTYPE; is64bitSolarisFlag varchar2(1) := '0'; p_index number := 1; p_batchSize number := 5000; p_deleteFlag number := 1; p_homeName varchar(320); p_targetFlag number := 0; p_lastHomeAndTargetType varchar2(2500) := ' '; p_currentHomeAndTargetType varchar2(2500) := null; MODULE_NAME VARCHAR2(20) := 'CPF_POLICY'; BEGIN OPEN C1; LOOP FETCH C1 INTO c1Rec; EXIT when C1%NOTFOUND; IF c1Rec.HOME_PLATFORM_TYPE = 'OUI' THEN IF c1Rec.HOME_ARU_PLATFORM = 453 OR c1Rec.HOME_ARU_PLATFORM = 23 THEN IF c1Rec.HOME_HOST_BIT_LENGTH = 64 THEN -- Home is solaris and host is 64 bit is64bitSolarisFlag := '1'; ELSE -- Home is solaris and host is 32- bit is64bitSolarisFlag := '0'; c1Rec.HOME_ARU_PLATFORM := 453; -- Making sure that the ARU platform becomes that oof solaris 32-bit END IF; ELSE -- This is the case when home plat type is 'OUI' but not solaris is64bitSolarisFlag := '0'; END IF; ELSE -- This is the case when home platform type is ARU is64bitSolarisFlag := '0'; END IF; p_currentHomeAndTargetType := c1Rec.HOME_ID || c1Rec.HOME_TARGET_TYPE; IF p_lastHomeAndTargetType = p_currentHomeAndTargetType THEN -- Log a message saying that this is a repeated entry and will not be considered for Policy violation IF emdw_log.p_is_debug_set THEN emdw_log.debug('Repeated entries with same Oracle_Home and target type found '||c1Rec.HOME_HOST||'-'||c1Rec.HOME_DIRECTORY||'::'||c1Rec.HOME_TARGET_TYPE||'. Discarding the entry with version '|| c1Rec.HOME_VERSION , MODULE_NAME); END IF ; p_targetFlag := 1; ELSE p_targetFlag := 0; END IF; p_lastHomeAndTargetType := p_currentHomeAndTargetType; IF p_targetFlag <> 1 THEN IF c1Rec.HOME_NAME IS NULL THEN p_homeName := c1Rec.HOME_DIRECTORY; ELSE p_homeName := c1Rec.HOME_DIRECTORY || '(' || c1Rec.HOME_NAME || ')'; END IF; p_home_record := CPF_HOME_RECORD(c1Rec.HOME_HOST, c1Rec.HOME_DIRECTORY,p_homeName, c1Rec.HOME_VERSION, c1Rec.HOST_TARGET_GUID, c1Rec.HOME_ID, is64bitSolarisFlag,c1Rec.HOME_HOST_BIT_LENGTH, c1Rec.HOME_TARGET_TYPE, c1Rec.HOME_ARU_PLATFORM); p_homeRecords.EXTEND; p_homeRecords(p_index) := p_home_record; p_index := p_index + 1; END IF; p_targetFlag := 0; IF p_index = (p_batchSize+1) THEN IF p_deleteFlag = 1 THEN SETUP_CPF_ADV_HOME_PATCH(p_homeRecords, p_deleteFlag); p_deleteFlag := 0; ELSE SETUP_CPF_ADV_HOME_PATCH(p_homeRecords, p_deleteFlag); END IF; p_index := 1; p_homeRecords := CPF_HOME_ARRAY(); -- empty the record here END IF; END LOOP; CLOSE C1; IF p_homeRecords.count <> 0 THEN SETUP_CPF_ADV_HOME_PATCH(p_homeRecords, p_deleteFlag); -- Run it for the final batch of homes END IF; -- Insert the new timestamp in MGMT_PARAMETERS delete from MGMT_PARAMETERS where parameter_name = 'rfm_timestamp'; insert into MGMT_PARAMETERS (parameter_name, parameter_value) values ('rfm_timestamp', p_currentTime); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END RUN_SETUP_CPF_ADV_HOME_PATCH; -------------------------------------------------------------------------------- -- This method is specific to the critical patch advisory functionality -- policy. It is the function that is called by that policy to get rollup information -- for display on the critical patch page. -- -- Returns a MGMT_BUG_ADVISORY_VIOLATIONS table. -- FUNCTION CPF_PAGE_INFO RETURN MGMT_BUG_PATCH_ADVISORIES_LIST IS l_cpf_table MGMT_BUG_PATCH_ADVISORIES_LIST; l_cpf_obj MGMT_BUG_PATCH_ADVISORIES; CURSOR C1 IS select unique ahp.patch_guid as patch_guid, ahp.advisory_name, a.url from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context rv, mgmt_targets t, mgmt_bug_adv_home_patch ahp, mgmt_bug_advisory a where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = rv.target_guid and v.policy_guid = rv.policy_guid and v.key_value = rv.key_value and rv.column_name = 'ADVISORY_NAME' and t.target_guid = v.target_guid and ahp.advisory_name = rv.column_str_value and ahp.target_guid = t.target_guid and a.advisory_name = ahp.advisory_name order by ahp.patch_guid; c1Rec C1%ROWTYPE; indx NUMBER; BEGIN select MGMT_BUG_PATCH_ADVISORIES( patch_id, patch_guid, patch_type, product_name, release_name, platform_name, null, null, hc ) bulk collect into l_cpf_table from ( select unique ap.patch_id, ahp.patch_guid, ap.patch_type, prod.product_name, rel.release_name, plat.platform_name, NULL, NULL, count(unique ahp.host_name || ahp.home_location) as hc from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context rv, mgmt_targets t, mgmt_bug_adv_home_patch ahp, mgmt_bug_available_patch ap, mgmt_bug_patch_platform pp, mgmt_aru_platforms plat, mgmt_aru_releases rel, mgmt_aru_products prod where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = rv.target_guid and v.policy_guid = rv.policy_guid and v.key_value = rv.key_value and rv.column_name = 'ADVISORY_NAME' and t.target_guid = v.target_guid and ahp.advisory_name = rv.column_str_value and ahp.target_guid = t.target_guid and pp.patch_guid = ahp.patch_guid and ap.ap_guid = pp.ap_guid and ap.product_id = prod.product_id and ap.release_id = rel.release_id and pp.platform_id = plat.platform_id group by ap.patch_id, ahp.patch_guid, ap.patch_type, prod.product_name, rel.release_name, plat.platform_name order by ahp.patch_guid); OPEN C1; INDX := 1; LOOP FETCH C1 INTO c1Rec; EXIT WHEN c1%NOTFOUND; -- Expects there is always a corresponding entry, so if the current patch -- guid doesn't match, the next one is it. IF l_cpf_table(INDX).PATCH_GUID <> c1Rec.PATCH_GUID THEN INDX := INDX+1; END IF; IF l_cpf_table(INDX).ALERTS IS NULL THEN l_cpf_table(INDX).ALERTS := c1Rec.ADVISORY_NAME; l_cpf_table(INDX).URLS := c1Rec.URL; ELSE l_cpf_table(INDX).ALERTS := l_cpf_table(INDX).ALERTS || ', ' || c1Rec.ADVISORY_NAME; l_cpf_table(INDX).URLS := l_cpf_table(INDX).URLS || ', ' || c1Rec.URL; END IF; END LOOP; CLOSE C1; RETURN l_cpf_table; END CPF_PAGE_INFO; -------------------------------------------------------------------------------- -- This method is specific to the critical patch advisory functionality -- policy. It is the function that is called by critical patch advisories -- bean to get rollup information for a specified group. -- -- Returns a MGMT_BUG_ADVISORY_VIOLATIONS table. -- FUNCTION CPF_PAGE_INFO_FILTER_BY_GROUP( p_compositeTargetName VARCHAR2, p_compositeTargetType VARCHAR2) RETURN MGMT_BUG_PATCH_ADVISORIES_LIST IS l_cpf_table MGMT_BUG_PATCH_ADVISORIES_LIST; l_cpf_obj MGMT_BUG_PATCH_ADVISORIES; CURSOR C1 IS select unique ahp.patch_guid as patch_guid, ahp.advisory_name, a.url from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_violation_context orv, mgmt_bug_adv_home_patch ahp, mgmt_bug_advisory a, (select unique host.target_guid, host.host_name as host_name, p.property_value as home_location from mgmt_targets t, mgmt_targets host, mgmt_target_properties p, mgmt$group_flat_memberships m where m.composite_target_type = p_compositeTargetType and m.composite_target_name = p_compositeTargetName and m.member_target_guid = t.target_guid and t.host_name = host.target_name and host.target_type = 'host' and p.target_guid = t.target_guid and p.property_name = 'OracleHome') t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and HEXTORAW(t.target_guid) = HEXTORAW(ahp.target_guid) and t.target_guid = v.target_guid and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and arv.column_name = 'ADVISORY_NAME' and ahp.advisory_name = arv.column_str_value and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and hrv.column_name = 'HOME_LOCATION' and hrv.column_str_value = t.home_location and ahp.home_location = hrv.column_str_value and v.target_guid = orv.target_guid and v.policy_guid = orv.policy_guid and v.key_value = orv.key_value and orv.column_name = 'HOST_NAME' and orv.column_str_value = t.host_name and ahp.host_name = orv.column_str_value and a.advisory_name = ahp.advisory_name order by ahp.patch_guid; c1Rec C1%ROWTYPE; indx NUMBER; BEGIN select MGMT_BUG_PATCH_ADVISORIES( patch_id, patch_guid, patch_type, product_name, release_name, platform_name, null, null, hc ) bulk collect into l_cpf_table from ( select unique ap.patch_id, ahp.patch_guid, ap.patch_type, prod.product_name, rel.release_name, plat.platform_name, NULL, NULL, count(unique ahp.host_name || ahp.home_location) as hc from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_bug_adv_home_patch ahp, mgmt_violation_context hrv, mgmt_violation_context orv, mgmt_bug_available_patch ap, mgmt_bug_patch_platform pp, mgmt_aru_platforms plat, mgmt_aru_releases rel, mgmt_aru_products prod, (select unique host.target_guid as host_guid, host.host_name as host_name, p.property_value as home_location from mgmt_targets t, mgmt_targets host, mgmt_target_properties p, mgmt$group_flat_memberships m where m.composite_target_type = p_compositeTargetType and m.composite_target_name = p_compositeTargetName and m.member_target_guid = t.target_guid and t.host_name = host.target_name and host.target_type = 'host' and p.target_guid = t.target_guid and p.property_name = 'OracleHome') t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and t.host_guid = v.target_guid and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and arv.column_name = 'ADVISORY_NAME' and ahp.advisory_name = arv.column_str_value and HEXTORAW(ahp.target_guid) = HEXTORAW(t.host_guid) and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and hrv.column_name = 'HOME_LOCATION' and hrv.column_str_value = t.home_location and ahp.home_location = hrv.column_str_value and v.target_guid = orv.target_guid and v.policy_guid = orv.policy_guid and v.key_value = orv.key_value and orv.column_name = 'HOST_NAME' and orv.column_str_value = t.host_name and ahp.host_name = orv.column_str_value and pp.patch_guid = ahp.patch_guid and ap.ap_guid = pp.ap_guid and ap.product_id = prod.product_id and ap.release_id = rel.release_id and pp.platform_id = plat.platform_id group by ap.patch_id, ahp.patch_guid, ap.patch_type, prod.product_name, rel.release_name, plat.platform_name order by ahp.patch_guid); OPEN C1; INDX := 1; LOOP FETCH C1 INTO c1Rec; EXIT WHEN c1%NOTFOUND; -- Expects there is always a corresponding entry, so if the current patch -- guid doesn't match, the next one is it. IF l_cpf_table(INDX).PATCH_GUID <> c1Rec.PATCH_GUID THEN INDX := INDX+1; END IF; IF l_cpf_table(INDX).ALERTS IS NULL THEN l_cpf_table(INDX).ALERTS := c1Rec.ADVISORY_NAME; l_cpf_table(INDX).URLS := c1Rec.URL; ELSE l_cpf_table(INDX).ALERTS := l_cpf_table(INDX).ALERTS || ', ' || c1Rec.ADVISORY_NAME; l_cpf_table(INDX).URLS := l_cpf_table(INDX).URLS || ', ' || c1Rec.URL; END IF; END LOOP; CLOSE C1; RETURN l_cpf_table; END CPF_PAGE_INFO_FILTER_BY_GROUP; ------------------------------------------------------------------------------------------ -- This procedure populates the table MGMT_CPF_METRIC_SOURCE with data from -- CPF_QUERY. -- PROCEDURE SETUP_CPF_METRIC_SOURCE( p_targetGuid RAW := NULL ) IS BEGIN IF p_targetGuid IS NULL THEN -- if the target guid is null then we want to refresh the entire table. DELETE FROM MGMT_CPF_METRIC_SOURCE; INSERT INTO MGMT_CPF_METRIC_SOURCE ( ADVISORY_NAME, IMPACT , ADVISORY_ABSTRACT , HOME_LOCATION_DISPLAY, HOME_LOCATION, HOST_NAME , ADVISORY_URL , PATCH_GUIDS , TARGET_GUID , PATCHES, CONTAINER_GUID, IS_VALID) SELECT ADVISORY_NAME, IMPACT , ADVISORY_ABSTRACT , HOME_LOCATION_DISPLAY, HOME_LOCATION, HOST_NAME , ADVISORY_URL , PATCH_GUIDS , TARGET_GUID , PATCHES, CONTAINER_GUID, PATCH_VALID_STATUS FROM TABLE(CAST (CPF_POLICY.CPF_QUERY(NULL) AS MGMT_BUG_ADVISORY_VIOLATIONS)); ELSE -- if the target guid is not null then refresh data for the target DELETE FROM MGMT_CPF_METRIC_SOURCE WHERE p_targetGuid = TARGET_GUID; INSERT INTO MGMT_CPF_METRIC_SOURCE ( ADVISORY_NAME, IMPACT , ADVISORY_ABSTRACT , HOME_LOCATION_DISPLAY, HOME_LOCATION, HOST_NAME , ADVISORY_URL , PATCH_GUIDS , TARGET_GUID , PATCHES, CONTAINER_GUID, IS_VALID) SELECT ADVISORY_NAME, IMPACT , ADVISORY_ABSTRACT , HOME_LOCATION_DISPLAY, HOME_LOCATION, HOST_NAME , ADVISORY_URL , PATCH_GUIDS , TARGET_GUID , PATCHES, CONTAINER_GUID, PATCH_VALID_STATUS FROM TABLE(CAST (CPF_POLICY.CPF_QUERY(p_targetGuid) AS MGMT_BUG_ADVISORY_VIOLATIONS)); END IF; END SETUP_CPF_METRIC_SOURCE; ----------------------------------------------------------------------------------------- -- This function is used to obtain the DBMS_LOCK for RFM Job -- FUNCTION get_exclusive_lock RETURN NUMBER IS BEGIN return DBMS_LOCK.REQUEST( '1073741935107374193519',dbms_lock.x_mode,0,false); END; ----------------------------------------------------------------------------------------- -- This function is used to release the DBMS_LOCK for RFM Job -- FUNCTION release_lock RETURN NUMBER IS BEGIN return DBMS_LOCK.RELEASE( '1073741935107374193519'); END ; ------------------------------------------------------------------------------------------ -- This procedure evaluates the Advisory and Affected Homes count for CPF -- PROCEDURE GET_ADV_HOME_COUNT(p_adv_count OUT number, p_homes_count OUT number, p_host_name IN varchar2,p_home_location IN varchar2, p_composite_name IN varchar2, p_composite_type IN varchar2, p_type IN number) IS p_super_user_number number := 0; p_patch_validation_str varchar(10) := 'false'; p_patch_validation_on boolean; TYPE violations_number_type IS RECORD ( violated_advs_count number, violated_home_count number); violations_record violations_number_type; p_host_home_concatenated varchar2(100); BEGIN p_super_user_number := mgmt_user.has_priv(mgmt_user.get_current_em_user, 'SUPER_USER') ; BEGIN SELECT nvl(parameter_value, 'false') into p_patch_validation_str FROM mgmt_parameters WHERE parameter_name = 'Patch Validation'; EXCEPTION WHEN NO_DATA_FOUND THEN p_patch_validation_str := 'false'; END ; p_patch_validation_on := p_patch_validation_str = 'true'; if ( p_type = 0) THEN if ( p_super_user_number = 1 OR (p_super_user_number = 0 AND NOT p_patch_validation_on)) THEN select count(*) into p_adv_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_bug_advisory s, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'ADVISORY_NAME' and s.advisory_name = p.column_str_value and t.target_guid = v.target_guid group by p.column_name, p.column_str_value ); select count(*) into p_homes_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'HOST_HOME_VALUE' and t.target_guid = v.target_guid group by p.column_name, p.column_str_value ) ; elsif (p_super_user_number = 0 AND p_patch_validation_on) THEN select count(*) into p_adv_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_violation_context f, mgmt_bug_advisory s, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'ADVISORY_NAME' and s.advisory_name = p.column_str_value and v.target_guid = f.target_guid and v.policy_guid = f.policy_guid and v.key_value = f.key_value and v.collection_timestamp = f.collection_timestamp and f.column_name = 'IS_VALID' and f.column_str_value = 'Y' and t.target_guid = v.target_guid group by p.column_name, p.column_str_value ); select count(*) into p_homes_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_violation_context f, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'HOST_HOME_VALUE' and v.target_guid = f.target_guid and v.policy_guid = f.policy_guid and v.key_value = f.key_value and v.collection_timestamp = f.collection_timestamp and f.column_name = 'IS_VALID' and f.column_str_value = 'Y' and t.target_guid = v.target_guid group by p.column_name, p.column_str_value ) ; end if; elsif (p_type = 3) then if ( p_super_user_number = 1 OR (p_super_user_number = 0 AND NOT p_patch_validation_on)) THEN select count(*) into p_adv_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_bug_advisory s, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'ADVISORY_NAME' and s.advisory_name = p.column_str_value and t.target_guid = v.target_guid and t.target_name = p_host_name and t.target_type = 'host' group by p.column_name, p.column_str_value ) ; select count(*) into p_homes_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'HOME_LOCATION' and t.target_guid = v.target_guid and t.target_name = p_host_name and t.target_type = 'host' group by p.column_name, p.column_str_value ); elsif (p_super_user_number = 0 AND p_patch_validation_on) THEN select count(*) into p_adv_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_violation_context f, mgmt_bug_advisory s, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'ADVISORY_NAME' and s.advisory_name = p.column_str_value and v.target_guid = f.target_guid and v.policy_guid = f.policy_guid and v.key_value = f.key_value and v.collection_timestamp = f.collection_timestamp and f.column_name = 'IS_VALID' and f.column_str_value = 'Y' and t.target_guid = v.target_guid and t.target_name = p_host_name and t.target_type = 'host' group by p.column_name, p.column_str_value ) ; select count(*) into p_homes_count from ( WITH policy AS ( select /*+ index(p) */ policy_guid from mgmt_policies where author = 'ORACLE' and target_type = 'host' and policy_name = 'Critical Patch Advisories for Oracle Homes' ) select p.column_name as column_name, p.column_str_value as column_str_value from policy, mgmt_current_violation v, mgmt_violation_context p, mgmt_violation_context f, mgmt_targets t where policy.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = p.target_guid and v.policy_guid = p.policy_guid and v.key_value = p.key_value and v.collection_timestamp = p.collection_timestamp and p.column_name = 'HOME_LOCATION' and v.target_guid = f.target_guid and v.policy_guid = f.policy_guid and v.key_value = f.key_value and v.collection_timestamp = f.collection_timestamp and f.column_name = 'IS_VALID' and f.column_str_value = 'Y' and t.target_guid = v.target_guid and t.target_name = p_host_name and t.target_type = 'host' group by p.column_name, p.column_str_value ); end if; elsif (p_type = 1) then p_host_home_concatenated := p_host_name||','||p_home_location; if ( p_super_user_number = 1 OR (p_super_user_number = 0 AND NOT p_patch_validation_on)) THEN select count(unique arv.column_str_value) into p_adv_count from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_targets t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = arv.collection_timestamp and arv.column_name = 'ADVISORY_NAME' and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and hrv.column_str_value = p_host_home_concatenated and t.target_guid = v.target_guid; elsif (p_super_user_number = 0 AND p_patch_validation_on) THEN select /*+ ORDERED INDEX(arv) INDEX(hrv) INDEX(orv) */ count(unique arv.column_str_value) into p_adv_count from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_violation_context orv, mgmt_targets t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = arv.collection_timestamp and arv.column_name = 'ADVISORY_NAME' and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and hrv.column_str_value = p_host_home_concatenated and v.target_guid = orv.target_guid and v.policy_guid = orv.policy_guid and v.key_value = orv.key_value and v.collection_timestamp = orv.collection_timestamp and orv.column_name = 'IS_VALID' and orv.column_str_value = 'Y' and t.target_guid = v.target_guid; end if; p_homes_count := 1; elsif (p_type = 2) then if ( p_super_user_number = 1 OR (p_super_user_number = 0 AND NOT p_patch_validation_on)) THEN if(p_composite_type = 'cluster') then --- Query for getting Advisory and home count for p_composite_type select adv_count, home_count into violations_record from ( with my_targets as ( select t.host_name as host_name, t.target_guid as target_guid, a.assoc_guid as assoc_guid, a.assoc_target_guid as assoc_target_guid from mgmt_targets t, mgmt_target_assocs a, mgmt_target_assoc_defs d where d.association_type = 'contains' and a.assoc_guid = d.assoc_guid and t.target_guid = a.source_target_guid and t.target_name = p_composite_name and t.target_type = 'cluster' ) select count(unique arv.column_str_value) as adv_count, count(unique hrv.column_str_value) as home_count from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_targets t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and t.target_guid = v.target_guid and hrv.column_str_value in ( select mt.host_name||','||p.property_value from mgmt_target_properties p, my_targets mt where p.target_guid = mt.target_guid and p.property_name = 'OracleHome' union -- getting dependent RAC Homes from here select o.host_name||','||p.property_value from mgmt_target_assocs a1, mgmt_target_assocs b, mgmt_target_assocs c, mgmt_targets m, mgmt_targets n, mgmt_targets o, mgmt_target_properties p, mgmt_target_assoc_defs d1, my_targets mt where d1.association_type = 'hosted_by' and m.target_guid = mt.assoc_target_guid and b.assoc_guid = d1.assoc_guid and b.assoc_target_guid = m.target_guid and n.target_type = 'rac_database' and n.host_name = m.target_name and b.source_target_guid = n.target_guid and -- n is composite rac target n.target_guid = c.source_target_guid and c.assoc_guid = mt.assoc_guid and o.target_guid = c.assoc_target_guid and -- o are targets within rac p.target_guid = o.target_guid and p.property_name = 'OracleHome' union -- getting ASM Homes from here select g.host_name||','||p.property_value from mgmt_target_assocs a, mgmt_targets m, mgmt_targets g, mgmt_target_properties p, my_targets mt where m.target_guid = mt.assoc_target_guid and g.target_type = 'osm_instance' and g.host_name = m.target_name and p.target_guid = g.target_guid and p.property_name = 'OracleHome' ) ); else select count(unique arv.column_str_value), count(unique hrv.column_str_value) into violations_record from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_targets mt, mgmt_targets h, mgmt_target_properties tp, mgmt_flat_target_assoc assoc, mgmt_bug_advisory c where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = arv.collection_timestamp and arv.column_name = 'ADVISORY_NAME' and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and h.target_guid = v.target_guid and hrv.column_str_value = h.host_name ||','||tp.property_value and assoc.is_membership = 1 and assoc.source_target_guid = mgmt_target.generate_target_guid(p_composite_name,p_composite_type) and mt.target_guid = assoc.assoc_target_guid and mt.emd_url = h.emd_url and h.target_type = 'host' and tp.target_guid = mt.target_guid and tp.property_name = 'OracleHome' and arv.column_str_value = c.advisory_name; end if; elsif (p_super_user_number = 0 AND p_patch_validation_on) THEN if (p_composite_type = 'cluster') then --- Query for getting Advisory and home count for p_composite_type select adv_count, home_count into violations_record from ( with my_targets as ( select t.host_name as host_name, t.target_guid as target_guid, a.assoc_guid as assoc_guid, a.assoc_target_guid as assoc_target_guid from mgmt_targets t, mgmt_target_assocs a, mgmt_target_assoc_defs d where d.association_type = 'contains' and a.assoc_guid = d.assoc_guid and t.target_guid = a.source_target_guid and t.target_name = p_composite_name and t.target_type = 'cluster' ) select /*+ ORDERED INDEX(arv) INDEX(hrv) INDEX(orv) */ count(unique arv.column_str_value) as adv_count, count(unique hrv.column_str_value) as home_count from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_violation_context orv, mgmt_targets t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = arv.collection_timestamp and arv.column_name = 'ADVISORY_NAME' and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and v.target_guid = orv.target_guid and v.policy_guid = orv.policy_guid and v.key_value = orv.key_value and v.collection_timestamp = orv.collection_timestamp and orv.column_name = 'IS_VALID' and orv.column_str_value = 'Y' and t.target_guid = v.target_guid and hrv.column_str_value in ( select mt.host_name||','||p.property_value from mgmt_target_properties p, my_targets mt where p.target_guid = mt.target_guid and p.property_name = 'OracleHome' union -- getting dependent RAC Homes from here select o.host_name||','||p.property_value from mgmt_target_assocs a1, mgmt_target_assocs b, mgmt_target_assocs c, mgmt_targets m, mgmt_targets n, mgmt_targets o, mgmt_target_properties p, mgmt_target_assoc_defs d1, my_targets mt where d1.association_type = 'hosted_by' and m.target_guid = mt.assoc_target_guid and b.assoc_guid = d1.assoc_guid and b.assoc_target_guid = m.target_guid and n.target_type = 'rac_database' and n.host_name = m.target_name and b.source_target_guid = n.target_guid and -- n is composite rac target n.target_guid = c.source_target_guid and c.assoc_guid = mt.assoc_guid and o.target_guid = c.assoc_target_guid and -- o are targets within rac p.target_guid = o.target_guid and p.property_name = 'OracleHome' union -- getting ASM Homes from here select g.host_name||','||p.property_value from mgmt_target_assocs a, mgmt_targets m, mgmt_targets g, mgmt_target_properties p, my_targets mt where m.target_guid = mt.assoc_target_guid and g.target_type = 'osm_instance' and g.host_name = m.target_name and p.target_guid = g.target_guid and p.property_name = 'OracleHome' ) ); -- getting p_composite_name Homes from here else select /*+ ORDERED INDEX(arv) INDEX(hrv) INDEX(orv) */ count(unique arv.column_str_value), count(unique hrv.column_str_value) into violations_record from mgmt_policies p, mgmt_current_violation v, mgmt_violation_context arv, mgmt_violation_context hrv, mgmt_violation_context orv, mgmt_targets t where p.author = 'ORACLE' and p.target_type = 'host' and p.policy_name = 'Critical Patch Advisories for Oracle Homes' and p.policy_guid = v.policy_guid and (v.exempt_code = 0 or (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and v.target_guid = arv.target_guid and v.policy_guid = arv.policy_guid and v.key_value = arv.key_value and v.collection_timestamp = arv.collection_timestamp and arv.column_name = 'ADVISORY_NAME' and v.target_guid = hrv.target_guid and v.policy_guid = hrv.policy_guid and v.key_value = hrv.key_value and v.collection_timestamp = hrv.collection_timestamp and hrv.column_name = 'HOST_HOME_VALUE' and v.target_guid = orv.target_guid and v.policy_guid = orv.policy_guid and v.key_value = orv.key_value and v.collection_timestamp = orv.collection_timestamp and orv.column_name = 'IS_VALID' and orv.column_str_value = 'Y' and t.target_guid = v.target_guid and hrv.column_str_value in (select h.host_name||','||tp.property_value from mgmt_targets mt, mgmt_targets h, mgmt_target_properties tp, mgmt$group_flat_memberships m where m.composite_target_name = p_composite_name and m.composite_target_type = p_composite_type and m.member_target_guid = mt.target_guid and mt.host_name = h.target_name and h.target_type = 'host' and tp.target_guid = mt.target_guid and tp.property_name = 'OracleHome'); end if; end if; p_adv_count := violations_record.violated_advs_count; p_homes_count := violations_record.violated_home_count; end if; EXCEPTION WHEN NO_DATA_FOUND THEN p_adv_count := 0; p_homes_count := 0; END GET_ADV_HOME_COUNT; END CPF_POLICY; / show errors;