datablogs: Oracle 11g
Showing posts with label Oracle 11g. Show all posts
Showing posts with label Oracle 11g. Show all posts

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

 |   |  No comments  |  , , ,

In today’s always-on world, database downtime can cost businesses a fortune. Setting up a reliable Disaster Recovery (DR) solution ensures your organization can quickly recover from unexpected failures.

This guide walks you through a practical, step-by-step process of implementing Oracle 11g Standard Edition DR, from enabling archive logs to recovering the database at the DR site.

Why This Matters

  • Business Continuity: Minimize downtime during outages.

  • Data Protection: Ensure no transaction is lost.

  • Compliance: Meet RTO/RPO requirements.

Prerequisites

Before starting, ensure:

  • Oracle 11g Standard Edition is installed on both DC (Primary) and DR (Standby) servers.

  • Network connectivity & firewall rules allow archive log and backup file transfer between DC and DR.

  • Adequate storage is provisioned for backups and archive logs.

High-Level DR Workflow

[DC: Primary Database]
   |
   |--- Archive Logs + RMAN Backups ---> [DR: Standby Database]
                                              |
                                              --> Restore + Recover --> Open DB

Step-by-Step Implementation

Step 1: Install Oracle 11g

Install Oracle 11g Standard Edition on both DC and DR servers.

Step 2: Enable Archive Logging (DC)

Archive logs capture every committed change — critical for recovery.

SQL>SELECT LOG_MODE FROM V$DATABASE;

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;

Step 3: Force Logging & Supplemental Logs

Ensure redo logs are always generated and enough data is captured for recovery.

SQL>ALTER DATABASE FORCE LOGGING;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 4: Configure Recovery Parameters

Increase recovery area size if required:

SQL>SHOW PARAMETER db_recovery;
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=10g;

Step 5: Configure RMAN

Enable control file autobackup and retention policy:

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Step 6: Take Full Backup (DC)

RMAN>BACKUP DATABASE;

This will back up all datafiles and perform an autobackup of control file & SPFILE.

Step 7: Prepare DR Instance

  • Shutdown instance

  • Remove old datafiles (oradata)

  • Start in NOMOUNT mode

STARTUP NOMOUNT;

Step 8: Move Backup Files to DR

Transfer flash_recovery_area folder to the DR server.

Step 9: Restore Control File & Database

RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN>ALTER DATABASE MOUNT;
RMAN>RESTORE DATABASE;

Step 10: Recover Database

Update the catalog in RMAN before proceeding Recover Database 

RMAN> CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
searching for all files that match the pattern C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG no files found to be unknown to the database

Use RMAN or SQL*Plus:
SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Enter AUTO to apply logs automatically.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1046243 generated at 09/16/2025 08:25:59 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC
ORA-00280: change 1046243 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1048238 generated at 09/16/2025 08:35:03 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_21_NDL89XF1_.ARC
ORA-00280: change 1048238 for thread 1 is in sequence #21
ORA-00278: log file 'C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC' no longer needed for this recovery

Finally open with resetlogs:

SQL>ALTER DATABASE OPEN RESETLOGS;

Step 11: Validate

Run validation queries:

SQL>SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;
SQL>SELECT file#, checkpoint_change# FROM v$datafile_header ORDER BY file#;
SQL>SELECT checkpoint_change# FROM v$database;

Ensure no archive log gaps remain.

RMAN Handy Scripts

Here are some ready-to-use RMAN scripts for ongoing maintenance:

Catalog & Recover

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
  RESTORE ARCHIVELOG ALL;
  RECOVER DATABASE;
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
  RELEASE CHANNEL c1;
}

Point-in-Time Recovery

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  SET UNTIL SEQUENCE 18 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL c1;
}

Key Takeaways

  • Automate archive log shipping from DC to DR to ensure near real-time recovery capability.

  • Regularly run RMAN crosschecks to keep backup metadata in sync.

  • Validate DR site periodically by performing trial recovery exercises.

With this approach, you can be confident that your Oracle 11g environment is disaster-ready and downtime can be minimized.