psalvini@maytag.waterloo.edu (Paul Salvini) (03/12/91)
We at Wilfird Laurier are looking for suggestions on how to backup our ORACLE databases efficiently and with as little interruptions as possible. So... How do you do it? - we have done cpio and hdumps and would like suggestions to other alternatives or scripts that you might be using. Daryl Erwin (via University of Waterloo account) -- =============================================================================== Paul A. Salvini (519) 884-1973 x2913 Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca Department of Computing Services
rad@genco.bungi.com (Bob Daniel) (03/13/91)
In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >We at Wilfird Laurier are looking for suggestions on how to backup >our ORACLE databases efficiently and with as little interruptions as >possible. > >So... How do you do it? > - we have done cpio and hdumps and would like suggestions to other >alternatives or scripts that you might be using. If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. Also, an export of the entire database before a backup is a safety net. We do both. I have a tape autobackup script setup to occur overnight. First, it exports the entire database, then shutdown the database, then backs up the entire system (or it may be an incremental backup).
mdeltoro@druco.ATT.COM (DeltoroMA) (03/14/91)
in article <1991Mar12.134145.15364@maytag.waterloo.edu>, psalvini@maytag.waterloo.edu (Paul Salvini) says: > > We at Wilfird Laurier are looking for suggestions on how to backup > our ORACLE databases efficiently and with as little interruptions as > possible. > > So... How do you do it? > - we have done cpio and hdumps and would like suggestions to other > alternatives or scripts that you might be using. If you just want to backup the database use Oracle's Import/Export facilities. I would run a job (via cron - if you are using unix) that will export the appropriate data from the database. If you ever need to restore it then use import. -- ********************************************************************** Michael 'DT' Del Toro 303-538-1764 AT&T Bell Labs, Denver, CO mdeltoro@druco.COM.ATT
jeff@u02.svl.cdc.com (Jeff Needham) (03/15/91)
rad@genco.bungi.com (Bob Daniel) writes: >In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >>We at Wilfird Laurier are looking for suggestions on how to backup >>our ORACLE databases efficiently and with as little interruptions as >>possible. >> >>So... How do you do it? >> - we have done cpio and hdumps and would like suggestions to other >>alternatives or scripts that you might be using. >If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. If you are even lazier and don't feel like backing all the library directories and binaries, just back up ?/dbs/*.dbf. Typically, tablespaces and log files follow the convention that they have a .dbf suffix, so this would ensure you get all the database files. Not everyone uses this convention, and not everyone keeps their tablespaces in ?/dbs. A full/incremental dump script running over $ORACLE_HOME, as above, would mean you only dump the product directories once a week. Sites also archieve their offline logs through NFS to protect the production instance. jeff -- Waiting for the Oracle port to the Oberheim OB-Xa | Jeffrey Needham | Yet Another Oracle Performance Group | Control Data - Santa Clara, CA - INTERNET jeff@hawk.svl.cdc.com
mac@sequent.UUCP (Cormac Burke) (03/19/91)
In article <559@genco.bungi.com> rad@genco.bungi.com (Bob Daniel) writes: >In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >>We at Wilfird Laurier are looking for suggestions on how to backup >>our ORACLE databases efficiently and with as little interruptions as >>possible. >> >>So... How do you do it? > >If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. I'd be careful of this. There's no guarantee that this will back up all of your datafiles. I'd suggest querying the system catalogs first to check what your data files are (select file_name from sys.dba_data_files;). This can be particularly relevant if you're using raw disk partitions. Cormac Burke Sequent Computer Systems
psalvini@maytag.waterloo.edu (Paul Salvini) (03/21/91)
I recently posted a request to find out how other Oracle sites are backing up their data. What follows is a summary of their replys. In summary, they state all the obvious solutions that we had thought about. But still good pointers anyways. Thanks for your replys, it was much appreciated. Enjoy! ---------------------- From watmath!watserv1!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!elroy.jpl.nasa.gov!ames!vsi1!daver!genco!rad Mon Mar 18 08:36:29 EST 1991 Article 7506 of comp.databases: Path: maytag!watmath!watserv1!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!elroy.jpl.nasa.gov!ames!vsi1!daver!genco!rad >From: rad@genco.bungi.com (Bob Daniel) Newsgroups: comp.databases Subject: Re: ORACLE: Backup suggestions wanted Message-ID: <559@genco.bungi.com> Date: 13 Mar 91 15:47:08 GMT References: <1991Mar12.134145.15364@maytag.waterloo.edu> Reply-To: rad@genco.bungi.com (Bob Daniel) Distribution: na Organization: Gentry & Assoc. Excelsior Springs, MO Lines: 14 In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >We at Wilfird Laurier are looking for suggestions on how to backup >our ORACLE databases efficiently and with as little interruptions as >possible. > >So... How do you do it? > - we have done cpio and hdumps and would like suggestions to other >alternatives or scripts that you might be using. If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. Also, an export of the entire database before a backup is a safety net. We do both. I have a tape autobackup script setup to occur overnight. First, it exports the entire database, then shutdown the database, then backs up the entire system (or it may be an incremental backup). From watmath!watserv1!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!mips!cs.uoregon.edu!ogicse!milton!uw-beaver!zephyr.ens.tek.com!tektronix!sequent!mac Tue Mar 19 08:29:16 EST 1991 Article 7561 of comp.databases: Path: maytag!watmath!watserv1!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!mips!cs.uoregon.edu!ogicse!milton!uw-beaver!zephyr.ens.tek.com!tektronix!sequent!mac >From: mac@sequent.UUCP (Cormac Burke) Newsgroups: comp.databases Subject: Re: ORACLE: Backup suggestions wanted Message-ID: <55524@sequent.UUCP> Date: 18 Mar 91 22:32:19 GMT References: <1991Mar12.134145.15364@maytag.waterloo.edu> <559@genco.bungi.com> Reply-To: mac@sequent.UUCP (Cormac Burke) Distribution: na Organization: Sequent Computer Systems, Inc Lines: 17 In article <559@genco.bungi.com> rad@genco.bungi.com (Bob Daniel) writes: >In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >>We at Wilfird Laurier are looking for suggestions on how to backup >>our ORACLE databases efficiently and with as little interruptions as >>possible. >> >>So... How do you do it? > >If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. I'd be careful of this. There's no guarantee that this will back up all of your datafiles. I'd suggest querying the system catalogs first to check what your data files are (select file_name from sys.dba_data_files;). This can be particularly relevant if you're using raw disk partitions. Cormac Burke Sequent Computer Systems From hpp@cbnewsm.att.com Tue Mar 12 15:21:51 1991 Received: from att.att.com by maytag.waterloo.edu with SMTP id <AA04288>; Tue, 12 Mar 91 15:21:43 EST Message-Id: <9103122021.AA04288@maytag.waterloo.edu> Date: Tue, 12 Mar 91 14:10:47 EST From: hpp@hrmso.att.com (Hariharan P Panamanna) To: psalvini@maytag Subject: Re: ORACLE: Backup suggestions wanted Newsgroups: comp.databases In-Reply-To: <1991Mar12.134145.15364@maytag.waterloo.edu> Organization: AT&T Bell Laboratories Cc: Status: OR Have you tried the EXPORT & IMPORT ORACLE utilities? They are not the best in the world, but quite good. You can EXPORT selectively like entire database,some users etc to a file and IMPORT the same when required. --Hari cbnewsm!att!com!hpp From hall@cs.sfu.ca Wed Mar 13 19:13:20 1991 Received: from relay.cdnnet.ca by maytag.waterloo.edu with SMTP id <AA05014>; Wed, 13 Mar 91 19:13:05 EST From: hall@cs.sfu.ca Received: by relay.CDNnet.CA (4.1/1.14) id AA09442; Wed, 13 Mar 91 16:12:54 PST Date: 13 Mar 91 16:04 -0800 To: psalvini@maytag In-Reply-To: <1991Mar12.134145.15364@maytag.waterloo.edu> Message-Id: <9103140004.AA00387@acrab.cs.sfu.ca> Subject: Re: ORACLE: Backup suggestions wanted Status: OR Paul, What version are you running? If you have version 6 you can use the export facility. The Utilities Users' Guide (Part No. 3602-V6.0) explains its user for backups. I have the following cshell script run every night using cron. #!/bin/csh -f # requires 1 argument - ORACLE_SID of DB to be backed up # creates a complete backup on 1st of month, cumulative every Friday unset noclobber setenv ORACLE_HOME /usr/local/Oracle setenv ORACLE_SID $argv[1] #The following removes trailing colon from PATH and adds ORACLE_HOME/bin if ( $PATH !~ *$ORACLE_HOME* ) \ setenv PATH `echo $PATH | sed 's/\(.*\):$/\1/'`:$ORACLE_HOME/bin set dir = $ORACLE_HOME"/backup/" set x=`date` set xday=$x[1] if ($x[3] == 1) then set exptype = ("complete" "Com") else if ($xday == "Fri") then set exptype = ("cumulative" "Cum") else set exptype= ("incremental" "Inc$xday") endif set file="$dir$ORACLE_SID$exptype[2].exp" set output="$dir$ORACLE_SID$exptype[2].msg" exp system/halloween file=$file inctype=$exptype[1] >&! $output set noclobber Gary Hall | Voice (604) 291-3208 Centre for Systems Science | Fax (604) 291-4951 Simon Fraser University | email hall@cs.sfu.ca Burnaby, B.C. V5A 1S6 | Article 7502 of comp.databases: Path: maytag!watmath!watserv1!utgpu!news-server.csri.toronto.edu!rutgers!ub.d.umn.edu!cs.umn.edu!uc!shamash!jeff From: jeff@u02.svl.cdc.com (Jeff Needham) Newsgroups: comp.databases Subject: Re: ORACLE: Backup suggestions wanted Message-ID: <31398@shamash.cdc.com> Date: 14 Mar 91 20:21:16 GMT References: <1991Mar12.134145.15364@maytag.waterloo.edu> <559@genco.bungi.com> Sender: usenet@shamash.cdc.com Distribution: na Lines: 28 rad@genco.bungi.com (Bob Daniel) writes: >In article <1991Mar12.134145.15364@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >>We at Wilfird Laurier are looking for suggestions on how to backup >>our ORACLE databases efficiently and with as little interruptions as >>possible. >> >>So... How do you do it? >> - we have done cpio and hdumps and would like suggestions to other >>alternatives or scripts that you might be using. >If the database is shutdown, the backup of $ORACLE_HOME directory is sufficient. If you are even lazier and don't feel like backing all the library directories and binaries, just back up ?/dbs/*.dbf. Typically, tablespaces and log files follow the convention that they have a .dbf suffix, so this would ensure you get all the database files. Not everyone uses this convention, and not everyone keeps their tablespaces in ?/dbs. A full/incremental dump script running over $ORACLE_HOME, as above, would mean you only dump the product directories once a week. Sites also archieve their offline logs through NFS to protect the production instance. jeff -- Waiting for the Oracle port to the Oberheim OB-Xa | Jeffrey Needham | Yet Another Oracle Performance Group | Control Data - Santa Clara, CA - INTERNET jeff@hawk.svl.cdc.com From watmath!watserv1!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!usc!apple!uokmax!occrsh!fang!att!drutx!druco!mdeltoro Mon Mar 18 08:37:14 EST 1991 Article 7519 of comp.databases: Path: maytag!watmath!watserv1!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!usc!apple!uokmax!occrsh!fang!att!drutx!druco!mdeltoro >From: mdeltoro@druco.ATT.COM (DeltoroMA) Newsgroups: comp.databases Subject: Re: ORACLE: Backup suggestions wanted Message-ID: <7832@drutx.ATT.COM> Date: 13 Mar 91 21:42:21 GMT References: <1991Mar12.134145.15364@maytag.waterloo.edu> Sender: news@drutx.ATT.COM Distribution: na Lines: 22 in article <1991Mar12.134145.15364@maytag.waterloo.edu>, psalvini@maytag.waterloo.edu (Paul Salvini) says: > > We at Wilfird Laurier are looking for suggestions on how to backup > our ORACLE databases efficiently and with as little interruptions as > possible. > > So... How do you do it? > - we have done cpio and hdumps and would like suggestions to other > alternatives or scripts that you might be using. If you just want to backup the database use Oracle's Import/Export facilities. I would run a job (via cron - if you are using unix) that will export the appropriate data from the database. If you ever need to restore it then use import. -- ********************************************************************** Michael 'DT' Del Toro 303-538-1764 AT&T Bell Labs, Denver, CO mdeltoro@druco.COM.ATT -- =============================================================================== Paul A. Salvini (519) 884-1973 x2913 Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca Department of Computing Services