RE: Archive Mode On for Read Only DB
- From: "Carel-Jan Engel" <cjpengel.dbalert@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 4 Mar 2004 16:02:49 +0100 (CET)
As far is I know and can think of, there is no added value. But, to speak
with Sir Jonathan: Test!
The question is: does archive log mode do more than archiving logfiles
whenever a log switch is performed?
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
> 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@xxxxxxxxx 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@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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
