RE: Database backup question.Thank You
oh god I was teasing about flashback query! I don't really think it's robust enough for production recoveries. Technically you don't need server-managed undo to use flashback query, it will work with manually managed rollback segments. But you have less of a chance for the data to still be in the rollback segment than you do with automatic undo management. and I've done the recover table from backup -- in fact that's what we did, on a 7.3.4 database when the programmer did an update without the where clause and then committed the transaction. although we used a database link and did our own update of the original data back into the production database as we couldn't take the table away from the app and it was historical data that had been changed. As for those developer flashbacks, see above. He did live after I got through with him. barely. :) --- Jeremiah Wilton <[EMAIL PROTECTED]> wrote: > It is pretty easy to restore and recover a single table to an > arbitrary point in time from a physical backup. I don't think Oracle > needs to provide an extra feature. > > You restore a small subset of the database (system, rollbacks and the > tablespace with the table in it), offline drop the datafiles you > didn't restore, and roll the "mini-clone" forward to the point in > time > you want. > > Export/import the table from the "mini-clone" into the original > database via named pipes. > > Query flashback won't work past a certain timeframe, and it won't > work > on tables that have been mangled by DDL (drop/truncate). And you > have > to use server-managed undo to use query flashback. > > Personally, there are a lot of queries the developers here have come > up with that I have flashbacks of anyway, usually around 3 or 4 in > the > morning. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Tue, 28 May 2002, Rachel Carmichael wrote: > > > isn't that supposed to be flashback query? :) > > > > --- Gene Sais <[EMAIL PROTECTED]> wrote: > > > maybe 10i will allow table pt in time recovery :) > > > > > > >>> [EMAIL PROTECTED] 05/25/02 05:53PM >>> > > > you mean the export? it's a lot easier to recover a single table > from > > > an export and let everyone else keep working. AFAIK, Oracle still > > > doesn't do table-level recovery, the lowest granularity is > > > tablespace. > > > > > > Also, exports are good at letting you clone users and application > > > schemas > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jeremiah Wilton > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database backup question.Thank You
It is pretty easy to restore and recover a single table to an arbitrary point in time from a physical backup. I don't think Oracle needs to provide an extra feature. You restore a small subset of the database (system, rollbacks and the tablespace with the table in it), offline drop the datafiles you didn't restore, and roll the "mini-clone" forward to the point in time you want. Export/import the table from the "mini-clone" into the original database via named pipes. Query flashback won't work past a certain timeframe, and it won't work on tables that have been mangled by DDL (drop/truncate). And you have to use server-managed undo to use query flashback. Personally, there are a lot of queries the developers here have come up with that I have flashbacks of anyway, usually around 3 or 4 in the morning. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Tue, 28 May 2002, Rachel Carmichael wrote: > isn't that supposed to be flashback query? :) > > --- Gene Sais <[EMAIL PROTECTED]> wrote: > > maybe 10i will allow table pt in time recovery :) > > > > >>> [EMAIL PROTECTED] 05/25/02 05:53PM >>> > > you mean the export? it's a lot easier to recover a single table from > > an export and let everyone else keep working. AFAIK, Oracle still > > doesn't do table-level recovery, the lowest granularity is > > tablespace. > > > > Also, exports are good at letting you clone users and application > > schemas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database backup question.Thank You
isn't that supposed to be flashback query? :) --- Gene Sais <[EMAIL PROTECTED]> wrote: > maybe 10i will allow table pt in time recovery :) > > >>> [EMAIL PROTECTED] 05/25/02 05:53PM >>> > you mean the export? it's a lot easier to recover a single table from > an export and let everyone else keep working. AFAIK, Oracle still > doesn't do table-level recovery, the lowest granularity is > tablespace. > > I could be wrong. > > Also, exports are good at letting you clone users and application > schemas > > --- Kevin Lange <[EMAIL PROTECTED]> wrote: > > If you truely mean that ALL of your databases are in ArchiveLog > Mode, > > why > > would you do that to your Test and Dev databases ? > > > > -Original Message- > > Sent: Friday, May 24, 2002 2:33 PM > > To: Multiple recipients of list ORACLE-L > > > > > > my backup strategy, fwiw: > > > > prod - cold monthly, hot 2x week, exp weekly. > > test - cold, hot, exp occassional, always can refresh from prod. > > dev - cold & hot occassional, exp daily. > > > > all dbs are in archivelogmode! > > > > gene > > > > >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> > > lets not forget the classic "exp". > > > > 1. Production database (where you can't lose a single > > transaction) - ARCHIVEMODE absolutely > > > > 2. Development database (few hrs of transactions ok to > > lose) - cold backups > > > > 3. Development database (no schema changes, say an > > application is being developed with a tool such as > > using Oracle designer) - a simple 'exp un/pwd' of the > > user, is the simplest, quickest, lightest, least > > headache,... may also be considered. > > > > Keith > > > > > > > > Date: Fri, 24 May 2002 09:12:02 -0800 > > To: "Multiple recipients of list ORACLE-L" > > <[EMAIL PROTECTED]> > > Reply-to: [EMAIL PROTECTED] > > Address | Add to Address Book > > Organization: Fat City Network Services, San Diego, > > California > > > > > > Hi Tim and Connor, > > > > Thanks you all for your very helpful feedback. I do > > appreciate it very much. In fact, we are in > > development at this point, so the database is small > > and transaction volume is very low. Therefore, my > > choice for primary backup method is the cold backups. > > However, to safeguard against unsual things, which > > might happen to the database, I will take your advice > > to run my database in ARCHIVELOG mode. The hot backup > > will be used. Again, thanks for your very quick > > responses. > > > > Regards, > > > > Trang > > > > Tim Gorman <[EMAIL PROTECTED]> wrote: > > > > Trang, > > > > Theoretically, the online redo log files are be > > necessary, but the world has a habit of making a > > shambles of the theoretical. Let's say, in the event > > that you automate your Friday script, you'll probably > > come to realize that SHUTDOWN IMMEDIATE is far from > > perfect (as well as far from immediate!). Over time, > > you'll probably construct some kind of "fail-safe" > > mechanism to SHUTDOWN ABORT if the initial SHUTDOWN > > IMMEDIATE doesn't shut down after a period of time. > > Pretty standard thing that DBAs have been writing for > > years. Hopefully, after the SHUTDOWN ABORT they also > > STARTUP RESTRICT and then SHUTDOWN NORMAL, but you > > can't count on it... > > > > So, here's the point: what if you take a cold backup > > in NOARCHIVELOG mode after a SHUTDOWN ABORT (that > > should have been a SHUTDOWN IMMEDIATE and wasn't) and > > you have *not* backed up those online redo log files? > > Answer: unusable backup. So, back up everything: all > > datafiles, controlfiles, and online redo logfiles. > > The latter are not too big anyway -- what's the point > > of excluding them? > > > > It is wise to take a cold backup after a clean > > shutdown, but you can even get a valid backup after a > > SHUTDOWN ABORT or a crash if you've backed up the > > online redo archive log files. When you restart > > Oracle, an instance recovery will occur automatically, > > and you might not even know it. Just be certain that > > the instance is truly "dead" when you take your "cold" > > backup... > > > > With regards to switching between ARCHIVELOG and > > NOARCHIVELOG, it's a waste of effort from a > > recoverability standpoint. At most it may be > > interesting, but as soon as you switch out of > > ARCHIVELOG mode, nothing you've done while in > > ARCHIVELOG mode is valid anymore. Leave it one way or > > the other, and then leave it... > > > > ...just my $0.02... > > > > Another $0.02: use RMAN for your cold backups. Then > > you won't forget anything, because RMAN will remember > > for you... > > > > Hope this helps... > > > > -Tim > > - Original Message - > > To: Multiple recipients of list ORACLE-L > > Sent: Thursday, May 23, 2002 5:33 PM > > > > > > Hi All, > > > > I need to perform a consistent backup for my whole > > database every Friday by using operating system > > utilities. My database has been currently
RE: Database backup question.Thank You
maybe 10i will allow table pt in time recovery :) >>> [EMAIL PROTECTED] 05/25/02 05:53PM >>> you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. I could be wrong. Also, exports are good at letting you clone users and application schemas --- Kevin Lange <[EMAIL PROTECTED]> wrote: > If you truely mean that ALL of your databases are in ArchiveLog Mode, > why > would you do that to your Test and Dev databases ? > > -Original Message- > Sent: Friday, May 24, 2002 2:33 PM > To: Multiple recipients of list ORACLE-L > > > my backup strategy, fwiw: > > prod - cold monthly, hot 2x week, exp weekly. > test - cold, hot, exp occassional, always can refresh from prod. > dev - cold & hot occassional, exp daily. > > all dbs are in archivelogmode! > > gene > > >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> > lets not forget the classic "exp". > > 1. Production database (where you can't lose a single > transaction) - ARCHIVEMODE absolutely > > 2. Development database (few hrs of transactions ok to > lose) - cold backups > > 3. Development database (no schema changes, say an > application is being developed with a tool such as > using Oracle designer) - a simple 'exp un/pwd' of the > user, is the simplest, quickest, lightest, least > headache,... may also be considered. > > Keith > > > > Date: Fri, 24 May 2002 09:12:02 -0800 > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Reply-to: [EMAIL PROTECTED] > Address | Add to Address Book > Organization: Fat City Network Services, San Diego, > California > > > Hi Tim and Connor, > > Thanks you all for your very helpful feedback. I do > appreciate it very much. In fact, we are in > development at this point, so the database is small > and transaction volume is very low. Therefore, my > choice for primary backup method is the cold backups. > However, to safeguard against unsual things, which > might happen to the database, I will take your advice > to run my database in ARCHIVELOG mode. The hot backup > will be used. Again, thanks for your very quick > responses. > > Regards, > > Trang > > Tim Gorman <[EMAIL PROTECTED]> wrote: > > Trang, > > Theoretically, the online redo log files are be > necessary, but the world has a habit of making a > shambles of the theoretical. Let's say, in the event > that you automate your Friday script, you'll probably > come to realize that SHUTDOWN IMMEDIATE is far from > perfect (as well as far from immediate!). Over time, > you'll probably construct some kind of "fail-safe" > mechanism to SHUTDOWN ABORT if the initial SHUTDOWN > IMMEDIATE doesn't shut down after a period of time. > Pretty standard thing that DBAs have been writing for > years. Hopefully, after the SHUTDOWN ABORT they also > STARTUP RESTRICT and then SHUTDOWN NORMAL, but you > can't count on it... > > So, here's the point: what if you take a cold backup > in NOARCHIVELOG mode after a SHUTDOWN ABORT (that > should have been a SHUTDOWN IMMEDIATE and wasn't) and > you have *not* backed up those online redo log files? > Answer: unusable backup. So, back up everything: all > datafiles, controlfiles, and online redo logfiles. > The latter are not too big anyway -- what's the point > of excluding them? > > It is wise to take a cold backup after a clean > shutdown, but you can even get a valid backup after a > SHUTDOWN ABORT or a crash if you've backed up the > online redo archive log files. When you restart > Oracle, an instance recovery will occur automatically, > and you might not even know it. Just be certain that > the instance is truly "dead" when you take your "cold" > backup... > > With regards to switching between ARCHIVELOG and > NOARCHIVELOG, it's a waste of effort from a > recoverability standpoint. At most it may be > interesting, but as soon as you switch out of > ARCHIVELOG mode, nothing you've done while in > ARCHIVELOG mode is valid anymore. Leave it one way or > the other, and then leave it... > > ...just my $0.02... > > Another $0.02: use RMAN for your cold backups. Then > you won't forget anything, because RMAN will remember > for you... > > Hope this helps... > > -Tim > - Original Message - > To: Multiple recipients of list ORACLE-L > Sent: Thursday, May 23, 2002 5:33 PM > > > Hi All, > > I need to perform a consistent backup for my whole > database every Friday by using operating system > utilities. My database has been currently operating > in NOARCHIVELOG mode, so the only files need to be > backed up are datafiles, control files, the > initialization parameter file and other oracle product > initialization files (Based on Oracle8.1.6 Backup and > Recovery Guide). Since the files in this type of > backup are all consistent and do not need recovery, so > the online logs are not needed. Since on
RE: Database backup question.Thank You
you mean the export? it's a lot easier to recover a single table from an export and let everyone else keep working. AFAIK, Oracle still doesn't do table-level recovery, the lowest granularity is tablespace. I could be wrong. Also, exports are good at letting you clone users and application schemas --- Kevin Lange <[EMAIL PROTECTED]> wrote: > If you truely mean that ALL of your databases are in ArchiveLog Mode, > why > would you do that to your Test and Dev databases ? > > -Original Message- > Sent: Friday, May 24, 2002 2:33 PM > To: Multiple recipients of list ORACLE-L > > > my backup strategy, fwiw: > > prod - cold monthly, hot 2x week, exp weekly. > test - cold, hot, exp occassional, always can refresh from prod. > dev - cold & hot occassional, exp daily. > > all dbs are in archivelogmode! > > gene > > >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> > lets not forget the classic "exp". > > 1. Production database (where you can't lose a single > transaction) - ARCHIVEMODE absolutely > > 2. Development database (few hrs of transactions ok to > lose) - cold backups > > 3. Development database (no schema changes, say an > application is being developed with a tool such as > using Oracle designer) - a simple 'exp un/pwd' of the > user, is the simplest, quickest, lightest, least > headache,... may also be considered. > > Keith > > > > Date: Fri, 24 May 2002 09:12:02 -0800 > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Reply-to: [EMAIL PROTECTED] > Address | Add to Address Book > Organization: Fat City Network Services, San Diego, > California > > > Hi Tim and Connor, > > Thanks you all for your very helpful feedback. I do > appreciate it very much. In fact, we are in > development at this point, so the database is small > and transaction volume is very low. Therefore, my > choice for primary backup method is the cold backups. > However, to safeguard against unsual things, which > might happen to the database, I will take your advice > to run my database in ARCHIVELOG mode. The hot backup > will be used. Again, thanks for your very quick > responses. > > Regards, > > Trang > > Tim Gorman <[EMAIL PROTECTED]> wrote: > > Trang, > > Theoretically, the online redo log files are be > necessary, but the world has a habit of making a > shambles of the theoretical. Let's say, in the event > that you automate your Friday script, you'll probably > come to realize that SHUTDOWN IMMEDIATE is far from > perfect (as well as far from immediate!). Over time, > you'll probably construct some kind of "fail-safe" > mechanism to SHUTDOWN ABORT if the initial SHUTDOWN > IMMEDIATE doesn't shut down after a period of time. > Pretty standard thing that DBAs have been writing for > years. Hopefully, after the SHUTDOWN ABORT they also > STARTUP RESTRICT and then SHUTDOWN NORMAL, but you > can't count on it... > > So, here's the point: what if you take a cold backup > in NOARCHIVELOG mode after a SHUTDOWN ABORT (that > should have been a SHUTDOWN IMMEDIATE and wasn't) and > you have *not* backed up those online redo log files? > Answer: unusable backup. So, back up everything: all > datafiles, controlfiles, and online redo logfiles. > The latter are not too big anyway -- what's the point > of excluding them? > > It is wise to take a cold backup after a clean > shutdown, but you can even get a valid backup after a > SHUTDOWN ABORT or a crash if you've backed up the > online redo archive log files. When you restart > Oracle, an instance recovery will occur automatically, > and you might not even know it. Just be certain that > the instance is truly "dead" when you take your "cold" > backup... > > With regards to switching between ARCHIVELOG and > NOARCHIVELOG, it's a waste of effort from a > recoverability standpoint. At most it may be > interesting, but as soon as you switch out of > ARCHIVELOG mode, nothing you've done while in > ARCHIVELOG mode is valid anymore. Leave it one way or > the other, and then leave it... > > ...just my $0.02... > > Another $0.02: use RMAN for your cold backups. Then > you won't forget anything, because RMAN will remember > for you... > > Hope this helps... > > -Tim > - Original Message - > To: Multiple recipients of list ORACLE-L > Sent: Thursday, May 23, 2002 5:33 PM > > > Hi All, > > I need to perform a consistent backup for my whole > database every Friday by using operating system > utilities. My database has been currently operating > in NOARCHIVELOG mode, so the only files need to be > backed up are datafiles, control files, the > initialization parameter file and other oracle product > initialization files (Based on Oracle8.1.6 Backup and > Recovery Guide). Since the files in this type of > backup are all consistent and do not need recovery, so > the online logs are not needed. Since online redo > logs is very crucial for recovery, so my question is > do I need to back up the on
RE: Database backup question.Thank You
Allright, My response wasn't meant to confuse you. I will try to do better. a cold backup -- by definition taken when your database is shutdown. The shutdown process is normal. If you shutdown using shutdown immediate or abort you will need your online redo logs in order to recover from this backup. It is generally recommended that you startup restrict the database after this and then shutdown normal. This will ensure you have a "clean" backup. The cold backup should include your data files, control files, your archived redo logs(if in archivelog mode), and your online redo logs. Again your online redo logs aren't technically required, but wouldn't we all feel stupid if being technically correct cost us a clean recovery, so back them up as well. ok now hot backups. Hot backups are made with your database up and running. This kind of backup requires you to place your tablespaces in backup mode. This can be done manually or through scripting. Your database is required to be in archivelog mode, in order for this kind of backup scheme to work. Since your database is up and running while it's files are being copied, it is by definition an inconsistent backup. Oracle will use archived logs to recover from this kind of backup. As I said before you can place your DB in archivelog mode and back it up cold. If fact I would recommend doing it this way for a while, until you are comfortable with the backup/recover process. I highly recommend you check into Rama Veluri's "Oracle Backup and Recovery Handbook". You should at least refer to the Oracle Backup and Recovery guide. Good Luck, Steve McCLure -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Meomeo NguyenSent: Friday, May 24, 2002 1:54 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Database backup question.Thank You Thank you all for your responses. You all really confused me about this issue. The whole database backups can be consistent or inconsistent. To perform a consistent whole databse backup is the only valid backup option for databases running in NOARCHIVELOG mode. In contrast, an inconsistent backup is valid if the database is running in ARCHIVELOG mode. So, my question is why having my databse running in the ARCHIVELOG mode, I can still perform both consistent and inconsistent backup options? Steve posted : "I don't understandy why you would switch to and from archivelog mode. You can establish a complete cold backup with your datafiles, control files, and online redo logs. Actually you don't need the online redo logs, but that used to be the case so I always back those up as well. Shut your DB down (normal), and back these files up. That is a complete cold backup." Steve, I switch to and from archivelog mode because the best way to back up the contents of the current online redo log is always to archive it, then back up the archived logs. Again, I do appreciate you all for your help. I do need very clear picture about this issue. Since I have not done anything like this before so I do need your feedback badly. Thanks in advance Trang
Re: Database backup question.Thank You
Meomeo, Your confusion will be eliminated by acquiring 'Oracle Backup and Recovery Handbook' by Rama Velpuri. Read it: do all of the examples. There is *no* other way. Jared On Friday 24 May 2002 13:53, Meomeo Nguyen wrote: > Thank you all for your responses. You all really confused me about this > issue. The whole database backups can be consistent or inconsistent. To > perform a consistent whole databse backup is the only valid backup option > for databases running in NOARCHIVELOG mode. In contrast, an inconsistent > backup is valid if the database is running in ARCHIVELOG mode. So, my > question is why having my databse running in the ARCHIVELOG mode, I can > still perform both consistent and inconsistent backup options? Steve posted > : > "I don't understandy why you would switch to and from archivelog mode. You > can establish a complete cold backup with your datafiles, control files, > and online redo logs. Actually you don't need the online redo logs, but > that used to be the case so I always back those up as well. Shut your DB > down (normal), and back these files up. That is a complete cold backup." > Steve, I switch to and from archivelog mode because the best way to back up > the contents of the current online redo log is always to archive it, then > back up the archived logs. Again, I do appreciate you all for your help. > I do need very clear picture about this issue. Since I have not done > anything like this before so I do need your feedback badly. Thanks in > advance > Trang > > Gene Sais <[EMAIL PROTECTED]> wrote: my backup strategy, fwiw: > > prod - cold monthly, hot 2x week, exp weekly. > test - cold, hot, exp occassional, always can refresh from prod. > dev - cold & hot occassional, exp daily. > > all dbs are in archivelogmode! > > gene > > >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> > > lets not forget the classic "exp". > > 1. Production database (where you can't lose a single > transaction) - ARCHIVEMODE absolutely > > 2. Development database (few hrs of transactions ok to > lose) - cold backups > > 3. Development database (no schema changes, say an > application is being developed with a tool such as > using Oracle designer) - a simple 'exp un/pwd' of the > user, is the simplest, quickest, lightest, least > headache,... may also be considered. > > Keith > > > > Date: Fri, 24 May 2002 09:12:02 -0800 > To: "Multiple recipients of list ORACLE-L" > > Reply-to: [EMAIL PROTECTED] > Address | Add to Address Book > Organization: Fat City Network Services, San Diego, > California > > > Hi Tim and Connor, > > Thanks you all for your very helpful feedback. I do > appreciate it very much. In fact, we are in > development at this point, so the database is small > and transaction volume is very low. Therefore, my > choice for primary backup method is the cold backups. > However, to safeguard against unsual things, which > might happen to the database, I will take your advice > to run my database in ARCHIVELOG mode. The hot backup > will be used. Again, thanks for your very quick > responses. > > Regards, > > Trang > > Tim Gorman wrote: > > Trang, > > Theoretically, the online redo log files are be > necessary, but the world has a habit of making a > shambles of the theoretical. Let's say, in the event > that you automate your Friday script, you'll probably > come to realize that SHUTDOWN IMMEDIATE is far from > perfect (as well as far from immediate!). Over time, > you'll probably construct some kind of "fail-safe" > mechanism to SHUTDOWN ABORT if the initial SHUTDOWN > IMMEDIATE doesn't shut down after a period of time. > Pretty standard thing that DBAs have been writing for > years. Hopefully, after the SHUTDOWN ABORT they also > STARTUP RESTRICT and then SHUTDOWN NORMAL, but you > can't count on it... > > So, here's the point: what if you take a cold backup > in NOARCHIVELOG mode after a SHUTDOWN ABORT (that > should have been a SHUTDOWN IMMEDIATE and wasn't) and > you have *not* backed up those online redo log files? > Answer: unusable backup. So, back up everything: all > datafiles, controlfiles, and online redo logfiles. > The latter are not too big anyway -- what's the point > of excluding them? > > It is wise to take a cold backup after a clean > shutdown, but you can even get a valid backup after a > SHUTDOWN ABORT or a crash if you've backed up the > online redo archive log files. When you restart > Oracle, an instance recovery will occur automatically, > and you might not even know it. Just be certain that > the instance is truly "dead" when you take your "cold" > backup... > > With regards to switching between ARCHIVELOG and > NOARCHIVELOG, it's a waste of effort from a > recoverability standpoint. At most it may be > interesting, but as soon as you switch out of > ARCHIVELOG mode, nothing you've done while in > ARCHIVELOG mode is valid anymore. Leave it one way or > the other, and then leave it... > > ...just my $0.02... > > Another $0.02: use RMAN for your col
RE: Database backup question.Thank You
b/c the dba has to develop and test on occassion :). i like all dbs the same. oracle is much friendlier to various recoveries in archivelogmode. besides, i generate < .01% of the archives i generate in prod. i can afford a few mb. again, this is my strategy, one of many. whether its best or worst, its the one i feel most comfortable w/. hth, gene ps. exports are very impt to developers. its the easiest way to restore 1 object. >>> [EMAIL PROTECTED] 05/24/02 04:03PM >>> If you truely mean that ALL of your databases are in ArchiveLog Mode, why would you do that to your Test and Dev databases ? -Original Message- Sent: Friday, May 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L my backup strategy, fwiw: prod - cold monthly, hot 2x week, exp weekly. test - cold, hot, exp occassional, always can refresh from prod. dev - cold & hot occassional, exp daily. all dbs are in archivelogmode! gene >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> lets not forget the classic "exp". 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to ba
Re: Database backup question.Thank You
Thank you all for your responses. You all really confused me about this issue. The whole database backups can be consistent or inconsistent. To perform a consistent whole databse backup is the only valid backup option for databases running in NOARCHIVELOG mode. In contrast, an inconsistent backup is valid if the database is running in ARCHIVELOG mode. So, my question is why having my databse running in the ARCHIVELOG mode, I can still perform both consistent and inconsistent backup options? Steve posted : "I don't understandy why you would switch to and from archivelog mode. You can establish a complete cold backup with your datafiles, control files, and online redo logs. Actually you don't need the online redo logs, but that used to be the case so I always back those up as well. Shut your DB down (normal), and back these files up. That is a complete cold backup." Steve, I switch to and from archivelog mode because the best way to back up the contents of the current online redo log is always to archive it, then back up the archived logs. Again, I do appreciate you all for your help. I do need very clear picture about this issue. Since I have not done anything like this before so I do need your feedback badly. Thanks in advance Trang Gene Sais <[EMAIL PROTECTED]> wrote: my backup strategy, fwiw:prod - cold monthly, hot 2x week, exp weekly.test - cold, hot, exp occassional, always can refresh from prod.dev - cold & hot occassional, exp daily.all dbs are in archivelogmode!gene>>> [EMAIL PROTECTED] 05/24/02 03:04PM >>>lets not forget the classic "exp".1. Production database (where you can't lose a singletransaction) - ARCHIVEMODE absolutely2. Development database (few hrs of transactions ok tolose) - cold backups3. Development database (no schema changes, say anapplication is being developed with a tool such asusing Oracle designer) - a simple 'exp un/pwd' of theuser, is the simplest, quickest, lightest, leastheadache,... may also be considered.KeithDate: Fri, 24 May 2002 09:12:02 -0800 To! ! : "Multiple recipients of list ORACLE-L"<[EMAIL PROTECTED]>Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego,California Hi Tim and Connor, Thanks you all for your very helpful feedback. I doappreciate it very much. In fact, we are indevelopment at this point, so the database is smalland transaction volume is very low. Therefore, mychoice for primary backup method is the cold backups. However, to safeguard against unsual things, whichmight happen to the database, I will take your adviceto run my database in ARCHIVELOG mode. The hot backupwill be used. Again, thanks for your very quickresponses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]>wrote: Trang,Theoretically, the online redo log files are benecessary, but the world has a habit of making ashambles of the theoretical. Let's say, in the e! ! ventthat you automate your Friday script, you'll probablycome to realize that SHUTDOWN IMMEDIATE is far fromperfect (as well as far from immediate!). Over time,you'll probably construct some kind of "fail-safe"mechanism to SHUTDOWN ABORT if the initial SHUTDOWNIMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing foryears. Hopefully, after the SHUTDOWN ABORT they alsoSTARTUP RESTRICT and then SHUTDOWN NORMAL, but youcan't count on it...So, here's the point: what if you take a cold backupin NOARCHIVELOG mode after a SHUTDOWN ABORT (thatshould have been a SHUTDOWN IMMEDIATE and wasn't) andyou have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: alldatafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the pointof excluding them?It is wise to take a cold backup afte! ! r a cleanshutdown, but you can even get a valid backup after aSHUTDOWN ABORT or a crash if you've backed up theonline redo archive log files. When you restartOracle, an instance recovery will occur automatically,and you might not even know it. Just be certain thatthe instance is truly "dead" when you take your "cold"backup...With regards to switching between ARCHIVELOG andNOARCHIVELOG, it's a waste of effort from arecoverability standpoint. At most it may beinteresting, but as soon as you switch out ofARCHIVELOG mode, nothing you've done while inARCHIVELOG mode is valid anymore. Leave it one way orthe other, and then leave it..just my $0.02...Another $0.02: use RMAN for your cold backups. Thenyou won't forget anything, because RMAN will rememberfor you...Hope this helps...-Tim- Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, Ma! ! y 23, 2002 5:33 PMHi All,I need to perform a consistent backup for my wholedatabase every Friday by using operating systemutilities. My database has been currently operatingin NOARCHIVELOG mode, so the only files need to bebacked up are datafiles, control files, theinitialization parameter file and
Re: Database backup question.Thank You
Kevin Lange wrote: > > If you truely mean that ALL of your databases are in ArchiveLog Mode, why > would you do that to your Test and Dev databases ? > > -Original Message- > Sent: Friday, May 24, 2002 2:33 PM > To: Multiple recipients of list ORACLE-L > > my backup strategy, fwiw: > > prod - cold monthly, hot 2x week, exp weekly. > test - cold, hot, exp occassional, always can refresh from prod. > dev - cold & hot occassional, exp daily. > > all dbs are in archivelogmode! > > gene > It's sound practice to test that you can use your backups to recover. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Database backup question.Thank You
If you truely mean that ALL of your databases are in ArchiveLog Mode, why would you do that to your Test and Dev databases ? -Original Message- Sent: Friday, May 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L my backup strategy, fwiw: prod - cold monthly, hot 2x week, exp weekly. test - cold, hot, exp occassional, always can refresh from prod. dev - cold & hot occassional, exp daily. all dbs are in archivelogmode! gene >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> lets not forget the classic "exp". 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to back up the whole database: after the database was closed cleanly and all the above mentioned files had been backed up into the tape. I had to restart the database and mount but not open, then switched between NOARCHIVELOG mode to ARCHIVELOG mode in order to archive the online redo log files. Finally, I copied all archived redo log files into the tape while the database was open and operated in ARCHIVELOG mode. when it was all done, I then switched the database back to NOARCHIVELO
Re: Database backup question.Thank You
my backup strategy, fwiw: prod - cold monthly, hot 2x week, exp weekly. test - cold, hot, exp occassional, always can refresh from prod. dev - cold & hot occassional, exp daily. all dbs are in archivelogmode! gene >>> [EMAIL PROTECTED] 05/24/02 03:04PM >>> lets not forget the classic "exp". 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to back up the whole database: after the database was closed cleanly and all the above mentioned files had been backed up into the tape. I had to restart the database and mount but not open, then switched between NOARCHIVELOG mode to ARCHIVELOG mode in order to archive the online redo log files. Finally, I copied all archived redo log files into the tape while the database was open and operated in ARCHIVELOG mode. when it was all done, I then switched the database back to NOARCHIVELOG mode. Just wondered whether my procedure to perform a whole consistent database backup is correct? Am I safe to this point? Your help is greatly appreciated it. Your help is greatly appreciated. Thanks in advance, Trang ___
Re: Database backup question.Thank You
lets not forget the classic "exp". 1. Production database (where you can't lose a single transaction) - ARCHIVEMODE absolutely 2. Development database (few hrs of transactions ok to lose) - cold backups 3. Development database (no schema changes, say an application is being developed with a tool such as using Oracle designer) - a simple 'exp un/pwd' of the user, is the simplest, quickest, lightest, least headache,... may also be considered. Keith Date: Fri, 24 May 2002 09:12:02 -0800 To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Reply-to: [EMAIL PROTECTED] Address | Add to Address Book Organization: Fat City Network Services, San Diego, California Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to back up the whole database: after the database was closed cleanly and all the above mentioned files had been backed up into the tape. I had to restart the database and mount but not open, then switched between NOARCHIVELOG mode to ARCHIVELOG mode in order to archive the online redo log files. Finally, I copied all archived redo log files into the tape while the database was open and operated in ARCHIVELOG mode. when it was all done, I then switched the database back to NOARCHIVELOG mode. Just wondered whether my procedure to perform a whole consistent database backup is correct? Am I safe to this point? Your help is greatly appreciated it. Your help is greatly appreciated. Thanks in advance, Trang __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Keith Peterson INET: [EMAIL PROTECTED] Fat City Network S
Re: Database backup question.Thank You
Good choice...archivelog mode will make your life as a DBA much easier. Have a look at doing hot backups with rman. Regards, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, May 24, 2002 1:12 PM > > Hi Tim and Connor, > Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. > Regards, > Trang > Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and ! > ! > online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim- Original Message - From: Meomeo! > ! > Nguyen To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PMSubject: Database backup question. > > Hi All, > > I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to back up the whole database: > > after the database was closed cleanly and all the above mentioned files had been backed up into the tape. I had to restart the database and mount but not open, then switched between NOARCHIVELOG mode to ARCHIVELOG mode in order to archive the online redo log files. Finally, I copied all archived redo log files into the tape while the database was open and operated in ARCHIVELOG mode. when it was all done, I then switched the database back to NOARCHIVELOG mode. Just wondered whether my procedure to perform a whole consistent database backup is correct? Am I safe to this point? Your help is greatly appreciated it. Your help is greatly appreciated. > > Thanks in advance, > > Trang > > > > - > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience > > > - > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListG
Re: Database backup question.Thank You
Hi Tim and Connor, Thanks you all for your very helpful feedback. I do appreciate it very much. In fact, we are in development at this point, so the database is small and transaction volume is very low. Therefore, my choice for primary backup method is the cold backups. However, to safeguard against unsual things, which might happen to the database, I will take your advice to run my database in ARCHIVELOG mode. The hot backup will be used. Again, thanks for your very quick responses. Regards, Trang Tim Gorman <[EMAIL PROTECTED]> wrote: Trang, Theoretically, the online redo log files are be necessary, but the world has a habit of making a shambles of the theoretical. Let's say, in the event that you automate your Friday script, you'll probably come to realize that SHUTDOWN IMMEDIATE is far from perfect (as well as far from immediate!). Over time, you'll probably construct some kind of "fail-safe" mechanism to SHUTDOWN ABORT if the initial SHUTDOWN IMMEDIATE doesn't shut down after a period of time. Pretty standard thing that DBAs have been writing for years. Hopefully, after the SHUTDOWN ABORT they also STARTUP RESTRICT and then SHUTDOWN NORMAL, but you can't count on it... So, here's the point: what if you take a cold backup in NOARCHIVELOG mode after a SHUTDOWN ABORT (that should have been a SHUTDOWN IMMEDIATE and wasn't) and you have *not* backed up those online redo log files? Answer: unusable backup. So, back up everything: all datafiles, controlfiles, and online redo logfiles. The latter are not too big anyway -- what's the point of excluding them? It is wise to take a cold backup after a clean shutdown, but you can even get a valid backup after a SHUTDOWN ABORT or a crash if you've backed up the online redo archive log files. When you restart Oracle, an instance recovery will occur automatically, and you might not even know it. Just be certain that the instance is truly "dead" when you take your "cold" backup... With regards to switching between ARCHIVELOG and NOARCHIVELOG, it's a waste of effort from a recoverability standpoint. At most it may be interesting, but as soon as you switch out of ARCHIVELOG mode, nothing you've done while in ARCHIVELOG mode is valid anymore. Leave it one way or the other, and then leave it... ...just my $0.02... Another $0.02: use RMAN for your cold backups. Then you won't forget anything, because RMAN will remember for you... Hope this helps... -Tim - Original Message - From: Meomeo Nguyen To: Multiple recipients of list ORACLE-L Sent: Thursday, May 23, 2002 5:33 PM Subject: Database backup question. Hi All, I need to perform a consistent backup for my whole database every Friday by using operating system utilities. My database has been currently operating in NOARCHIVELOG mode, so the only files need to be backed up are datafiles, control files, the initialization parameter file and other oracle product initialization files (Based on Oracle8.1.6 Backup and Recovery Guide). Since the files in this type of backup are all consistent and do not need recovery, so the online logs are not needed. Since online redo logs is very crucial for recovery, so my question is do I need to back up the online redo log files as I choose to perform cold backup type for my entire database weekly? Here is step by step what I did to back up the whole database: after the database was closed cleanly and all the above mentioned files had been backed up into the tape. I had to restart the database and mount but not open, then switched between NOARCHIVELOG mode to ARCHIVELOG mode in order to archive the online redo log files. Finally, I copied all archived redo log files into the tape while the database was open and operated in ARCHIVELOG mode. when it was all done, I then switched the database back to NOARCHIVELOG mode. Just wondered whether my procedure to perform a whole consistent database backup is correct? Am I safe to this point? Your help is greatly appreciated it. Your help is greatly appreciated. Thanks in advance, Trang Do You Yahoo!?LAUNCH - Your Yahoo! Music ExperienceDo You Yahoo!? LAUNCH - Your Yahoo! Music Experience
