Back-out Steps from R12.1 to 12.0-SP1-CUM3

Products

• CA Workload Automation AE

Issue/Introduction

Please provide Back-out Steps from AutoSys R12.1 to  12.0-SP1-CUM3.

Environment

Release : 12.1

Resolution

Autosys Backout - Release 12.1 to 12.0 SP1 CUM3
This document has information on how to backout AutoSys Release 12.1 to 12.0 SP1 CUM3.

Before Upgrade from 12.0 SP1 CUM3 to 12.1
-    Backup 
-    $AUTO_ROOT directory
cp -fpr /opt/CA/WorkloadAutomationAE /opt/CA/WorkloadAutomationAE_r12sp1cum3

-    $CASHCOMP directory.
cp -fpr /opt/CA/SharedComponents /opt/CA/SharedComponents_r12sp1cum3

-    Autosys Startup scripts. All the files matching /etc/init.d/waae* /etc/init.d/CA-*
    mkdir /opt/CA/WorkloadAutomationAE_r12sp1cum3/etc
    cp -fpr /etc/init.d/waae* /etc/init.d/CA-* /opt/CA/WorkloadAutomationAE_r12sp1cum3/etc
Steps to Back out AutoSys from 12.1 to 12.0 SP1 CUM3
-    The new Kubernetes/Openshift Jobs, Kubernetes Connection Profiles, Proxy Connection Profiles and Security Profiles should be deleted before proceeding with the backout.

-    Stop all the AutoSys Services.
            unisrvcntr stop ALL

-    Run backout.sql database script, you may need to run this step multiple times and fix data before proceeding to next step.  
sqlplus -s aedbadmin/password@sid @backout.sql

You can proceed to next step when you see the below message as output of backout.sql
USER ACTION: NO ACTION REQUIRED *** Run RefreshAEDB to proceed with Backout *** 
    Note: Fix data if required after running backout.sql

-    Make a copy of current $AUTO_ROOT and $CASHCOMP folders. Also the startup scripts.
mkdir /opt/CA/WorkloadAutomationAE/etc
cp -fpr /etc/init.d/waae* /etc/init.d/CA-* /opt/CA/WorkloadAutomationAE/etc
            mv /opt/CA/WorkloadAutomationAE /opt/CA/WorkloadAutomationAE_r12.1
mv /opt/CA/SharedComponents /opt/CA/SharedComponents_r12.1

-    Copy the 12.0 SP1 CUM3 Backup folders 
cp -fpr /opt/CA/WorkloadAutomationAE_r12sp1cum3 /opt/CA/WorkloadAutomationAE
cp -fpr /opt/CA/SharedComponents_r12sp1cum3  /opt/CA/SharedComponents
cp -fpr /opt/CA/WorkloadAutomationAE_r12sp1cum3/etc/* /etc/init.d

    
-    Run Refresh AEDB.

-    Start all the AutoSys Services.
            unisrvcntr start ALL

==== backout.sql =================
set echo on;
set pagesize 10000;
set linesize 10000;
set serveroutput on format wrapped;
alter session set recyclebin=off;

DECLARE 
  l_row_count number := 0;
  can_proceed_with_backout boolean := TRUE;
  aedbadmin_user varchar2(64);
  aedb_index varchar2(150);

BEGIN
  dbms_output.put_line('Running Backout SQL Script');

  -- UJO_SECURITY_PROFILE
  SELECT count(name) into l_row_count FROM ujo_security_profile;
  IF (l_row_count <> 0)
  THEN 
    can_proceed_with_backout := FALSE;
    dbms_output.put_line('');
    dbms_output.put_line('*** Security Profile are introduced in 12.1.');
    dbms_output.put_line('*** Hence, to proceed with the Backout, delete the below listed Security Profile(s) and re-run the script.');
    for row in (SELECT name FROM ujo_security_profile) loop
        dbms_output.put_line(row.name);
    end loop;
  END IF;

  -- UJO_AUDIT_MSG
  l_row_count := 0;
  select count(audit_info_num) into l_row_count from ujo_audit_msg where part_no <> 1;
  IF (l_row_count <> 0)
  THEN 
    can_proceed_with_backout := FALSE;
    dbms_output.put_line('');
    dbms_output.put_line('*** Drop rows having part_no not equal to 1 in ujo_audit_msg table');
    dbms_output.put_line('*** The part_no column in ujo_audit_msg is introduced in R12.1 to support attribute values having value length more than 4K and for Multi-String attributes.');
    dbms_output.put_line('*** Hence for R12.0 SP1 CUM3, to drop this column, the rows with part_no value should be deleted to avoid Primary Key Voilation.');
    dbms_output.put_line('*** Run Below SQL queries to delete the rows and re-run the script.');
    for row in (select audit_info_num, seq_no, part_no from ujo_audit_msg where part_no <> 1) loop
        dbms_output.put_line('delete from ujo_audit_msg where audit_info_num = ' || row.audit_info_num || ' and seq_no = ' || row.seq_no || ' and part_no = ' || row.part_no || ';');
    end loop;
  END IF;

  -- UJO_CONNECTIONPROFILE
  l_row_count := 0;
  select count(name) into l_row_count from ujo_connectionprofile where type in (502,503);
  IF ( l_row_count <> 0 )
  THEN 
    can_proceed_with_backout := FALSE;  
    dbms_output.put_line('');
    dbms_output.put_line('*** New Connection Profiles are introduced in 12.1 to support Kubernetes/Openshift Jobs.');
    dbms_output.put_line('*** Hence, to backout, the below mentioned connection profiles should be deleted.');
    for row in (select name from ujo_connectionprofile where type in (502,503)) loop
        dbms_output.put_line(row.name);
    end loop;
    dbms_output.put_line('');
    dbms_output.put_line('*** Re-run the script after deleting the Connection Profiles.');
  END IF;

  -- UJO_PASSWORD_VAULT
  l_row_count := 0;
  SELECT count(name) into l_row_count FROM ujo_password_vault where vault_info1 <> NULL;
  IF ( l_row_count <> 0 )
  THEN 
    can_proceed_with_backout := FALSE;  
    dbms_output.put_line('');
    dbms_output.put_line('*** A new Column vault_info1 is introduced in AutoSys 12.1 to support new Password Vault');
    dbms_output.put_line('*** Hence, to backout, the below mentioned Vaults should be deleted.');
    for row in (select name from ujo_password_vault where vault_info1 <> NULL) loop
        dbms_output.put_line(row.name);
    end loop;
    dbms_output.put_line('');
    dbms_output.put_line('*** Re-run the script after deleting the Vaults.');
  END IF;
    
  -- UJO_GENERIC_JOB, check for new job types in ujo_job
  l_row_count := 0;
  select count(job_name) into l_row_count from ujo_job where job_type in (268,269,270,271) and is_active = 1 and is_currver = 1;
  IF ( l_row_count <> 0 )
  THEN 
    can_proceed_with_backout := FALSE;  
    dbms_output.put_line('');
    dbms_output.put_line('*** Kubernetes/Openshift job types in AutoSys 12.1');
    dbms_output.put_line('*** Hence, to backout, the below mentioned Kubernetes/Openshift job(s) should be deleted.');
    for row in (select job_name from ujo_job where job_type in (268,269,270,271) and is_active = 1 and is_currver = 1) loop
        dbms_output.put_line(row.job_name);
    end loop;
    dbms_output.put_line('');
    dbms_output.put_line('*** Re-run the script after deleting the Jobs.');
  END IF;
  
  -- UJO_JOBTYPE
  l_row_count := 0;
  SELECT count(job_type) into l_row_count from ujo_jobtype where length(exe_name) > 255;
  IF ( l_row_count <> 0 )
  THEN 
    can_proceed_with_backout := FALSE;  
    dbms_output.put_line('');
    dbms_output.put_line('*** The length of command attribute of User Defined Job types was 255 and increased to 1000 in AutoSys 12.1.');
    dbms_output.put_line('*** Hence, to backout, their usage length have to be not more than 255 characters');
    dbms_output.put_line('*** Either delete or reduce the length of command attribute for below mentioned User Defined Job types');
    for row in (SELECT job_type from ujo_jobtype where length(exe_name) > 255) loop
        dbms_output.put_line(row.job_type);
    end loop;
  END IF;
  
  -- UJO_STRINGS
  l_row_count := 0;
  select count(str_oid) into l_row_count from ujo_strings where part_num <> 1;
  IF (l_row_count <> 0)
  THEN 
    can_proceed_with_backout := FALSE;
    dbms_output.put_line('');
    dbms_output.put_line('*** Drop rows having part_num not equal to 1 in ujo_strings table');
    dbms_output.put_line('*** The part_num column in ujo_strings is introduced in R12.1 to support attribute values having value length more than 4K and for Multi-String attributes.');
    dbms_output.put_line('*** Hence, to backout, the rows with part_num value not equal to 1 should be deleted to avoid Primary Key Voilation.');
    dbms_output.put_line('*** Run Below SQL queries to delete the rows and re-run the script.');
    for row in (select str_oid, seq_num, part_num from ujo_strings where part_num <> 1) loop
        dbms_output.put_line('delete from ujo_strings where str_oid = ' || row.str_oid || ' and seq_num = ' || row.seq_num || ' and part_num = ' || row.part_num);
    end loop;
  END IF;

  -- CHECK IF ANY ACTION IS REQUIRED
  IF ( can_proceed_with_backout = FALSE )
  THEN
    dbms_output.put_line('');
    dbms_output.put_line('USER ACTION: *** Fix above reported items and run this script again ***');
    RETURN;
  ELSE
    -- Delete all meta data
    delete from ujo_meta_afm_types;
    delete from ujo_meta_restart_afm_types;
    delete from ujo_meta_enumerations;
    delete from ujo_meta_parse_keywords;
    delete from ujo_meta_parse_extruntime;
    delete from ujo_meta_presentation;
    delete from ujo_meta_properties;
    delete from ujo_meta_root_keywords;
    delete from ujo_meta_rules;
    delete from ujo_meta_types;
    delete from ujo_meta_ws_elements; 
    
    execute immediate 'select user from dual' into aedbadmin_user;
    execute immediate 'SELECT tablespace_name from dba_indexes where index_name = UPPER(''xpkujo_audit_msg'') and owner = ('''|| aedbadmin_user ||''')' into aedb_index;

    -- Drop part_no in table ujo_audit_msg and recreate the Key and Index
    execute immediate 'ALTER TABLE ujo_audit_msg DROP CONSTRAINT xpkujo_audit_msg'; 
    execute immediate 'DROP INDEX xpkujo_audit_msg'; 
    execute immediate 'ALTER TABLE ujo_audit_msg DROP COLUMN part_no';
    execute immediate 'CREATE UNIQUE INDEX xpkujo_audit_msg ON ujo_audit_msg (audit_info_num ASC, seq_no ASC)  tablespace ' || aedb_index;     
    execute immediate 'ALTER TABLE ujo_audit_msg ADD CONSTRAINT xpkujo_audit_msg PRIMARY KEY (audit_info_num, seq_no)';     
    
    
    -- Drop String9 in ujo_connectionprofile
    execute immediate 'ALTER TABLE ujo_connectionprofile DROP COLUMN String9';
    
    -- Drop vault_info1 in table ujo_password_vault
    execute immediate 'ALTER TABLE ujo_password_vault DROP COLUMN vault_info1';
    
    -- Drop Columns from ujo_generic_job
    execute immediate 'ALTER TABLE ujo_generic_job DROP (string7, string8, string9, string10, string11, num4, num5)';
    execute immediate 'ALTER TABLE ujo_generic_job MODIFY string6 varchar2(3950)';
    
    -- Modify Column exe_name in ujo_jobtype
    execute immediate 'ALTER TABLE ujo_jobtype MODIFY exe_name varchar2(255)';
        
    -- Modify Column name in ujo_meta_types
    execute immediate 'ALTER TABLE ujo_meta_types MODIFY name varchar2(20)';
    
    -- Drop part_num in table ujo_strings
    execute immediate 'ALTER TABLE ujo_strings DROP CONSTRAINT xpkujo_strings'; 
    execute immediate 'DROP INDEX xpkujo_strings'; 
    execute immediate 'ALTER TABLE ujo_strings DROP COLUMN part_num';
    execute immediate 'CREATE UNIQUE INDEX xpkujo_strings ON ujo_strings (str_oid ASC, seq_num ASC)  tablespace ' || aedb_index; 
    execute immediate 'ALTER TABLE ujo_strings ADD CONSTRAINT xpkujo_strings PRIMARY KEY (str_oid, seq_num)'; 

    execute immediate 'DROP TABLE ujo_meta_version CASCADE CONSTRAINTS';
    execute immediate 'DROP TABLE ujo_meta_esj_data CASCADE CONSTRAINTS';
    execute immediate 'DROP TABLE ujo_meta_plugins CASCADE CONSTRAINTS';
    execute immediate 'DROP TABLE ujo_secrets CASCADE CONSTRAINTS';
    execute immediate 'DROP TABLE ujo_security_profile CASCADE CONSTRAINTS';
    delete from ujo_next_oid where field = 'sec_oid';

    -- Update SAFE_APP_VERSION to 12 SP1 CUM3
    execute immediate 'update ujo_alamode set str_val = ''12.0.01'' where type = ''SAFE_APP_VERSION'''; 

    
    dbms_output.put_line('');
    dbms_output.put_line('USER ACTION: NO ACTION REQUIRED *** Run RefreshAEDB to proceed with Backout ***');
    dbms_output.put_line('');
  END IF;
END;
/

EXIT;