Oracle provides two approaches to this type of backup, RMAN and "not RMAN." I am going to deal with "not RMAN" only, as is meets the backup and recovery requirements that most environments have, without the management, complexity, and learning curve of RMAN. (Please note that RMAN does provide additional features, but I have seldom seen those features be of value for most organizations.) The procedure used in this tip is based on examples of hot backups I have seen at various companies and floating around on the Internet. So if it looks suspiciously familiar, so be it. My goal was to put together a script that did the job, made sense to me, was easy for others to understand and implement, and was as portable as possible between Windows and Unix.

This procedure consists of two files. The first is Windows command file or Unix shell script that does little more than execute the second file, a SQL script. The first file, using the Windows example, is

REM hot_backup.bat
REM
REM Parameters into SQL*Plus command
REM 1 = U = Unix, W = Windows
REM 2 = hot backup directory. Destination for backed up files.
REM     Must exist and contain an archive subdirectory. Include ending slash.
REM 3 = Output directory. Destination for output files from script. Must exist. Include ending slash.
REM
SET oracle_sid=alydan
SQLPLUS "/ as sysdba" @"C:\oracle\scripts\hot_backup.sql" W C:\oracle\backup\ C:\oracle\backup\out\
EXIT

The most important part of this script is the parameters, since there is not much else there. The parameters are

• W or U to indicate if the platform is Windows or Unix. This determines the command used to delete files from the prior backup, to copy files for the new backup, and which slash to use in file paths. Under Windows, ocopy is used to to copy the database files. This is an Oracle supplied utility that allows open files to be backed up under Windows.
• The directory, in this example c:\oracle\backup\, where the backup will be stored. A subdirectory, archive, must exist under this directory.
• The directory, in this example c:\oracle\backup\out\, where the procedure will store a generated script to do the actual backup and a log file from the backup.

The second file is the SQL script. It will run unchanged between Windows and Unix.

-- hot_backup.sql
--
-- Parameters
-- &1 = U = Unix, W = Windows
-- &2 = hot backup directory. Destination for backed up files.
--      Must exist and contain an archive subdirectory. Include ending slash.
-- &3 = Output directory. Destination for output files from script. Must exist. Include ending slash.
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 200
SET PAGES 0
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
--
SPOOL &3.temp_hot_backup.sql
--
DECLARE
   k_out_dest      CONSTANT VARCHAR2(100) := '&3';    -- Destination for generated output files.
   k_platform      CONSTANT VARCHAR2(1) := '&1';      -- U for Unix, W for Windows
   l_copy_cmd      VARCHAR2(5);                       -- Copy command
   l_copy_dest     VARCHAR2(100) := '&2';             -- Destination for backed up files.
   l_destination   v$archive_dest.destination%TYPE;   -- Destination from which archives are backed up
   l_log_mode      v$database.log_mode%TYPE;          -- Check to see if database is in archivelog mode
BEGIN
--
-- Get database info
   SELECT log_mode
   INTO   l_log_mode
   FROM   v_$database;
--
-- Check if database is in archivelog mode
   IF l_log_mode <> 'ARCHIVELOG' THEN
      RAISE_APPLICATION_ERROR (-20000, 'ERROR: Database must be in ARCHIVELOG mode');
   END IF;
--
-- Write spool command to generated script
   DBMS_OUTPUT.PUT_LINE ('SPOOL ' || k_out_dest || 'hot_backup.log');
--
-- Build commands to remove files from prior hot backup
   IF k_platform = 'W' THEN                        -- Windows
      DBMS_OUTPUT.PUT_LINE ('HOST del /q ' || l_copy_dest || '*.*');
      DBMS_OUTPUT.PUT_LINE ('HOST del /q ' || l_copy_dest || 'archive\*.*');
      l_copy_cmd := 'ocopy';
   ELSE                                            -- Unix
      DBMS_OUTPUT.PUT_LINE ('HOST rm -f ' || l_copy_dest || '*.*');
      DBMS_OUTPUT.PUT_LINE ('HOST rm -f ' || l_copy_dest || 'archive/*.*');
      l_copy_cmd := 'cp';
   END IF;
--
-- Loop through tablespaces and build backup commands. Do not backup temporary tablespaces.
   FOR r_tablespace IN
      (SELECT tablespace_name
      FROM    dba_tablespaces
      WHERE   contents <> 'TEMPORARY'
      ORDER BY tablespace_name) LOOP
      DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE ' || r_tablespace.tablespace_name || ' BEGIN BACKUP;');
--
-- Loop through tablespace data files to build copy commands
      FOR r_datafile IN
         (SELECT file_name
         FROM    dba_data_files
         WHERE   tablespace_name = r_tablespace.tablespace_name) LOOP
         DBMS_OUTPUT.PUT_LINE ('HOST ' || l_copy_cmd || ' ' || r_datafile.file_name || ' ' ||
            l_copy_dest);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE ' || r_tablespace.tablespace_name || ' END BACKUP;');
   END LOOP;
--
-- Build commands to backup the control file and switch logfiles.
-- Switching the log file creates the final archive log needed for recovery with this backup.
   DBMS_OUTPUT.PUT_LINE ('ALTER SYSTEM SWITCH LOGFILE;');
   DBMS_OUTPUT.PUT_LINE ('ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
   DBMS_OUTPUT.PUT_LINE ('ALTER DATABASE BACKUP CONTROLFILE TO ' || '''' || l_copy_dest ||
      'control_backup.ctl'';');
--
-- Get a valid archive destination from which archives can be backed up
   SELECT destination
   INTO   l_destination
   FROM   v$archive_dest
   WHERE  status = 'VALID'
   AND    binding = 'MANDATORY'
   AND    ROWNUM < 2;
--
-- Add end slash to since the archive destination does not include it. Figure out if Windows or Unix.
   IF k_platform = 'W' THEN                        -- Windows
      l_destination := l_destination || '\';
      l_copy_dest := l_copy_dest || 'archive\';
   ELSE                                            -- Unix
      l_destination := l_destination || '/';
      l_copy_dest := l_copy_dest || 'archive/';
   END IF;
--
-- Backup archives
   DBMS_OUTPUT.PUT_LINE ('HOST ' || l_copy_cmd || ' ' ||  l_destination || '*.* ' || l_copy_dest);
--
-- Write spool off command to generated script
   DBMS_OUTPUT.PUT_LINE ('SPOOL OFF');
END;
/
SPOOL OFF
SET ECHO ON
SET FEEDBACK ON
--
-- Comment out the following line to create the backup script without running it
@&3.temp_hot_backup.sql
EXIT

I think the above script is documented well enough that I don't need to say too much more about it. It generates a backup script named temp_hot_backup.sql that is then executed. The generated script looks like this.

SPOOL C:\oracle\backup\out\hot_backup.log

HOST del /q C:\oracle\backup\*.*

HOST del /q C:\oracle\backup\archive\*.*

ALTER TABLESPACE CWMLITE BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\CWMLITE01.DBF C:\oracle\backup\

ALTER TABLESPACE CWMLITE END BACKUP;

ALTER TABLESPACE DRSYS BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\DRSYS01.DBF C:\oracle\backup\

ALTER TABLESPACE DRSYS END BACKUP;

ALTER TABLESPACE EXAMPLE BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\EXAMPLE01.DBF C:\oracle\backup\

ALTER TABLESPACE EXAMPLE END BACKUP;

ALTER TABLESPACE INDX BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\INDX01.DBF C:\oracle\backup\

ALTER TABLESPACE INDX END BACKUP;

ALTER TABLESPACE ODM BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\ODM01.DBF C:\oracle\backup\

ALTER TABLESPACE ODM END BACKUP;

ALTER TABLESPACE SYSTEM BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\SYSTEM01.DBF C:\oracle\backup\

ALTER TABLESPACE SYSTEM END BACKUP;

ALTER TABLESPACE TOOLS BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\TOOLS01.DBF C:\oracle\backup\

ALTER TABLESPACE TOOLS END BACKUP;

ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\UNDOTBS01.DBF C:\oracle\backup\

ALTER TABLESPACE UNDOTBS1 END BACKUP;

ALTER TABLESPACE USERS BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\USERS01.DBF C:\oracle\backup\

ALTER TABLESPACE USERS END BACKUP;

ALTER TABLESPACE XDB BEGIN BACKUP;

HOST ocopy C:\ORACLE\ORADATA\ALYDAN\XDB01.DBF C:\oracle\backup\

ALTER TABLESPACE XDB END BACKUP;

ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

ALTER DATABASE BACKUP CONTROLFILE TO 'C:\oracle\backup\control_backup.ctl';

HOST ocopy c:\oracle\oradata\alydan\archive\*.* C:\oracle\backup\archive\

SPOOL OFF

I am not going to include the log file, but it is the SQL*Plus feedback of each of the above operations.

Next week, a simple recovery.

Note: This tip was tested using Oracle9i on Windows.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.

2000 Turnberry Circle, Glenmoore, PA 19343
Voice: (610) 942-1979
Fax: (610) 942-1990
Email

© 2006 Alydan Consulting, Inc.