Setting Up Cross-Region Replication in OCI Object Storage

 Setting Up Cross-Region Replication in OCI Object Storage

 

Setting Up Cross-Region Replication in OCI Object Storage

Disaster recovery and high availability are critical in cloud systems. Oracle Cloud Infrastructure (OCI) provides a built-in feature to automatically replicate Object Storage buckets across regions — with no custom scripting.

🌍 What is Cross-Region Replication?

Cross-region replication allows you to copy all objects from a bucket in one OCI region to another region’s bucket in real time or near-real time. This helps with:

• Disaster recovery (DR)

• Compliance requirements

• Faster access from different geographies

🛠 How to Configure It

1. Go to OCI Console → Object Storage → Your Bucket

2. In the bucket details, select "Create Replication Policy"

3. Choose:

   • Destination region

   • Target bucket

   • Optional: Replicate only objects with a specific prefix

4. Click "Create" — that’s it!

OCI will handle the replication automatically for every new object you upload.

💡 Use Case Example

If your primary region is Mumbai, you can replicate your data to Hyderabad for DR. In case Mumbai goes down, your data is safe and accessible in Hyderabad.

✅ Benefits

• No custom scripts

• Handles data replication securely and efficiently

• Works well with lifecycle rules and archive policies

Save Cloud Costs by Archiving Old Files in OCI Object Storage

Save Cloud Costs by Archiving Old Files in OCI Object Storage

If you're storing backups, logs, or rarely accessed data in Oracle Cloud Infrastructure (OCI), you're likely using Object Storage - Standard Tier by default. But did you know you can automatically move old files to the cheaper Archive tier?

OCI’s Lifecycle Policies let you define rules that move or delete files based on age — no manual work, no scripts.

🛠️ How to Archive Old Files Automatically

Here’s how to set up a simple rule:

1. Login to the OCI Console

2. Go to Object Storage → Your Bucket → Lifecycle Policies

3. Click "Create Policy"

4. Set:

   • Action: Archive

   • Condition: If object is older than 30 days

   • (Optional) Add a prefix if you only want to target certain folders

Once this is active, OCI will automatically move files older than 30 days to the Archive tier.

📉 Why Archive Tier?

Tier	Access Time	Cost	Use Case
Standard	Immediate	Higher	Recent backups, hot data
Archive	Up to 4 hrs	Very low	Old backups, logs, cold data

💡 Example Use Case

You have a bucket storing database dumps daily. With lifecycle rules, you can keep only the recent 30 days in Standard tier and archive the rest — saving storage costs without losing data.

✅ Final Tip

Pair this with another rule to delete archived files after 180 days, and you’ve built a full storage lifecycle — all automated.

Creating a Custom Backup Strategy for Oracle Databases on OCI Compute Using Object Storage

 

Introduction

While Oracle Cloud Infrastructure (OCI) offers built-in backup capabilities for Autonomous and DBCS databases, many DBAs run Oracle databases on OCI Compute (i.e., manually installed databases on VMs). For these, automated and cost-effective backups must be designed manually.

In this post, we’ll walk through how to implement a custom backup strategy using RMAN + shell scripts, and push backups to OCI Object Storage, ensuring secure, scalable, and low-cost storage of your database backups.

Why Use Object Storage for Backups?

• Cost-effective (only pay for what you store)

• Highly durable (99.999999999%)

• Reduces local disk usage

• Can be encrypted, versioned, and lifecycle-managed

• Easily integrated with RMAN and shell scripts

Step 1: Set Up OCI Object Storage Bucket

1. Go to OCI Console → Object Storage → Create Bucket

2. Choose:

   • Bucket Name: db-backups-prod

   • Storage Tier: Standard

   • Visibility: Private

3. Copy the namespace (you'll need this in the script)

Step 2: Install OCI CLI on the DB Server

curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh | bash
 After install, configure: 

oci setup config

Provide tenancy OCID, user OCID, region, private key path, etc. 

Step 3: Create the RMAN Backup Script

rman target / <<EOF
RUN {
  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
  BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/backups/DB_%U.bkp';
  BACKUP CURRENT CONTROLFILE FORMAT '/u01/backups/CTL_%U.ctl';
  RELEASE CHANNEL ch1;
}
EOF

Step 4: Upload Backup Files to Object Storage

Here’s the shell script to upload all .bkp files to OCI:

#!/bin/bash

# Variables
BACKUP_DIR="/u01/backups"
BUCKET_NAME="db-backups-prod"
NAMESPACE="your_namespace"
OCI_PROFILE="DEFAULT"

# Upload all backups
for file in $BACKUP_DIR/*.bkp $BACKUP_DIR/*.ctl; do
  oci os object put -ns $NAMESPACE -bn $BUCKET_NAME --file "$file" --profile $OCI_PROFILE
done

# Optional: Delete local backups after upload
rm -f $BACKUP_DIR/*.bkp $BACKUP_DIR/*.ctl
 

Step 5: Automate with Cron 

crontab -e

# Backup and upload every night at 2 AM
0 2 * * * /home/oracle/scripts/backup_and_upload.sh

Security Best Practices

• Use an OCI dynamic group and IAM policy if using Instance Principals.

• Encrypt backup sets with CONFIGURE ENCRYPTION in RMAN.

• Use Object Storage lifecycle policies to auto-expire older backups.

Conclusion

Even without Autonomous DB or DBCS, you can create a robust and scalable backup system on OCI using just RMAN, shell scripting, and Object Storage. It gives you full control, reduces costs, and meets compliance needs with proper encryption and retention.

 

 

 

How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

Introduction

Monitoring the Oracle alert log is one of the simplest yet most effective ways to catch early warnings of database issues—like ORA errors, corrupt blocks, or memory issues. Many DBAs rely on OEM or third-party tools, but what if you need a lightweight, script-based solution?

This blog will walk you through creating a shell script that monitors the Oracle alert log, detects new errors, and sends an email notification—all in under 15 minutes.

Why Monitor the Alert Log Manually?

• Lightweight and works even without monitoring tools.

• Can be customized for any Oracle version or error type.

• Works in secure environments with no internet access.

Step-by-Step: Build Your Alert Log Monitor

Pre-requisites

• Linux/Unix-based Oracle Server

• Mailx configured (for email notifications)

• Access to $ORACLE_BASE/diag/rdbms/.../trace/alert_<SID>.log

1. Create the Alert Log Monitor Script

#!/bin/bash

# Set Environment
ORACLE_SID=PROD
ORACLE_BASE=/u01/app/oracle
ALERT_LOG="$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_${ORACLE_SID}.log"
LAST_CHECK="/tmp/last_check_${ORACLE_SID}.txt"
EMAIL="dba_team@example.com"

# Initialize last check timestamp
if [ ! -f "$LAST_CHECK" ]; then
  date +"%b %d %H" > "$LAST_CHECK"
fi

# Read last timestamp
LAST_TIME=$(cat "$LAST_CHECK")

# Check for new ORA- errors
grep "$LAST_TIME" -A 500 "$ALERT_LOG" | grep "ORA-" > /tmp/errors_found.txt

if [ -s /tmp/errors_found.txt ]; then
  mailx -s "ALERT: Oracle DB $ORACLE_SID - Errors Detected" "$EMAIL" < /tmp/errors_found.txt
fi

# Update timestamp for next run
date +"%b %d %H" > "$LAST_CHECK"
 

2. Schedule with Cron

crontab -e

# Run every 15 minutes
*/15 * * * * /home/oracle/scripts/alert_log_monitor.sh

3. Example Email Notification

Subject: ALERT: Oracle DB PROD - Errors Detected

ORA-04031: unable to allocate bytes of shared memory
ORA-00600: internal error code, arguments: [13011]
 

Benefits of This Script

• Works even if Oracle Enterprise Manager is down.

• Easily extensible for other log files (listener, ASM, etc.)

• Great for non-production or DR environments.

  Conclusion

  In environments where simplicity, reliability, and customization matter, shell-based alert log monitoring can be a lifesaver. With just a few lines of code, you gain real-time insight into database issues—no OEM or third-party tool required.

  Start small. Tune it. And make it part of your DBA toolkit.

   

 

 

 

Understanding Oracle SQL Plan Baselines for Performance Stability

 Understanding Oracle SQL Plan Baselines for Performance Stability

Introduction

As Oracle DBAs, we often encounter unexpected SQL performance regressions after statistics collection, database upgrades, or plan changes. One of the lesser-used but powerful tools to combat such issues is the SQL Plan Baseline feature. This blog post aims to demystify SQL Plan Baselines—what they are, why they matter, and how to implement them in real-world scenarios.

What is a SQL Plan Baseline?

A SQL Plan Baseline is a set of accepted execution plans stored in the data dictionary that Oracle uses to ensure consistent query performance. If a SQL statement's newly generated plan differs from the baseline and isn't verified yet, Oracle will prefer the known "accepted" plan—thus avoiding sudden regressions.

Why Use SQL Plan Baselines?

• Prevents performance regressions after statistics gathering or DB upgrades.

• Maintains execution plan consistency for critical SQL statements.

• Allows safe testing of new plans before full adoption.

How SQL Plan Baselines Work

1. A SQL statement is executed.

2. If no baseline exists, Oracle uses the best cost-based plan and stores it as a "candidate".

3. A DBA can manually verify and accept the candidate plan.

4. Once accepted, Oracle uses the plan for future executions unless a better one is verified and promoted.

   Step-by-Step: How to Create a SQL Plan Baseline

   1. Capture the Plan Manually

   -- Enable plan baseline capture at session level
   ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
   
   -- Run your SQL
   SELECT /*+ gather_plan_statistics */ * FROM employees WHERE department_id = 10;
    

   2. Load Plans into Baseline from Cursor Cache 

   DECLARE
     l_plans_loaded PLS_INTEGER;
   BEGIN
     l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
       sql_id => 'abcd1234abcd1234'
     );
   END;
   /
   3. Verify Baseline Exists

   SELECT sql_handle, plan_name, enabled, accepted
   FROM dba_sql_plan_baselines
   WHERE sql_text LIKE '%employees%';
    

   Advanced Usage

Evolving Plans: You can allow Oracle to test better plans automatically using plan evolution.

Transporting Baselines: Use DBMS_SPM.CREATE_STGTAB_BASELINE to export/import across databases.

Disabling Bad Plans: If a baseline plan is no longer good, you can disable it without removing it.

Best Practices

• Regularly monitor baselines for outdated or rejected plans.

• Avoid accepting plans without testing in QA.

• Combine with SQL Plan Management (SPM) policies in critical production environments.

  Conclusion

  SQL Plan Baselines are a powerful tool for ensuring SQL performance stability across changes. They are often overlooked but can make a significant difference in environments where query performance is business-critical. Start with critical queries and evolve your baseline strategy to cover more ground as your database evolves.

   

 

 

PATCH IMPACT ANALYSIS IN ORACLE EBS 12.2

 

PATCH IMPACT ANALYSIS IN ORACLE EBS 12.2

 Step 1: Download the patch in MOS

 34436371

  

  

 

  Step 2- To check staging directory

 

Navigate to Sys administrator -> oracle application manager ->patching and utilities

 

 In select feature tab please select patch impact and click go to selected feature

  

 

 In that tab click the patch wizard preferences task icon

  

 

 In that pages its sows staging directory so we need to move that patch to this same location

  

 

 Step 3- Move that patch to staging directory location and unzip it

 

 Step 4- Choose recommended analysis task icon

 

 Step 5 - select the analysis specific patch and give the patch number and click ok button

 

  Step 6- click confirmation once done the request submitted please note the request ID

 

 

 

  259170061.

 To view the running status of the request please choose job staus option

 

 

Its shows running status

  

  After some time it will completed.

 

 We can check the request id running status via conc manager ->find request

 

 

Step 7 - Execute below query and mention the correct analysis patch number its give report .

 Export the file in excel format.

 select b.APPLICATION_NAME Application,s.directory,s.filename,

DECODE(s.typeid, 'upgrade', 'Changed File', 'not applied', 'Unchanged File', 'new', 'New File') "Impact Type",

s.NEW_VERSION "Version in Patch",

s.OLD_VERSION "Version in APPL_TOP",

s.FILES_AFFECTED "Objects Affected"

from apps.fnd_application_vl b,    

apps.FND_IMP_PSMaster2 s    

where s.app_short_name=b.APPLICATION_SHORT_NAME

and s.bug_no='34436371';