| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Archive Mode On for Read Only DB
Gene,
In a read only database, I would be surprised if you generated *any* archive log files. So asking if they would be usefull during a recovery is non-sensible.
As to whether it should be *in* archivelog mode - it's totally up to you because it really doesn't matter. If it were up to me, my answer is no - don't do it as, from my point of view, if I have a DB in archivelog mode, then I worry about the archivelogs filling up the disk. If the db is *not* in ALM, then I don't have to worry about it - one less thing to manage, thank you very much.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Gene Sais [mailto:Gsais_at_co.palm-beach.fl.us]
Sent: Thursday, March 04, 2004 9:12 AM
To: oracle-l_at_freelists.org
Subject: RE: Archive Mode On for Read Only DB
Thanks. I would not be applying archives to recover a read only database, since I would not have any. But does archive log mode add any other value to recovering a read-only db?
>>> cjpengel.dbalert_at_xs4all.nl 3/4/2004 8:37:45 AM >>>
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_at_xs4all.nl 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, <http://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_at_lifetouch.com
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [ mailto:oracle-l-bounce_at_freelists.org]On
<mailto:oracle-l-bounce_at_freelists.org]On>
> Behalf Of Gene Sais
> Sent: Wednesday, March 03, 2004 10:12 AM
> To: oracle-l_at_freelists.org; 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_at_LIFETOUCH.COM 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_at_lifetouch.com
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org]On
<mailto:oracle-l-bounce_at_freelists.org]On>
> < mailto:oracle-l-bounce_at_freelists.org]On
<mailto:oracle-l-bounce_at_freelists.org]On> >
> Behalf Of Gene Sais
> Sent: Wednesday, March 03, 2004 9:04 AM
> To: oracle-l_at_freelists.org
> 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>
> < http://www.orafaq.com <http://www.orafaq.com> >
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
<http://www.freelists.org/archives/oracle-l/>
> < http://www.freelists.org/archives/oracle-l/
<http://www.freelists.org/archives/oracle-l/> >
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
<http://www.freelists.org/help/fom-serve/cache/1.html>
> < http://www.freelists.org/help/fom-serve/cache/1.html
<http://www.freelists.org/help/fom-serve/cache/1.html> >
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
<http://www.freelists.org/archives/oracle-l/>
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
<http://www.freelists.org/help/fom-serve/cache/1.html>
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ <http://www.freelists.org/archives/oracle-l/> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html <http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 04 2004 - 11:25:08 CST
 |
 |