stored procedure to make compressed cold backup
One thing you can do is instead of just an ordinary copy, you can use PKZIP to compress as it copies.
I am enclosing the source code from a stored procedure I wrote which will build a batch file to do this.
I am not promising that this will work in your environment, so please inspect it carefully before you run it. You will probably need to change a few things. Running the stored procedure does not perform the backup, instead it spools a batch file via DBMS_OUTPUT which will actully perform the backup.
You will need to get a copy of pkzip25 from http://www.pkware.com to use this.
CREATE OR REPLACE
Procedure create_cold_backup
(in_source_dir varchar2,
in_dest_dir varchar2,
in_start_stop_db boolean) as
-- Author : John Dorlon
-- Date : 8/17/2000
-- Comments :
-- It is outside the scope of this procedure to copy the
-- INIT.ORA file and files in the NETWORK\ADMIN directory.
-- Be sure not to leave them out of your backup routine.
-- if in_start_stop_db is true:
-- 1) The output from this stored procedure will :
-- a) stop the database
-- b) zip and copy the database files in parallel
-- to the in_dest_dir directory
-- c) start the database
-- 2) The actual datafiles are copied and in_source_dir
-- is not used.
-- if in_start_stop_db is false
-- 1) The output from this stored procedure will zip
-- and copy the database files in parallel from
-- the in_source_dir directory to the in_dest_dir
-- directory
-- 2) The procedure assumes that you are using copies of
-- the database files which already reside in the
-- in_source_dir directory.
-- assumes 3 character file extensions!
cursor c_copy_files(source_dir varchar2, dest_dir varchar2) is
select 'del ' || dest_dir || f.no_ext || '.zip' del,
'start /min /low pkzip25 -add -lev=1 ' || dest_dir || f.no_ext || '.zip ' || source_dir || f.file_name copy_zip,
'start /min /low pkzip25 -add -lev=1 ' || dest_dir || f.no_ext || '.zip ' || f.full_path db_zip,
'rem start /min /low pkzip25 -extract ' || dest_dir || f.no_ext || '.zip' extract_cmd
from (select file_name full_path,
substr(file_name, lastchar(file_name, '\')+1) file_name,
substr(substr(file_name, lastchar(file_name, '\')+1), 1, length(substr(file_name, lastchar(file_name, '\')+1)) -4) no_ext,
bytes file_size
from sys.dba_data_files
union
select name full_path,
substr(name, lastchar(name, '\')+1),
substr(substr(name, lastchar(name, '\')+1), 1, length(substr(name, lastchar(name, '\')+1)) -4) no_ext,
1 file_size
from sys.v_$controlfile
union
Select MEMBER full_path,
substr(MEMBER, lastchar(MEMBER, '\')+1),
substr(substr(MEMBER, lastchar(MEMBER, '\')+1), 1, length(substr(MEMBER, lastchar(MEMBER, '\')+1)) -4) no_ext,
BYTES file_size
From SYS.V_$LOGFILE LF, SYS.V_$LOG L
WHERE L.GROUP# = LF.GROUP#
) f
order by f.file_size desc;
v c_copy_files%rowtype;
ver sys.v_$version.banner%type;
dbname sys.v_$database.name%type;
begin
if in_start_stop_db then
select banner
into ver
from sys.v_$version
where rownum = 1;
if instr(ver,' 8.1') > 0 then
select name
into dbname
from sys.v_$database;
dbms_output.put_line('NET STOP OracleService' || dbname || ' /y');
if instr(ver,' 8.1.5') > 0 then
dbms_output.put_line('NET STOP Oracle815TNSListener /y');
elsif instr(ver,' 8.1.6') > 0 then
dbms_output.put_line('NET STOP Oracle816TNSListener /y');
end if;
end if;
if instr(ver, ' 7.') > 0 then
dbms_output.put_line('NET STOP OracleServiceORCL /y');
dbms_output.put_line('NET STOP OracleTNSListener /y');
end if;
end if;
for v in c_copy_files(in_source_dir, in_dest_dir) loop
dbms_output.put_line(v.del);
dbms_output.put_line('sleep 1');
if in_start_stop_db then
dbms_output.put_line(v.db_zip);
else
dbms_output.put_line(v.copy_zip);
end if;
dbms_output.put_line('sleep 5');
end loop;
dbms_output.put_line('REM ***************************************************');
dbms_output.put_line('REM * The next several lines can be uncommented to *');
dbms_output.put_line('REM * unzip the files in parallel when needed. *');
dbms_output.put_line('REM ***************************************************');
for v in c_copy_files(in_source_dir, in_dest_dir) loop
dbms_output.put_line(v.extract_cmd);
end loop;
if in_start_stop_db then
dbms_output.put_line(':WaitForZip');
dbms_output.put_line('Sleep 60');
dbms_output.put_line('pstat | find "PKZIP25" && Goto WaitForZip');
if instr(ver,' 8.1') > 0 then
dbms_output.put_line('NET START OracleService' || dbname || ' /y');
if instr(ver,' 8.1.5') > 0 then
dbms_output.put_line('NET START Oracle815TNSListener /y');
elsif instr(ver,' 8.1.6') > 0 then
dbms_output.put_line('NET START Oracle816TNSListener /y');
end if;
end if;
if instr(ver, ' 7.') > 0 then
dbms_output.put_line('NET START OracleServiceORCL /y');
dbms_output.put_line('NET START OracleTNSListener /y');
end if;
end if;
end;
/