How can I perform an automatic HOT or Online Oracle backup without using any backup utility?

By: John Kazerooni

Steps to create an automated Oracle HOT or Online backup to
your hard disk or SAN. 

1- Create a directory that you want your backup files to be

stored.
example:        $cd /u07
                $mkdir hotbackup
                $chown -R oracle:dba hotbackup
                $chmod -R 770 coldbackup

2- Add the following line to the database parameter.
example:        utl_file_dir=/u07/hotbackup

3- Grant select privileges to the user who wants to run this 
backup process directly, instead of using roles. If the owner is SYS
ignore this step.
example:        GRANT SELECT ON v_$datafile TO xxx;
                GRANT SELECT ON v_$logfile TO xxx;
                GRANT SELECT ON v_$controlfile TO xxx;

4- Write a procedure to create a script to perform the actual

HOT or ONLINE backup.

CREATE OR REPLACE PROCEDURE script4hotbackup 
        (p_filepath IN         VARCHAR2,
         p_orahome  IN        VARCHAR2,
         p_sid      IN        VARCHAR2)

IS

        -- Get the tablespace name
        cursor c_tablespace is
                select distinct tablespace_name from dba_data_files;

        -- Get the datafiles
        cursor c_filename (p_tablespace VARCHAR2) is 
                select file_name from dba_data_files
                where tablespace_name = p_tablespace;

        output_file  UTL_FILE.FILE_TYPE;
        v_msg        VARCHAR2(100);
        v_path       VARCHAR2(100);
        v_arch       VARCHAR2(100) := '/u01/app/oracle/admin/<sid>/arch';

BEGIN
        v_path := p_filepath;
        output_file := UTL_FILE.FOPEN(v_path,
                'script4coldbackup.sh','w');

        -- preparation for shutdown database...
        UTL_FILE.PUTF(output_file,'%s \n',
                '#!/bin/sh');
        UTL_FILE.PUTF(output_file,
                'ORACLE_SID=%s \n',p_sid); 
        UTL_FILE.PUTF(output_file,'%s \n',
                'export ORACLE_SID');
        UTL_FILE.PUTF(output_file,
                'ORACLE_HOME=%s \n',p_orahome);     
        UTL_FILE.PUTF(output_file,'%s \n',
                'export ORACLE_SID');
        UTL_FILE.PUTF(output_file,'%s \n',
                '$ORACLE_HOME/bin/sqlplus /nolog << EOF');
        UTL_FILE.PUTF(output_file,'%s \n',
                'CONNECT internal/');
        UTL_FILE.PUTF(output_file,'%s  %s/coldbackup.log \n',
                'spool',v_path);
        UTL_FILE.PUTF(output_file,'%s \n',
                'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');

        -- Process Online backup for each tablespace.
        FOR this IN c_tablespace LOOP
                UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s BEGIN BACKUP;\n',
                                this.tablespace_name);

                -- process the datafiles in each tablespace
                FOR v_datafile IN c_filename (this.tablespace_name) LOOP
                    UTL_FILE.PUTF(output_file,'host cp %s %s;\n',
                                v_datafile.file_name,
                                v_path);
                END LOOP;

                UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s END BACKUP;\n',
                                this.tablespace_name);

        END LOOP;

        -- Process ONLINE backup for the controlfiles 
        UTL_FILE.PUTF(output_file,
                'ALTER DATABASE BACKUP CONTROLFILE TO %s/controlbkup.ctl;\n',
                v_path);        

        -- Swith the online redo log file.
        UTL_FILE.PUTF(output_file,
                'ALTER SYSTEM SWITCH LOGFILE;\n');           

        -- Copy all archive log files...
        UTL_FILE.PUTF(output_file,'host cp  %s/arc*.log %s\n',
                                v_arch,
                                v_path);

        -- Remove all copied archive log files...
        UTL_FILE.PUTF(output_file,'host rm  %s/arc*.log\n',
                                v_arch);

        UTL_FILE.PUTF(output_file,'%s \n',
               'exit');
        UTL_FILE.PUTF(output_file,'%s \n',
                'EOF');

        UTL_FILE.FCLOSE_ALL;
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
     UTL_FILE.PUTF(output_file,'Invalid Path');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.INVALID_MODE THEN
     UTL_FILE.PUTF(output_file,'Invalid Mode');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.INVALID_OPERATION then
     UTL_FILE.PUTF(output_file,'Invalid Operation');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.INVALID_FILEHANDLE then
     UTL_FILE.PUTF(output_file,'Invalid Filehandle');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.WRITE_ERROR then
     UTL_FILE.PUTF(output_file,'Write Error');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.READ_ERROR then
     UTL_FILE.PUTF(output_file,'Read Error');
     UTL_FILE.FCLOSE(output_file);

   WHEN UTL_FILE.INTERNAL_ERROR then
     UTL_FILE.PUTF(output_file,'Internal Error');
     UTL_FILE.FCLOSE(output_file);

   WHEN OTHERS THEN
     UTL_FILE.PUTF(output_file,'others');
     UTL_FILE.FCLOSE(output_file);

END script4hotbackup;
/

5- Write a unix script to create the "script4hotbackup.sh" script and 
then run it in the hotbackup directory. Call it runhotbackup.sh.

#!/bin/sh
ORACLE_SID=
export ORACLE_SID
BACKUP_HOME=/u07/hotbackup
export BACKUP_HOME
ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect INTERNAL/
DECLARE
        v_bckhome       VARCHAR2(100)   := '/u07/hotbackup';
        v_orahome       VARCHAR2(100)   := '/u01/app/oracle/product/8.1.7';
        v_sid           VARCHAR2(100)   := 'your-sid';
BEGIN
         script4hotbackup(v_bckhome,v_orahome,v_sid);
END;
/
exit
EOF

chmod 770 $BACKUP_HOME/script4hotbackup.sh

cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/sqlnet.ora $BACKUP_HOME/.
cp $ORACLE_BASE/admin/trav/pfile/inittrav.ora $BACKUP_HOME/.

$BACKUP_HOME/script4hotbackup.sh

6- Schedule this job to run as an oracle user in the crontab file.