admin/mars
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
mars/MARS_Packages/REL01_ADDITIONS/MARS-826/README.md
Grzegorz Michalski e3ff1618ce Przeniesienie
2026-02-03 13:32:06 +01:00

13 KiB
Raw Permalink Blame History

MARS-826: CSDB Historical Data Export to HIST Bucket

🎯 Implementation Status: 🚧 IN DEVELOPMENT

Implementation Date: 2025-12-02
Database Version: Oracle 23ai
Package Used: CT_MRDS.DATA_EXPORTER
Status: Development ⚙️

📋 Overview

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)

  1. OU_LM.LEGACY_ADHOC_ADJ_HEADER → history/ARCHIVE/LM/LM_ADHOC_ADJUSTMENTS_HEADER (~5 records)
  2. OU_LM.LEGACY_ADHOC_ADJ_ITEM → history/ARCHIVE/LM/LM_ADHOC_ADJUSTMENTS_ITEM (~102 records)
  3. 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

  1. OU_LM.LEGACY_BALANCESHEET_HEADER → history/ARCHIVE/LM/LM_BALANCESHEET_HEADER (~81,853 records)
  2. 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)

  1. OU_LM.LEGACY_CSM_ADJ_HEADER → history/ARCHIVE/LM/LM_CSM_ADJUSTMENTS_HEADER (~186 records)
  2. OU_LM.LEGACY_CSM_ADJ_ITEM → history/ARCHIVE/LM/LM_CSM_ADJUSTMENTS_ITEM (~3,785 records)
  3. 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

  1. OU_LM.LEGACY_STANDING_FACILITY → history/ARCHIVE/LM/LM_STANDING_FACILITIES (~1,205,002 records)
  2. 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)

  1. OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_HEADER → history/ARCHIVE/LM/LM_CURRENT_ACCOUNTS_HEADER
  2. 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

  1. OU_LM.LEGACY_FORECAST_HEADER → history/ARCHIVE/LM/LM_FORECAST_HEADER (~42,504 records)
  2. 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)

  1. OU_LM.LEGACY_QR_ADJ_HEADER → history/ARCHIVE/LM/LM_QRE_ADJUSTMENTS_HEADER (~123 records)
  2. OU_LM.LEGACY_QR_ADJ_ITEM → history/ARCHIVE/LM/LM_QRE_ADJUSTMENTS_ITEM (~59,952 records)
  3. 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)

  1. OU_LM.LEGACY_TTS_HEADER → history/ARCHIVE/LM/LM_TTS_HEADER (~560 records)
  2. 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

  1. OU_LM.LEGACY_ADHOC_ADJ_HEADER
  2. OU_LM.LEGACY_ADHOC_ADJ_ITEM
  3. OU_LM.LEGACY_ADHOC_ADJ_ITEM_HEADER
  4. OU_LM.LEGACY_BALANCESHEET_HEADER
  5. OU_LM.LEGACY_BALANCESHEET_ITEM
  6. OU_LM.LEGACY_CSM_ADJ_HEADER
  7. OU_LM.LEGACY_CSM_ADJ_ITEM
  8. OU_LM.LEGACY_CSM_ADJ_ITEM_HEADER
  9. OU_LM.LEGACY_STANDING_FACILITY
  10. OU_LM.LEGACY_STANDING_FACILITY_HEADER
  11. OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_HEADER
  12. OU_MRR.LEGACY_MRR_IND_CURRENT_ACCOUNT_ITEM
  13. OU_LM.LEGACY_FORECAST_HEADER
  14. OU_LM.LEGACY_FORECAST_ITEM
  15. OU_LM.LEGACY_QR_ADJ_HEADER
  16. OU_LM.LEGACY_QR_ADJ_ITEM
  17. OU_LM.LEGACY_QR_ADJ_ITEM_HEADER
  18. OU_LM.LEGACY_TTS_HEADER
  19. 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:

  1. 01_ Export ADHOC_ADJ tables (3 tables)
  2. 02_ Export BALANCESHEET tables (2 tables, ~7.6M records) ⏱️
  3. 03_ Export CSM_ADJ tables (3 tables)
  4. 04_ Export STANDING_FACILITY tables (2 tables, ~1.2M records) ⏱️
  5. 05_ Export MRR_IND_CURR_ACC tables (2 tables)
  6. 06_ Export FORECAST tables (2 tables, ~21.6M records) ⏱️⏱️
  7. 07_ Export QR_ADJ tables (3 tables)
  8. 08_ Export TTS tables (2 tables)
  9. 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

  1. Deletes all exported Parquet files from HIST/ARCHIVE/LM/{TABLE_NAME}/ folders
  2. Uses DBMS_CLOUD.DELETE_OBJECT for each file in all 19 table directories
  3. Verifies cleanup by counting remaining files (should be 0)
  4. 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

🔗 Related Documentation

📞 Support

For questions or issues with the export process:

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

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

Reference in New Issue View Git Blame Copy Permalink