Windows Oracle database administration scripts for hot backup and export backup

Automated conventional hot backup, export backup and their clean up script on a windows environment are pasted below - it is advised to go through the script / test on a Dev-Test environment before moving over to Prod since a couple of locations are to be hardcoded based on the environment. Most of the work is being carried out by pl/sql scripts, this gave me little chance to improvise my pl/sql skills ;-).


The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.

Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.

###############################
# # #   H O T   B K U P   # # #
###############################

################################
### hot_backup_on_disk.bat  ####
################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-bkup_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-hot_bkup.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\03-arch_switch.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\04-ctrl_file.sql

###########################
####  01-bkup_dest.sql  ###
###########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\bkup_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\hot\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\bkup_dest_create.sql
exit

##########################
###  02-hot_bkup.sql  ####
##########################


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\hot_bkup_copy.sql
declare
cursor ts is select distinct(tablespace_name) 
from dba_data_files;
cursor fn (ts_name in VARCHAR2) is select file_name 
from dba_data_files
where tablespace_name=ts_name order by 1;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Datafiles --');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
for c in ts
loop
dbms_output.put_line('alter tablespace '||c.tablespace_name - 
||' begin backup;');
for d in fn (c.tablespace_name)
loop
dbms_output.put_line('host copy '||d.file_name|| - 
' D:\oracle\backups\hot\'||dir||'\.');
end loop;
dbms_output.put_line('alter tablespace '|| - 
c.tablespace_name||' end backup;');
dbms_output.put_line(chr(0));
end loop;
end;
/
spool off
@D:\oracle\backups\scripts\hot_bkup_copy.sql
exit

#############################
####  03-arch_switch.sql ####
#############################

###
### The LOG_ARCHIVE_FORMAT parameter
### plays a significant role in here
### the format of archive files that 
### is used here would be
### ${ORACLE_SID}_ARCH_*_*001, which
### has to be changed appropriately
### based on your environment
###


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\arch_switch_copy.sql
declare
prev_seq number;
i number;
j number;
k number;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
select sequence# into i from v$log where status='CURRENT';
prev_seq:=i-1;
execute immediate ('alter system switch logfile');
select sequence# into j from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
select sequence# into k from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Logfiles ---');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
dbms_output.put_line('host copy - 
D:\oracle\oradata\arch\PROD_ARCH_*'||prev_seq||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||i||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||j||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||k||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\arch_switch_copy.sql
exit

##########################
###  04-ctrl_file.sql  ###
##########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\ctrl_file_copy.sql
declare
dir varchar2(20);
 
begin
select to_char(sysdate,'DD-MON-YYYY') into 
dir from dual;
execute immediate 'alter session set tracefile_identifier='||''''||dir||'''';
execute immediate ('alter database backup controlfile to trace');
execute immediate 'alter database backup controlfile to'||''''||'D:\oracle\backups\hot\'||dir||'\controlfile_'||dir||'_bin.bkup'||'''';
dbms_output.put_line('host move D:\oracle\admin\PROD\udump\*'-
||dir||'*.trc D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\ctrl_file_copy.sql
exit

###############################
# # #   E X P   B K U P   # # #
###############################

####################################
###   export_backup_on_disk.bat  ###
####################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-exp_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-exp_bkup.sql

#########################
###  01-exp_dest.sql  ###
#########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\exp_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\exp\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\exp_dest_create.sql
exit

#########################
###  02-exp_bkup.sql  ###
#########################

set head off
set feed off
set line 300
set serveroutput on
spool D:\oracle\backups\scripts\exp_bkup_full.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host exp system/manager file=D:\oracle\backups\exp\'||dir||'\PROD_full.dmp log=D:\oracle\backups\exp\'||dir||'\PROD_full.log full=y buffer=10485760 consistent=y compress=n');
end;
/
spool off
@D:\oracle\backups\scripts\exp_bkup_full.sql
exit

#######################################
# # #   C L E A N U P   J O B S   # # #
#######################################

###########################
### delete_hot_bkup.bat ###
###########################

###
### Keeps the last 5 days of hot backups  
### on disk while deleting anything older
### than that
###

forfiles /p "D:\oracle\backups\hot" /s /m *.* /d -05 /c "cmd /c del @path"

###########################
### delete_exp_bkup.bat ###
###########################

###
### keeps the last 5 days of export backups
### on disk while deleting anything older than
### that
###

forfiles /p "D:\oracle\backups\exp" /s /m *.* /d -05 /c "cmd /c del @path"