Hi Gene, No, I didn't mistunderstand your question. However, apparently I didn't make my point very clear. What I tried to point out is that, when an active R/W database can be recovered from an 'open backup', a R/O database won't be a problem. When no log-switches occur, archive log mode won't add too much to your recoverability. Which archives do you want to apply to a R/O database? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === > Carel-Jan - Thanks for your email, but I think you misunderstood my > question :). I would never backup an open read write database and > assume its good (i.e. w/out altering tbs begin backup, etc.). > > My question was: I am backing up a open READ ONLY database (using alter > database open read only, not by tbs) and questioning if archive log mode > turned on could benefit me? From some responses, it seems there is no > need to have archive mode turned on for a read only database. Thanks > for Connor's link, good stuff. > Gene > >>>> cjpengel.dbalert@xxxxxxxxx 3/3/2004 4:16:49 PM >>> > > At 06:18 PM 3/3/2004, you wrote: > Gene - Perhaps someone on the list has directly tried this. > Yes, I have. It's a war story with a happy ending. Last October I was > called in with a custome for one day of consultancy, discussing a > backup/restore strategy (I prefer to create a restore/backup strategy) > for a DWH they were going to setup for a customer of them. > So far, so good. The delivery day was somewhere at the beginning of > this year, but got postponed to Feb 1st. > > Jan 29th I received a phone call. They accidently dropped a 300 million > row facttable. No worries I said, you have implemented the backup > strategy we discussed in October, haven't you?. 'No, we haven't, the > system isn't production yet' they answered sadly. The only thing we have > is a tar backup of an open database, created last Sunday. I discussed > the possibilities to do the restore, but somehow they didn't try and > recovered otherwise. > > Wednesday Feb. 18 the phone rang: 'We were testing a database reorg and > now we've accidently dropped a multi-multi-GIG tablespace issuing 'Drop > tablespace <TS> including contents and datafiles;' After issuing the > command we discovered we were connected to the production schema ISO the > test schema'. No worries I said, this time you have enabled your > backup'. He responded: '.............' (silence). 'OK, that silence > lasts to long, what do you have'. There was a backup of an open > database, created at Monday, while the database was rebuilding indexes. > There were several logswitches whilst the backup bas made. To mak things > even worse they created the problem at Tuseday and started fiddling > around with the remains of the database without first making a proper (I > would suggest physical, i.e. dd-backup). It is all on Sun Slowaris with > Mirrored DAS. They called me in, and we worked two nights on the > subject. First night was simulating the whole situation with backing up > an open index building database, do some more work, and drop the > tablespace. > > I had some phonecalls with Peter Gram and Johannes Djernaes from > Miracle. These Miracle-full guys are amazing at this.What we planned and > did was this: > > Backup everything that was left ot a separate area on disk. Free up > enough space to hold the SYSTEM, > UNDO, TOOLS and dropped tablespace. > Restore the mentioned tablespaces from the Monday tape with the open > backup. We were lucky: there was a controlfile more recent than the > datafiles of these tablespace on the tape Startup mount the database > ALTER DATABASE DATAFILE # OFFLINE for all unrestored datafiles RECOVER > DATAFILE # for all restored datafiles ALTER DATABSE OPEN. This worked, > and now this tablespace was available again. After some struggling with > constraints/indexes causing the tablespace not to be selfcontained the > tablespace was exported using the transportable tablespace feautures. > > Next steps were: Backup the transported tablespace to another disk > Restore all datafiles/controlfiles/redologfiles that were backupped > during the first step Startup this database Import the restored > tablespace At this point, theoratically one can start rebuilding > indexes/re-enabling constraints. > Just to stay on the safe side, an extra tablespace was created and all > objects in the restored tablespace were moved to this tablespace. > Transportable tablespaces come with some bugs, and we wouldn't risk to > hit anyone of them. After moving all objects (inlcuding some LOB's) > indexes were recreated an constraints enabled. > > Everything is fine now. > > Remark: The tablespace that was dropped wasn't hit by any objects for > several days before the backup was made. > > This case illustratetes my opinion that, from the view of a DBA, 'EVERY > DATABASE IS A PRODUCTION DATABASE'. Excepth maybe the test-thing on your > laptop/desktop. > > The argument, that a database (or DWH in this case) hasn't reached > production state yet is stupid. What have the consultants, setting up > the database for over three months been doing then? Is development no > production? Lack of time to implement a backup-procedure is no excuse. > Maybe it is for the DBA, but it isn't for his manager. > > They've learned their lesson. They called in a consultant to implement > the backup rightaway. > > > Regarding READONLY databases, please read also Connor McDonalds note > about slow readonly at www.oracledba.co.uk, look under Administration, > the note is from 14/06/2002 > > Regards, Carel-Jan > > === > If you think education is expensive, try ignorance. (Derek Bok) > === > > > > It is > theoretically possible to get a good backup on a quiet but open > database > with just a cold backup, but not the sort of thing you want to bet your > job > on. One idea would be for you to take your backup and restore it on a > test > system. It is always a good practice to test your database restore > anyway. > Then you would be assured throughout the year that you do indeed have > a > valid backup. But I would do it each year since there might be some > condition that would make the backup succeed 50% of the time, you would > be > covered all the time. > > > > Dennis Williams > DBA > Lifetouch, Inc. > dwilliams@xxxxxxxxxxxxx > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On > Behalf Of Gene Sais > Sent: Wednesday, March 03, 2004 10:12 AM > To: oracle-l@xxxxxxxxxxxxx; DENNIS WILLIAMS > Subject: RE: Archive Mode On for Read Only DB > > > Dennis - The database is opened in read only mode. The database > changes > once a yr to be updated w/ new images. At that time, I put the db in > read > write mode, add the images, then open db in read only and back it up > while > db is open. It is a web query db that I would like to minimize down > time. > Is there any benefit to putting this db in archive log mode? I don't > see > any, but I may be missing something :). > > Thanks for your help, > Gene > >>>> DWILLIAMS@xxxxxxxxxxxxx 3/3/2004 10:47:26 AM >>> > > Gene - By read only, do you mean the contents of the database are > never > changing? Do you ever take it out of read only mode, like to change > something? Why back it up occasionally? Just do a cold backup once and > save > the tapes. > > > > Dennis Williams > DBA > Lifetouch, Inc. > dwilliams@xxxxxxxxxxxxx > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [ > mailto:oracle-l-bounce@xxxxxxxxxxxxx]On > <mailto:oracle-l-bounce@xxxxxxxxxxxxx]On> > Behalf Of Gene Sais > Sent: Wednesday, March 03, 2004 9:04 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: Archive Mode On for Read Only DB > > > I have an 8i read only database that is used for query of images. > Occassionally, I back it up using OS utilities (cp, tar, TSM, etc) > while the > db is open. > > Question: Is there any benefit to having this db in archive log mode? > Since > it is in read only mode, I see no benefit or am I missing something? > > Thanks for any insight you may provide, > Gene > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > <http://www.orafaq.com> > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > <//www.freelists.org/archives/oracle-l/> > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > <//www.freelists.org/help/fom-serve/cache/1.html> > ----------------------------------------------------------------- > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------