Scenario Overview

This backup strategy is tailored for database environments that function similarly to OLAP systems rather than high-frequency OLTP applications. In cases where data modification rates are minimal and the instance operates in NOARCHIVELOG mode, real-time point-in-time recovery is not required. However, safeguarding against physical disk failure remains critical. Implementing a scheduled cold backup serves as an effective protection mechanism for these specific constraints.

Implementation Strategy

The solution involves utilizing a secondary storage volume mounted to the server. The process requires identifying the physical locations of all Oracle components, including data files, control files, parameter files, redo logs, and temporary files. These paths are used to dynamically generate operating system copy commands. The entire workflow is orchestrated using a Windows batch file and automated via the Task Scheduler.

Configuration Steps

1. SQL*Plus Preparation Script

Create a SQL script that queries the database dictionary to locate all necessary physical files. This script outputs a secondary batch file containing the specific copy commands required for the backup.

set feedback off
set heading off
set verify off
set trimspool on
set pagesize 0
set linesize 300
define backup_dest = 'D:\Backups\ENTDB\ColdStore'
define copy_script = 'D:\Backups\ENTDB\ColdStore\run_copy.cmd'
spool &copy_script
select 'copy ' || name || ' &backup_dest' from v$controlfile
union all
select 'copy ' || name || ' &backup_dest' from v$datafile
union all
select 'copy ' || member || ' &backup_dest' from v$logfile
union all
select 'copy ' || name || ' &backup_dest' from v$tempfile
/
create pfile = '&backup_dest\initENTDB.ora' from spfile;
spool off
exit

2. Orchestration Batch File

The main batch script manages the database lifecycle. It handles logging, clears the previous backup directory, shuts down the instance, executes the file copy commands generated by the SQL script, and restores the database to an open state.

@echo off
setlocal
set LOG_PATH=D:\Logs\OracleBackup.log
set ORACLE_SID=ENTDB
set TARGET_DIR=D:\Backups\ENTDB\ColdStore

echo ================================== >> %LOG_PATH%
echo Backup Job Started >> %LOG_PATH%
echo Timestamp: %date% %time% >> %LOG_PATH%
echo ================================== >> %LOG_PATH%

if exist %TARGET_DIR% (
    rmdir /s /q %TARGET_DIR%
)
mkdir %TARGET_DIR%

sqlplus /nolog @D:\Scripts\shutdown_and_generate.sql

call %TARGET_DIR%\run_copy.cmd

sqlplus /nolog @D:\Scripts\startup_instance.sql

echo ================================== >> %LOG_PATH%
echo Backup Job Finished >> %LOG_PATH%
echo Timestamp: %date% %time% >> %LOG_PATH%
echo ================================== >> %LOG_PATH%

3. Task Scheduler Configuration

Configure the Windows Task Scheduler to trigger the orchestration batch file at the desired interval. Ensure the task is configured to run with highest privileges to guarantee access to the Oracle service and write permissions on the backup volume.