RE: Change based recovery
Thank you all for your suggestions. I really have no time for downtime during the week, but we have space to keep the archived logs on disk but not a backup (even a partial hot backup) of the database unless I move it to a tape, which is too much time and resource consuming, so here is what I've been doing until now: - We make a cold backup + verify every weekend, fortunately we are allowed to stop the database on Saturdays and Sundays. - We backup all archived logs every night during the week + exports of the tables to a disk on the server, then copy them to a tape. We never delete them until a new cold backup is performed, this way I have all the archived logs copied several times in several tapes every week. I have at least two sets of tapes (15 tapes counting them all) which I reuse every 2 weeks, so I keep 2 verified cold backups + their archived logs for 2 weeks, which has proved enough for now. We store them in a safety fire proof case in a different building. As for RMAN I've never tried it, do you think it could help me in any way considering my backup strategy? Thanks! Fermin. -Mensaje original- De: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Enviado el: lunes, 11 de agosto de 2003 16:40 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin - And it is always a good idea to keep at least the previous backup to the last. Often this just costs an extra tape, but could be very useful in the unlikely case there is something wrong with the most recent back. With backing up once/week, realize that in a recovery situation, if Oracle cannot read a single archived redo log, recovery stops at that point. Also, if you are considering changing your backup strategy, you should consider RMAN. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 6:24 AM To: Multiple recipients of list ORACLE-L I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to alter database backup controlfile to 'filename'; , use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the alter database open resetlogs. One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- Sent: Thursday, August 07, 2003 8:21 AM To: Multiple recipients of list ORACLE-L Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size126925024 bytes Database Buffers 131072000 bytes Redo Buffers262144 bytes Database mounted. SVRMGR recover database until change 10349; Media recovery complete. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS
RE: Change based recovery
Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message-From: Dobson, Lisa [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 8:21 AMTo: Multiple recipients of list ORACLE-LSubject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mountORACLE instance started.Total System Global Area 258304260 bytesFixed Size 45092 bytesVariable Size 126925024 bytesDatabase Buffers 131072000 bytesRedo Buffers 262144 bytesDatabase mounted.SVRMGR recover database until change 10349;Media recovery complete.
RE: Change based recovery
Fermin Thanks for explaining your situation in more detail. I had a production database in a similar configuration for a couple of years. Just make sure to communicate the risks to everyone involved. The main risk I see is that you are very dependent on the archive logs to bring your database up to date. How much this matters will depend on your application. If users can re-enter data, then the risk is small. If there is no way to recreate the transactions, then the risk is greater. If the risk could be mitigated with more disk space, then this can be a pretty strong selling point to management if it was presented in a way that they could understand it. The main advantage RMAN could bring to your situation is the ability to perform incremental backups during the week. Only the changed blocks are backed up so for many databases, a much smaller file is produced. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 12, 2003 4:24 AM To: Multiple recipients of list ORACLE-L Thank you all for your suggestions. I really have no time for downtime during the week, but we have space to keep the archived logs on disk but not a backup (even a partial hot backup) of the database unless I move it to a tape, which is too much time and resource consuming, so here is what I've been doing until now: - We make a cold backup + verify every weekend, fortunately we are allowed to stop the database on Saturdays and Sundays. - We backup all archived logs every night during the week + exports of the tables to a disk on the server, then copy them to a tape. We never delete them until a new cold backup is performed, this way I have all the archived logs copied several times in several tapes every week. I have at least two sets of tapes (15 tapes counting them all) which I reuse every 2 weeks, so I keep 2 verified cold backups + their archived logs for 2 weeks, which has proved enough for now. We store them in a safety fire proof case in a different building. As for RMAN I've never tried it, do you think it could help me in any way considering my backup strategy? Thanks! Fermin. -Mensaje original- De: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Enviado el: lunes, 11 de agosto de 2003 16:40 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin - And it is always a good idea to keep at least the previous backup to the last. Often this just costs an extra tape, but could be very useful in the unlikely case there is something wrong with the most recent back. With backing up once/week, realize that in a recovery situation, if Oracle cannot read a single archived redo log, recovery stops at that point. Also, if you are considering changing your backup strategy, you should consider RMAN. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 6:24 AM To: Multiple recipients of list ORACLE-L I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to alter database backup controlfile to 'filename'; , use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the alter database open resetlogs. One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- Sent: Thursday
RE: Change based recovery
Fermin, Yes, It can be done!! Just with your cold backup and your archive logs. Basics: 1) Get this straight - A cold backup does not require any kind of recovery. When you restore a cold backup your DB will be old but will NOT require any recovery. 2) If you want to bring a old database to current time, you have to apply the archive logs. In case of a cold backup you cannot (read till the end) do it as your control files do not recognize newer archive logs generated after the backup was taken. The Work around is to re-create the control file using the create control file script (see below) and then recover the database using the command RECOVER DATABASE USING BACKUP CONTROL FILE, This command will ask you for the archive logs; on supplying the archive logs, the database will be recovered further. 3) In case of a hot backup the backup is in-consistent. So after restoring a backup it WILL ask for recovery which will happen from the archive logs. This is same as the above but there is no Downtime involved in hot backups. Creating control file: The script to create the control file can be generated using the following command: SQL ALTER DATABASE BACKUP CONTROL FILE TO TRACE; This will create a .trc file in the UDUMP directory of the database. You will have to edit it before running to create the control file. Browse thru Metalink for more info on this. Hope this helps! Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 6:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery Well I am quite de-motivated actually!! but at least it is good to know I was (partially) wrong and that I will feel safer after reading your comments, thanks! I am really in doubt now, but I remember when we were testing we did recover all datafiles (the ones that are stated in the v$datafile table) from a cold backup exceptcontrolfiles and redolog files; we were able to restore the whole database with the commands I wrote down in my first message. If I am still wrong, will you please be kind enough to tell me which are the exact commands needed to recover the whole database from a cold backupif I have no online backups and I lose everything except for the archived logs? can it really be done? Thank you so much! Fermin. -Mensaje original- De: Venu Gopal [mailto:[EMAIL PROTECTED] Enviado el: lunes, 11 de agosto de 2003 13:44 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin, I dont want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as its a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if its a cold DB backup and recover the DB using BACKUP CONTROL FILE option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado
RE: Change based recovery
Well I am quite de-motivated actually!! but at least it is good to know I was (partially) wrong and that I will feel safer after reading your comments, thanks! I am really in doubt now, but I remember when we were testing we did recover all datafiles (the ones that are stated in the v$datafile table) from a cold backup exceptcontrolfiles and redolog files; we were able to restore the whole database with the commands I wrote down in my first message. If I am still wrong, will you please be kind enough to tell me which are the exact commands needed to recover the whole database from a cold backupif I have no online backups and I lose everything except for the archived logs? can it really be done? Thank you so much! Fermin. -Mensaje original-De: Venu Gopal [mailto:[EMAIL PROTECTED]Enviado el: lunes, 11 de agosto de 2003 13:44Para: Multiple recipients of list ORACLE-LAsunto: RE: Change based recovery Fermin, I dont want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as its a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if its a cold DB backup and recover the DB using BACKUP CONTROL FILE option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin BernausSent: Monday, August 11, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original-De: Hand, Michael T [mailto:[EMAIL PROTECTED]Enviado el: viernes, 08 de agosto de 2003 18:10Para: Multiple recipients of list ORACLE-LAsunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to "alter database backup controlfile to 'filename'; ", use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the "alter database open resetlogs". One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message-From: Dobson, Lisa [mailto:[EMAIL PROTECTED]Sent: Thursday, August 07, 2003 8:21 AMTo: Multiple recipients of list ORACLE-LSubject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Fri
RE: Change based recovery
I've been reading your messages with much interest. I have
some experience with database administration and I have done many tests, but
I've not tried what I am going to expose in this message, maybe you can help. We
do cold backups on a regular basis (every weekend) then just backup the archive
log every day, then delete them every time a new cold backup is done. We have
tested it and if all database files (parameters file, datafiles, control files)
except for one control file and the archived logs were lost we could recover the
entire database issuing the following commands after restoring all missing files
and mounting the database:
SET AUTORECOVERY ON
RECOVER DATABASE
ALTER DATABASE OPEN
My questions are:
1 - Could a complete restore be done even if we lost
ALL control files? can we recover the entire database from a cold backup
provided we have all archived logs until the failure time?
2 - If the answer is yes, what is the advantage of doing
on-line backups of datafiles and control files?
Thanks for your answers, I always learn so much from this
list!!
Fermin.
-Mensaje original-De: Hand, Michael T
[mailto:[EMAIL PROTECTED]Enviado el: viernes, 08 de agosto de
2003 18:10Para: Multiple recipients of list
ORACLE-LAsunto: RE: Change based recovery
Lisa,
The
3rd option (besides shuting down source database and using a controlfile
trace) is to "alter database backup controlfile to 'filename'; ", use this
file, then proceed with the recovery as Venu suggests. I've used this
method on a hot backup to roll the database forward. Also, don't bother
restoring the redo logs as you will be overwriting / recreating them with the
"alter database open resetlogs". One more thing I noticed. Your
until change number looks to me like an archive sequence number rather than
the SCN it needs to be. Hope this helps.
Mike
Hand
-Original Message-From: Dobson, Lisa
[mailto:[EMAIL PROTECTED]Sent: Thursday, August 07,
2003 8:21 AMTo: Multiple recipients of list
ORACLE-LSubject: Change based recovery
Hi Guys and Gals,
We are currently doing
some testing to enable us to move our production database from one unix box
to another.
We are running a 7.3.4
db in archivelog mode. The approach that management want to use is to
restore the database on the new server from a backup and then roll it
forward using the archived redo logs.
I have a full cold back
up from last Friday. I have restored the datafiles, controlfiles and redo
logs onto our test server from the backup tape, and then ftp'd the archived
logs over.
I then do -
SVRMGR startup
mountORACLE instance started.Total System Global
Area 258304260 bytesFixed
Size
45092 bytesVariable
Size
126925024 bytesDatabase
Buffers
131072000 bytesRedo
Buffers
262144 bytesDatabase mounted.SVRMGR recover database until
change 10349;Media recovery complete.
RE: Change based recovery
Fermin, I dont want to de-motivate you, but I really doubt whether your backup strategy really works. The command that you have mentioned below will NOT do a complete recovery as its a cold backup. As for your questions: 1) You can recover your entire database in either case (Cold or Hot), If you have your archive logs. Difference being, You have recreate your control file if its a cold DB backup and recover the DB using BACKUP CONTROL FILE option. 2) Lets look at it the other way; you do NOT need any downtime for Hot backups while you need downtime for Cold backups. Downtime could be very expensive depending on the type of database. Secondly, you can take a hot backup very frequently as it does not involve any downtime. Recent backup means less recovery is required and less time to bring up the database. Let me know if you need anymore info. Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fermin Bernaus Sent: Monday, August 11, 2003 4:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Change based recovery I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to alter database backup controlfile to 'filename'; , use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the alter database open resetlogs. One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- From: Dobson, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 8:21 AM To: Multiple recipients of list ORACLE-L Subject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size 126925024 bytes Database Buffers 131072000 bytes Redo Buffers 262144 bytes Database mounted. SVRMGR recover database until change 10349; Media recovery complete. **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
RE: Change based recovery
Well I did not tell you that I am regularly backing up all archive logs during the day as well, so that I am not dependant of just one backup made at night, just in case all archived logs were lost because no, we can not afford all people be entering last day's data again, we don't keep trace of most of them and we would have so many problems I don't want to think about that situation! Thanks to your message I am considering using RMAN :) maybe I'll go for it after summer, if I have the time... thanks again! I suppose it has got the ability to make both incremental and differential backups, but please do not waste your time with me any more, I'll read the manuals when the time arrives! Regards, .. FermÃn Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Enviado el: martes, 12 de agosto de 2003 16:39 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin Thanks for explaining your situation in more detail. I had a production database in a similar configuration for a couple of years. Just make sure to communicate the risks to everyone involved. The main risk I see is that you are very dependent on the archive logs to bring your database up to date. How much this matters will depend on your application. If users can re-enter data, then the risk is small. If there is no way to recreate the transactions, then the risk is greater. If the risk could be mitigated with more disk space, then this can be a pretty strong selling point to management if it was presented in a way that they could understand it. The main advantage RMAN could bring to your situation is the ability to perform incremental backups during the week. Only the changed blocks are backed up so for many databases, a much smaller file is produced. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 12, 2003 4:24 AM To: Multiple recipients of list ORACLE-L Thank you all for your suggestions. I really have no time for downtime during the week, but we have space to keep the archived logs on disk but not a backup (even a partial hot backup) of the database unless I move it to a tape, which is too much time and resource consuming, so here is what I've been doing until now: - We make a cold backup + verify every weekend, fortunately we are allowed to stop the database on Saturdays and Sundays. - We backup all archived logs every night during the week + exports of the tables to a disk on the server, then copy them to a tape. We never delete them until a new cold backup is performed, this way I have all the archived logs copied several times in several tapes every week. I have at least two sets of tapes (15 tapes counting them all) which I reuse every 2 weeks, so I keep 2 verified cold backups + their archived logs for 2 weeks, which has proved enough for now. We store them in a safety fire proof case in a different building. As for RMAN I've never tried it, do you think it could help me in any way considering my backup strategy? Thanks! Fermin. -Mensaje original- De: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Enviado el: lunes, 11 de agosto de 2003 16:40 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Fermin - And it is always a good idea to keep at least the previous backup to the last. Often this just costs an extra tape, but could be very useful in the unlikely case there is something wrong with the most recent back. With backing up once/week, realize that in a recovery situation, if Oracle cannot read a single archived redo log, recovery stops at that point. Also, if you are considering changing your backup strategy, you should consider RMAN. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 6:24 AM To: Multiple recipients of list ORACLE-L I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could
RE: Change based recovery
Fermin - And it is always a good idea to keep at least the previous backup to the last. Often this just costs an extra tape, but could be very useful in the unlikely case there is something wrong with the most recent back. With backing up once/week, realize that in a recovery situation, if Oracle cannot read a single archived redo log, recovery stops at that point. Also, if you are considering changing your backup strategy, you should consider RMAN. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 11, 2003 6:24 AM To: Multiple recipients of list ORACLE-L I've been reading your messages with much interest. I have some experience with database administration and I have done many tests, but I've not tried what I am going to expose in this message, maybe you can help. We do cold backups on a regular basis (every weekend) then just backup the archive log every day, then delete them every time a new cold backup is done. We have tested it and if all database files (parameters file, datafiles, control files) except for one control file and the archived logs were lost we could recover the entire database issuing the following commands after restoring all missing files and mounting the database: SET AUTORECOVERY ON RECOVER DATABASE ALTER DATABASE OPEN My questions are: 1 - Could a complete restore be done even if we lost ALL control files? can we recover the entire database from a cold backup provided we have all archived logs until the failure time? 2 - If the answer is yes, what is the advantage of doing on-line backups of datafiles and control files? Thanks for your answers, I always learn so much from this list!! Fermin. -Mensaje original- De: Hand, Michael T [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 18:10 Para: Multiple recipients of list ORACLE-L Asunto: RE: Change based recovery Lisa, The 3rd option (besides shuting down source database and using a controlfile trace) is to alter database backup controlfile to 'filename'; , use this file, then proceed with the recovery as Venu suggests. I've used this method on a hot backup to roll the database forward. Also, don't bother restoring the redo logs as you will be overwriting / recreating them with the alter database open resetlogs. One more thing I noticed. Your until change number looks to me like an archive sequence number rather than the SCN it needs to be. Hope this helps. Mike Hand -Original Message- Sent: Thursday, August 07, 2003 8:21 AM To: Multiple recipients of list ORACLE-L Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size126925024 bytes Database Buffers 131072000 bytes Redo Buffers262144 bytes Database mounted. SVRMGR recover database until change 10349; Media recovery complete. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Change based recovery
Hi Guys and Gals,
We are currently doing some
testing to enable us to move our production database from one unix box to
another.
We are running a 7.3.4 db
in archivelog mode. The approach that management want to use is to restore
the database on the new server from a backup and then roll it forward using the
archived redo logs.
I have a full cold back up
from last Friday. I have restored the datafiles, controlfiles and redo logs onto
our test server from the backup tape, and then ftp'd the archived logs
over.
I then do -
SVRMGR startup
mountORACLE instance started.Total System Global
Area 258304260 bytesFixed
Size
45092 bytesVariable
Size
126925024 bytesDatabase
Buffers
131072000 bytesRedo
Buffers
262144 bytesDatabase mounted.SVRMGR recover database until change
10349;Media recovery complete.
I would have expected it to
display the names of the logs, butit
doesn't, and when I check the alert log it shows 'No Media Recovery
required'.
Where am I going wrong? I can't understand why it won't
apply the archived logs. (Too hot today and brain not working
properly!)
TIA.
Lisa Dobson Database Analyst Home Group Ltd This message is intended only for the use of the person(s) ("Intended Recipient") to whom it is addressed. It may contain information, which is privileged and confidential. Accordingly any dissemination, distribution, copying or other use of this message or any of its content by any person other than the Intended Recipient may constitute a breach of civil or criminal law and is strictly prohibited. If you are not the Intended Recipient, please contact the sender as soon as possible.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
RE: Change based recovery
Lisa,
Shut
down your production database and FTP the control files over also. This
will make your control files waaayyy ahead of your data files, thus forcing a
recovery.
Right
now, your database does not need recovery - everything is in synch. Your
control files don't know anything about the archive logs because they are set
back to last Friday - thus they cannot move beyond that.
Good
Luck!
Tom Mercadante Oracle Certified Professional
-Original Message-From: Dobson, Lisa
[mailto:[EMAIL PROTECTED]Sent: Thursday, August 07,
2003 8:21 AMTo: Multiple recipients of list
ORACLE-LSubject: Change based recovery
Hi Guys and Gals,
We are currently doing
some testing to enable us to move our production database from one unix box to
another.
We are running a 7.3.4 db
in archivelog mode. The approach that management want to use is to
restore the database on the new server from a backup and then roll it forward
using the archived redo logs.
I have a full cold back
up from last Friday. I have restored the datafiles, controlfiles and redo logs
onto our test server from the backup tape, and then ftp'd the archived logs
over.
I then do -
SVRMGR startup
mountORACLE instance started.Total System Global
Area 258304260 bytesFixed
Size
45092 bytesVariable
Size
126925024 bytesDatabase
Buffers
131072000 bytesRedo
Buffers
262144 bytesDatabase mounted.SVRMGR recover database until change
10349;Media recovery complete.
I would have expected it
to display the names of the logs, butit doesn't, and when I check the alert log it
shows 'No Media Recovery required'.
Where am I going wrong? I can't understand why it
won't apply the archived logs. (Too hot today and brain not working
properly!)
TIA.
Lisa Dobson Database Analyst Home Group Ltd
This
message is intended only for the use of the person(s) ("Intended Recipient")
to whom it is addressed. It may contain information, which is privileged and
confidential. Accordingly any dissemination, distribution, copying or other
use of this message or any of its content by any person other than the
Intended Recipient may constitute a breach of civil or criminal law and is
strictly prohibited. If you are not the Intended Recipient, please contact the
sender as soon as possible.
This
e-mail has been scanned for all viruses by Star Internet. Theservice is
powered by MessageLabs. For more information on a proactiveanti-virus
service working around the clock, around the globe, visit:http://www.star.net.uk
RE: Change based recovery
Hi, When you are restoring from a cold backup you dont have to recover and your database will be old. If you want to bring it to current time then recreate the control file and recover it using RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; this will ask you for archive logs and then you have to supply them. Create the control file from a trace file generated on the running database using the following command: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Cheers! Venu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dobson, Lisa Sent: Thursday, August 07, 2003 5:51 PM To: Multiple recipients of list ORACLE-L Subject: Change based recovery Hi Guys and Gals, We are currently doing some testing to enable us to move our production database from one unix box to another. We are running a 7.3.4 db in archivelog mode. The approach that management want to use is to restore the database on the new server from a backup and then roll it forward using the archived redo logs. I have a full cold back up from last Friday. I have restored the datafiles, controlfiles and redo logs onto our test server from the backup tape, and then ftp'd the archived logs over. I then do - SVRMGR startup mount ORACLE instance started. Total System Global Area 258304260 bytes Fixed Size 45092 bytes Variable Size 126925024 bytes Database Buffers 131072000 bytes Redo Buffers 262144 bytes Database mounted. SVRMGR recover database until change 10349; Media recovery complete. I would have expected it to display the names of the logs, butit doesn't, and when I check the alert log it shows 'No Media Recovery required'. Where am I going wrong? I can't understand why it won't apply the archived logs. (Too hot today and brain not working properly!) TIA. Lisa Dobson Database Analyst Home Group Ltd This message is intended only for the use of the person(s) (Intended Recipient) to whom it is addressed. It may contain information, which is privileged and confidential. Accordingly any dissemination, distribution, copying or other use of this message or any of its content by any person other than the Intended Recipient may constitute a breach of civil or criminal law and is strictly prohibited. If you are not the Intended Recipient, please contact the sender as soon as possible. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
