Back up

[sureshgane]

I have ORACLE 8.1.6 Running on Windows N/T.There is a database instance called TEST.The database is meant for training & rarely the data grows.I have never done the back up of this databse.So I thought as a precautionary I will do the back up.I would like to know is it ok if I do a logical backup I mean doing a export of the database by logging as DBA or I need to do a physical backup.If so what all files I should do a back up & how to do that
Thanks
Suresh

[Kishore]

Since it is a test Db, simply do a cold backup. Shutdown the database.

Copy all DBF or ORA files.
Copy Init<SID>.ora file
Copy all controlfiles
Copy all redolog files

Doing export also will help.

Hope this helps

[sureshgane]

Thanks Kishore.But say if abc.dbf(IS 2 GB) AGAIN WE NEED 2GB FOR BACKUP.If I am wrong correct me.So I guess in dos prompt u need to do copy abc.dbf xyz.dbf.
But the problem is that I am running out of space in my server.So please send the best solution.Sorry for the trouble

[sreddy]

You are Right. you need equivalent space on the server to save the file unless you compress it after copying(Imean one datafile after another, assuming you have multiple big datafiles for your data)

If you are running outta space,

1)either ftp unwanted files to some other server where you have space
2)zip your backup files, Imean to comressed mode
3)Use full=y and export the dmp file and save it after zipping it/Ftp it to a server where you have enough room.

Note: Make sure you are using 'BINARY' mode for your ftp.




[Edited by sreddy on 12-28-2000 at 02:37 PM]

[jdorlon]

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 [url]http://www.pkware.com[/url] 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;
/

[jdorlon]

another trick you can do before a cold backup to save space is to shrink the datafiles for the temporary and rollback tablespaces before the backup, and then grow them back to normal after the backup.