Oracle hot backup scripts Oracle Tips by Burleson Consulting

Chris Pretorius has published this Oracle hot backup script.  This hot backup script does not use RMAN.  For a complete listing of verified and correct Oracle hot backup scripts, see the Oracle script download.  WARNING - These Oracle hot backup scripts are contributed by third parties and they have not been tested.  Run these hot backup scripts at your own risk.

Here is Chris's abbreviated Oracle hot backup script:

set heading off
set echo off
set feedback off
set verify off
set pagesize 0
set linesize 300

spool D:\BACKUP\backupdb.sql
SEE CODE DEPOT FOR FULL HOT BACKUP SCRIPTS
select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;
select 'REM -- Cleaning Up...' from dual;
select 'host del /Q &1\*.*' from dual;
select 'REM -- START LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM -- SWICTHING LOGFILES...' from dual;
select 'alter system switch logfile;' from dual;
select 'REM --PERFORMING ONLINE BACKUP OF TABLESPACES...' from dual;
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||'
host copy '||file_name||' D:\BACKUP'||chr(10)||'
alter tablespace '||tablespace_name||' end backup;'
from Remote DBA_data_files
WHERE FILE_NAME NOT LIKE '%SYS_READ%';
select 'alter system switch logfile;' from dual;
select 'REM --FINISH LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM --COPYING REDO LOGS....' from dual;
select 'host copy '||member||' D:\BACKUP' from v$logfile;
select 'REM --COPYING CONTROLFILES ...' from dual;
select 'host copy '||name||' D:\BACKUP' from v$controlfile;
select 'REM --backing up controlfile to trace...' from dual;
alter database backup controlfile to trace;
select 'REM --COPYING ARCHIVE LOGS ...' from dual;
select 'host copy '||destination||'\*.* D:\BACKUP' from v$archive_dest
where status = 'VALID';
select 'REM --DELETING ARCHIVE LOGS ...' from dual;
select 'host del /Q /F '||destination||'\*.*' from v$archive_dest
where status = 'VALID';
select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;

spool off

set heading on
set echo on
set feedback on
set verify on

spool D:\BACKUP\backupdb.log

@D:\BACKUP\backupdb.sql

spool off



REM ***************************************************
REM VERSION: 1.2
REM CREATED DATE: 23 May 2005
REM CREATED BY: Ernst Karner
REM LAST MODIFIED ON: 17 March 2008
REM LAST MODIFIED BY: Christian Pretorius
REM
REM VERSION HISTORY:
REM 23/05/2005, V1.1 - Added version information
REM 23/05/2005, V1.2 - Removed duplicate environment variables in log file.
REM 17/03/2008, V1.3 - Added password variable for cross version compatibility
REM - Removed the SYS_READ from the datafile backup, this is to
REM eliminate ALERT.LOG errors
REM
REM
REM ***************************************************
SEE CODE DEPOT FOR FULL HOT BACKUP SCRIPTS
set bck_ver=1.3

REM ***************************************************
REM * ENTER THE REQUIRED SETTINGS IN THE SECTION BELOW*
REM ***************************************************

REM ***************************************************
REM *** ENTER THE PROPERTY'S NAME AFTER THE = SIGN ****
REM ***************************************************

set property=

REM ***************************************************
REM ** IF THE DATABASE NAME IS NOT "OPERA", ENTER **
REM ** THE DATABASE NAME AFTER THE = SIGN, OTHERIon **
REM ** LEAVE AS IS **
REM ***************************************************

set oracle_sid=opera

REM ***************************************************
REM ** ENTER THE DRIVE LETTER ON WHICH THE BACKUP **
REM ** FOLDER WILL BE CREATED. ONLY ENTER THE DRIVE **
REM ** LETTER FOLLOWED BY : (EXAMPLE: E:) **
REM ***************************************************

set backup_drive=d:

REM ***************************************************
REM ** ENTER THE ORACLE SYSTEM PASSWORD FOR THE **
REM ** BACKUP. THIS IS TO ENSURE CROSS VERSION **
REM ** COMPATIBILITY **
REM ***************************************************

set password=manager

REM ***************************************************
REM ** ENTER THE CLIENT'S SMTP (OUTGOING MAIL) **
REM ** SERVER NAME OR IP ADDRESS, **
REM ** EXAMPLE: smtp.is.co.za OR 196.37.124.29 **
REM ** OBTAIN THIS INFORMATION FROM THE PROPERTY'S **
REM ** ADMINISTRATOR **
REM ***************************************************

set smtp_server=mail1.micros.co.za

REM ***************************************************
REM ** ENTER A VALID EMAIL ADDRESS RECOGNIZED BY THE **
REM ** PROPERTY'S MAIL SERVER, EXAMPLE: **
REM ** it@client.co.za OBTAIN THIS **
REM ** INFORMATION FROM THE PROPERTY'S ADMINISTRATOR **
REM ***************************************************

set mail_username=cpretorius

REM ***************************************************
REM ** ENTER THE LIST OF RECIPIENTS WHO WILL RECEIVE **
REM ** THE BACKUP LOG FILE DAILY. SEPARATE THE **
REM ** RECIPIENTS BY A COMMA (,). DO NOT REMOVE **
REM ** Remote DBA@micros.co.za **
REM ** EXAMPLE: Remote DBA@micros.co.za,it@client.co.za **
REM ***************************************************

REM set mail_recipients=Remote DBA@micros.co.za
set mail_recipients=cpretorius@micros.co.za



REM ***************************************************
REM ***************************************************
REM **** DO NOT CHANGE ANYTHING FROM HERE ON !!! ****
REM ***************************************************
REM ***************************************************


set backup_path=%backup_drive%\BACKUP
set log_path=%backup_path%\LOG
MD %backup_path%
MD %log_path%


echo ************************************************** > %log_path%\oracle_hot_backup.log
echo ******** START OF OPERA ONLINE BACKUP ************ >> %log_path%\oracle_hot_backup.log
echo ************************************************** >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log
set bck_ver >> %log_path%\oracle_hot_backup.log
set property >> %log_path%\oracle_hot_backup.log
set oracle_sid >> %log_path%\oracle_hot_backup.log
set backup_drive >> %log_path%\oracle_hot_backup.log
set smtp_server >> %log_path%\oracle_hot_backup.log
set mail_username >> %log_path%\oracle_hot_backup.log
set mail_recipients >> %log_path%\oracle_hot_backup.log
set password >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log

date /t >> %log_path%\oracle_hot_backup.log
time /t >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log

sqlplus system/%password%@opera @%backup_drive%\ONLINE\online_bck.sql %backup_path% %log_path%

echo. >> %log_path%\oracle_hot_backup.log

type %log_path%\backupdb.log >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log

echo ************* BACKUP FOLDER CONTENTS ************* >> %log_path%\oracle_hot_backup.log
echo. >> %log_path%\oracle_hot_backup.log
dir %backup_path% /oe /on >> %log_path%\oracle_hot_backup.log
echo ************************************************** >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log

date /t >> %log_path%\oracle_hot_backup.log
time /t >> %log_path%\oracle_hot_backup.log

echo. >> %log_path%\oracle_hot_backup.log

echo ************************************************** >> %log_path%\oracle_hot_backup.log
echo ******** END OF OPERA ONLINE BACKUP ************** >> %log_path%\oracle_hot_backup.log
echo ************************************************** >> %log_path%\oracle_hot_backup.log


The second one is the online_bck.sql

set heading off
set echo off
set feedback off
set verify off
set pagesize 0
set linesize 300

spool &1\backupdb.sql

select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;
select 'REM -- Cleaning Up...' from dual;
select 'host del /Q &1\*.*' from dual;
select 'REM -- START LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM -- SWICTHING LOGFILES...' from dual;
select 'alter system switch logfile;' from dual;
select 'REM --PERFORMING ONLINE BACKUP OF TABLESPACES...' from dual;
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||'
host copy '||file_name||' &1'||chr(10)||'
alter tablespace '||tablespace_name||' end backup;'
from Remote DBA_data_files
WHERE FILE_NAME NOT LIKE '%SYS_READ%';
select 'alter system switch logfile;' from dual;
select 'REM --FINISH LOG SEQUENCE IS '||to_char(min(sequence#))||'' from v$log;
select 'REM --COPYING REDO LOGS....' from dual;
select 'host copy '||member||' &1' from v$logfile;
select 'REM --COPYING CONTROLFILES ...' from dual;
select 'host copy '||name||' &1' from v$controlfile;
select 'REM --backing up controlfile to trace...' from dual;
alter database backup controlfile to trace;
select 'REM --COPYING ARCHIVE LOGS ...' from dual;
select 'host copy '||destination||'\*.* &1' from v$archive_dest
where status = 'VALID';
select 'REM --DELETING ARCHIVE LOGS ...' from dual;
select 'host del /Q /F '||destination||'\*.*' from v$archive_dest
where status = 'VALID';
select 'REM --'||to_char(sysdate, 'Day, DD/MM/RRRR, HH24:MI:SS') from dual;

spool off

set heading on
set echo on
set feedback on
set verify on

spool &2\backupdb.log

@&1\backupdb.sql

spool off

exit
 

Oracle 11g and Expert Systems Technology

Oracle 11g

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.