How can I perform an automatic COLD or OFFLINE Oracle backup without using any backup utility?

By: John Kazerooni

Steps to perform an automated Oracle COLD backup to your disk or SAN. 

1- Create a directory that you want your coldbackup files to be
stored.
Example:        $cd /u07
                $mkdir coldbackup
                $chmod -R 770 coldbackup

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

3- Grant select privileges to the user who wants to run this 

backup process directly, instead of using roles. If the onwer
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 stored procedure, that will create a UNIX script, that
then, perform your COLD backup.

CREATE OR REPLACE PROCEDURE script4coldbackup 
        (p_filepath IN         VARCHAR2,
         p_orahome  IN        VARCHAR2,
         p_sid      IN        VARCHAR2)
IS
        -- Get the datafiles
        cursor c_filename is 
                select name from v$datafile;

        -- Get the controlfiles
        cursor c_controlfile is 
                select name from v$controlfile;

        -- Get the logfiles
        cursor c_logfile is 
                select member from v$logfile;

        output_file        UTL_FILE.FILE_TYPE;
        v_msg               VARCHAR2(100);
        v_path              VARCHAR2(100);

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;');

        UTL_FILE.PUTF(output_file,'%s \n',
                'SHUTDOWN IMMEDIATE;');

        -- process the datafiles
        FOR this IN c_filename LOOP
                UTL_FILE.PUTF(output_file,'host cp  %s  %s\n',
                                this.name,
                                v_path);
        END LOOP;

        -- process the controlfiles
        FOR this IN c_controlfile LOOP
                UTL_FILE.PUTF(output_file,'host cp  %s  %s\n',
                                this.name,
                                v_path);

        END LOOP;               

        -- process the logfiles
        FOR this IN c_logfile LOOP
                UTL_FILE.PUTF(output_file,'host cp  %s  %s\n',
                                this.member,
                                v_path);
        END LOOP;               

        -- preparation for startup the database...
        UTL_FILE.PUTF(output_file,'%s \n',
                'CONNECT internal/');

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

        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 script4coldbackup;
/

5- Write a unix script to create the "script4coldbackup.sh" script and 
then run it in the coldbackup directory. Call it runcoldbackup.sh.

#!/bin/sh
ORACLE_SID=trav
export ORACLE_SID
BACKUP_HOME=/u07/coldbackup
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/coldbackup';
        v_orahome       VARCHAR2(100)   := '/u01/app/oracle/product/8.1.7';
        v_sid           VARCHAR2(100)   := 'trav';
BEGIN
        script4coldbackup(v_bckhome,v_orahome,v_sid);
END;
/
exit
EOF

chmod 770 $BACKUP_HOME/script4coldbackup.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/script4coldbackup.sh

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