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