Try this simple hot backup script

By Kenny Smith 15 Dec 2003 | IOUG

Digg This!     StumbleUpon     Del.icio.us

The following is backup and recovery tip #2 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.

I've seen hot backup scripts a plenty. Most of them are quite involved. Maybe you'd like a simple one that you can build upon. Heck, you can even use this script just like this. These commands, run from SQL*Plus, use PL/SQL to create a hot backup script from the data dictionary.

set feedback off pagesize 0 heading off verify off
set linesize 100 trimspool on
Rem Set SQL*Plus user variables used in script 
define dir = '/backup'
define fil = '/tmp/open_backup_commands.sql'
define spo = '&dir/open_backup_output.lst'
prompt *** Spooling to &fil
set serveroutput on
spool &fil
prompt spool &spo
prompt alter system switch logfile;;
DECLARE
 CURSOR cur_tablespace IS
  SELECT tablespace_name
    FROM dba_tablespaces;
 CURSOR cur_datafile (tn VARCHAR) IS
  SELECT file_name
    FROM dba_data_files
   WHERE tablespace_name = tn;
BEGIN
  FOR ct IN cur_tablespace LOOP
    dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
                          ' begin backup;');
    FOR cd IN cur_datafile (ct.tablespace_name) LOOP
   dbms_output.put_line ('host cp '||cd.file_name||' &dir');
    END LOOP;
    dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
                          ' end backup;');
  END LOOP;
END;
/
prompt alter system switch logfile;;
prompt spool off
spool off;
Rem Run the copy file commands from the operating system
@&fil

On Linux, connect to SQL*Plus as SYSDBA and run this script like this:

LINUX> sqlplus /nolog
SQL> connect system/manager as SYSDBA
SQL> @open_backup.sql

For more information see Chapter 5 of "Oracle backup and recovery 101."

Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.

Tags: Hot backups,  VIEW ALL TAGS

Digg This!     StumbleUpon     Del.icio.us