MARS-826 implements one-time historical data export for CSDB tables from operational database (OU_LM/OU_MRR schemas) to HIST bucket (ARCHIVE) in Parquet format with Hive-style partitioning.

Key Objectives

Data Migration: Move historical CSDB data from LEGACY_ tables to HIST bucket
Column Mapping: Map A_ETL_LOAD_SET_KEY[_FK] → A_WORKFLOW_HISTORY_KEY
Bulk Export: Process 19 tables with ~31M total records
Weekend Execution: Non-critical timing allows weekend bulk processing
Prerequisites: LEGACY_ prefixed tables must exist before running export

📁 Project Structure

MARS_Packages/REL01_POST_DEACTIVATION/MARS-826/
├── install_mars826.sql                                    # 📥 Main Installation Script
├── 00_MARS_826_pre_check_existing_files.sql               # Pre-check: Existing archive files
├── 01_MARS_826_export_ADHOC_ADJ_tables.sql                # Export ADHOC_ADJ (3 tables)
├── 02_MARS_826_export_BALANCESHEET_tables.sql             # Export BALANCESHEET (2 tables, 7.6M records)
├── 03_MARS_826_export_CSM_ADJ_tables.sql                  # Export CSM_ADJ (3 tables)
├── 04_MARS_826_export_STANDING_FACILITY_tables.sql        # Export STANDING_FACILITY (2 tables, 1.2M records)
├── 05_MARS_826_export_MRR_IND_CURR_ACC_tables.sql         # Export MRR_IND_CURR_ACC (2 tables)
├── 06_MARS_826_export_FORECAST_tables.sql                 # Export FORECAST (2 tables, 21.6M records)
├── 07_MARS_826_export_QR_ADJ_tables.sql                   # Export QR_ADJ (3 tables)
├── 08_MARS_826_export_TTS_tables.sql                      # Export TTS (2 tables)
├── 09_MARS_826_verify_exports.sql                         # Verification: File listing
├── 10_MARS_826_verify_record_counts.sql                   # Verification: Record count comparison
├── rollback_mars826.sql                                   # 🔄 Main Rollback Script
├── 91_MARS_826_rollback_ADHOC_ADJ_tables.sql              # Rollback ADHOC_ADJ
├── 92_MARS_826_rollback_BALANCESHEET_tables.sql           # Rollback BALANCESHEET
├── 93_MARS_826_rollback_CSM_ADJ_tables.sql                # Rollback CSM_ADJ
├── 94_MARS_826_rollback_STANDING_FACILITY_tables.sql      # Rollback STANDING_FACILITY
├── 95_MARS_826_rollback_MRR_IND_CURR_ACC_tables.sql       # Rollback MRR_IND_CURR_ACC
├── 96_MARS_826_rollback_FORECAST_tables.sql               # Rollback FORECAST
├── 97_MARS_826_rollback_QR_ADJ_tables.sql                 # Rollback QR_ADJ
├── 98_MARS_826_rollback_TTS_tables.sql                    # Rollback TTS
├── 99_MARS_826_verify_rollback.sql                        # Rollback Verification
└── README.md                                              # 📝 This Documentation

📊 Tables to Export (19 Total)

Group 1: ADHOC_ADJ Tables (3 tables, ~209 records)

OU_LM.LEGACY_ADHOC_ADJ_HEADER → history/ARCHIVE/LM/LM_ADHOC_ADJUSTMENTS_HEADER (~5 records)
OU_LM.LEGACY_ADHOC_ADJ_ITEM → history/ARCHIVE/LM/LM_ADHOC_ADJUSTMENTS_ITEM (~102 records)
OU_LM.LEGACY_ADHOC_ADJ_ITEM_HEADER → history/ARCHIVE/LM/LM_ADHOC_ADJUSTMENTS_ITEM_HEADER (~102 records)

Group 2: BALANCESHEET Tables (2 tables, ~7.6M records) ⚠️ LARGE

OU_LM.LEGACY_BALANCESHEET_HEADER → history/ARCHIVE/LM/LM_BALANCESHEET_HEADER (~81,853 records)
OU_LM.LEGACY_BALANCESHEET_ITEM → history/ARCHIVE/LM/LM_BALANCESHEET_ITEM (~7,603,340 records)

Group 3: CSM_ADJ Tables (3 tables, ~7,756 records)

OU_LM.LEGACY_CSM_ADJ_HEADER → history/ARCHIVE/LM/LM_CSM_ADJUSTMENTS_HEADER (~186 records)
OU_LM.LEGACY_CSM_ADJ_ITEM → history/ARCHIVE/LM/LM_CSM_ADJUSTMENTS_ITEM (~3,785 records)
OU_LM.LEGACY_CSM_ADJ_ITEM_HEADER → history/ARCHIVE/LM/LM_CSM_ADJUSTMENTS_ITEM_HEADER (~3,785 records)

Group 4: STANDING_FACILITY Tables (2 tables, ~1.2M records) ⚠️ LARGE

OU_LM.LEGACY_STANDING_FACILITY → history/ARCHIVE/LM/LM_STANDING_FACILITIES (~1,205,002 records)
OU_LM.LEGACY_STANDING_FACILITY_HEADER → history/ARCHIVE/LM/LM_STANDING_FACILITIES_HEADER (~2,647 records)

Group 5: MRR_IND_CURR_ACC Tables (2 tables - OU_MRR schema)

OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_HEADER → history/ARCHIVE/LM/LM_CURRENT_ACCOUNTS_HEADER
OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_ITEM → history/ARCHIVE/LM/LM_CURRENT_ACCOUNTS_ITEM

Group 6: FORECAST Tables (2 tables, ~21.6M records) ⚠️ VERY LARGE

OU_LM.LEGACY_FORECAST_HEADER → history/ARCHIVE/LM/LM_FORECAST_HEADER (~42,504 records)
OU_LM.LEGACY_FORECAST_ITEM → history/ARCHIVE/LM/LM_FORECAST_ITEM (~21,643,855 records)

Group 7: QR_ADJ Tables (3 tables, ~62,573 records)

OU_LM.LEGACY_QR_ADJ_HEADER → history/ARCHIVE/LM/LM_QRE_ADJUSTMENTS_HEADER (~123 records)
OU_LM.LEGACY_QR_ADJ_ITEM → history/ARCHIVE/LM/LM_QRE_ADJUSTMENTS_ITEM (~59,952 records)
OU_LM.LEGACY_QR_ADJ_ITEM_HEADER → history/ARCHIVE/LM/LM_QRE_ADJUSTMENTS_ITEM_HEADER (~2,498 records)

Group 8: TTS Tables (2 tables, ~1,120 records)

OU_LM.LEGACY_TTS_HEADER → history/ARCHIVE/LM/LM_TTS_HEADER (~560 records)
OU_LM.LEGACY_TTS_ITEM → history/ARCHIVE/LM/LM_TTS_ITEM (~560 records)

Total Records: ~31,000,000 records across 19 tables

Complete Table List

OU_LM.LEGACY_ADHOC_ADJ_HEADER
OU_LM.LEGACY_ADHOC_ADJ_ITEM
OU_LM.LEGACY_ADHOC_ADJ_ITEM_HEADER
OU_LM.LEGACY_BALANCESHEET_HEADER
OU_LM.LEGACY_BALANCESHEET_ITEM
OU_LM.LEGACY_CSM_ADJ_HEADER
OU_LM.LEGACY_CSM_ADJ_ITEM
OU_LM.LEGACY_CSM_ADJ_ITEM_HEADER
OU_LM.LEGACY_STANDING_FACILITY
OU_LM.LEGACY_STANDING_FACILITY_HEADER
OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_HEADER
OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_ITEM
OU_LM.LEGACY_FORECAST_HEADER
OU_LM.LEGACY_FORECAST_ITEM
OU_LM.LEGACY_QR_ADJ_HEADER
OU_LM.LEGACY_QR_ADJ_ITEM
OU_LM.LEGACY_QR_ADJ_ITEM_HEADER
OU_LM.LEGACY_TTS_HEADER
OU_LM.LEGACY_TTS_ITEM

🔄 Column Mapping

All tables require mapping of the key column used for partitioning:

Old Column Name	New Column Name	Tables Affected
`A_ETL_LOAD_SET_KEY_FK`	`A_WORKFLOW_HISTORY_KEY`	ADHOC_ADJ (3), CSM_ADJ (3), STANDING_FACILITY (2), FORECAST (2), QR_ADJ (3), TTS (2)
`A_ETL_LOAD_SET_KEY`	`A_WORKFLOW_HISTORY_KEY`	BALANCESHEET (2), MRR_IND_CURR_ACC (2)

Mapping Implementation:

-- Example for tables with A_ETL_LOAD_SET_KEY_FK
pColumnList => 'T.COL1, T.COL2, ..., T.A_ETL_LOAD_SET_KEY_FK AS A_WORKFLOW_HISTORY_KEY'

-- Example for tables with A_ETL_LOAD_SET_KEY
pColumnList => 'T.COL1, T.COL2, ..., T.A_ETL_LOAD_SET_KEY AS A_WORKFLOW_HISTORY_KEY'

📥 Installation

Prerequisites

Oracle Database 23ai with CT_MRDS.DATA_EXPORTER package
Access to OU_LM and OU_MRR schemas
HIST bucket (history) configured and accessible
Sufficient storage space for ~31M records in Parquet format

Execution Requirements

Timing: Weekend bulk execution (non-time-critical)
Duration: Estimated 2-4 hours depending on system load
Connection: Execute as ADMIN user for cross-schema operations

Installation Steps

# Execute installation script
Get-Content "MARS_Packages/REL01_POST_DEACTIVATION/MARS-826/install_mars826.sql" | sql "ADMIN/password@service"

# Log file will be created: INSTALL_MARS_826_<PDB>_<timestamp>.log

Installation Script Workflow:

01_ Export ADHOC_ADJ tables (3 tables)
02_ Export BALANCESHEET tables (2 tables, ~7.6M records) ⏱️
03_ Export CSM_ADJ tables (3 tables)
04_ Export STANDING_FACILITY tables (2 tables, ~1.2M records) ⏱️
05_ Export MRR_IND_CURR_ACC tables (2 tables)
06_ Export FORECAST tables (2 tables, ~21.6M records) ⏱️⏱️
07_ Export QR_ADJ tables (3 tables)
08_ Export TTS tables (2 tables)
09_ Verify all exports completed successfully

🧪 Verification

The installation includes comprehensive automated verification:

Pre-Installation Check (00_*)

Existing Files Audit: Lists all current Parquet files in HIST bucket before export
Storage Analysis: Shows file counts, total size (MB), and status for each table
Baseline Establishment: Determines if export is fresh or will overwrite existing data

Post-Export Verification (09_* and 10_*)

File Verification (09_):

-- Automated check of exported files in HIST bucket
-- Groups by table name with file counts and sizes
-- Verifies Hive-style partitioning structure

Record Count Validation (10_):

-- Compares source table row counts with exported Parquet files
-- Shows schema, table name, source records, file count, and size
-- Validates data completeness for all 19 tables

Manual Verification

-- Check HIST bucket for exported files
SELECT object_name, bytes, time_created
FROM DBMS_CLOUD.LIST_OBJECTS(
    credential_name => 'DEF_CRED_ARN',
    location_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frtgjxu7zl7c/b/history/'
)
WHERE object_name LIKE 'ARCHIVE/LM/%'
ORDER BY time_created DESC;

📝 Expected Output

HIST Bucket Structure (Parquet with Hive Partitioning)

history/ARCHIVE/LM/
├── LM_ADHOC_ADJUSTMENTS_HEADER/
│   └── PARTITION_YEAR=YYYY/PARTITION_MONTH=MM/*.parquet
├── LM_BALANCESHEET_ITEM/
│   └── PARTITION_YEAR=YYYY/PARTITION_MONTH=MM/*.parquet (7.6M records)
├── LM_FORECAST_ITEM/
│   └── PARTITION_YEAR=YYYY/PARTITION_MONTH=MM/*.parquet (21.6M records)
└── ... (15 more table folders)

⚠️ Important Notes

Performance Considerations

Large Tables: BALANCESHEET_ITEM (7.6M), FORECAST_ITEM (21.6M) may take significant time
Weekend Execution: Recommended to avoid impact on production workloads
Monitoring: Check log file for progress and any errors

Data Validation

Column Mapping: Verify A_WORKFLOW_HISTORY_KEY is correctly mapped
Record Counts: Compare source vs. exported record counts
Date Ranges: Ensure all historical data is included

🔄 Rollback

This package includes comprehensive rollback scripts to restore the state before installation by deleting all exported Parquet files from HIST bucket:

Rollback Scripts

rollback_mars826.sql - Master rollback orchestration script (with SPOOL logging)
91_MARS_826_rollback_ADHOC_ADJ_tables.sql - Delete ADHOC_ADJ exports (3 tables)
92_MARS_826_rollback_BALANCESHEET_tables.sql - Delete BALANCESHEET exports (2 tables, ~7.6M records)
93_MARS_826_rollback_CSM_ADJ_tables.sql - Delete CSM_ADJ exports (3 tables)
94_MARS_826_rollback_STANDING_FACILITY_tables.sql - Delete STANDING_FACILITY exports (2 tables, ~1.2M records)
95_MARS_826_rollback_MRR_IND_CURR_ACC_tables.sql - Delete MRR_IND_CURR_ACC exports (2 tables)
96_MARS_826_rollback_FORECAST_tables.sql - Delete FORECAST exports (2 tables, ~21.6M records - LARGEST)
97_MARS_826_rollback_QR_ADJ_tables.sql - Delete QR_ADJ exports (3 tables)
98_MARS_826_rollback_TTS_tables.sql - Delete TTS exports (2 tables)
99_MARS_826_verify_rollback.sql - Verify complete file deletion

Rollback Execution

-- Execute from SQLcl/SQL*Plus as CT_MRDS user
@rollback_mars826.sql
-- Prompts for confirmation (type YES to proceed)
-- Creates log file: rollback_mars826_YYYYMMDD.log

What Rollback Does

Deletes all exported Parquet files from HIST/ARCHIVE/LM/{TABLE_NAME}/ folders
Uses DBMS_CLOUD.DELETE_OBJECT for each file in all 19 table directories
Verifies cleanup by counting remaining files (should be 0)
Logs all operations to timestamped log file

Important Notes

Source tables remain untouched (read-only operations, no data modifications)
Rollback is destructive - permanently deletes exported files from HIST bucket
Large datasets warning - FORECAST_ITEM deletion may take several minutes (~21.6M records)
Re-export possible - can re-run install_mars826.sql after rollback if needed

📞 Support

For questions or issues with the export process:

Check log file: INSTALL_MARS_826_<PDB>_<timestamp>.log
Verify bucket access and credentials
Check process logs: SELECT * FROM CT_MRDS.A_PROCESS_LOG WHERE LOG_TIMESTAMP > SYSDATE - 1 ORDER BY LOG_TIMESTAMP DESC;
Contact database team lead

Last Updated: 2025-12-02
Document Version: 1.0.0
Author: Grzegorz Michalski

README.md

MARS-826: CSDB Historical Data Export to HIST Bucket

🎯 Implementation Status: 🚧 IN DEVELOPMENT

📋 Overview