Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Example Hot Backup Script on Unix

Oracle Tips by Burleson Consulting

SOURCE 1    Example of script to generate a hot backup script on UNIX.

Note:  DO NOT run this hot backup script unless you have fully tested it in a development environment.  For a complete listing of Oracle hot backup scripts, see the Oracle script download.


REM Script to create a hot backup script on UNIX
REM Created 6/23/98 MRA
REM
create table bu_temp (line_no number,line_txt varchar2(2000))
storage (initial 1m next 1m pctincrease 0);
truncate table bu_temp;
set verify off embedded off
define dest_dir=&1;
declare
--
-- Declare cursors
--
-- Cursor to get all tablespace names
--
cursor get_tbsp is
select tablespace_name from dba_tablespaces;
--
-- cursor to create BEGIN BACKUP command
--
cursor bbu_com (tbsp varchar2) is
SEE CODE DEPOT FOR FULL HOT BACKUP SCRIPTS
select
'alter tablespace '||tablespace_name||' begin backup;'
from dba_tablespaces where tablespace_name=tbsp;
--
-- Cursor to create HOST backup commands
--
cursor tar1_com (tbsp varchar2) is
select '/bin/tar cvf - '||file_name||' \'
from dba_data_files where tablespace_name=tbsp
and file_id=(select min(file_id)from dba_data_files
where tablespace_name=tbsp);
--
cursor tar2_com (tbsp varchar2) is
select
file_name||' \'
from dba_data_files where tablespace_name=tbsp
and file_id>(select min(file_id) from dba_data_files
where tablespace_name=tbsp);
--
cursor tar3_com (tbsp varchar2) is
select '/bin/tar cvrf - '||file_name||' \'
from dba_data_files where tablespace_name=tbsp
and file_id=(select min(file_id)from dba_data_files
where tablespace_name=tbsp);
--
cursor comp_com (tbsp varchar2) is
select
'|compress>&&dest_dir/'||tablespace_name||'_'||to_char(sysdate,'dd_mon_yy')||'.Z'||chr(10)||'exit'
from dba_tablespaces where tablespace_name=tbsp;
 
--
-- Cursor to create END BACKUP command
--
cursor ebu_com (tbsp varchar2) is
select
'alter tablespace '||tablespace_name||' end backup;' from
dba_tablespaces
where tablespace_name=tbsp;
--
-- Cursor to create redo log HOST backup commands
--
cursor tar1_rdo is
select '/bin/tar cvrf -  \'
from dual;
--
cursor tar2_rdo is
select
member||' \'
from v$logfile;
--
cursor comp_rdo is
select
'|compress>&&dest_dir/redo_logs_'||to_char(sysdate,'dd_mon_yy')||'.Z'||chr(10)||'exit'
from dual;
--
-- Temporary variable declarations
--
tbsp_name varchar2(64);
line_num number:=0;
line_text varchar2(2000);
min_value number;
first_tbsp boolean;
--
-- Begin build of commands into temporary table
--
begin
--
-- first, create script header
--
line_num := line_num+1;
select 'REM Online Backup Script for '||name||' instance'
into line_text from v$database;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script uses UNIX tar format backup commands' 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM created on '||to_char(sysdate, 'dd-mon-yyyy hh24:mi')||' by user '||user 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM developed for RevealNet by Mike Ault - DMR Consulting Group 7-Oct-1998' 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script expects to be fed backup directory location on execution.' 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM Script should be re-run anytime physical structure of database altered.' 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
select 'REM ' 
into line_text from dual;
insert into bu_temp values (line_num,line_text);
line_num := line_num+1;
--
-- Now get tablespace names and loop through until all are handled
--
open get_tbsp;
first_tbsp:=TRUE;
loop
--
-- Get name
--
      fetch get_tbsp into tbsp_name;
      exit when get_tbsp%NOTFOUND;
--
-- Add comments to script showing which tablespace
--
      select 'REM' into line_text from dual;
      insert into bu_temp values (line_num,line_text);
      line_num:=line_num+1;
      select 'REM Backup for tablespace '||tbsp_name into line_text from dual;
      insert into bu_temp values (line_num,line_text);
      line_num:=line_num+1;
      select 'REM' into line_text from dual;
      insert into bu_temp values (line_num,line_text);
      line_num:=line_num+1;
--
-- Get begin backup command built for this tablespace
--
      open bbu_com (tbsp_name);
      fetch bbu_com into line_text;
      insert into bu_temp values (line_num,line_text);
      line_num:=line_num+1;
      close bbu_com;
--
-- The actual backup commands are per datafile, open cursor and loop
--
      open tar1_com (tbsp_name);
      open tar2_com (tbsp_name);
open tar3_com (tbsp_name);
      open comp_com (tbsp_name);
      min_value:=line_num;
      loop
            if line_num=min_value
            then
                        select 'host' into line_text from dual;
                        insert into bu_temp values (line_num,line_text);
                        line_num:=line_num+1;
                  if first_tbsp THEN
                  fetch tar1_com into line_text;
                else
                  fetch tar3_com into line_text;
                end if;
            else
                  fetch tar2_com into line_text;
                        exit when tar2_com%NOTFOUND;
 SEE CODE DEPOT FOR FULL HOT BACKUP SCRIPTS
            insert into bu_temp values (line_num,line_text);
            line_num:=line_num+1;
           first_tbsp:=FALSE;
      end loop;
      fetch comp_com into line_text;
      insert into bu_temp values (line_num,line_text);
    line_num:=line_num+1;
      close tar1_com;
      close tar2_com;
close tar3_com;
      close comp_com;
--
-- Build end backup command for this tablespace
--
  open ebu_com(tbsp_name);
  fetch ebu_com into line_text;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  close ebu_com;
end loop;
  close get_tbsp;
--
-- Backup redo logs, normally you won't recover redo logs you
-- will use your current redo logs so current SCN information not lost
-- commands just here for completeness
--
  select 'REM' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM Backup for redo logs' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM Normally you will not recover redo logs' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
--
-- Create host backup commands for all redo logs
--
  open tar1_rdo;
  open tar2_rdo;
  open comp_rdo;
  min_value:=line_num;
  loop
      if line_num=min_value
      then
       select 'host' into line_text from dual;
       insert into bu_temp values (line_num,line_text);
       line_num:=line_num+1;
       fetch tar1_rdo into line_text;
      else
       fetch tar2_rdo into line_text;
       exit when tar2_rdo%NOTFOUND;
      end if;
      insert into bu_temp values (line_num,line_text);
      line_num:=line_num+1;
  end loop;
  fetch comp_rdo into line_text;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  close tar1_rdo;
  close tar2_rdo;
  close comp_rdo;
--
-- Now get all archive logs, performing a switch to be sure all
-- required archives are written out
--
  select 'REM' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM Backup for archive logs' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'REM' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'alter system switch logfile;' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'archive log all;' into line_text from dual;
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
--
-- The next command builds the actual backup command based on the
-- value of the log_archive_dest initialization parameter, it looks for the
-- last right square bracket in the name and just uses that section with
-- a wildcard
--
  select 'host compress '||substr (value,1,instr(value,'/',-1,1))||'*'
  into line_text from v$parameter where name='log_archive_dest';
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
  select 'host tar cvrf - '||substr (value,1,instr(value,'/',-1,1))||'*.Z'||
  '|compress>&&dest_dir/'||
  substr (value,instr(value,'/',-1,1)+1,length(value))||'_'||to_char(sysdate,'dd_mon_yy')||'.Z'
  into line_text from v$parameter where name='log_archive_dest';
  insert into bu_temp values (line_num,line_text);
  line_num:=line_num+1;
--
-- Next, backup a control file just to be sure
-- we have a good one available that is current with this backup
--
   select 'alter database backup control file to
'||'&&dest_dir'||'/ora_conbackup_'||to_char(sysdate,'dd_mon_yy')||'.bac;' 
   into line_text from dual;
   insert into bu_temp values (line_num,line_text);
   line_num:=line_num+1;
end;
/
rem
rem Now generate output based on bu_temp table contents
rem
set verify off feedback off heading off termout off pages 0
set embedded on lines 132
column line_no noprint
column dbname new_value db noprint
select name dbname from v$database;
spool rep_out\&db\thot_bu.sql
select * from bu_temp order by line_no;
spool off
rem directory syntax for UNIX
rem
rem host  sed '1,$ s/ *$//g' thot_bu.sql>hot_bu.sql
rem
drop table bu_temp;
set verify on feedback on heading on termout on pages 22
set embedded off lines 80
clear columns
undef dest_dir


The output from the script for my test database is shown in Source 2.


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.