| 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
Thank you Michael.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
At 10:41 PM 3/4/2004, you wrote:
>There are a few things one can NOT do when the DB is in NOArchiveLog, for
>example take a tablespace offline without a checkpoint (OFFLINE IMMEDIATE).
>The fake recovery trick is also dependent on Archivelog mode.
>
>Again, considering that the DB is read only (and it is allowed to do the ALTER
>DATABASE OPEN READ ONLY that hasnt been/seen any Standby stuff - tested on 92,
>and it is in my 816 docs onwards) then you cant switch logfiles (ORA-16000).
>If I crash a DB (shutdown abort) in READ ONLY mode there is no recovery during
>the next startup. That proves you can take a "cold" backup of a READ ONLY DB
>without fear.
>
>The long and short of it - IMHO - drop the archive log mode, drop the backups
>and do one (OK, two copies if you really want to) simple cold backup after you
>have loaded fresh data and are back in READ ONLY mode.
>
>Still, its only my opinion - its your job/data, Gene.
>
>Michael Möller, Miracle
>
>Carel-Jan Engel wrote:
>
> > 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_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
> > -----------------------------------------------------------------
>
>----------------------------------------------------------------
>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
>-----------------------------------------------------------------
-- 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 - 17:51:20 CST
 |
 |