iSelfSchooling - Automated Oracle COLD or OFFLINE backup

“Sometimes people carry to such perfection the mask they have assumed that in due course they actually become the person they seem.” W. Somerset Maugham (1874 - 1965), The Moon and Sixpence

Automated Oracle COLD or OFFLINE backup

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

Create a folder

1- Create a directory that you want your coldbackup files to be stored.

Example: $cd /u07

$mkdir coldbackup

$chmod -R 770 coldbackup

UTL_FILE_DIR parameter

2- Add the following line to the database parameter. This parameter gives permission to Oracle to write or read from defined directory.

Example: utl_file_dir=/u07/coldbackup

Grant Privileges

3- Grant select privileges to the user who wants to run this backup process directly, instead of using roles. Notice that 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;

Write a procedure

4- Write a stored procedure that will create a UNIX script, and then performs 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;

/

Write a Script

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

Schedule to run COLD Backup

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

“Life is full of misery, loneliness, and suffering - and it's all over much too soon.” Woody Allen (1935 - )

Questions:

Q: What are the steps to perform an automated Oracle COLD backup?

Q: What does the UTL_FILE_DIR parameter?

Q: What does the following SQL statements do?

GRANT SELECT ON v_$datafile TO xxx;

GRANT SELECT ON v_$logfile TO xxx;

GRANT SELECT ON v_$controlfile TO xxx;

Q: Write a stored procedure that will create a UNIX script, and then performs your COLD backup.

Q: Write a UNIX or DOS script to create the "script4coldbackup.sh" script and then run it in the coldbackup directory. Call it RunColdBackup.sh.