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

iSelfSchooling.com Since 1999 References | Search more | Oracle Syntax | Free Online Oracle Training

Home .Services Login Start Learning Certification . .Share your BELIEF(s)...

. Online Accounting     .Copyright & User Agreement   |

    .Vision      .Biography     .Acknowledgement
.Contact Us      .Comments/Suggestions       Email2aFriend    |

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Lesson 26

"Life is what we make it, always has been, always will be."

-Grandma Moses (1860-1961)

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

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)

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