152 lines
6.1 KiB
MySQL
152 lines
6.1 KiB
MySQL
-- ===================================================================
|
|
-- MARS-1096 ROLLBACK SCRIPT: Revert A_PROCESS_LOG Changes
|
|
-- ===================================================================
|
|
-- Purpose: Rollback all changes from MARS-1096 installation
|
|
-- - Revert log_message column from VARCHAR2(20000) to VARCHAR2(4000)
|
|
-- - Remove daily interval partitioning (restore to non-partitioned table)
|
|
-- - Preserve all existing data during rollback
|
|
-- Author: Grzegorz Michalski
|
|
-- Date: 2025-12-03
|
|
-- Version: 1.0.0
|
|
-- ===================================================================
|
|
|
|
-- Dynamic spool file generation (using SYS_CONTEXT - no DBA privileges required)
|
|
var filename VARCHAR2(100)
|
|
BEGIN
|
|
:filename := 'ROLLBACK_MARS_1096_' || SYS_CONTEXT('USERENV', 'CON_NAME') || '_' || TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') || '.log';
|
|
END;
|
|
/
|
|
column filename new_value _filename
|
|
select :filename filename from dual;
|
|
spool &_filename
|
|
|
|
SET ECHO OFF
|
|
SET TIMING ON
|
|
SET SERVEROUTPUT ON SIZE UNLIMITED
|
|
SET PAUSE OFF
|
|
|
|
PROMPT =========================================================================
|
|
PROMPT MARS-1096: Rollback A_PROCESS_LOG Changes
|
|
PROMPT =========================================================================
|
|
PROMPT WARNING: This will reverse all changes from MARS-1096 installation!
|
|
PROMPT
|
|
PROMPT Changes to be reverted:
|
|
PROMPT - Revert log_message column: VARCHAR2(20000) -> VARCHAR2(4000)
|
|
PROMPT - Remove daily interval partitioning
|
|
PROMPT - Restore to non-partitioned table structure
|
|
PROMPT
|
|
PROMPT CRITICAL: All existing data will be preserved during rollback
|
|
PROMPT =========================================================================
|
|
|
|
-- Confirm rollback with user
|
|
ACCEPT continue CHAR PROMPT 'Type YES to continue with rollback, or Ctrl+C to abort: '
|
|
WHENEVER SQLERROR EXIT SQL.SQLCODE
|
|
BEGIN
|
|
IF '&continue' IS NULL OR TRIM('&continue') IS NULL OR UPPER(TRIM('&continue')) != 'YES' THEN
|
|
RAISE_APPLICATION_ERROR(-20001, 'Rollback aborted by user');
|
|
END IF;
|
|
END;
|
|
/
|
|
WHENEVER SQLERROR CONTINUE
|
|
|
|
PROMPT
|
|
PROMPT =========================================================================
|
|
PROMPT Step 1: Rollback Table Structure
|
|
PROMPT =========================================================================
|
|
|
|
DECLARE
|
|
v_row_count NUMBER := 0;
|
|
v_table_exists NUMBER := 0;
|
|
BEGIN
|
|
-- Check if old backup table still exists
|
|
SELECT COUNT(*) INTO v_table_exists
|
|
FROM user_tables
|
|
WHERE table_name = 'A_PROCESS_LOG_OLD';
|
|
|
|
IF v_table_exists > 0 THEN
|
|
-- If backup exists, simply restore it
|
|
DBMS_OUTPUT.PUT_LINE('Restoring from backup table A_PROCESS_LOG_OLD');
|
|
|
|
EXECUTE IMMEDIATE 'ALTER TABLE CT_MRDS.A_PROCESS_LOG RENAME TO A_PROCESS_LOG_PARTITIONED';
|
|
EXECUTE IMMEDIATE 'ALTER TABLE CT_MRDS.A_PROCESS_LOG_OLD RENAME TO A_PROCESS_LOG';
|
|
|
|
-- Revert column size
|
|
EXECUTE IMMEDIATE 'ALTER TABLE CT_MRDS.A_PROCESS_LOG MODIFY (log_message VARCHAR2(4000))';
|
|
|
|
EXECUTE IMMEDIATE 'DROP TABLE CT_MRDS.A_PROCESS_LOG_PARTITIONED PURGE';
|
|
DBMS_OUTPUT.PUT_LINE('Rollback complete using backup table.');
|
|
ELSE
|
|
-- No backup, need to convert back manually
|
|
DBMS_OUTPUT.PUT_LINE('No backup found. Converting partitioned table to non-partitioned.');
|
|
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM CT_MRDS.A_PROCESS_LOG' INTO v_row_count;
|
|
DBMS_OUTPUT.PUT_LINE('Records to migrate: ' || v_row_count);
|
|
|
|
-- Create non-partitioned table
|
|
EXECUTE IMMEDIATE q'[CREATE TABLE CT_MRDS.A_PROCESS_LOG_NP (
|
|
a_process_log_key NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
guid VARCHAR2(32),
|
|
Username VARCHAR2(128),
|
|
Osuser VARCHAR2(128),
|
|
Machine VARCHAR2(64),
|
|
Module VARCHAR2(64),
|
|
process_name VARCHAR2(200),
|
|
procedure_name VARCHAR2(200),
|
|
procedure_parameters VARCHAR2(20000),
|
|
log_level VARCHAR2(10),
|
|
log_message VARCHAR2(4000),
|
|
log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
|
|
)]';
|
|
|
|
-- Copy all data
|
|
EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO CT_MRDS.A_PROCESS_LOG_NP (
|
|
guid, Username, Osuser, Machine, Module, process_name, procedure_name,
|
|
procedure_parameters, log_level, log_message, log_timestamp
|
|
) SELECT guid, Username, Osuser, Machine, Module, process_name, procedure_name,
|
|
procedure_parameters, log_level, log_message, log_timestamp
|
|
FROM CT_MRDS.A_PROCESS_LOG';
|
|
|
|
COMMIT;
|
|
|
|
-- Swap tables
|
|
EXECUTE IMMEDIATE 'ALTER TABLE CT_MRDS.A_PROCESS_LOG RENAME TO A_PROCESS_LOG_PARTITIONED';
|
|
EXECUTE IMMEDIATE 'ALTER TABLE CT_MRDS.A_PROCESS_LOG_NP RENAME TO A_PROCESS_LOG';
|
|
EXECUTE IMMEDIATE 'DROP TABLE CT_MRDS.A_PROCESS_LOG_PARTITIONED PURGE';
|
|
|
|
DBMS_OUTPUT.PUT_LINE('Rollback complete.');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT =========================================================================
|
|
PROMPT Step 2: Verify Rollback
|
|
PROMPT =========================================================================
|
|
|
|
-- Verify column size reverted
|
|
SELECT column_name, data_type, data_length
|
|
FROM user_tab_columns
|
|
WHERE table_name = 'A_PROCESS_LOG' AND column_name = 'LOG_MESSAGE';
|
|
|
|
-- Verify partitioning removed
|
|
SELECT COUNT(*) AS partition_count
|
|
FROM user_tab_partitions
|
|
WHERE table_name = 'A_PROCESS_LOG';
|
|
|
|
-- Verify record count preserved
|
|
SELECT COUNT(*) AS total_records FROM CT_MRDS.A_PROCESS_LOG;
|
|
|
|
PROMPT
|
|
PROMPT =========================================================================
|
|
PROMPT MARS-1096 Rollback - COMPLETED
|
|
PROMPT =========================================================================
|
|
PROMPT Check the log file for complete rollback details.
|
|
PROMPT Expected results:
|
|
PROMPT - LOG_MESSAGE column: VARCHAR2(4000)
|
|
PROMPT - Partition count: 0 (non-partitioned)
|
|
PROMPT - Total records: Should match pre-rollback count
|
|
PROMPT =========================================================================
|
|
|
|
spool off
|
|
quit;
|