DBAQuest.com

• gen_file_names.sql - This script logs into the database and generates copy commands for the datafiles and controlfiles. It takes the backup destination directory as a parameter


• shutdown.sql - This script shuts down the database after the filenames have been retrieved


• startup.sql - This script starts up the database after the files are copied

@echo OFF
@if not "%ECHO%"=="" echo %ECHO%
@if not "%OS%"=="Windows_NT" goto EOF

REM -------------------------------------------------------------------------------
REM Name:         c:\scripts\cold_backup.bat
REM Author:       Murali Sreedhar
REM Usage:	  cold_backup.bat SID
REM Reqd. Files:  c:\scripts\sql\gen_file_names.sql, 
REM               c:\scripts\sql\shutdown.sql, 
REM		  c:\scripts\sql\startup.sql
REM Description:  Performs cold backup of specified database. Change the environment
REM		  variables according to your specific environment.
REM Disclaimer:   Use this script at your own risk. The author is not responsible
REM		  for any loss arising out of the use of this script.
REM -------------------------------------------------------------------------------

REM Prompt for SID

if {%1}=={} echo Please provide the SID as a parameter. & (goto :END)

REM Set up environment variables

set ORACLE_HOME=c:\oracle\ora90
set ORACLE_SID=%1
set BACKUP_DIR=c:\backup
set USER_PWD="/ as sysdba"
set SCRIPTS_SQL=c:\scripts\sql
set SCRIPTS_LOG=c:\scripts\log
set SCRIPTS_CMD=c:\scripts\cmd


title Cold Backup of %1

REM Get a list of files to backup and generate the COPY commands
REM The copy commands are sent to another script called backup_child_SID.bat
REM which will do the actual backup. This temporary file is later deleted.
REM The backup directory is included as a parameter to the SQL file to be
REM included in the copy commands.
 
%ORACLE_HOME%\bin\sqlplus -s %USER_PWD% @%SCRIPTS_SQL%\gen_file_names.sql %BACKUP_DIR% > %SCRIPTS_CMD%\backup_child_%1.bat

REM If for some reason, generation of file names failed, abort the backup
REM and log the cause of the error in our error log file.

@for /f %%I in ('type %SCRIPTS_CMD%\backup_child_%1.bat ^| findstr/c:"ORA-"') do (
  echo %%I >> %SCRIPTS_LOG%\cold_bkp_error.log
  goto :END
)

REM On the other hand, if our child script was created properly, shutdown the database and run it !

date /t >> %SCRIPTS_LOG%\cold_bkp_%1.log
time /t >> %SCRIPTS_LOG%\cold_bkp_%1.log
%ORACLE_HOME%\bin\sqlplus -s %USER_PWD% @%SCRIPTS_SQL%\shutdown.sql >> %SCRIPTS_LOG%\cold_bkp_%1.log
if exist %SCRIPTS_CMD%\backup_child_%1.bat call %SCRIPTS_CMD%\backup_child_%1.bat
%ORACLE_HOME%\bin\sqlplus -s %USER_PWD% @%SCRIPTS_SQL%\startup.sql >> %SCRIPTS_LOG%\cold_bkp_%1.log
date /t >> %SCRIPTS_LOG%\cold_bkp_%1.log
time /t >> %SCRIPTS_LOG%\cold_bkp_%1.log

REM Delete the temporary file (the child script) to avoid confusion later

del %SCRIPTS_CMD%\backup_child_%1.bat >> %SCRIPTS_LOG%\cold_bkp_%1.log

:END

set pages 0
set head off
set verify off
set feedback off
select 'xcopy '||file_name||' &&1\' from dba_data_files
/
select 'xcopy '||name||' &&1\' from v$controlfile
/
exit

shutdown immediate
exit

startup
exit