Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/rec/rec_post_data_upgrade.sql /st_emdbsa_11.2/1 2009/03/30 12:23:46 pbantis Exp $
Rem
Rem rec_post_data_upgrade.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      rec_post_data_upgrade.sql
Rem
Rem    DESCRIPTION
Rem      Fixes (Database) Backup jobs that were incorrectly upgraded from 10.1 to 10.2.
Rem
Rem      When (Database) Backup jobs were upgraded from 10.1 to 10.2, the script 
Rem      incorrectly omitted a Perl 'exit' command ('exit($result);') at the end.
Rem      This script addresses that issue.
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     03/26/09 - Bug 7600450 append Perl 'exit' command.

-- 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) := 'exit($result);';
  l_origscript_value_new VARCHAR2(64) := ' exit($result);';
  l_exit_index NUMBER;

  -- Name used for logging (sysman.emdw_trace_data).
  LOG_NAME CONSTANT VARCHAR2(11) := 'EM.MGMT_REC';

BEGIN                                                                              
  -- Save the currently logged in user
  current_user := mgmt_user.get_current_em_user();
  IF EMDW_LOG.p_is_debug_set
  THEN
    EMDW_LOG.debug('rec_post_data_upgrade: current_user : ' || current_user, LOG_NAME);
  END IF;

  -- 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);
      
      -- Look for the Perl 'exit' command in the 'rman_perl_script' large parameter value.
      l_exit_index := INSTR(l_origscript_value_clob, l_origscript_value_old);
      -- If it can't be found (10.1->10.2 backup job upgrade bug), then append it to the end.
      IF (l_exit_index = 0)
      THEN
        -- Append the Perl exit command.
        DBMS_LOB.WRITEAPPEND(l_origscript_value_clob, LENGTH(l_origscript_value_new), l_origscript_value_new);
        COMMIT;
      ELSE
        ROLLBACK;
      END IF;

      -- Revert back to the current user.
      setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);

    EXCEPTION WHEN OTHERS
    THEN
      EMDW_LOG.error('rec_post_data_upgrade: Exception occured while updating Backup job', LOG_NAME);
      setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);
      EMDW_LOG.error('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, LOG_NAME);
    END;

  END LOOP;
END;
/
SET DEFINE ON
show errors;