Oracle Standard Edition Log Shipping in Windows ~ datablogs

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.

Share:

0 comments:

Post a Comment