Sidecar Databases on OCI: A Practical Strategy for Modernization and Legacy Workloads

Modernizing to Oracle Autonomous AI Database accelerates transactional workloads, but many organizations still depend on legacy batch processing and reporting. Rewriting or retiring these systems upfront often introduces unnecessary risk and disruption. A sidecar database strategy enables modernization to move forward while protecting business-critical processes.

This post walks through a step-by-step SOP, followed by batch and reporting scenarios with real SQL examples.

Assumptions

• ADB-S/ADB-D = Primary database
• Oracle Base Database = Sidecar database
• Both databases already exist in Oracle Cloud Infrastructure
• Private connectivity is already available
• You have:
• ADMIN access on ADB-S
• DBA access on the Base Database

SOP: Step-by-Step Sidecar Implementation

Step 1: Create Sidecar Schemas

On Sidecar Base Database

CREATE USER sidecar_app IDENTIFIED BY Sidecar#123;
GRANT CONNECT, RESOURCE TO sidecar_app;

Optional: Separate Batch & Reporting

CREATE USER sidecar_batch IDENTIFIED BY Batch#123;
CREATE USER sidecar_rpt IDENTIFIED BY Report#123;

GRANT CONNECT, RESOURCE TO sidecar_batch;
GRANT CONNECT, RESOURCE TO sidecar_rpt;

Step 2: Create DB Link User on Sidecar

CREATE USER dblink_user IDENTIFIED BY Dblink#123;
GRANT CREATE SESSION TO dblink_user;

This user will only be used for cross-database access.

Step 3: Create Database Link from ADB-S

On ADB-S

CREATE DATABASE LINK sidecar_dbl
CONNECT TO dblink_user IDENTIFIED BY Dblink#123
USING 'sidecar_tns';

Test Connectivity

SELECT SYSDATE FROM dual@sidecar_dbl;

Expected output:
Current timestamp returned

Step 4: Create Sample Transactional Data in ADB-S

On ADB-S

CREATE TABLE orders (
order_id     NUMBER,
customer_id  NUMBER,
order_date   DATE,
amount       NUMBER
);

Insert Sample Data

INSERT INTO orders VALUES (1, 101, SYSDATE-1, 500);
INSERT INTO orders VALUES (2, 102, SYSDATE-1, 1200);
INSERT INTO orders VALUES (3, 103, SYSDATE,   700);
COMMIT;

Scenario 1: Batch Processing Using Sidecar

Business Need

· Run nightly batch jobs

· Heavy calculations

· Generate files or summaries

· Do NOT impact ADB-S performance

Step 1: Create Batch Tables in Sidecar

On Sidecar

CREATE TABLE batch_orders (
  order_id     NUMBER,
  customer_id  NUMBER,
  order_date   DATE,
  amount       NUMBER
);

Step 2: Push Data from ADB-S to Sidecar

On ADB-S

INSERT INTO batch_orders@sidecar_dbl
SELECT *
FROM orders
WHERE order_date = TRUNC(SYSDATE-1);

COMMIT;

Step 3: Run Batch Logic in Sidecar

Example: Daily Sales Summary

CREATE TABLE daily_sales_summary (
  sales_date DATE,
  total_amount NUMBER
);
INSERT INTO daily_sales_summary
SELECT order_date, SUM(amount)
FROM batch_orders
GROUP BY order_date;
COMMIT;

Step 4: Optional — Push Results Back to ADB-S

On ADB-S

CREATE TABLE sales_summary (
  sales_date DATE,
  total_amount NUMBER
);
INSERT INTO sales_summary
SELECT *
FROM daily_sales_summary@sidecar_dbl;

COMMIT;

Result

· Batch runs are isolated

Get satyabrata mishra’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

· ADB-S unaffected

· Sidecar failures do not block transactions

Scenario 2: Reporting Workloads Using Sidecar

Business Need

· Historical reports

· Large scans

· Aggregations

· Stable performance

Step 1: Create Reporting Tables in Sidecar

CREATE TABLE rpt_orders (
  order_id     NUMBER,
  customer_id  NUMBER,
  order_date   DATE,
  amount       NUMBER
);

Step 2: Refresh Reporting Data

On ADB-S

INSERT INTO rpt_orders@sidecar_dbl
SELECT * FROM orders;

COMMIT;

Step 3: Run Heavy Report Queries (Sidecar Only)

SELECT customer_id,
       COUNT(*) AS total_orders,
       SUM(amount) AS total_spent
FROM rpt_orders
GROUP BY customer_id;

Result

· Reports do not scan ADB-S tables

· Predictable report runtime

· Lower resource usage on ADB-S

Scenario 3: Month-End Financial Reporting

Step 1: Month-End Snapshot

CREATE TABLE month_end_orders AS
SELECT * FROM rpt_orders
WHERE order_date BETWEEN
      TRUNC(ADD_MONTHS(SYSDATE,-1),'MM')
  AND LAST_DAY(ADD_MONTHS(SYSDATE,-1));

Step 2: Month-End Aggregation

SELECT TO_CHAR(order_date,'YYYY-MM') AS month,
       SUM(amount) AS revenue
FROM month_end_orders
GROUP BY TO_CHAR(order_date,'YYYY-MM');

Result

· Stable month-end processing

· No pressure on ADB-S

· Auditable historical snapshots

Scenario 4: Failure Handling Example

Simulated Failure

· Sidecar database is down

· Batch job fails

Impact

· Batch job fails

· Reports delayed

· ADB-S transactions continue normally

This isolation is intentional and by design.

Security Best Practices (Command Level)

-- Revoke unnecessary privileges
REVOKE RESOURCE FROM dblink_user;

-- Audit DB link usage
AUDIT DATABASE LINK;

Key Takeaways

· Sidecar strategy is process-driven, not architecture-heavy

· Batch and reporting workloads belong outside ADB-S

· Scheduled data movement beats real-time coupling

· Failure isolation protects production systems