Auto Shutdown and Restart of Oracle DB Systems in OCI Using Functions

 

🔹 Introduction

Oracle Cloud Infrastructure (OCI) Database Systems incur compute costs even when idle. If you're running non-production or development environments, one simple cost-saving strategy is to automate their shutdown at night and restart in the morning.

In this blog, I’ll walk you through setting up an automated stop/start schedule for your DB Systems using OCI Functions (or OCI CLI if preferred). This is a practical cost control technique every Oracle DBA should know.

🔹 Use Case

✔️ You have a DB System (Bare Metal or VM) running in OCI
✔️ It is used only during business hours
✔️ You want to stop it at 7 PM and start it at 7 AM automatically
✔️ You want a serverless and secure way to do this

🔹 Option 1: OCI CLI + Cron (Simpler for DBAs)

If you're using a Linux jump server or Bastion host:

✅ Create a Shell Script to Stop DB

#!/bin/bash
DB_OCID="<your_db_system_ocid>"
oci db system stop --db-system-id $DB_OCID --wait-for-state STOPPED

✅ Create a Script to Start DB

#!/bin/bash
DB_OCID="<your_db_system_ocid>"
oci db system start --db-system-id $DB_OCID --wait-for-state AVAILABLE

✅ Add to Crontab

Edit crontab:

crontab -e

Add:

0 19 * * * /home/opc/stop_db.sh
0 7 * * * /home/opc/start_db.sh

That's it. Your DB system now auto-starts and stops every day!

🔹 Option 2: OCI Functions + Event Schedule (Serverless Approach)

This is a more cloud-native, scalable approach.

✅ Step 1: Create OCI Function Application

• Go to Developer Services → Functions

• Create an Application in your preferred compartment and subnet

✅ Step 2: Create a Python Function

Use this Python function to stop/start a DB system:

import io
import json
import oci

def handler(ctx, data: io.BytesIO = None):
    signer = oci.auth.signers.get_resource_principals_signer()
    db_client = oci.database.DatabaseClient(config={}, signer=signer)

    body = json.loads(data.getvalue())
    action = body.get("action")  # 'start' or 'stop'
    db_system_id = body.get("db_system_id")

    if action == "stop":
        response = db_client.stop_db_system(db_system_id)
    elif action == "start":
        response = db_client.start_db_system(db_system_id)
    else:
        return json.dumps({"status": "invalid action"})

    return json.dumps({"status": "submitted", "action": action})

✅ Step 3: Deploy and Test Function

Use fn CLI to deploy the function and test it with:

fn invoke myapp stop_db --content '{"action": "stop", "db_system_id": "<ocid>"}'

✅ Step 4: Create Scheduled Rule

• Go to Observability & Management → Events

• Create a Scheduled Rule

  • Time: Daily at 07:00 and 19:00

  • Action: Invoke Function with payload (start/stop + OCID)

💡 Vignesh’s Tip

If your DB system is critical, always check its current lifecycle state before issuing start/stop commands. Add checks like:

STATE=$(oci db system get --db-system-id $DB_OCID --query 'data."lifecycle-state"' --raw-output)

This avoids redundant API calls or errors.

🔹 Benefits

• ⏱️ Zero manual intervention

• 💰 Significant cost savings for idle environments

• 🔒 Secure and auditable (Functions run with resource principals)

• ⚙️ Easy to scale across multiple DB systems

🔹 Conclusion

Automating the start and stop of Oracle DB Systems using OCI CLI or Functions is a smart way to save costs without compromising control. Whether you're a DBA, cloud engineer, or DevOps team, this approach helps keep your cloud bill in check while maintaining full flexibility.

OCI Audit Log Analysis for Oracle Database Events Using CLI

 

🔹 Introduction

Every action in Oracle Cloud Infrastructure (OCI)—from creating a database to stopping a compute instance—is logged in the Audit service. For Oracle DBAs, this is a hidden gem: a way to track who did what, when, and from where.

In this blog, I’ll show you how to extract and analyze audit logs specifically for Oracle Database-related events, using the OCI CLI. You don’t need to set up any logging service—Audit is enabled by default for all OCI tenancies.

🔹 Why DBAs Should Care About Audit Logs

Audit logs help you:

• 🧾 Track DB system creation, deletion, and restarts

• 🔐 Identify who accessed or modified DB-related resources

• 🧯 Investigate incidents or unauthorized changes

• 💼 Meet compliance and internal audit requirements

🔹 What You’ll Learn

• Enable and understand audit logs

• Fetch logs using OCI CLI

• Filter logs for Oracle Database events

• Decode and interpret log details

• Optional: Export to CSV or file

🔹 Step 1: Prerequisites

• OCI CLI installed and configured

• Know your:

  • Compartment OCID

  • Region

🔹 Step 2: Basic Command to Fetch Audit Logs

oci audit event list \
--compartment-id <your_compartment_ocid> \
--start-time 2025-07-25T00:00:00Z \
--end-time 2025-08-02T23:59:59Z \
--all

This returns all audit events for the given date range.

🔹 Step 3: Filter for Oracle Database Events Only

Use --query to filter specific database services:

oci audit event list \
--compartment-id <your_compartment_ocid> \
--start-time 2025-07-25T00:00:00Z \
--end-time 2025-08-02T23:59:59Z \
--query "data[?contains(eventName, 'Db')]"

You can filter further for specific actions:

• CreateDbSystem

• UpdateDbSystem

• StopDbSystem

• DeleteDbHome

• LaunchAutonomousDatabase

🔹 Step 4: Understand Key Fields in Output

Each log event contains:

• eventName: Action taken (e.g., StopDbSystem)

• principalName: Who performed the action

• sourceIP: From where it was done

• requestAction: Full JSON of the request

• responseTime: Execution duration

• eventTime: When the event occurred

🔍 Example:

{
  "eventName": "StopDbSystem",
  "principalName": "vignesh.dba@example.com",
  "sourceIpAddress": "103.23.42.11",
  "eventTime": "2025-08-01T14:02:13Z"
}

🔹 Step 5: Save Audit Logs to File

oci audit event list \
--compartment-id <your_compartment_ocid> \
--start-time 2025-07-25T00:00:00Z \
--end-time 2025-08-02T23:59:59Z \
--all > db_audit_logs.json

You can parse and convert to CSV using tools like jq or Python.

🔹 Advanced: Filter Specific Action (e.g., DB Stop Events Only)

oci audit event list \
--compartment-id <your_compartment_ocid> \
--query "data[?eventName=='StopDbSystem']" \
--all

This gives you a focused view on any unplanned or unauthorized DB shutdowns.

🔹 Bonus: Combine with Logging Analytics (Optional)

If you’re using OCI Logging Analytics, you can stream audit logs to it and build custom dashboards:

• Query:
  eventName = "CreateDbSystem" | count by principalName

• Visualization:
  Top users who created DB systems in the last 30 days

💡 Vignesh’s Tip

Audit log data is retained for 90 days by default in OCI. If your security policy requires longer retention, export logs to Object Storage periodically.

You can even automate this using a scheduled function or CLI-based cron job.

🔹 Conclusion

OCI Audit logs are an underrated but powerful tool for DBAs. With a few CLI commands, you gain deep visibility into who’s touching your Oracle Database systems and can track events for auditing, security, and troubleshooting.

Securing Oracle Cloud Linux Instances: Security Lists vs Network Security Groups (NSGs)

 

🔹 Introduction

When launching Oracle Linux compute instances or database systems in Oracle Cloud Infrastructure (OCI), security is a top priority. Two critical features that control network access in OCI are:

• 🔸 Security Lists

• 🔸 Network Security Groups (NSGs)

Many admins and even some Oracle DBAs are unclear about when to use which. In this post, I’ll explain both in a simple, real-world scenario that applies directly to database environments.

🔹 What You'll Learn

• What are Security Lists and NSGs

• Key differences between the two

• Best use cases for each

• A working example: Allowing access to Oracle DB port 1521

• My personal recommendation as a DBA

🔹 1. What is a Security List?

A Security List is attached to a Subnet in OCI. All compute instances in that subnet inherit its rules.

Example Rule:
Allow Ingress TCP Port 22 from CIDR 0.0.0.0/0 (for SSH access)

🔹 2. What is a Network Security Group (NSG)?

An NSG is like a mini-firewall attached to a specific instance or resource, not to the whole subnet.

It gives resource-level granularity, especially useful when multiple DB or app servers are in the same subnet.

🔸 Key Differences

Feature	Security List	Network Security Group (NSG)
Attached To	Subnet	Individual resources (VM, DB System)
Granularity	Broad (all in subnet)	Fine-grained (specific resource)
Rule Changes	Affects all in subnet	Only affects attached resource
Use Case	Simpler setups	Secure and complex architectures
Default for DB Systems	Often not editable	Fully supported and flexible

🔹 3. Real-World Use Case: Allow DB Port 1521 for a Specific App Server

🔧 Scenario: You want to allow only one application server to access the DB port 1521 on your Oracle Database system in OCI.

✅ Option 1: Security List Approach

• Add Ingress Rule:

  • Protocol: TCP

  • Port: 1521

  • Source CIDR: <AppServer-IP>/32

🔴 Problem: Any other compute instance in the subnet can also use this rule.

✅ Option 2: NSG Approach (Recommended)

• Create NSG: nsg-db-access

• Add Ingress Rule:

  • Protocol: TCP

  • Port: 1521

  • Source: Another NSG (e.g., nsg-app-server)

• Attach this NSG only to the DB instance

✔️ Advantage: Only the app server can talk to the DB. More secure and flexible.

🔹 4. How to Create NSGs (via Console)

1. Go to Networking → Network Security Groups → Create

2. Name: nsg-db-access

3. Add rule: Ingress TCP Port 1521 from nsg-app-server

4. Attach this NSG to your DB system under its Network settings

🔹 Best Practices

• ✅ Use NSGs for DB systems, always

• ✅ Create separate NSGs for App, DB, Web tiers

• ✅ Keep Security Lists for basic subnet rules like SSH

• 🚫 Avoid using wide CIDR blocks (0.0.0.0/0) for database ports

💡 Vignesh’s Tip

You can automate NSG rule creation using OCI CLI:

oci network nsg-rule add --nsg-id ocid1.networksecuritygroup.oc1... \
--direction INGRESS --protocol 6 --is-stateless false \
--source-type NETWORK_SECURITY_GROUP --source-id ocid1.networksecuritygroup.oc1... \
--tcp-options '{"destinationPortRange": {"min": 1521, "max": 1521}}'

This is very useful when provisioning resources via scripts or Terraform.

🔹 Conclusion

Security Lists and NSGs both play crucial roles in OCI, but understanding their differences helps you build secure and modular cloud environments. For Oracle DBAs, using NSGs for database port control is a best practice.

Automate RMAN Backup Upload to Oracle Cloud Object Storage via Pre-authenticated Request (PAR)

 

🔹 Introduction

When managing backups for Oracle databases, especially in cloud or hybrid environments, uploading RMAN backups to Oracle Cloud Object Storage is a cost-effective and scalable solution. In this blog, I’ll walk you through an easier and lightweight method—using Pre-authenticated Requests (PARs) instead of configuring the full backup module.

Ideal for:

• One-time uploads

• Lightweight databases

• Automation without exposing full credentials

🔹 What is a Pre-authenticated Request (PAR)?

A PAR is a temporary URL that allows access to a specific Object Storage bucket or object without needing a user login or an API key. You can upload, download, or delete files securely with an expiry date

🔹 Use Case

✔️ You take local RMAN backups on a DB server (on-prem or OCI VM)
✔️ You want to push them to Oracle Object Storage for disaster recovery
✔️ You don’t want to configure Oracle Backup Module or DB System native integration

🔹 Step-by-Step: Create PAR and Upload RMAN Backup

 ✅ Step 1: Take Your RMAN Backup

Example RMAN command (level 0 full backup):

rman target /
BACKUP DATABASE FORMAT '/u01/app/oracle/backup/rman_%U.bkp';

✅ Step 2: Create a Bucket in OCI

From the OCI Console:

• Go to Object Storage → Buckets

• Click Create Bucket

  • Name: rman-backups

  • Storage Tier: Standard

  • Encryption: Default

✅ Step 3: Create a Pre-authenticated Request (PAR)

Go to the bucket → Pre-authenticated Requests → Create PAR

• Name: upload-rman-backup

• Access Type: Permit uploads to this bucket

• Expiration: e.g., 7 days

• Click Create Pre-authenticated Request

You’ll get a URL like:

https://objectstorage.ap-mumbai-1.oraclecloud.com/p/PARKEY/n/namespace-string/b/rman-backups/o/

✅ Step 4: Upload Using curl or rclone

Assuming your RMAN backup is /u01/app/oracle/backup/rman_abc123.bkp:

curl -T rman_abc123.bkp "https://objectstorage.ap-mumbai-1.oraclecloud.com/p/PARKEY/n/namespace-string/b/rman-backups/o/rman_abc123.bkp"

Done! Your backup is now in OCI Object Storage.

✅ Optional: Automate the Upload in Shell Script

#!/bin/bash
BACKUP_DIR="/u01/app/oracle/backup"
PAR_URL="https://objectstorage.ap-mumbai-1.oraclecloud.com/p/PARKEY/n/namespace-string/b/rman-backups/o/"

for file in $BACKUP_DIR/*.bkp; do
    filename=$(basename "$file")
    curl -T "$file" "${PAR_URL}${filename}"
done

Schedule via cron to run after your backup job.

🔐 Security Note

• Never share PAR URLs publicly—they provide direct access.

• Always set an expiration on PAR.

• Delete the PAR once upload is completed (via Console or CLI).

💡 Vignesh’s Tip

You can also create PARs using the OCI CLI:

oci os preauth-request create --access-type ObjectWrite --bucket-name rman-backups --name upload-rman-backup --time-expires "<RFC3339 timestamp>"

Useful for scripting and DevOps workflows.

🔹 Conclusion

Using Pre-authenticated Requests for RMAN backup uploads is a fast, secure, and flexible alternative to more complex OCI-native integrations. Whether you are an on-prem DBA or working in OCI VMs, this method helps you back up to the cloud without complexity.

Monitor Oracle Cloud Object Storage Space Usage via OCI CLI

 

🔹 Introduction

Managing storage efficiently is essential, especially when dealing with large backups or logs in Oracle Cloud Infrastructure (OCI). While the OCI Console provides visibility, it’s not always ideal for scripting or automation. In this post, we’ll explore how to use the OCI Command Line Interface (CLI) to monitor the space usage of your Object Storage buckets.

🔹 What You’ll Learn

• How to install and configure OCI CLI

• List all buckets in a compartment

• Retrieve storage usage details per bucket

• Generate a summary report

• Optional: Automate and send email alerts

🔹 Step 1: Install & Configure OCI CLI

If you haven’t already installed the CLI:

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

After installation, run:

oci setup config

You'll be prompted to enter:

• Tenancy OCID

• User OCID

• Compartment OCID

• Region

• API key (which it generates)

🔹 Step 2: List Buckets in Your Compartment

oci os bucket list --compartment-id <your_compartment_ocid> --all

Output will show all buckets, their names, and creation timestamps.

🔹 Step 3: Check Storage Usage per Bucket

To get the total size of a bucket (in bytes):

oci os object list --bucket-name <bucket_name> --query "sum([*].size)" --raw-output

🔸 Sample Shell Script to Loop All Buckets:

#!/bin/bash

COMPARTMENT_ID="<your_compartment_ocid>"
BUCKETS=$(oci os bucket list --compartment-id $COMPARTMENT_ID --query "data[*].name" --raw-output)

echo "Storage Report for Buckets in OCI:"
echo "----------------------------------"

for bucket in $BUCKETS; do
    SIZE=$(oci os object list --bucket-name "$bucket" --query "sum([*].size)" --raw-output)
    SIZE_MB=$(echo "scale=2; $SIZE / 1048576" | bc)
    echo "Bucket: $bucket - Size: $SIZE_MB MB"
done

🔹 Step 4: Automate Storage Monitoring (Optional)

You can run the script daily and email the output:

crontab -e

Add this line to run at 8 AM daily:

0 8 * * * /home/opc/check_oci_storage.sh | mail -s "OCI Bucket Usage Report" your@email.com

🔹 Tips for DBAs

• Use Storage Class filters (Standard vs Archive) to reduce costs.

• Enable Lifecycle Policies to auto-delete old files.

• Avoid keeping RMAN backups longer than needed.

🔹 Vignesh’s Tip

For temporary one-time uploads (like logs or exported tables), use Pre-Authenticated Requests (PARs). They give you a URL without needing to expose keys or public access.

🔹 Conclusion

Monitoring storage usage with OCI CLI not only helps you manage cost but also keeps your backup strategy efficient. For DBAs and DevOps engineers working in hybrid environments, this approach enables easy scripting and reporting.