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 08:01:15 -0500
Message-Id: <s046e254.089@ISSFS2.co.palm-beach.fl.us>


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
-----------------------------------------------------------------
Received on Thu Mar 04 2004 - 07:00:34 CST

Original text of this message

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