Rem drv:
Rem
Rem $Header: license_rac_pkgbodys.sql 04-sep-2007.01:03:08 ajdsouza Exp $
Rem
Rem license_rac_pkgbodys.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem license_rac_pkgbodys.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem ajdsouza 08/31/07 - bug fix 5494116
Rem bram 07/12/06 - Backport bram_bug-5147069 from main
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem paachary 02/08/05 - Changed the package name from mgmt_rac_license to em_rac_license
Rem gsbhatia 02/07/05 - updating repmgr header
Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid
Rem ktlaw 01/11/05 - add repmgr header
Rem bram 12/30/04 - bram_rac_lictrans
Rem bram 12/30/04 - Created
Rem
CREATE OR REPLACE PACKAGE BODY em_rac_license
IS
-- Function>is_racmember
-- Purpose : To check if a target is associated with a rac database
-- Input Parameters
-- p_target_type : Target_Type whose 'rac membership' needs to be checeked
-- p_target_guid : GUID of the target,whose 'rac membership' needs to be checked
-- OUTPUT Parameter 'TRUE' if associated with rac database,'FALSE' if not
FUNCTION is_racmember
(
p_target_type mgmt_targets.target_type%TYPE DEFAULT NULL,
p_target_guid mgmt_targets.target_guid%TYPE
)
RETURN BOOLEAN
IS
l_result_string BOOLEAN:=FALSE;
l_count NUMBER:=0;
BEGIN
SELECT
count(1) --Check if the target is associated with a rac_database
INTO
l_count
FROM
mgmt_targets a,
mgmt_target_assocs b,
mgmt_targets c
WHERE
a.target_guid =b.assoc_target_guid AND
a.target_type =p_target_type AND
b.source_target_guid=c.target_guid AND
c.target_type =MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE AND
b.assoc_target_guid =p_target_guid;
--If the given target is part of a RAC setup then return TRUE--
IF ((l_count>0) OR (p_target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE))THEN
l_result_string:=TRUE;
END IF;
RETURN(l_result_string);
END is_racmember;
-- Private Procedure->get_rac_data
-- Purpose :To get the list of instance guid and cluster node name for a given cluster Db
-- Input Parameters
-- p_target_guid : Target GUID of the cluster database for which its cluster instances needs to be retreived
-- OUTPUT Parameter rac_cursor returns the list of instance guid and cluster node name
PROCEDURE get_rac_data
(
p_target_guid IN mgmt_targets.target_guid%TYPE,
rac_cursor OUT cursorType
)
IS
BEGIN
OPEN rac_cursor FOR
SELECT
distinct a.assoc_target_guid cluster_instance_guid,
instance.host_name cluster_node_name
FROM
mgmt_targets cluster_db,
mgmt_target_assocs a,
mgmt_targets instance
WHERE
cluster_db.target_guid=p_target_guid AND
a.source_target_guid =cluster_db.target_guid AND
instance.target_guid =a.assoc_target_guid AND
a.assoc_guid =MGMT_ASSOC.g_contains_guid AND -- 'is_member'
cluster_db.target_type=MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE;
END get_rac_data;
-- Procedure->get_asm_data
-- Purpose : To return the list of ASM associated with a instance or Standalone Database
-- Input Parameters
-- p_target_guid :Target GUID on which the ASM association needs to be retreived,this(target guid) could
-- either be a instance or of a standalone database
-- OUTPUT Parameter p_asm_cursor return the list of ASM guid for a given
-- instance or standalone database
PROCEDURE get_asm_data
(
p_target_guid IN mgmt_targets.target_guid%TYPE,
p_asm_cursor OUT cursorType
)
IS
BEGIN
--Get the asm guid based on the target_guid of either the standalone database
-- or the instance associated with it
OPEN p_asm_cursor FOR
SELECT
t.target_guid asm_guid
FROM
mgmt_targets t,
mgmt_target_properties p
WHERE
t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE AND
p.property_name = MGMT_GLOBAL.G_SID_PROPERTY_NAME AND
t.target_guid = p.target_guid AND
UPPER(p.property_value) =
(
SELECT
upper(property_value)
FROM
mgmt_target_properties
WHERE
property_name =MGMT_GLOBAL.G_OSM_PROPERTY_NAME AND
property_value != ' ' AND
target_guid =p_target_guid --
) ;
END get_asm_data;
-- Private Procedure->get_cluster_data
-- Purpose :To get the list of cluster guid for a given cluster node
-- Input Parameters
-- p_cluster_node_guid: GUID of the Cluster Node
-- p_target_type: Target Type of the cluster
-- OUTPUT Parameter p_cluster_cursor returns the list of cluster guid
PROCEDURE get_cluster_data
(
p_cluster_node_guid IN mgmt_targets.target_guid%TYPE,
p_target_type IN mgmt_targets.target_type%TYPE,
p_cluster_cursor OUT cursorType
)
IS
BEGIN
OPEN p_cluster_cursor FOR
SELECT
assoc.source_target_guid --cluster_guid
FROM
mgmt_targets host,
mgmt_target_assocs assoc,
mgmt_targets clust
WHERE
host.target_guid =assoc.assoc_target_guid AND
host.target_guid =p_cluster_node_guid AND
assoc.source_target_guid=clust.target_guid AND
assoc.assoc_guid =mgmt_assoc.g_contains_guid AND
clust.target_type =p_target_type;
END get_cluster_data;
-- Function->get_cluster_guid
-- Purpose :Knowing the target_guid of a Cluster Node,find details about the outer cluster
-- Input Parameters
-- p_cluster_node_guid: GUID of the Cluster Node
-- p_target_type: Target Type of the cluster
FUNCTION get_cluster_guid
(
p_cluster_node_guid IN mgmt_targets.target_guid%TYPE,
p_target_type IN mgmt_targets.target_type%TYPE
)
RETURN VARCHAR2
IS
p_cluster_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;--to store the cluster GUID
BEGIN
SELECT
assoc.source_target_guid --cluster_guid
INTO
p_cluster_guid
FROM
mgmt_targets host,
mgmt_target_assocs assoc,
mgmt_targets clust
WHERE
host.target_guid =assoc.assoc_target_guid AND
host.target_guid =p_cluster_node_guid AND
assoc.source_target_guid=clust.target_guid AND
assoc.assoc_guid =mgmt_assoc.g_contains_guid AND
clust.target_type =p_target_type;
RETURN (p_cluster_guid);
END get_cluster_guid;
-- Public Procedure->grant_racdb_licenseinfo
-- Purpose :The procedure grant license on the selected packs on the Cluster database
-- and its memmber and assocciations
-- Input Parameters
-- p_target_guid: Cluster Database GUID
-- p_pack_name: License Pack to be applied
--Notes-
--License on a package granted to a Cluster Database propogates to its instances
--License granted to the instance is propogated to the node on which it resides
--License info from the cluster node propogates to its parent cluster
--Cluster Instance and Standalone Database can have a ASM instance associated to it
--License info propogates from the Cluster instance or Standalone database to the ASM
PROCEDURE grant_racdb_licenseinfo
(
p_target_guid IN mgmt_targets.target_guid%TYPE,
p_pack_name IN mgmt_licensed_targets.pack_name%TYPE
)
IS
l_current_license_status VARCHAR2(10);
l_cluster_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;
l_cluster_instance_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;
l_clustdb_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;
l_asm_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;
l_cluster_node_guid mgmt_targets.target_guid%TYPE DEFAULT NULL;
l_cluster_node_name mgmt_targets.host_name%TYPE;
l_cluster_cur cursorType;
rac_cur cursorType;
asm_cur cursorType;
BEGIN
IF ((p_target_guid IS NULL) AND (p_pack_name is NULL)) THEN
raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
'Either the Target GUID or the pack name is not specified');
END IF;
--check if the rac_database is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>p_target_guid,
p_pack_label=>p_pack_name,
p_from_target_guid=>p_target_guid
);
--if FALSE then insert into mgmt_licensed_targets
--Insert the rac_database association IN mgmt_licensed_targets as
-- Target_guid Pack_id From_Target_guid
--================================================================
--
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(p_target_guid,p_pack_name,p_target_guid);
END IF;
--Given the rac Database GUID retreive its cluster instance guid
-- and the corresponding cluster node associated with it
get_rac_data(
p_target_guid=>p_target_guid,--racdb guid
rac_cursor =>rac_cur
);
LOOP
FETCH
rac_cur
INTO
l_cluster_instance_guid,
l_cluster_node_name;
EXIT WHEN rac_cur%NOTFOUND;
--Check if Instance<->rac_databse association is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_cluster_instance_guid,
p_pack_label=>p_pack_name,
p_from_target_guid=>p_target_guid--rac_databse guid
);
--Insert the Instance<->rac-database association in mgmt_licensed_targets as
-- Target_guid Pack_id From_Target_guid
--===================================================
--
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_cluster_instance_guid,p_pack_name,p_target_guid);
END IF;
--get the clusterNode guid based on the cluster node name
l_cluster_node_guid:=mgmt_target.get_target_guid
(
target_name_in=>l_cluster_node_name,
target_type_in=>mgmt_global.G_HOST_TARGET_TYPE
);
--Check if Cluster node<->ClusterInstance association is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_cluster_node_guid,
p_pack_label=>p_pack_name,
p_from_target_guid=>l_cluster_instance_guid
);
--Insert the Cluster node<->Cluster Instance association in mgmt_licensed_targets as
-- Target_guid Pack_id From_Target_guid
--===================================================
--
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_cluster_node_guid,p_pack_name,l_cluster_instance_guid);
END IF;
--Get the cluster guid on which the node recides
get_cluster_data
(
p_cluster_node_guid =>l_cluster_node_guid,
p_target_type =>MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE,
p_cluster_cursor =>l_cluster_cur
);
LOOP FETCH l_cluster_cur INTO l_cluster_guid;
EXIT WHEN l_cluster_cur%NOTFOUND;
--Check if Cluster<->ClusterNode association is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_cluster_guid,
p_pack_label=>p_pack_name,
p_from_target_guid=>l_cluster_node_guid
);
--Insert the Cluster guid<->Cluster Node association
-- Target_guid Pack_id From_Target_guid
--=============================================================
--
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_cluster_guid,p_pack_name,l_cluster_node_guid);
END IF;
END LOOP; --end of l_cluster_cur --
END LOOP;--end of rac_cr--
END grant_racdb_licenseinfo;
-- Public Procedure->rac_license_assoc_create
-- Purpose :The procedure grant license on the selected packs on the Cluster database
-- and its memmber and assocciations
-- Input Parameters
-- p_assoc_def_name --Association def name for which this callback should be called (optional).
-- If assoc def name is not specified, then the callback is called
-- for associations of all types.
-- p_source_target_name -Target name of the Cluster Db from which the association needs to be remove
-- p_source_target_type -Target type of the Cluster Db i.e rac_database
-- p_assoc_target_name --Associate target name whose license info needs to be deleted
-- p_assoc_target_type --Associate target type whose license info needs to be deleted
-- p_scope_target_name
-- p_scope_target_type
--source_target_guid assoc_target_guid assoc_type--
--------------------------------------------------------
--Cluster DB cluster_instance member
--cluster_db cluster_node hosted by
--cluster_instance cluster_node hosted by
--cluster cluster_node member
--ias host hosted by
PROCEDURE rac_license_assoc_create
(
p_assoc_def_name VARCHAR2,
p_source_target_name VARCHAR2,
p_source_target_type VARCHAR2,
p_assoc_target_name VARCHAR2,
p_assoc_target_type VARCHAR2,
p_scope_target_name VARCHAR2,
p_scope_target_type VARCHAR2
)
IS
l_component_target_guid mgmt_targets.target_guid%TYPE;
l_source_target_guid mgmt_targets.target_guid%TYPE;
l_current_license_status VARCHAR2(20);
l_cluster_guid mgmt_targets.target_guid%TYPE;
l_count NUMBER;
asm_cur cursortype;
l_asm_guid mgmt_targets.target_guid%TYPE;
l_cluster_cur cursortype;
BEGIN
--get the source guid based on source_target_name and type
l_source_target_guid := mgmt_target.get_target_guid
(
target_type_in =>p_source_target_type,
target_name_in =>p_source_target_name
);
--get the association guid based on assoc_target_name and type
l_component_target_guid := mgmt_target.get_target_guid
(
target_type_in =>p_assoc_target_type,
target_name_in =>p_assoc_target_name
);
IF ( p_source_target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE AND
p_assoc_target_type=mgmt_global.G_DATABASE_TARGET_TYPE ) THEN
-- The license from RAC instance will get deleted
DELETE
mgmt_licensed_targets
WHERE
target_guid=l_component_target_guid;
-- The license from the Cluster Node and ASM (if any) will get deleted
DELETE
mgmt_licensed_targets
WHERE
from_target_guid=l_component_target_guid;
-- Get the package information belonging to the rac database
FOR c1 IN
(
SELECT
distinct pack_name
FROM
mgmt_licensed_targets
WHERE
from_target_guid=l_source_target_guid
)
LOOP
IF (p_assoc_target_type=mgmt_global.G_DATABASE_TARGET_TYPE) THEN
--p_assoc_target_type is>oracle-database
--check if an asm is associated with a standalone database
EM_RAC_LICENSE.get_asm_data
(
p_target_guid=>l_component_target_guid,
p_asm_cursor=>asm_cur
);
LOOP
FETCH
asm_cur
INTO
l_asm_guid;
EXIT WHEN asm_cur%NOTFOUND;
--check if asm is already licensed--
--if not licensed then insert into mgmt_licensed_targets table as
-- TARGET_GUID FROM_TARGET_GUID-
-- -
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_asm_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>l_component_target_guid
);
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_asm_guid,c1.pack_name,l_component_target_guid);
END IF;
END LOOP;--end asm_cur
END IF;
--Check if instance<->rac_database association is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_component_target_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>l_source_target_guid
);
--Insert rac_database association if not licensed
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_component_target_guid,c1.pack_name,l_source_target_guid);
END IF;
-- Get all the associations of the rac instance such as its node, ASM etc.
FOR c_assoc IN
(
SELECT
mgmt_target_assocs.assoc_target_guid,mgmt_targets.target_type
FROM
mgmt_target_assocs, mgmt_targets
WHERE
mgmt_target_assocs.source_target_guid = l_component_target_guid AND
mgmt_target_assocs.assoc_target_guid = mgmt_targets.target_guid AND
mgmt_targets.target_type IN (
SELECT
target_type
FROM
mgmt_licensable_target_types
WHERE
pack_target_type=p_assoc_target_type
)
)
LOOP
-- Check if the individual associations are already licnesed due to
-- the license on the cluster instance.
-- If not then grant license to the associations.
-- Check if the associations contains cluster node.
-- If yes, get the cluster guid on which the cluster node resides.
IF (c_assoc.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>c_assoc.assoc_target_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>l_component_target_guid
);
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(c_assoc.assoc_target_guid,c1.pack_name,l_component_target_guid);
END IF;
--Get the cluster guid on which the node recides
get_cluster_data
(
p_cluster_node_guid =>c_assoc.assoc_target_guid,
p_target_type =>MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE,
p_cluster_cursor =>l_cluster_cur
);
LOOP FETCH l_cluster_cur INTO l_cluster_guid;
EXIT WHEN l_cluster_cur%NOTFOUND;
-- Check if Cluster <->Cluster Node combination is already licensed.
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>l_cluster_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>c_assoc.assoc_target_guid
);
-- If not, then grant license to the combination for the package.
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(l_cluster_guid,c1.pack_name,c_assoc.assoc_target_guid);
END IF;
END LOOP; -- end loop of l_cluster_cur
ELSIF (c_assoc.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE) THEN
SELECT
count(*)
INTO
l_count
FROM
mgmt_targets t,
mgmt_target_properties p
WHERE
t.target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE AND
p.property_name = MGMT_GLOBAL.G_SID_PROPERTY_NAME AND
t.target_guid = p.target_guid AND
t.target_guid = c_assoc.assoc_target_guid AND
UPPER(p.property_value) =
(
SELECT
upper(property_value)
FROM
mgmt_target_properties
WHERE
property_name =MGMT_GLOBAL.G_OSM_PROPERTY_NAME AND
property_value != ' ' AND
target_guid = l_component_target_guid--
) ;
IF (l_count>0) THEN
--Check if ASM is licensed
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>c_assoc.assoc_target_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>l_component_target_guid
);
--Insert the asm guid<->instance guid association in mgmt_licensed_targets
-- -
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(c_assoc.assoc_target_guid,c1.pack_name,l_component_target_guid);
END IF;
END IF;
ELSE
l_current_license_status:= em_license.is_target_pack_licensed
(
p_target_guid=>c_assoc.assoc_target_guid,
p_pack_label=>c1.pack_name,
p_from_target_guid=>l_component_target_guid
);
IF (l_current_license_status='FALSE') THEN
INSERT INTO mgmt_licensed_targets
(target_guid, pack_name, from_target_guid)
VALUES
(c_assoc.assoc_target_guid,c1.pack_name,l_component_target_guid);
END IF;
END IF;
END LOOP;-- FOR c_assoc
END LOOP; -- FOR C1
END IF;
END rac_license_assoc_create;
-- Public Procedure->rac_license_assoc_delete
-- Purpose :When an association to a cluster database is removed than the corresponding
-- license entries from mgmt_licensed_target table is deleted
-- Input Parameters
-- p_assoc_def_name
-- p_source_target_name -Target name of the Cluster Db from which the association needs to be remove
-- p_source_target_type -Target type of the Cluster Db i.e rac_database
-- p_assoc_target_name --Associate target name whose license info needs to be deleted
-- p_assoc_target_type --Associate target type whose license info needs to be deleted
-- p_scope_target_name
-- p_scope_target_type
PROCEDURE rac_license_assoc_delete
(
p_assoc_def_name VARCHAR2,
p_source_target_name VARCHAR2,
p_source_target_type VARCHAR2,
p_assoc_target_name VARCHAR2,
p_assoc_target_type VARCHAR2,
p_scope_target_name VARCHAR2,
p_scope_target_type VARCHAR2
)
IS
l_component_target_guid mgmt_targets.target_guid%type DEFAULT NULL;
l_source_target_guid mgmt_targets.target_guid%type DEFAULT NULL;
l_cluster_node_guid mgmt_targets.target_guid%type DEFAULT NULL;
l_cluster_guid mgmt_targets.target_guid%type DEFAULT NULL;
l_count NUMBER;
l_cluster_cur cursortype;
BEGIN
--get the guid of the source to be deleted
l_source_target_guid := mgmt_target.get_target_guid
(
target_type_in =>p_source_target_type,
target_name_in => p_source_target_name
);
--get the guid of the association to be deleted
l_component_target_guid := mgmt_target.get_target_guid
(
target_type_in =>p_assoc_target_type,
target_name_in => p_assoc_target_name
);
IF ( p_source_target_type=mgmt_global.G_RAC_DATABASE_TARGET_TYPE AND
p_assoc_target_type=mgmt_global.G_DATABASE_TARGET_TYPE ) THEN
--license information for the instance is deleted
DELETE
mgmt_licensed_targets
WHERE
target_guid=l_component_target_guid OR --instance license info
from_target_guid=l_component_target_guid; --node license info
ELSIF (p_source_target_type=mgmt_global.G_DATABASE_TARGET_TYPE) THEN
--when a instance association is deleted the license into about itself is deleted
--then there is a check if to see if the node is licensed by any other inatsnce
--if not then the node license information is deleted
--get cluster node
SELECT
target_guid
INTO
l_cluster_node_guid
FROM
mgmt_targets
WHERE
target_name IN
(
SELECT
host_name
FROM
mgmt_targets
WHERE
target_guid = l_source_target_guid
);
-- There may be multiple instances on the same cluster node.
-- So, deleting one rac instance should not remove the license from the
-- cluster due to cluster node.
-- So, first check if there are other instances which are on the same node.
SELECT
count(*)
INTO
l_count
FROM
mgmt_licensed_targets
WHERE
target_guid = l_cluster_node_guid;
IF (l_count = 0 ) THEN
--get cluster guid
EM_RAC_LICENSE.get_cluster_data
(
p_cluster_node_guid =>l_cluster_node_guid,
p_target_type =>mgmt_global.G_CLUSTER_TARGET_TYPE,
p_cluster_cursor =>l_cluster_cur
);
LOOP FETCH l_cluster_cur INTO l_cluster_guid;
EXIT WHEN l_cluster_cur%NOTFOUND;
DELETE
mgmt_licensed_targets
WHERE
from_target_guid=l_cluster_node_guid AND
target_guid=l_cluster_guid;
END LOOP; -- end loop for l_cluster_cur
END IF;--l_count
END IF;
END rac_license_assoc_delete;
END em_rac_license;
/