Skip to content

README.md

Latest commit

 

History

History
675 lines (469 loc) · 13.2 KB

README.md

File metadata and controls

675 lines (469 loc) · 13.2 KB
Raw

Mini ERP Application Support & Operations Lab

Overview

This project simulates a small enterprise ERP application support environment covering procurement, purchase orders, invoicing, payments, application users and batch processing.

The project was built as a hands-on Application Support / Production Support lab to practise troubleshooting issues across the database, application log and Linux operating-system layers.

Key areas covered include:

  • Oracle SQL troubleshooting
  • PL/SQL support logic
  • Application and batch log analysis
  • Linux / Unix command-line troubleshooting
  • Bash shell scripting
  • SQL*Plus automation
  • Batch job monitoring
  • Data validation and reconciliation
  • Incident investigation and root cause analysis
  • Operational health-check automation
  • Support documentation

Technology Stack

  • Oracle AI Database Free
  • Oracle SQL Developer
  • SQL
  • PL/SQL
  • SQL*Plus
  • Ubuntu Linux on WSL2
  • Bash / Shell Scripting
  • Git / GitHub

ERP Data Model

The Mini ERP database contains the following core entities:

  • Suppliers
  • Purchase Orders
  • Purchase Order Lines
  • Invoices
  • Payments
  • Application Users
  • Batch Jobs

The tables are linked using primary keys, foreign keys, unique constraints and business validation rules.

The simplified business flow is:

Supplier
   |
   v
Purchase Order
   |
   +---- Purchase Order Lines
   |
   v
Invoice
   |
   v
Payment

Application users and batch jobs are also included to simulate common Application Support scenarios such as login issues and scheduled batch-processing failures.

Project Structure

mini-erp-application-support/
├── database/
│   ├── 01_create_tables.sql
│   ├── 02_sample_data.sql
│   ├── 03_support_queries.sql
│   └── 04_plsql_support.sql
│
├── scripts/
│   ├── analyse_logs.sh
│   ├── check_failed_jobs.sh
│   ├── check_invoice_issues.sh
│   └── daily_health_check.sh
│
├── logs/
│   └── mini_erp_app.log
│
├── docs/
│   ├── RUNBOOK.md
│   └── INCIDENT_SCENARIOS.md
│
├── README.md
└── .gitignore

Generated health-check reports are excluded from Git through .gitignore.

Database Scripts

01_create_tables.sql

Creates the Mini ERP database schema including:

  • ERP_SUPPLIERS
  • ERP_PURCHASE_ORDERS
  • ERP_PO_LINES
  • ERP_INVOICES
  • ERP_PAYMENTS
  • ERP_USERS
  • ERP_BATCH_JOBS

The schema includes:

  • Primary keys
  • Foreign keys
  • Unique constraints
  • Check constraints
  • Default values
  • Basic data-integrity validation

02_sample_data.sql

Loads sample ERP data designed to support both normal processing and troubleshooting scenarios.

The dataset intentionally includes several business and operational anomalies so that incidents can be investigated using SQL.

03_support_queries.sql

Contains SQL queries used during simulated Application Support incidents.

Typical investigations include:

  • Checking invoice status
  • Checking purchase-order status
  • Comparing PO header and PO line totals
  • Checking supplier relationships
  • Investigating payment attempts
  • Detecting duplicate invoices
  • Investigating locked users
  • Checking failed batch jobs
  • Detecting supplier mismatches
  • Reconciling invoice and payment amounts

04_plsql_support.sql

Contains PL/SQL examples used to simulate application-side business logic and support activities.

Examples include:

  • Anonymous PL/SQL blocks
  • Variable declarations
  • SELECT INTO
  • IF / ELSE logic
  • Exception handling
  • Stored procedures
  • Functions
  • Custom application errors

A stored procedure was created to simulate invoice processing validation.

A function was created to calculate the outstanding balance of an invoice based on successfully processed payments.

Simulated Application Support Incidents

The project includes several troubleshooting scenarios based on typical ERP support issues.

INC001 - Invoice Stuck in PENDING

An invoice remained in PENDING status and could not progress through payment processing.

Investigation included:

  • Checking the invoice record
  • Checking the related purchase order
  • Checking supplier status
  • Comparing invoice and PO amounts
  • Reviewing payment attempts

The investigation identified that the purchase order had not progressed to the required status.

INC002 - Purchase Order Amount Mismatch

A purchase order header amount did not match the calculated total of its individual PO lines.

The investigation compared:

PO Header Amount
vs
SUM(Quantity × Unit Price)

The issue demonstrated reconciliation between header-level and line-level transaction data.

INC003 - Duplicate Supplier Invoice

Two invoice records contained the same supplier invoice number.

The investigation included:

  • Supplier validation
  • Invoice-number comparison
  • Payment-history review
  • Duplicate identification

The scenario demonstrates why business-level validation may be required even when individual foreign keys remain valid.

INC004 - Locked Application User

A Finance user was unable to log in to the ERP application.

Application logs showed that the account had been rejected because its status was LOCKED.

The database record was then checked and validated before simulating an approved account-status correction.

INC005 - Failed Payment Export Batch

A PAYMENT_EXPORT batch job failed after processing records.

The investigation identified:

Invalid supplier ID 999

The incident demonstrated:

  • Batch-log analysis
  • Database validation
  • Failed-job investigation
  • Upstream-data validation
  • Safe rerun considerations

INC006 - Batch Completed with Zero Records

A scheduled batch job reported SUCCESS but processed zero records even though eligible business records existed.

The investigation demonstrated that a technically successful process may still represent a business-processing failure.

INC007 - Invoice Supplier Mismatch

An invoice referenced a valid supplier and a valid purchase order, but the invoice supplier did not match the supplier associated with the purchase order.

This demonstrated an important distinction between:

Referential integrity

and:

Business-data integrity

INC008 - Overpaid Invoice

Processed payments exceeded the validated invoice amount.

The investigation reconciled:

Invoice Amount
vs
Processed Payment Total

The scenario also demonstrated the importance of preserving payment audit history rather than directly modifying historical payment transactions.

INC009 - Fully Paid Invoice with Incorrect Status

An invoice had been completely paid, but its status remained APPROVED instead of PAID.

The investigation confirmed:

  • Invoice amount
  • Successfully processed payments
  • Outstanding balance
  • Invoice status

The issue demonstrated a workflow/status synchronisation problem.

Application Log Troubleshooting

A sample application log is provided at:

logs/mini_erp_app.log

It contains INFO, WARN and ERROR events covering invoice processing, payment export and user-login scenarios.

Linux commands used for log investigation include:

cat
head
tail
less
grep
awk
cut
sort
uniq
wc

Example:

grep "INV-50003" logs/mini_erp_app.log

This identifies all log entries related to a specific invoice.

Another example:

grep -E "WARN|ERROR" logs/mini_erp_app.log

This filters the application log to warning and error events.

A simple log-level summary can be generated using:

awk '{print $3}' logs/mini_erp_app.log | sort | uniq -c

Linux System Troubleshooting

The project also includes basic Linux troubleshooting practice using:

ps
top
free
df
du
find
kill

These commands were used to practise investigating:

  • Running processes
  • CPU usage
  • Memory utilisation
  • Filesystem utilisation
  • Directory size
  • File locations
  • Background processes
  • Process termination

Shell Automation

Four Bash scripts were created to automate common Application Support checks.

analyse_logs.sh

Analyses the Mini ERP application log.

The script reports:

  • Total log entries
  • INFO count
  • WARN count
  • ERROR count
  • Warning details
  • Error details

If application errors are detected, the script returns a non-zero exit code.

Example:

Total log entries : 11
INFO entries      : 7
WARN entries      : 2
ERROR entries     : 2

[FAIL] Application log contains 2 error(s).

check_failed_jobs.sh

Connects to Oracle using SQL*Plus and checks the ERP_BATCH_JOBS table for failed batch executions.

It reports information such as:

Job Run ID
Job Name
Records Processed
Error Message

Example:

Failed jobs detected: 1

Job Run ID       : 9002
Job Name         : PAYMENT_EXPORT
Records Processed: 8
Error Message    : Invalid supplier ID 999

check_invoice_issues.sh

Performs automated business-data validation against Oracle.

The current checks detect:

  • Fully paid invoices with an incorrect invoice status
  • Overpaid invoices
  • Supplier mismatches between invoices and purchase orders

Example:

Invoice issues detected: 3

ISSUE | FULLY_PAID_STATUS | INV-50001 | 4500 | 4500 | APPROVED
ISSUE | OVERPAID | INV-50004 | 5000 | 5500
ISSUE | SUPPLIER_MISMATCH | INV-50006 | 102 | 101 | PO-10001

daily_health_check.sh

Runs the individual support checks and produces a consolidated operational health report.

The script executes:

Application Log Check
        +
Batch Job Check
        +
Invoice Data Check
        ↓
Daily Health Check Summary

Example output:

Application Logs : FAIL
Batch Jobs       : FAIL
Invoice Data     : FAIL
Overall Status   : FAIL

A timestamped report is automatically created under the logs directory.

Example:

health_check_20260815_212106.log

Exit Codes

The Bash scripts use exit codes so that their results can be consumed by other automation tools.

General behaviour:

0  = Successful / healthy
1  = Support issue detected
2+ = Script, configuration or execution error

For example, when failed batch jobs are detected:

./scripts/check_failed_jobs.sh
echo $?

may return:

1

This approach allows the scripts to be integrated with schedulers, CI/CD pipelines or monitoring systems.

Database Connection and Security

Database passwords are not stored directly in the Shell scripts.

The scripts read the database password from the DB_PASSWORD environment variable.

Example:

read -s -p "Oracle password: " DB_PASSWORD
echo
export DB_PASSWORD

The scripts then access the value at runtime.

Sensitive files such as .env files are excluded through .gitignore.

For a real production environment, credentials would normally be managed using an approved secrets-management mechanism rather than plaintext files.

Running the Health Checks

From the project root directory:

cd /path/to/mini-erp-application-support

Set the Oracle password:

read -s -p "Oracle password: " DB_PASSWORD
echo
export DB_PASSWORD

Run the application-log check:

./scripts/analyse_logs.sh

Run the failed-batch check:

./scripts/check_failed_jobs.sh

Run the invoice exception check:

./scripts/check_invoice_issues.sh

Run the complete health check:

./scripts/daily_health_check.sh

Troubleshooting Approach

The general investigation workflow used throughout the project is:

Incident / User Report
        ↓
Identify affected transaction or component
        ↓
Check application or batch logs
        ↓
Query relevant Oracle records
        ↓
Validate related business data
        ↓
Compare expected and actual state
        ↓
Identify root cause
        ↓
Apply or simulate approved remediation
        ↓
Reprocess if required
        ↓
Validate final state
        ↓
Document findings

This approach is intended to reflect a structured Application Support troubleshooting process rather than immediately changing production data.

Support Principles Demonstrated

The project follows several important operational principles:

  • Investigate before making data changes
  • Validate related records rather than checking only one table
  • Preserve audit history where possible
  • Use rollback during troubleshooting simulations
  • Avoid storing passwords directly in scripts
  • Use exit codes for automation
  • Separate technical success from business-processing success
  • Validate results after remediation
  • Document root cause, impact, resolution and validation steps

Key Skills Demonstrated

This project demonstrates hands-on practice with:

  • Application Support
  • Production Support concepts
  • Oracle SQL
  • PL/SQL
  • SQL*Plus
  • Relational database troubleshooting
  • Data reconciliation
  • Incident investigation
  • Root cause analysis
  • Batch job support
  • Application log analysis
  • Linux / Unix troubleshooting
  • Bash shell scripting
  • Support automation
  • Exit-code handling
  • Operational health checks
  • Support documentation

Disclaimer

This project is a self-contained support lab using simulated ERP data and incidents.

It is intended to demonstrate troubleshooting methodology, database support skills, Linux/Shell fundamentals and operational automation in a controlled environment.