Link to home
Start Free TrialLog in
Avatar of sharscho
sharscho

asked on

backup oracle db on windows

I have a backup script that I manually run on a windows 2003 server which I will include below.I manually change the backup directory name everyday before I run the script. But this is a hassle when I have a day of and so.  I wanted to schedule this job in windows so that this script can run automatically. I tried to schedule it already but somehow it can not do the connection part. so I want to know what needs to be added and how it needs to be added to this script so that it can set the environment, start sqlus and login and run the alter tablespace begin backup lines, create a backup controlfile that is able to recover the db with when needed, include the copy of the spfile to the backup directory and also the last archive log that is create with the alter system switch log statement this last archlog file needs to be copied or go the backup directory also. I want the files to be zipped also and I do have the special winzip module for that but don't know how to use it in a script.

I wanted the script to create a new directory every day so that the backup can go to a different directory every day. But I don't know or I don't have the knowledge to be able to script something like that. I think if this line can go at the first line and then if somehow it is projected in the ocopy command so that it uses that new directory everyday and so I don't need to change the script every day.

What also will work I think is that I create 7 directories and 7 scripts and schedule 7 backups in the schedular which will be easier. Then this directories will be overwritten every week. But I still needs the tips of how to script the start of sqlus, the login and the copy or creation of the backup controlfile, the arch log and the parameter file to the backup directory.

I don't want to use RMAN yet because I don't understand it so good and most of our databases are on unix with perfect scripts that does everything we want but this one is on Windows and that is my weakness here.

this is the script:
spool d:\oracle\backup\cusdb\BU168\bkup0406.log
alter tablespace system
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\SYSTEM01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace system
end backup;
alter tablespace undotbs
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\UNDOTBS01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace undotbs
end backup;
alter tablespace drsys
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\DRSYS01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace drsys
end backup;
alter tablespace example
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\EXAMPLE01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace example
end backup;
alter tablespace indx
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\INDX01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace indx
end backup;
alter tablespace tools
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\TOOLS01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace tools
end backup;
alter tablespace users
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\USERS01.DBF d:\oracle\backup\cusdb\BU168
alter tablespace users
end backup;
alter tablespace arch1cts
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\ARCH1CTS.DBF d:\oracle\backup\cusdb\BU168
alter tablespace arch1cts
end backup;
alter tablespace idx1cts
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\IDX01CTS.DBF d:\oracle\backup\cusdb\BU168
alter tablespace idx1cts
end backup;
alter tablespace idxcsa
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\IDXCSA.DBF d:\oracle\backup\cusdb\BU168
alter tablespace idxcsa
end backup;
alter tablespace datcsa
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\DATCSA.DBF d:\oracle\backup\cusdb\BU168
alter tablespace datcsa
end backup;
alter tablespace dat1cts
begin backup;
host ocopy D:\ORACLE\ORADATA\cusdb\DAT01CTS.DBF d:\oracle\backup\cusdb\BU168
alter tablespace dat1cts
end backup;
alter system switch logfile;
alter database backup controlfile to 'd:\oracle\backup\cusdb\BU168\bkupctrlfile.trc';
spool off
exit
Avatar of schwertner
schwertner
Flag of Antarctica image

This is not the usual way of making backups.
There is a theoretical error!

1. You use warm backup. This means that your DB is in ARCHIVELOG.
2. Every night you do warm backup
3. You do not backup the Archive Log files.

The usual and compact way to solve this task is:
1. Set the DB in Archivelog
2. To do Full Cold Backup (close the DB before this!)
3. Archive the Cold backup on another machine.
4. Archive on another machine ONLY the Archived Logs.


Using your current backup schema you will be not able to use the Archived logs.
Avatar of sharscho
sharscho

ASKER

The db is in arch log. I do copy the last created arch log, the script has a line that does that, to the backup directpry every night. so I do have arch logs and I do copy them to the backup directory everyday after that  the backup script is complete. I want to schedule the backup that is my problem and my question is how I can schedule it?
Why can't I use the arch logs with my current backup schema? please add some comments....
ASKER CERTIFIED SOLUTION
Avatar of helpneed
helpneed

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am using oracle 9i, is it possible to use this script with this version of oracle also?
The normally used backup/recovery schema is to make cold backup and to begin the archivelog process from that point. This is called 'incarnation" of the DB. Normally the DB resets the archive logs (you open the DB with resetlog option)

I am not sure that the intermediate warm backups can be used with that archivelogs. Theoretically the answer is yes, but I will strongly recommend to make an experiment on another box to be 100% sure that you can recover using warm backups and existing archivelogs. In the other case it is very risky!!! You have to be 100% sure that you will be able to recover the DB.

Why I recommend not to do backups every night?
The answer is: the db File backups (cold, warm) stores the whol dbf file, no matter if the file is 80% full with records or only 34%. This leads to enourmos disk space used for such backups. Of course there is an advantage - the recovery is fast. But only if it runs at all. The archvelogs and also all kind of backups done by RMAN are very compact.

So I will recommend to do experiments and to be conservative.
hi

 u can try this on 9i...
like schwertner, iam also saying that experiment,experiment,experiment..until  u l succed.....

regards