[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

backup oracle db on windows

Posted on 2005-04-07
7
Medium Priority
?
1,157 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:sharscho
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13726068
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.
0
 

Author Comment

by:sharscho
ID: 13726157
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?
0
 

Author Comment

by:sharscho
ID: 13727154
Why can't I use the arch logs with my current backup schema? please add some comments....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Accepted Solution

by:
helpneed earned 900 total points
ID: 13733770
hi

use this


rem Script Description: This script automates hot backups (only available when
rem                     the archivelog mode is on). It creates a script called hotback.sql,
rem                     that will automatically back up tablespaces and control files.
rem
rem
rem Output file:        hotbackups.sql

rem Usage Information:  SQLPLUS SYS/pswd
rem                     @autohotbackups.sql  {target directory}
rem

set serveroutput on
set heading off
set feedback off

spool hotbackups.sql

declare
fname  varchar2(80);
tname  varchar2(80);
tname1  varchar2(80);

cursor cur1 is
      select tablespace_name,file_name
       from v$datafile,sys.dba_data_files
       where enabled like '%WRITE%'
         and file# = file_id
       order by 1;

begin
  dbms_output.enable(32000);
  dbms_output.put_line('spool hotbackups');

  if cur1%ISOPEN
  then
     close cur1;
  end if;

  open cur1;

  fetch cur1 into tname,fname;

  tname1 := tname;
  dbms_output.put_line('alter tablespace '||tname||' begin backup;');

  while cur1%FOUND loop

        if tname1 != tname then
           dbms_output.put_line('alter tablespace '||tname1||' end backup;');
           dbms_output.put_line('alter tablespace '||tname||' begin backup;');
           tname1 := tname;
        end if;        

        dbms_output.put_line('!cp '||fname||' &&target_directory');

        fetch cur1 into tname,fname;

  end loop;
  dbms_output.put_line('alter tablespace '||tname1||' end backup;');

  close cur1;

  dbms_output.put_line('alter database backup controlfile to trace;');

  dbms_output.put_line('alter database backup controlfile to '||''''||
                       ' &&target_directory'||'/control.'||
                       to_char(sysdate,'DDMMYYYYHH24MISS')||''''||';');
  dbms_output.put_line('spool off');
end;
/
spool off
set heading on
set feedback on
set serveroutput off

-- Unremark/Uncomment the following line to run the script
-- or can be run from the sqlplus prompt.
-- @hotbackups


regards
0
 

Author Comment

by:sharscho
ID: 13733897
I am using oracle 9i, is it possible to use this script with this version of oracle also?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13733911
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.
0
 
LVL 5

Expert Comment

by:helpneed
ID: 13734513
hi

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

regards
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question