DBA Fundamentals

Automated Oracle HOT or ONLINE backup

$mkdir hotbackup

$chown -R oracle:dba hotbackup

$chmod -R 770 coldbackup

GRANT SELECT ON v_$logfile TO xxx;

GRANT SELECT ON v_$controlfile TO xxx;

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;

/

#!/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