Rem $Header: rec_post_data_upgrade.sql 18-jul-2007.19:05:46 pbantis Exp $
Rem
Rem rec_post_data_upgrade.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      rec_post_data_upgrade.sql
Rem
Rem    DESCRIPTION
Rem      Migrate Backup and BkpMgmt jobs to 10.2. 
Rem
Rem      This MUST be called after the registration of db job types because
Rem      when a new version of a job type is registered, all executions which
Rem      are currently in scheduled state are rescheduled (with the new jobtype
Rem      version). In this process, we first clean out all the old parameters
Rem      for the execution, re-run parameter sources and copy the job parameters
Rem      to the execution.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pbantis     07/18/07 - Set user to that of job owner.
Rem    pbantis     09/23/05 - pbantis_bug-4620012
Rem    pbantis     09/23/05 - Upgrade job parameters instead of adding. 
Rem    pbantis     09/22/05 - Created.
Rem

-- Upgrade Backup jobs.
set DEFINE OFF
DECLARE
  l_job_type VARCHAR2(64) := 'Backup';
  current_user VARCHAR2(128);
                                                                              
  -- 1st large input parameter: rman_perl_script
  l_origscript_param_name VARCHAR2(64) := 'rman_perl_script';
  l_origscript_value_clob CLOB;
  l_origscript_value_old VARCHAR2(64) := '$result = backup();';
  l_origscript_value_new VARCHAR2(256) := '&br_save_agent_env(); &br_prebackup($l_db_connect_string, $l_is_cold_backup, $l_use_rcvcat, $l_db_10_or_higher, $l_backup_strategy); my $result = &br_backup();';
  l_backup_index NUMBER;
                                                                              
  -- 2nd large input parameter: modified_rman_perl_script
  l_modscript_param_name VARCHAR2(64) := 'modified_rman_perl_script';
  l_modscript_param_found NUMBER := 1;
  l_modscript_value_id RAW(16);
  l_modscript_value_clob CLOB;
  l_modscript_value_string VARCHAR2(1) := ' ';

  -- regular parameter: db_name
  l_dbname_param_name VARCHAR2(64) := 'db_name';
  l_dbname_param_found NUMBER := 1;
  l_dbname_value_string VARCHAR2(256);
  l_reg_params_list MGMT_JOB_PARAM_LIST;
                                                                              
BEGIN                                                                              
  -- Save the currently logged in user
  current_user := mgmt_user.get_current_em_user();
  DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: current_user : ' || current_user);

  -- Loop through all of the backup jobs.
  FOR crec IN (SELECT j.job_name, j.job_owner, j.job_id, e.execution_id
    FROM MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY e
    WHERE j.job_id=e.job_id
    AND j.job_type=l_job_type
    AND e.status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
      MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
  LOOP
    BEGIN
    
      -- 11gR1 Need to be the job owner to get large parameter
      setemusercontext(crec.job_owner, MGMT_USER.OP_SET_IDENTIFIER);
    
      -- 1) Update the 1st large parameter 'rman_perl_script' .

      -- Get the 'rman_perl_script' large parameter value (for update).                                                                              
      l_origscript_value_clob := MGMT_JOB_ENGINE.GET_LARGE_PARAM(crec.job_id,
        crec.execution_id, l_origscript_param_name, 1);
      
      -- Find the old backup command in the 'rman_perl_script' large parameter value.
      l_backup_index := INSTR(l_origscript_value_clob, l_origscript_value_old);
      IF (l_backup_index > 0)
      THEN
        -- Insert the new backup command (which will overwrite the remainder of the script).
        DBMS_LOB.WRITE(l_origscript_value_clob, LENGTH(l_origscript_value_new), l_backup_index, l_origscript_value_new);
        COMMIT;
      END IF;
                                                                          
      -- 2) Create the 2nd large input parameter 'modified_rman_perl_script' if needed.
      SELECT count(*) INTO l_modscript_param_found
      FROM MGMT_JOB_PARAMETER
      WHERE job_id=crec.job_id
      AND parameter_name=l_modscript_param_name;
                                                                          
      IF (l_modscript_param_found = 0)
      THEN
        DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding parameter: ' || l_modscript_param_name);

        -- Add the parameter for the job.
        DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for job id=' || crec.job_id); 
        INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
          parameter_name, parameter_type, large_value)
        VALUES (crec.job_id, MGMT_JOB_ENGINE.NO_EXECUTION,
          l_modscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE, SYS_GUID())
        RETURNING large_value INTO l_modscript_value_id;
                                                                        
        -- Add the parameter value.
        INSERT INTO MGMT_JOB_LARGE_PARAMS(param_id, param_value)
        VALUES (l_modscript_value_id, empty_clob())
        RETURNING param_value INTO l_modscript_value_clob;
        DBMS_LOB.WRITE(l_modscript_value_clob, LENGTH(l_modscript_value_string), 1, l_modscript_value_string);

        -- Add the parameter for all of the (pertinent) executions.
        FOR drec IN (SELECT execution_id
          FROM MGMT_JOB_EXEC_SUMMARY
          WHERE job_id=crec.job_id
          AND status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
            MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
        LOOP
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for execution id=' || drec.execution_id); 
          INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
            parameter_name, parameter_type, large_value)
          VALUES (crec.job_id, drec.execution_id,
            l_modscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE, l_modscript_value_id);
        END LOOP;

        COMMIT;
      END IF;

      -- 3) Create the regular parameter 'db_name' if needed.
      SELECT count(*) INTO l_dbname_param_found
      FROM MGMT_JOB_PARAMETER
      WHERE job_id=crec.job_id
      AND parameter_name=l_dbname_param_name;
                                                                                                                                                    
      IF (l_dbname_param_found = 0)
      THEN
        BEGIN
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding parameter: ' || l_dbname_param_name);

          -- Get the db name from the repository.
          SELECT database_name INTO l_dbname_value_string
          FROM MGMT$DB_DBNINSTANCEINFO
          WHERE target_guid=
            (SELECT target_guid FROM MGMT_JOB_TARGET WHERE job_id=crec.job_id
             AND execution_id=crec.execution_id);
  
          -- Build the parameter list.
          l_reg_params_list := MGMT_JOB_PARAM_LIST();
          l_reg_params_list.extend(1);
          l_reg_params_list(1) := MGMT_JOB_PARAM_RECORD(l_dbname_param_name,
            MGMT_JOBS.PARAM_TYPE_SCALAR, l_dbname_value_string, null);
                                                                          
          -- Add the parameter for the job.
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for job id=' || crec.job_id); 
          MGMT_JOB_ENGINE.update_job_parameters(crec.job_id, MGMT_JOB_ENGINE.NO_EXECUTION, 
            l_reg_params_list, false, false, true, 1);           

          -- Add the parameter for all of the (pertinent) executions.
          FOR drec IN (SELECT execution_id
            FROM MGMT_JOB_EXEC_SUMMARY
            WHERE job_id=crec.job_id
            AND status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
              MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
          LOOP
            DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for execution id=' || drec.execution_id); 
            MGMT_JOB_ENGINE.update_job_parameters(crec.job_id, drec.execution_id, 
              l_reg_params_list, false, false, true, 1);           
          END LOOP;

          COMMIT;
        EXCEPTION WHEN NO_DATA_FOUND
        THEN
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Error occured while updating the db_name parameter.');
          setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Job name ' || crec.job_name || ' for job owner ' || 
            crec.job_owner || ' for job_id ' || crec.job_id || 
            ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
        END;          
      END IF;

    -- Revert back to the current user.
    setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);                                                                        
                                                                                                                                                      
    EXCEPTION WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Exception occured while updating Backup job');
      setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);
      DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Job name ' || crec.job_name || ' for job owner ' || 
        crec.job_owner || ' for job_id ' || crec.job_id || 
        ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
    END;
                                                                              
  END LOOP;
END;
/
SET DEFINE ON
show errors;

-- Upgrade Backup Mgmt jobs.
set DEFINE OFF
DECLARE
  l_job_type VARCHAR2(64) := 'BkpMgmt';
  current_user VARCHAR2(128);
                                                                              
  -- regular parameter: rman_script
  l_rmanscript_param_name VARCHAR2(64) := 'rman_script';
  l_rmanscript_param_found NUMBER := 0;
  l_rmanscript_value_string VARCHAR2(4000);

  -- 1st large input parameter: rman_perl_script
  l_origscript_param_name VARCHAR2(64) := 'rman_perl_script';
  l_origscript_param_found NUMBER := 1;
  l_origscript_value_id RAW(16);
  l_origscript_value_clob CLOB;
  l_origscript_value_string1 VARCHAR2(64) := '$rman_script="';
  l_origscript_value_string2 VARCHAR2(128) := '"; my $result=&br_rman($l_use_rcvcat, $l_db_connect_string); exit($result);';
                                                                              
  -- 2nd large input parameter: modified_rman_perl_script
  l_modscript_param_name VARCHAR2(64) := 'modified_rman_perl_script';
  l_modscript_param_found NUMBER := 1;
  l_modscript_value_id RAW(16);
  l_modscript_value_clob CLOB;
  l_modscript_value_string VARCHAR2(1) := ' ';                                                                              
BEGIN                                                                                
  -- Save the currently logged in user
  current_user := mgmt_user.get_current_em_user();
  DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: current_user : ' || current_user);

  -- Loop through all of the backup management jobs.
  FOR crec IN (SELECT j.job_name, j.job_owner, j.job_id, e.execution_id
    FROM   MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY e
    WHERE  j.job_id=e.job_id
    AND j.job_type=l_job_type
    AND e.status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
      MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
  LOOP
    BEGIN
    
      -- 11gR1 Need to be the job owner to get large parameter
      setemusercontext(crec.job_owner, MGMT_USER.OP_SET_IDENTIFIER);
    
      -- 1) Get the 'rman_script' parameter value
      SELECT count(*) INTO l_rmanscript_param_found
      FROM MGMT_JOB_PARAMETER
      WHERE job_id=crec.job_id
      AND execution_id=crec.execution_id
      AND parameter_name=l_rmanscript_param_name;
                                                                          
      IF (l_rmanscript_param_found = 1)
      THEN
        -- Get the rman_script value
        SELECT scalar_value INTO l_rmanscript_value_string
        FROM MGMT_JOB_PARAMETER
        WHERE job_id=crec.job_id
        AND execution_id=crec.execution_id
        AND parameter_name=l_rmanscript_param_name;

        -- 1a) Create the 1st large parameter 'rman_perl_script' if needed
        SELECT count(*) INTO l_origscript_param_found
        FROM MGMT_JOB_PARAMETER
        WHERE job_id=crec.job_id
        AND parameter_name=l_origscript_param_name;
                                                                            
        IF (l_origscript_param_found = 0)
        THEN                                                                          
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding parameter: ' || l_origscript_param_name);

          -- Add the parameter for the job.
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for job id=' || crec.job_id); 
          INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
            parameter_name, parameter_type, large_value)
          VALUES
            (crec.job_id, MGMT_JOB_ENGINE.NO_EXECUTION,
             l_origscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE,
             SYS_GUID())
          RETURNING large_value INTO l_origscript_value_id;
                                                                          
          -- Add the parameter value.
          INSERT INTO MGMT_JOB_LARGE_PARAMS(param_id, param_value)
          VALUES (l_origscript_value_id, empty_clob())
          RETURNING param_value INTO l_origscript_value_clob;

          -- Add the rman script variable ($rman_script=)                                                                                         
          DBMS_LOB.WRITE(l_origscript_value_clob, LENGTH(l_origscript_value_string1), 1, l_origscript_value_string1);
          -- Append the rman script value.
          DBMS_LOB.WRITEAPPEND(l_origscript_value_clob, LENGTH(l_rmanscript_value_string), l_rmanscript_value_string);
          -- Append the call to br_rman().
          DBMS_LOB.WRITEAPPEND(l_origscript_value_clob, LENGTH(l_origscript_value_string2), l_origscript_value_string2);                                                                            

          -- Add the parameter for all of the (pertinent) executions.
          FOR drec IN (SELECT execution_id
            FROM MGMT_JOB_EXEC_SUMMARY
            WHERE job_id=crec.job_id
            AND status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
              MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
          LOOP
            DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for execution id=' || drec.execution_id); 
            INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
              parameter_name, parameter_type, large_value)
            VALUES (crec.job_id, drec.execution_id,
              l_origscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE, l_origscript_value_id);
          END LOOP;

          COMMIT;
        END IF;
      END IF;
                                                                          
      -- 2) Create the 2nd large input parameter 'modified_rman_perl_script' if needed.
      SELECT count(*) INTO l_modscript_param_found
      FROM MGMT_JOB_PARAMETER
      WHERE job_id=crec.job_id
      AND parameter_name=l_modscript_param_name;
                                                                          
      IF (l_modscript_param_found = 0)
      THEN
        DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding parameter: ' || l_modscript_param_name);
                                                                        
        -- Add the parameter for the job.
        DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for job id=' || crec.job_id); 
        INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
          parameter_name, parameter_type, large_value)
        VALUES
          (crec.job_id, MGMT_JOB_ENGINE.NO_EXECUTION,
           l_modscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE,
           SYS_GUID())
        RETURNING large_value INTO l_modscript_value_id;
                                                                        
        -- Add the parameter value.
        INSERT INTO MGMT_JOB_LARGE_PARAMS(param_id, param_value)
        VALUES (l_modscript_value_id, empty_clob())
        RETURNING param_value INTO l_modscript_value_clob;
        DBMS_LOB.WRITE(l_modscript_value_clob, LENGTH(l_modscript_value_string), 1, l_modscript_value_string);

        -- Add the parameter for all of the (pertinent) executions.
        FOR drec IN (SELECT execution_id
          FROM MGMT_JOB_EXEC_SUMMARY
          WHERE job_id=crec.job_id
          AND status IN (MGMT_JOBS.SCHEDULED_STATUS, MGMT_JOBS.SUSPENDED_STATUS, 
            MGMT_JOBS.AGENTDOWN_STATUS, MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS))
        LOOP
          DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Adding the parameter for execution id=' || drec.execution_id); 
          INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
            parameter_name, parameter_type, large_value)
          VALUES (crec.job_id, drec.execution_id,
            l_modscript_param_name, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE, l_modscript_value_id);
        END LOOP;

        COMMIT;
      END IF;

    -- Revert back to the current user.
    setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);                                                                        
                                                                            
    EXCEPTION WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Exception occured while updating Backup Management job');
      setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);      
      DBMS_OUTPUT.PUT_LINE('rec_post_data_upgrade: Job name ' || crec.job_name || ' for job owner ' || 
        crec.job_owner || ' for job_id ' || crec.job_id || 
        ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM);
    END;
                                                                              
  END LOOP;
END;
/
SET DEFINE ON
show errors;