Defence Manufacturing: Full-Spectrum Database Overhaul
Delivered a comprehensive database transformation for a defence manufacturing system— spanning performance tuning, security compliance, Unicode migration, data modelling, installation, and patching across Oracle and SQL Server environments.
The Challenge
A defence manufacturing organisation operating mission-critical systems across Oracle 19c and SQL Server 2019 engaged DharmOps for a full-spectrum database overhaul. The system managed logistics, procurement, materials tracking, and operational reporting for defence production lines—where accuracy, security, and availability are non-negotiable.
The scope was unusually broad: slow query performance was affecting production scheduling, security audit findings required immediate remediation, a legacy single-byte character set needed converting to Unicode for international supplier data, and multiple database instances were running on unpatched versions with outdated data models. Automation scripts in Perl and Shell were also unmaintained and causing intermittent failures.
Scope of Work
1. Performance Tuning
- AWR and SQL Server Query Store analysis to identify top 20 resource-intensive queries
- Rewrote critical Oracle PL/SQL and SQL Server T-SQL procedures with set-based logic
- Created composite indexes on procurement and materials tracking tables
- Built Oracle materialized views for daily production summary reports
- Tuned SGA/PGA on Oracle and max server memory on SQL Server for the hardware profile
- Eliminated N+1 query patterns in Perl-driven batch reporting scripts
2. Security & Compliance
- Conducted full Oracle and SQL Server security audit against government baseline standards
- Removed all default accounts and revoked excessive privileges on 40+ database users
- Enabled Oracle Unified Auditing and SQL Server SQL Audit for full DML/DDL tracking
- Implemented Oracle Database Vault to restrict DBA access to sensitive procurement data
- Enforced encrypted connections (TLS 1.2+) across all application-to-database links
- Configured transparent data encryption (TDE) on SQL Server for data-at-rest protection
- Produced full compliance evidence documentation for defence audit requirements
3. Unicode Database Migration
- Assessed all Oracle tables for character set compatibility (WE8ISO8859P1 → AL32UTF8)
- Used Oracle Database Migration Assistant for Unicode (DMU) to identify data expansion risks
- Converted 200+ tables with multi-language supplier and component description data
- Validated all converted data with character-level comparison scripts
- Updated all application connection strings and NLS_CHARACTERSET settings
4. Data Modelling
- Reviewed existing schemas against actual production data access patterns
- Normalised several 1NF violations in materials and parts tracking tables
- Introduced proper foreign key constraints and cascading rules previously absent
- Redesigned a denormalised reporting schema causing data inconsistencies in audit trails
- Produced updated entity-relationship documentation for ongoing development teams
5. Database Installation & Patching
- Deployed fresh Oracle 19c instances on hardened Linux hosts following CIS benchmarks
- Applied all Oracle Critical Patch Updates (CPUs) and SQL Server Cumulative Updates
- Configured Oracle Grid Infrastructure for RAC-ready failover capability
- Set up SQL Server Always On Availability Groups for high availability on critical nodes
- Documented full runbook for future patching cycles including pre/post validation steps
6. Perl & Shell Script Remediation
- Audited all Perl and Shell database automation scripts for error handling and security
- Replaced hard-coded database credentials with Oracle Wallet and SQL Server credential objects
- Fixed intermittent batch job failures caused by unhandled connection timeouts in Perl DBI
- Refactored Shell-based backup scripts to use RMAN and SQL Server native backup with verification
- Established cron and SQL Server Agent schedules with alerting on failure
The Results
"We needed a team that could handle the full scope—performance, security, Unicode conversion, modelling, installation, patching, and our automation scripts. DharmOps delivered on every workstream simultaneously, with the discipline and documentation our defence audit requirements demanded."
— Head of IT Infrastructure, Defence Manufacturing Organisation
Technologies & Tools
Need a Comprehensive Database Overhaul?
From performance tuning and security compliance to Unicode migration and infrastructure patching—our team handles the full scope across Oracle, SQL Server, and hybrid environments.
BOOK FREE DIAGNOSTIC