iSelfSchooling - Automated Oracle HOT or ONLINE backup

“Now, I say to you today my friends, even though we face the difficulties of today and tomorrow, I still have a dream. It is a dream deeply rooted in the American dream. I have a dream that one day this nation will rise up and live out the true meaning of its creed: - 'We hold these truths to be self-evident, that all men are created equal.' “ Martin Luther King Jr. (1929 - 1968), Speech at Civil Rights March on Washington, August 28, 1963

Automated Oracle HOT or ONLINE backup

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

Create a folder

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

UTL_FILE_DIR parameter

2- Add the following line to the database parameter.

Example: utl_file_dir=/u07/hotbackup

Grant Privileges

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;

Write a procedure

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

'#!/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;');

-- Process Online backup for each tablespace.

FOR this IN c_tablespace LOOP

UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s BEGIN BACKUP;',

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

v_datafile.file_name,

v_path);

END LOOP;

UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s END BACKUP;',

this.tablespace_name);

END LOOP;

-- Process ONLINE backup for the controlfiles

UTL_FILE.PUTF(output_file,

'ALTER DATABASE BACKUP CONTROLFILE TO %s/controlbkup.ctl;',

v_path);

-- Swith the online redo log file.

UTL_FILE.PUTF(output_file,

'ALTER SYSTEM SWITCH LOGFILE;');

-- Copy all archive log files...

UTL_FILE.PUTF(output_file,'host cp %s/arc*.log %s',

v_arch,

v_path);

-- Remove all copied archive log files...

UTL_FILE.PUTF(output_file,'host rm %s/arc*.log',

v_arch);

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 script4hotbackup;

/

Write a Script

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

Schedule to run the HOT backup

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

“A strong positive mental attitude will create more miracles than any wonder drug.” Patricia Neal

Questions:

Q: What are the steps to create an automated Oracle HOT backup?

Q: Write a procedure to create a script to perform the actual HOT or ONLINE backup.

Q: Write a UNIX script to create the "script4hotbackup.sh" script and then run it in the hotbackup directory. Call it RunHotBackup.sh.