everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 39

Next >>


 

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.

 

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

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.