Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Archive Mode On for Read Only DB

RE: Archive Mode On for Read Only DB

From: Gene Sais <Gsais_at_co.palm-beach.fl.us>
Date: Thu, 04 Mar 2004 09:11:53 -0500
Message-Id: <s046f2e5.086@ISSFS2.co.palm-beach.fl.us>


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, 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
> 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>
> 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>
> ----------------------------------------------------------------
> 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>
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------



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
-----------------------------------------------------------------


----------------------------------------------------------------
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 - 08:12:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US