• Login
• Become a member
• RSS
• Part of the TechTarget network

This script (which obviously must be run by a DBA) automates hot backups. It assumes an underlying UNIX system, and the use of the 'cp' command to backup the files to a special backup directory the name of which is prompted for in the script. The script can easily be modified to take other assumptions into account. It is distributed in the hope that it will be useful for other database administrators performing their extremely imporatant backup and recovery tasks.

set linesize 600
set pause off
set pagesize 0
set echo off
set feedback off
--
--  Hot-backup requires our running in ARCHIVELOG mode.
--  This is tested from the outset. 
--
whenever sqlerror exit sql.sqlcode
declare
   dummy char(1);
begin
   select 'x'
   into dummy
   from v$database
   where log_mode = 'ARCHIVELOG';
exception
   when no_data_found then
      raise_application_error(-20000, 'Database not in ARCHIVELOG mode !');
end;
/
set scan off
--
--   Interactively input the name of the directory (or device)
--   where to backup. This kind of dialogue is likely to prove
--   a hindrance in automated, daily operations.
--   You can either hard-code the directory name by using
--      define backup_dir = ...
--   or suppose it is passed as an argument to the current
--   script, in which case you should replace &&backup_dir by
--   &1 everywhere in this script and comment the following
--   'accept' command
--
accept backup_dir prompt 'Backup directory : '
--
set recsep off
set scan on
set verify

 off
--
--   You must absolutely check that everything goes according
--   to plans! If the backup fails because of, say, a
--   'file system full' error, the script must exit with a
--   non-0 return code.
--
whenever sqlerror exit sql.sqlcode
whenever oserror exit 99
--
--    To be certain we have a consistent set of files which
--    we shall be able to recover, we also want to backup
--    all the redo log files generated during the backup,
--    and the final state of the control file.
--    Note that when recovering you normally must use the
--    current (i.e. at crash time) control file. However,
--    we want to be ready for the worst case scenario and
--    be able to use the current backup as if it were a
--    cold backup.
--    In order to know which redo log files have been generated
--    during the backup, we first identify which is, before
--    backup, the (current - 1) redo log file.
--
variable last_log varchar2(255)
begin
  --
  --   Format %S and %T (left-padded with zeros)
  --   are not supported because length is OS-dependent.
  --   Easy to support for a specific machine
  --
  select replace(pd.value, '?', '$ORACLE_HOME') ||
      replace(replace(pf.value, '%s', rtrim(to_char(l.sequence#))),
                  '%t', rtrim(to_char(l.thread#)))
  into :last_log
  from v$parameter pd,
       v$parameter pf,
       v$log l
  where pd.name = 'log_archive_dest'
    and pf.name = 'log_archive_format'
    and l.sequence# = (select max(sequence#)
                       from v$log
                       where status != 'CURRENT');
end;
/
--
--    Generate the backup script
--
--    online$ = 3 means a tablespace which once
--    existed but was dropped.
--
column dummy noprint
spool do_hbu.sql
select ts# dummy,
       1 dummy,
       'alter tablespace ' || name || ' begin backup;'
from sys.ts$
where online$ != 3
union
select f.ts#,
       2,
       'host cp ' || d.name || ' &&backup_dir'
from sys.file$ f,
     v$datafile d
where f.file# = d.file#
union
select ts#,
       3,
       'alter tablespace ' || name || ' end backup;'
from sys.ts$
where online$ != 3
order by 1, 2
/
spool off
--
--   Run the backup script just generated
--
set echo on
set feedback on
@do_hbu
--
--   Take a backup of the control file for the worst-case scenario
--   Do not worry, parameter replacement is not echoed but done.
--
alter database backup controlfile to '&&backup_dir./control.ctl' reuse;
--
--   Trigger a redo log switch to close the current redo log file
--
alter system switch logfile;
set echo off
--
--   Force the archival of all the redo log files
--
declare
  Stmt                  varchar2(100);
  cid                   number;
  dummy                 number;
  nothing_to_archive    exception;
  PRAGMA EXCEPTION_INIT(nothing_to_archive, -271);
begin
  Stmt := 'alter system archive log all';
  cid := dbms_sql.open_cursor;
  begin
    dbms_sql.parse(cid, Stmt, dbms_sql.native);
    dummy := dbms_sql.execute(cid);
  exception
    when nothing_to_archive then
          null;
    --
    --   Unexpected errors
    --
    when others then
        if (dbms_sql.is_open(cid))
        then
          dbms_sql.close_cursor(cid);
        end if;
        raise_application_error(-20000, Stmt || chr(10) || SQLERRM);
  end;
  dbms_sql.close_cursor(cid);
end;
/   
set feedback off
--
--   Backup all the redo log files generated during  hot backup
--
spool backup_log.sql
select 'host find '
       || replace(substr(p.value, 1, instr(p.value, '/', -1) - 1),
                  '?', '$ORACLE_HOME')
       || ' -type f -newer ' || :last_log || ' -exec cp {} &&backup_dir ;'
from v$parameter p
where p.name = 'log_archive_dest'
/
spool off
set echo on
@backup_log
--
-- That's all folks.
--
exit 0

--------------------------------------

rem   Must be run by a DBA.
rem
rem   IT IS EXTREMELY IMPORTANT TO RUN THIS SCRIPT AS SOON AS POSSIBLE
rem   IN THE EVENT OF FAILURE OF THE hotbackup.sql SCRIPT submitted above.
rem
--
--  CANCELLING IN-BACKUP STATE ...
--
set linesize 100
set pause off
set pagesize 0
set echo off
set feedback off
set recsep off
set scan off
spool cancel_hbu.sql
select 'alter tablespace ' || ts.name || ' end backup;'
from sys.ts$ ts
where ts.ts# in (select f.ts#
                 from sys.file$ f,
                      v$backup b
                 where f.file# = b.file#
                   and b.status = 'ACTIVE');
spool off
set echo on
set feedback on
@cancel_hbu
exit

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• The Best Backup and Recovery Web Links
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our new Ask the Experts feature: Our Oracle gurus are waiting to answer your toughest questions.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.