User managed automatic backup script

What is user managed online backup:

• Online backup is done by keeping the tablespace in backup mode and coping the respective datafiles to the backup directory and again making the tablespace end backup.

• For automating this process of user managed online backup a script should be run in the database level which will automatically does the above process.

• Keep the below script in a .sql file and execute it in the database level i.e.

SQL>@.sql
Assuming the following:

Base directory – ‘/d01/mt’
Backup directory – ‘/d01/mt/backup’

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 = '/d01/mt/backup'
define fil = '/d01/mt/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;

• After executing the above script a file with extension .sql will be created in the OS level. The name of the file will be given by the ‘define fil’ variable in the first file.

• The file name will be ‘open_backup_commands.sql'. This file consists of commands for keeping all the tablespaces in begin backup mode and copy the corresponding datafile to the backup directory in OS level and again keeping the tablespaces to end backup.

• Now execute the above script ‘open_backup_commands.sql' at database level
i.e. @open_backup_commands.sql

Here the above script will automatically does the following

• keeping the tablespaces in begin backup mode
• Coping the corresponding datafile to backup directory
• keeping the tablespaces in end backup mode

This is the script for automatic user managed online backup