BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 
 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter