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@(protected) 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@(protected) 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@(protected)
>
> -- --Original Message-- --
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)]On
> Behalf Of Gene Sais
> Sent: Wednesday, March 03, 2004 10:12 AM
> To: oracle-l@(protected); 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@(protected) 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@(protected)
>
> -- --Original Message-- --
> From: oracle-l-bounce@(protected) [
> mailto:oracle-l-bounce@(protected)]On
> <mailto:oracle-l-bounce@(protected)]On >
> Behalf Of Gene Sais
> Sent: Wednesday, March 03, 2004 9:04 AM
> To: oracle-l@(protected)
> 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@(protected)
> 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@(protected)
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
<HTML > <HEAD >
<META http-equiv=Content-Type content= "text/html; charset=iso-8859-1 " >
<META content= "MSHTML 6.00.2800.1226 " name=GENERATOR > </HEAD >
<BODY style= "MARGIN: 4px 4px 1px; FONT: 10pt Arial " >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? <BR > <BR >>>> cjpengel.dbalert@(protected) 3/4/2004 8:37:45 AM >>> <BR >
<DIV style= "COLOR: #000000 " >Hi Gene, <BR > <BR >No, I didn 't mistunderstand your question. However, apparently I didn 't <BR >make my point very clear. What I tried to point out is that, when an <BR >active R/W database can be recovered from an 'open backup ', a R/O database <BR >won 't be a problem. When no log-switches occur, archive log mode won 't add <BR >too much to your recoverability. Which archives do you want to apply to a <BR >R/O database? <BR > <BR >Regards, Carel-Jan <BR > <BR >=== <BR >If you think education is expensive, try ignorance. (Derek Bok) <BR >=== <BR > <BR > <BR >> Carel-Jan - Thanks for your email, but I think you misunderstood my <BR >> question :). I would never backup an open read write database and <BR >> assume its good (i.e. w/out altering tbs begin backup, etc.). <BR >> <BR >> My question was: I am backing up a open READ ONLY database (using alter <BR >> database open read only, not by tbs) and questioning if archive log mode <BR >> turned on could benefit me? From some responses, it seems there is no <BR >> need to have archive mode turned on for a read only database. Thanks <BR >> for Connor 's link, good stuff. <BR >> Gene <BR >> <BR >>>>> cjpengel.dbalert@(protected) 3/3/2004 4:16:49 PM >>> <BR >> <BR >> At 06:18 PM 3/3/2004, you wrote: <BR >> Gene - Perhaps someone on the list has directly tried this. <BR >> Yes, I have. It 's a war story with a happy ending. Last October I was <BR >> called in with a custome for one day of consultancy, discussing a <BR >> backup/restore strategy (I prefer to create a restore/backup strategy) <BR >> for a DWH they were going to setup for a customer of them. <BR >> So far, so good. The delivery day was somewhere at the beginning of <BR >> this year, but got postponed to Feb 1st. <BR >> <BR >> Jan 29th I received a phone call. They accidently dropped a 300 million <BR >> row facttable. No worries I said, you have implemented the backup <BR >> strategy we discussed in October, haven 't you?. 'No, we haven 't, the <BR >> system isn 't production yet ' they answered sadly. The only thing we have <BR >> is a tar backup of an open database, created last Sunday. I discussed <BR >> the possibilities to do the restore, but somehow they didn 't try and <BR >> recovered otherwise. <BR >> <BR >> Wednesday Feb. 18 the phone rang: 'We were testing a database reorg and <BR >> now we 've accidently dropped a multi-multi-GIG tablespace issuing 'Drop <BR >> tablespace <TS> including contents and datafiles; ' After issuing the <BR >> command we discovered we were connected to the production schema ISO the <BR >> test schema '. No worries I said, this time you have enabled your <BR >> backup '. He responded: '............. ' (silence). 'OK, that silence <BR >> lasts to long, what do you have '. There was a backup of an open <BR >> database, created at Monday, while the database was rebuilding indexes. <BR >> There were several logswitches whilst the backup bas made. To mak things <BR >> even worse they created the problem at Tuseday and started fiddling <BR >> around with the remains of the database without first making a proper (I <BR >> would suggest physical, i.e. dd-backup). It is all on Sun Slowaris with <BR >> Mirrored DAS. They called me in, and we worked two nights on the <BR >> subject. First night was simulating the whole situation with backing up <BR >> an open index building database, do some more work, and drop the <BR >> tablespace. <BR >> <BR >> I had some phonecalls with Peter Gram and Johannes Djernaes from <BR >> Miracle. These Miracle-full guys are amazing at this.What we planned and <BR >> did was this: <BR >> <BR >> Backup everything that was left ot a separate area on disk. Free up <BR >> enough space to hold the SYSTEM, <BR >> UNDO, TOOLS and dropped tablespace. <BR >> Restore the mentioned tablespaces from the Monday tape with the open <BR >> backup. We were lucky: there was a controlfile more recent than the <BR >> datafiles of these tablespace on the tape Startup mount the database <BR >> ALTER DATABASE DATAFILE # OFFLINE for all unrestored datafiles RECOVER <BR >> DATAFILE # for all restored datafiles ALTER DATABSE OPEN. This worked, <BR >> and now this tablespace was available again. After some struggling with <BR >> constraints/indexes causing the tablespace not to be selfcontained the <BR >> tablespace was exported using the transportable tablespace feautures. <BR >> <BR >> Next steps were: Backup the transported tablespace to another disk <BR >> Restore all datafiles/controlfiles/redologfiles that were backupped <BR >> during the first step Startup this database Import the restored <BR >> tablespace At this point, theoratically one can start rebuilding <BR >> indexes/re-enabling constraints. <BR >> Just to stay on the safe side, an extra tablespace was created and all <BR >> objects in the restored tablespace were moved to this tablespace. <BR >> Transportable tablespaces come with some bugs, and we wouldn 't risk to <BR >> hit anyone of them. After moving all objects (inlcuding some LOB 's) <BR >> indexes were recreated an constraints enabled. <BR >> <BR >> Everything is fine now. <BR >> <BR >> Remark: The tablespace that was dropped wasn 't hit by any objects for <BR >> several days before the backup was made. <BR >> <BR >> This case illustratetes my opinion that, from the view of a DBA, 'EVERY <BR >> DATABASE IS A PRODUCTION DATABASE '. Excepth maybe the test-thing on your <BR >> laptop/desktop. <BR >> <BR >> The argument, that a database (or DWH in this case) hasn 't reached <BR >> production state yet is stupid. What have the consultants, setting up <BR >> the database for over three months been doing then? Is development no <BR >> production? Lack of time to implement a backup-procedure is no excuse. <BR >> Maybe it is for the DBA, but it isn 't for his manager. <BR >> <BR >> They 've learned their lesson. They called in a consultant to implement <BR >> the backup rightaway. <BR >> <BR >> <BR >> Regarding READONLY databases, please read also Connor McDonalds note <BR >> about slow readonly at <A href= "http://www.oracledba.co.uk, " >www.oracledba.co.uk, </A > look under Administration, <BR >> the note is from 14/06/2002 <BR >> <BR >> Regards, Carel-Jan <BR >> <BR >> === <BR >> If you think education is expensive, try ignorance. (Derek Bok) <BR >> === <BR >> <BR >> <BR >> <BR >> It is <BR >> theoretically possible to get a good backup on a quiet but open <BR >> database <BR >> with just a cold backup, but not the sort of thing you want to bet your <BR >> job <BR >> on. One idea would be for you to take your backup and restore it on a <BR >> test <BR >> system. It is always a good practice to test your database restore <BR >> anyway. <BR >> Then you would be assured throughout the year that you do indeed have <BR >> a <BR >> valid backup. But I would do it each year since there might be some <BR >> condition that would make the backup succeed 50% of the time, you would <BR >> be <BR >> covered all the time. <BR >> <BR >> <BR >> <BR >> Dennis Williams <BR >> DBA <BR >> Lifetouch, Inc. <BR >> dwilliams@(protected) <BR >> <BR >> -- --Original Message-- -- <BR >> From: oracle-l-bounce@(protected) <BR >> [ <A href= "mailto:oracle-l-bounce@(protected)]On " >mailto:oracle-l-bounce@(protected)]On </A > <BR >> Behalf Of Gene Sais <BR >> Sent: Wednesday, March 03, 2004 10:12 AM <BR >> To: oracle-l@(protected); DENNIS WILLIAMS <BR >> Subject: RE: Archive Mode On for Read Only DB <BR >> <BR >> <BR >> Dennis - The database is opened in read only mode. The database <BR >> changes <BR >> once a yr to be updated w/ new images. At that time, I put the db in <BR >> read <BR >> write mode, add the images, then open db in read only and back it up <BR >> while <BR >> db is open. It is a web query db that I would like to minimize down <BR >> time. <BR >> Is there any benefit to putting this db in archive log mode? I don 't <BR >> see <BR >> any, but I may be missing something :). <BR >> <BR >> Thanks for your help, <BR >> Gene <BR >> <BR >>>>> DWILLIAMS@(protected) 3/3/2004 10:47:26 AM >>> <BR >> <BR >> Gene - By read only, do you mean the contents of the database are <BR >> never <BR >> changing? Do you ever take it out of read only mode, like to change <BR >> something? Why back it up occasionally? Just do a cold backup once and <BR >> save <BR >> the tapes. <BR >> <BR >> <BR >> <BR >> Dennis Williams <BR >> DBA <BR >> Lifetouch, Inc. <BR >> dwilliams@(protected) <BR >> <BR >> -- --Original Message-- -- <BR >> From: oracle-l-bounce@(protected) [ <BR >> <A href= "mailto:oracle-l-bounce@(protected)]On " >mailto:oracle-l-bounce@(protected)]On </A > <BR >> < <A href= "mailto:oracle-l-bounce@(protected)]On " >mailto:oracle-l-bounce@(protected)]On </A >> <BR >> Behalf Of Gene Sais <BR >> Sent: Wednesday, March 03, 2004 9:04 AM <BR >> To: oracle-l@(protected) <BR >> Subject: Archive Mode On for Read Only DB <BR >> <BR >> <BR >> I have an 8i read only database that is used for query of images. <BR >> Occassionally, I back it up using OS utilities (cp, tar, TSM, etc) <BR >> while the <BR >> db is open. <BR >> <BR >> Question: Is there any benefit to having this db in archive log mode? <BR >> Since <BR >> it is in read only mode, I see no benefit or am I missing something? <BR >> <BR >> Thanks for any insight you may provide, <BR >> Gene <BR >> <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >> Please see the official ORACLE-L FAQ: <A href= "http://www.orafaq.com " >http://www.orafaq.com </A > <BR >> < <A href= "http://www.orafaq.com " >http://www.orafaq.com </A >> <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >> To unsubscribe send email to: oracle-l-request@(protected) <BR >> put 'unsubscribe ' in the subject line. <BR >> -- <BR >> Archives are at <A href= "http://www.freelists.org/archives/oracle-l/ " >http://www.freelists.org/archives/oracle-l/ </A > <BR >> < <A href= "http://www.freelists.org/archives/oracle-l/ " >http://www.freelists.org/archives/oracle-l/ </A >> <BR >> FAQ is at <A href= "http://www.freelists.org/help/fom-serve/cache/1.html " >http://www.freelists.org/help/fom-serve/cache/1.html </A > <BR >> < <A href= "http://www.freelists.org/help/fom-serve/cache/1.html " >http://www.freelists.org/help/fom-serve/cache/1.html </A >> <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR >> <BR >> <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >> Please see the official ORACLE-L FAQ: <A href= "http://www.orafaq.com " >http://www.orafaq.com </A > <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >> To unsubscribe send email to: oracle-l-request@(protected) <BR >> put 'unsubscribe ' in the subject line. <BR >> -- <BR >> Archives are at <A href= "http://www.freelists.org/archives/oracle-l/ " >http://www.freelists.org/archives/oracle-l/ </A > <BR >> FAQ is at <A href= "http://www.freelists.org/help/fom-serve/cache/1.html " >http://www.freelists.org/help/fom-serve/cache/1.html </A > <BR >> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR > <BR > <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >Please see the official ORACLE-L FAQ: <A href= "http://www.orafaq.com " >http://www.orafaq.com </A > <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >To unsubscribe send email to: oracle-l-request@(protected) <BR >put 'unsubscribe ' in the subject line. <BR >-- <BR >Archives are at <A href= "http://www.freelists.org/archives/oracle-l/ " >http://www.freelists.org/archives/oracle-l/ </A > <BR >FAQ is at <A href= "http://www.freelists.org/help/fom-serve/cache/1.html " >http://www.freelists.org/help/fom-serve/cache/1.html </A > <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR > </DIV > </BODY > </HTML >
