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.

$mkdir coldbackup

$chmod -R 770 coldbackup

2- Add the following line to the database parameter.

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.

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 ',

'#!/bin/sh');

UTL_FILE.PUTF(output_file,

'ORACLE_SID=%s ',p_sid);

UTL_FILE.PUTF(output_file,'%s ',

'export ORACLE_SID');

UTL_FILE.PUTF(output_file,

'ORACLE_HOME=%s ',p_orahome);

UTL_FILE.PUTF(output_file,'%s ',

'export ORACLE_SID');

UTL_FILE.PUTF(output_file,'%s ',

'$ORACLE_HOME/bin/sqlplus /nolog << EOF');

UTL_FILE.PUTF(output_file,'%s ',

'CONNECT internal/');

UTL_FILE.PUTF(output_file,'%s %s/coldbackup.log ',

'spool',v_path);

UTL_FILE.PUTF(output_file,'%s ',

'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');

UTL_FILE.PUTF(output_file,'%s ',

'SHUTDOWN IMMEDIATE;');

-- process the datafiles

FOR this IN c_filename LOOP

UTL_FILE.PUTF(output_file,'host cp %s %s',

this.name,

v_path);

END LOOP;

-- process the controlfiles

FOR this IN c_controlfile LOOP

UTL_FILE.PUTF(output_file,'host cp %s %s',

this.name,

v_path);

END LOOP;

-- process the logfiles

FOR this IN c_logfile LOOP

UTL_FILE.PUTF(output_file,'host cp %s %s',

this.member,

v_path);

END LOOP;

-- preparation for startup the database...

UTL_FILE.PUTF(output_file,'%s ',

'CONNECT internal/');

UTL_FILE.PUTF(output_file,'%s ',

'STARTUP;');

UTL_FILE.PUTF(output_file,'%s ',

'exit');

UTL_FILE.PUTF(output_file,'%s ',

'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.