6 Replies Latest reply: Jun 13, 2012 7:26 PM by wbovard RSS

    SYSMAN.EM_FLAT_COMPTGT will not compile.

    879272
      I am on a 11.2.0.1 database and am having a problem compiling a couple of Packages in the SYSMAN schema. The first is the

      EM_FLAT_COMPTGT package. It keeps giving me the following error:

      Line # = 18 Column # = 14 Error Text = PLS-00302: component 'ENTER_SUPER_USER_MODE' must be declared
      Line # = 18 Column # = 4 Error Text = PL/SQL: Statement ignored
      Line # = 67 Column # = 14 Error Text = PLS-00302: component 'LEAVE_SUPER_USER_MODE' must be declared
      Line # = 67 Column # = 4 Error Text = PL/SQL: Statement ignored
      Line # = 75 Column # = 19 Error Text = PLS-00302: component 'LEAVE_SUPER_USER_MODE' must be declared
      Line # = 75 Column # = 9 Error Text = PL/SQL: Statement ignored

      Here is the code:
       AS
      
      
      ---
      --- PURPOSE
      ---    Updates the exploded memberships of the specified
      ---    group/composite in MGMT_FLAT_TARGET_MEMBERSHIPS
      ---    table.
      ---
      PROCEDURE update_flat_memberships(v_composite_guid IN RAW)
      IS
        l_tname mgmt_targets.TARGET_NAME%TYPE;
        l_ttype mgmt_targets.TARGET_TYPE%TYPE;
        l_current_user VARCHAR2(64) := NULL;
      BEGIN
         -- We go to super user mode here as the user modifying target may not
         -- have privileges on all possible parent groups.
          MGMT_USER.enter_super_user_mode(l_current_user);
      
         -- Delete the existing memberships
         DELETE FROM mgmt_flat_target_memberships
          WHERE composite_target_guid = v_composite_guid;
      
         -- Get the target name/type for the composite
         -- Do nothing if the target is not found in MGMT_TARGETS.
         -- Possibly the target is under deletion and therefore
         -- there is not need to recalculate the flat targets list.
         BEGIN
           SELECT target_name, target_type
             INTO l_tname, l_ttype
             FROM mgmt_targets
            WHERE target_guid = v_composite_guid;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             RETURN;
         END;
      
         -- Now recursively fetch the memberships from the
         -- mgmt_target_memberships table
         INSERT INTO mgmt_flat_target_memberships
           (composite_target_name,  composite_target_type, composite_target_guid,
            member_target_name, member_target_type,  member_target_guid,
            is_group_memb)
           SELECT DISTINCT l_tname, l_ttype, v_composite_guid,
                 member_target_name, member_target_type, member_target_guid, 0
             FROM  mgmt_target_memberships
            START WITH composite_target_guid = v_composite_guid
            CONNECT BY PRIOR member_target_guid = composite_target_guid;
      
         -- Set is_group flag to 1 for memberships that are derived from a group
         UPDATE mgmt_flat_target_memberships f1
            SET is_group_memb = 1
          WHERE composite_target_guid = v_composite_guid
           AND  EXISTS
               (SELECT 1
                  FROM   mgmt_flat_target_memberships f2,
                         mgmt_target_memberships m,
                         mgmt_type_properties p
                  WHERE  p.property_name = 'is_group'
                    AND  p.target_type   = m.composite_target_type
                    AND  (m.composite_target_guid = f2.member_target_guid OR
                          m.composite_target_guid = f2.composite_target_guid)
                    AND  f1.composite_target_guid = f2.composite_target_guid
                    AND  f1.member_target_guid = m.member_target_guid);
      
         -- Go out of superuser mode
         MGMT_USER.leave_super_user_mode(l_current_user);
      
      EXCEPTION
        WHEN OTHERS THEN
           dbms_output.put_line('Exit update_flat_memberships : ' || SQLERRM);
      
           -- Go out of superuser mode
           IF l_current_user IS NOT NULL THEN
              MGMT_USER.leave_super_user_mode(l_current_user);
           END IF;
           RAISE;
      
      END update_flat_memberships;
      
      
      ---
      --- PURPOSE
      ---   Updates the flattened list for parent groups of the specified
      ---   composite/group target.
      ---
      PROCEDURE update_parent_flat_memberships(v_composite_guid IN RAW)
      IS
        CURSOR parent_cursor IS
          SELECT DISTINCT composite_target_guid
            FROM mgmt_target_memberships
           START WITH member_target_guid = v_composite_guid
           CONNECT BY prior composite_target_guid = member_target_guid;
      
        TYPE p_parent_guids IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;
        l_parent_guids  p_parent_guids;
      BEGIN
        OPEN parent_cursor;
        FETCH parent_cursor BULK COLLECT INTO l_parent_guids;
        CLOSE parent_cursor;
      
        FOR i IN 1..l_parent_guids.COUNT LOOP
          UPDATE_FLAT_MEMBERSHIPS(l_parent_guids(i));
        END LOOP;
      
      END update_parent_flat_memberships;
      
      -- Adds the specified target to the specified groups lists
      PROCEDURE add_target_to_flat_memberships(v_target_name IN VARCHAR2,
                                               v_target_type IN VARCHAR2,
                                               v_target_guid IN RAW,
                                               v_groups IN SMP_EMD_NVPAIR_ARRAY)
      IS
        CURSOR parent_cursor IS
          SELECT DISTINCT composite_target_guid
            FROM mgmt_target_memberships
           START WITH member_target_guid IN
              (SELECT t.target_guid
                 FROM mgmt_targets t,
                      TABLE(CAST(v_groups AS SMP_EMD_NVPAIR_ARRAY)) g
                WHERE t.target_name = g.name
                  AND t.target_type = g.value)
           CONNECT BY prior composite_target_guid = member_target_guid;
      
        TYPE p_parent_guids IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER;
        l_parent_guids  p_parent_guids;
        l_tname mgmt_targets.TARGET_NAME%TYPE;
        l_ttype mgmt_targets.TARGET_TYPE%TYPE;
        l_group_guid  mgmt_targets.TARGET_GUID%TYPE;
      BEGIN
      
        -- Add the member to the parent groups
        FOR i IN 1..v_groups.COUNT LOOP
          SELECT target_guid
            INTO l_group_guid
            FROM mgmt_targets
           WHERE target_name = v_groups(i).name
             AND target_type = v_groups(i).value;
      
          -- Try inserting the target first; if it fails, update the
          -- existing membership
          BEGIN
            INSERT INTO mgmt_flat_target_memberships
              (composite_target_name,  composite_target_type, composite_target_guid,
               member_target_name, member_target_type,  member_target_guid,
               is_group_memb)
            VALUES
               (v_groups(i).name, v_groups(i).value, l_group_guid,
                v_target_name, v_target_type, v_target_guid, 1);
          EXCEPTION
            -- If the membership entry already exists, change the
            -- is_group_memb to 1 if needed
            WHEN DUP_VAL_ON_INDEX THEN
              UPDATE mgmt_flat_target_memberships
                 SET is_group_memb = 1
               WHERE composite_target_guid = l_group_guid
                 AND member_target_guid = v_target_guid
                 AND is_group_memb = 0;
          END;
        END LOOP;
      
      
        -- Now add the grand parents of the parent groups
        OPEN parent_cursor;
        FETCH parent_cursor BULK COLLECT INTO l_parent_guids;
        CLOSE parent_cursor;
      
        FOR i IN 1..l_parent_guids.COUNT LOOP
           -- Get the target name/type for the composite
          SELECT target_name, target_type
            INTO l_tname, l_ttype
            FROM mgmt_targets
           WHERE target_guid = l_parent_guids(i);
      
          -- Try inserting the target first; if it fails, update the
          -- existing membership
          BEGIN
            INSERT INTO mgmt_flat_target_memberships
              (composite_target_name,  composite_target_type, composite_target_guid,
               member_target_name, member_target_type,  member_target_guid,
               is_group_memb)
            VALUES
               (l_tname, l_ttype, l_parent_guids(i),
                v_target_name, v_target_type, v_target_guid, 1);
          EXCEPTION
            -- If the membership entry already exists, change the
            -- is_group_memb to 1 if needed
            WHEN DUP_VAL_ON_INDEX THEN
              UPDATE mgmt_flat_target_memberships
                 SET is_group_memb = 1
               WHERE composite_target_guid = l_parent_guids(i)
                 AND member_target_guid = v_target_guid
                 AND is_group_memb = 0;
          END;
      
        END LOOP;
      END add_target_to_flat_memberships;
      
      ---
      --- PURPOSE
      ---    Updates all parent groups upon deletion of a group.
      ---
      PROCEDURE handle_delete_group(v_composite_guid IN RAW)
      IS
        l_parent_guids MGMT_GUID_ARRAY;
      BEGIN
      
        -- Get the parents of the group being deleted
        SELECT MGMT_GUID_OBJ(composite_target_guid, NULL)
          BULK COLLECT INTO l_parent_guids
          FROM mgmt_flat_target_memberships
         WHERE member_target_guid = v_composite_guid;
      
        -- Delete the current memberships for the group and its parent groups
        DELETE FROM mgmt_flat_target_memberships
         WHERE (composite_target_guid IN
                  (SELECT guid FROM TABLE(CAST(l_parent_guids AS MGMT_GUID_ARRAY)))
                OR composite_target_guid = v_composite_guid);
      
        -- Finally update the parents memberships
        FOR i IN 1..l_parent_guids.COUNT LOOP
          UPDATE_FLAT_MEMBERSHIPS(l_parent_guids(i).guid);
        END LOOP;
      
      END handle_delete_group;
      
      
      ---
      --- PURPOSE
      ---   Called from loader before inserting rows into
      ---   MGMT_TARGET_MEMBERSHIPS table
      ---
      PROCEDURE start_memberships_rowset
      IS
      BEGIN
           p_updated_composite_targets := SMP_EMD_NVPAIR_ARRAY();
      END start_memberships_rowset;
      
      ---
      --- PURPOSE
      ---   Called whenever a composite target memberships are modified
      ---   during a Loader session
      ---
      PROCEDURE composite_target_modified(v_composite_target_name VARCHAR2,
                                          v_composite_target_type VARCHAR2)
      IS
        l_count NUMBER;
      BEGIN
         -- If the package variable is null, then we are not in a loader
         -- session
         IF (p_updated_composite_targets IS NULL) THEN
            RETURN;
         END IF;
      
         -- Add the composite target to the list if required
         BEGIN
           SELECT 1
             INTO l_count
             FROM TABLE(CAST(p_updated_composite_targets AS SMP_EMD_NVPAIR_ARRAY)) c
            WHERE c.name   = v_composite_target_name
              AND c.value  = v_composite_target_type;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             p_updated_composite_targets.extend(1);
             p_updated_composite_targets(p_updated_composite_targets.LAST) :=
               SMP_EMD_NVPAIR(v_composite_target_name, v_composite_target_type);
         END;
      
      END composite_target_modified;
      
      ---
      --- PURPOSE
      ---   Called from loader when done with inserting rows into
      ---   MGMT_TARGET_MEMBERSHIPS table
      ---
      PROCEDURE end_memberships_rowset
      IS
        l_target_guid mgmt_targets.TARGET_GUID%TYPE;
      BEGIN
        -- For each of the modified composites, update its memberships
        -- and its parents memberships
        FOR idx IN 1..p_updated_composite_targets.COUNT LOOP
      
          BEGIN
             SELECT target_guid
               INTO l_target_guid
               FROM mgmt_targets
              WHERE target_name = p_updated_composite_targets(idx).name
                AND target_type = p_updated_composite_targets(idx).value;
      
            update_flat_memberships(l_target_guid);
      
            update_parent_flat_memberships(l_target_guid);
          EXCEPTION
            -- this is called in the context of an agent upload, so never
            -- propagate the errors back to the agent
      
            -- Ignore non-existing targets case
            WHEN NO_DATA_FOUND THEN
               NULL;
      
            -- Log all the other errors
            WHEN OTHERS THEN
              mgmt_log.log_error(v_module_name_in => 'LOADER',
                                 v_error_code_in  =>  0,
                                 v_error_msg_in   => SUBSTR(SQLERRM, 1, 2000));
          END;
      
        END LOOP;
      
        -- Free the object
        p_updated_composite_targets.delete;
        p_updated_composite_targets := NULL;
      
      
      END end_memberships_rowset;
      
      END em_flat_comptgt;
      The second Package that will not compile is the SYSMAN.MGMT_ECM_POLICY Package.

      Here is the error:

      Line # = 855 Column # = 9 Error Text = PL/SQL: Statement ignored
      Line # = 856 Column # = 13 Error Text = PLS-00306: wrong number or types of arguments in call to 'MGMT_BUG_ADVISORY_VIOLATION'

      Here is a subsection of the code that the error refers to:
        BEGIN
          l_cpf_table := MGMT_BUG_ADVISORY_VIOLATIONS();
          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).HOST_NAME <> c1Rec.HOST_NAME OR
               l_cpf_table(l_cpf_table.LAST).HOME_LOCATION <> c1Rec.HOME_LOCATION 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.TARGET_GUID);
            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;
            END IF;
          END LOOP;
          CLOSE C1;
          RETURN l_cpf_table;
        END CPF_QUERY;
      This one is extra long so I only included a subset of the code but I can post the full code if requested.


      Anyone have any ideas?
      I am new to PL/SQL and appreciate any guidance you experts could provide.

      Regards,
      Dave
        • 1. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
          Srini Chavali-Oracle
          Pl post OS version - what changes caused these packages to become invalid ?

          HTH
          Srini
          • 2. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
            879272
            My apologies for the slow response...

            OS is Win 7 64bit.

            Full databases was datapumped from 10.2.0.3 into this database.
            After the datapump, these objects are invalid and do not compile.

            Thanks,
            Dave
            • 3. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
              user1314758
              Did anyone happen to resolve this issue? I am seeing the same problem on all of our 10.2.0.4 databases that we're migrating to 11.2.0.2.0. The same two packages with the same exact errors. We're running Red Hat Enterprise Linux 5.
              • 4. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
                wbovard
                Did you ever get an answer or figure anything out? I have exactly the same problem. I am on AIX and I created a new 11.1.0.7 database, no problem, no invalid objects. Then I did datapump import (from 10.2.0.5) with FULL=Y and I have these two invalid objects:
                package SYSMAN.EM_FLAT_COMPTGT
                package SYSMAN.MGMT_ECM_POLICY

                Thanks,
                Bill Bovard
                william.p.bovard@kp.org
                • 5. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
                  922976
                  Any response to your question yet? I also exported a 10.2.0.3 database and imported into 11.2.0.3 and see these same objects invalid so I was hoping you received some insight. Thanks in advance.
                  • 6. Re: SYSMAN.EM_FLAT_COMPTGT will not compile.
                    wbovard
                    I opened SR 3-5561123911 with Oracle and they instructed me to delete SYSMAN entirely if not planning to use DB Control. It worked for me. Here are the complete instructions they gave me:

                    1-Delete DB Control Configuration Files Manually:

                    Remove the following directories from your filesystem:
                    <ORACLE_HOME>/<hostname_sid>
                    <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

                    NOTE:
                    On Windows you also need to delete the DB Console service:
                    - run regedit
                    - navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
                    - locate the OracleDBConsole<sid> entry and delete it
                    - reboot the machine

                    2-Delete DB Control Repository Objects Manually:

                    Step 1: Drop AQ related objects in the SYSMAN schema
                    Logon SQLPLUS as user SYSMAN
                    SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);

                    Step 2: Drop the DB Control Repository Objects
                    Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
                    SQL> SHUTDOWN IMMEDIATE;
                    SQL> STARTUP RESTRICT;
                    SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
                    SQL> EXEC sysman.setEMUserContext(' ',5);
                    SQL> REVOKE dba FROM sysman;
                    SQL> DECLARE
                    CURSOR c1 IS
                    SELECT owner, synonym_name name
                    FROM dba_synonyms
                    WHERE table_owner = 'SYSMAN';
                    BEGIN
                    FOR r1 IN c1 LOOP
                    IF r1.owner = 'PUBLIC' THEN
                    EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
                    ELSE
                    EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
                    END IF;
                    END LOOP;
                    END;
                    /
                    SQL> DROP USER mgmt_view CASCADE;
                    SQL> DROP ROLE mgmt_user;
                    SQL> DROP USER sysman CASCADE;
                    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

                    This should remove SYSMAN and the views, etc., that correlate with it.
                    So after doing this, SYSMAN will be gone and you can install Grid Control.

                    I am expecting some of the queries to fail due to the inability to find the object. This woudl be considered normal.
                    Let me know how this goes for you.