Cold backup script for Windows 2000/2003/NT



My OraFAQ Blog

Contact me

For ease of use, this script is stored in two files. The first file is a SQL script that, when run in sql*plus, generates a DOS cold backup script. The second file is a DOS script that calls the first script, shuts down the database, runs the cold backup script that was just generated, and starts up the database.

You will have to create two additional files, shutdown.sql and startup.sql -- see comments in the second scipt. You will also need to precreate the backup log and, of course, customize the directories and SID for your system.



REM make_cold_backup.sql
REM copyright 2001-2004 N Roshak
REM this is a sql script to generate a DOS backup script

connect system/PASSWORD@DB
set heading off
set pagesize 0
set echo off
set feedback off
set verify off
set linesize 500
col mything for a500
spool C:\scripts\orabackup\DB\cold_backup.bat
prompt REM Script to take full cold database backup
prompt REM generated by make_cold_backup_scr.sql
prompt REM does not back up tempfiles
prompt REM
prompt set oracle_home=d:\oracle\ora92
prompt set backup_dir=j:\backup\datafiles\DB
select distinct 'copy ' || leaf
|| ' %backup_dir%' || substr(leaf,instr(leaf,'\',-1,1), length(leaf))
as  cmdstr
(select name as LEAF from v$datafile
select name from v$controlfile
select member from v$logfile)
prompt REM Copy init.ora and pwd files
prompt copy %oracle_home%\database\initDB.ora %backup_dir%\initDB.ora
prompt copy %oracle_home%\database\pwdDB.ora %backup_dir%\pwdDB.ora
spool off




REM backup.bat
REM this is a DOS batch script
REM to take a full cold backup
REM copyright 2002-2004 N Roshak

REM requires two sql scripts in addition to the above sql script:
REM shutdown.sql (connect / as sysdba, shutdown transactional, exit)
REM startup.sql (connect / as sysdba, startup open, exit)

set oracle_home=d:\oracle\ora92
set script_dir=c:\scripts\orabackup\DB
set shutdown=%script_dir%\shutdown.sql
set startup=%script_dir%\startup.sql
set scriptgen=%script_dir%\make_cold_backup_scr.sql
set log=%script_dir%\backup.log

echo >> %log%
echo ----------BEGIN FULL COLD BACKUP OF DB---------- >> %log%
date /T >> %log%
time /T >> %log%
echo Generating cold backup script... >> %log%
%oracle_home%\bin\sqlplus /nolog @%scriptgen%

date /T >> %log%
time /T >> %log%
echo Shutting down... >> %log%

%oracle_home%\bin\sqlplus /nolog @%shutdown%

echo Backing up... >> %log%
call %SCRIPT_DIR%\cold_Backup.bat >> %log% 2>&1

echo Starting up... >> %log%
%oracle_home%\bin\sqlplus /nolog @%startup%

date /T >> %log%
time /T >> %log%
echo Succesfully completed. >> %log%
echo -----------END FULL COLD BACKUP OF DB----------- >> %log%


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...