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;
-- Define variables
output_file
UTL_FILE.FILE_TYPE;
v_msg VARCHAR2(100);
v_path VARCHAR2(100);
BEGIN
-- set the variables
v_path := p_filepath;
output_file :=
UTL_FILE.FOPEN(v_path,
'script4coldbackup.sh','w');
-- preparation for shutdown
database...Your are write a script to backup
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;
/