Hi all,
If I take and full export of my db (logical backup) and also copy all the control files, data files and redo log files (physical), which one of the two methods saves me storage space. And why?
Thanks,
Printable View
Hi all,
If I take and full export of my db (logical backup) and also copy all the control files, data files and redo log files (physical), which one of the two methods saves me storage space. And why?
Thanks,
Export
Export is compressed
A cold backup of all the dB files is the size of the sum of all the files
Export/import is not a backup methodology
marist89 - http://www.dbasupport.com/forums/sho...6&pagenumber=3
I know that export is not a backup method. I just need a figures to justify the need for both physical and logical methods for our production db.
Do you mean copy the redo log files as in a cold backup? Why copy those files? I think every book (well, quite a few of the more authoratative ones anyway) explicity tell you NOT to copy the redo log files in a cold backup. That should save you some space.
In fact, you should copy online redo logs files with cold backup.Quote:
Originally posted by stecal:
Do you mean copy the redo log files as in a cold backup? Why copy those files? I think every book (well, quite a few of the more authoratative ones anyway) explicity tell you NOT to copy the redo log files in a cold backup. That should save you some space.
Sanjay
In fact, the only case when you need to copy online redo log files with cold backup is when your databas was shut down in ABORT mode prior to backup.Quote:
Originally posted by SANJAY_G
In fact, you should copy online redo logs files with cold backup.
Well, what happens if you lose everything (including redo logs) and have to recover from a cold backup?Quote:
Originally posted by jmodic
In fact, the only case when you need to copy online redo log files with cold backup is when your databas was shut down in ABORT mode prior to backup.
The only option you have is, restore datafiles, cotrolfile and open the database with RESETLOGS and you may also have to issue a fake recover command in order to avaoid ORA-01139
And when you open db with RESETLOGS all your previous hot backups will be invalidated.
I would still backup redo logs with cold backup to avoid going through all these hassles.
Sanjay
If you loose everything and have to restore from cold backup (BTW, why would you *have to* restore from cold backup, you could recover from hot backup as well), why would your previous hot backups be invalidated? They are still perfectly valid, no matter if you had to open your database with RESETLOGS. You still can recover to any point in time, both prior to resetlogs (in which case your starting backup would be any hot backup taken prior to RESETLOGS) and after it (in which case you would use any backup taken after the RESETLOGS).
[Edited by jmodic on 10-04-2002 at 05:13 AM]
Sanjay I guess you have not used RMAN for cold backups. RMAN does not backup online redo logs whatsoever, not in cold backups or hot backups.Quote:
Originally posted by SANJAY_G
In fact, you should copy online redo logs files with cold backup.Quote:
Originally posted by stecal:
Do you mean copy the redo log files as in a cold backup? Why copy those files? I think every book (well, quite a few of the more authoratative ones anyway) explicity tell you NOT to copy the redo log files in a cold backup. That should save you some space.
Sanjay
Allow me to disagree here jmodic,
*have to* is conditional of course but it COULD be a possible scenario.
e.g.
The cold backup is the only backup available
or
the hotbackup taken was much earlier than the coldbackup and there was not much (or none) activity after coldbackup and before db crash.
or
the "estimated recovery time" from last available hotbackup is much longer than that from last available coldbackup.
or
The business needs databse immediately available at cost of losing some data.
So in my view it is a possible recovery scenario.
And AFAIK Oracle recommends to take hot or coldbackup immediately after RESETLOGS because the previous backups can not be used to recover upto a point in time after RESETLOGS. (Yes, there is also a complicated procedure for "recovery across resetlogs")
And I think there may be many other solutions to recover BUT wouldn't it be just nice to backup redo logs with coldbackups (which doesn't take much time and space) to avoid all these hassles and recover cleanly from a coldbackup in the scenarios i mentioned above.
Please correct me if I am wrong.
Sanjay
I agree pando that RMAN doesn't backup redo logs whatsoever but I guess (not tested yet) it will recreate the redo logs in case you lose them and restore from a RMAN coldbackup and you need not use RESETLOGS.Quote:
Originally posted by pando:
Sanjay I guess you have not used RMAN for cold backups. RMAN does not backup online redo logs whatsoever, not in cold backups or hot backups.
But in case of traditional OS coldbackup I think the redo logs should be backed up.
Sanjay
well when you open resetlog the redo logs are recreated automatically!
and that´s what RMAN does
If RMAN does RESETLOGS while restoring from coldbackup do we need to issue "reset database" ?Quote:
Originally posted by Pando:
well when you open resetlog the redo logs are recreated automatically!
and that´s what RMAN does
And will it create a new incarnation of the databse in the recovery catalog ?
Just curious, because my understanding is, in RMAN you have to issue "reset databse" after RESETLOGS.
Sanjay
Sanyaj,Quote:
Originally posted by SANJAY_G
And I think there may be many other solutions to recover BUT wouldn't it be just nice to backup redo logs with coldbackups (which doesn't take much time and space) to avoid all these hassles and recover cleanly from a coldbackup in the scenarios i mentioned above.
The point is not in how much space or time it takes to backup redologs as a part of a cold backup, the main point is:
- it could be dangerous to have them with the cold backup
(of course, if DBA knows exectly what he is doing and if we can count on that he/she never makes mistakes, not even when he/she is under extreme pressure which is tipically during recovery occasions, then backuped redo logs mean no harm)
- backed online redo logs are never ever needed for recovery purposes in any recovery scenario
That's why even Oracle manuals suggest not to backup redo logs, not even with cold backups.
And if I might add my personal attitude to this "never include redo logs in cold backup": For occasional cold backups that have very specific purposes (for example for database cloning) and are tipically not stored on any tape for future use, I simply copy redo logs as well. But if I would have to include cold backup in any *periodical* backup scenario (cold backup once a day or once a week or ....) on archlog mode database, I would NEVER include copying redo logs in that scenario. NEVER.
please refer Note:61801.1 on Metalink
Although I don't subscribe to what apearantly is the conventional wisdom about backing up redo logs during a cold backup, I can't really see how it would be dangerous.Quote:
Originally posted by jmodic
- it could be dangerous to have them with the cold backup
(of course, if DBA knows exectly what he is doing and if we can count on that he/she never makes mistakes, not even when he/she is under extreme pressure which is tipically during recovery occasions, then backuped redo logs mean no harm)
[/B]
[opinion]
I don't use cold backups, so maybe I'm just talking from inexperience. Restore/recovery from a cold backup in noarchivelog mode is a simple procedure; restore all your files and bring up the database. Sure, if the redo logs are not there you can recreate them. In fact, if the redo logs are NOT present, I think it makes the restore MORE difficult.
[/opinion]
Jeff, that is exactly where all the danger is hidden!!! Because it is so simple and straightforward! Even very inexperienced DBA knows what he has to do with cold backup when database crashes - he simply hes to restore *everything* from backup and that's it, that's all it has to be done.Quote:
Originally posted by marist89
Restore/recovery from a cold backup in noarchivelog mode is a simple procedure; restore all your files and bring up the database.
Now change just a few things in the scenario and even not so inexperience DBA could make an unrecoverable mistake. Imagine the following:
Database is running in archivelog mode. Hot backup is taken every night except on Sunday night, when it is shut down so that cold backup can be taken once a week. (Now, don't ask me for the ratio behind this backup scenario, but it seems that is not so very unusual for the archivelog database to be backed up periodicaly with cold backup - yes, I know how stupid this actualy is!). On Monday afternoon database crashes bacause of a disk failure and DBA is urged to make it available as soon as posible without any data loss (original redo logs and todays archlogs are intact, so there should be no data loss).
The most recent backup is the cold backup from last night and DBA knows exactly what he has to do with cold backup. After all, everybody knows that - simply restore everything from the tape and that's it. After restore is complete, he begins recover database to apply archlogs. And after all archlogs are applied - bummer! He realised that he has overwritten online redo logs with the ones from cold backup! So he has to open database with resetlogs anyway, but more importantly - he lost all the transactions from online redo logs! He has lost them forever, unrecoverable, because redo logs were overwritten by unusable ones from cold backup.
This is not so unusual and not totaly hypotetical scenario - it has happened soooo many times that Orcale decided to include the recomendation of not to backup online redo logs in any kind of backup plan in their manuals in recent years!
And I emphasize again: There is not a single recovery scenario anyone could immagine in which backup redo logs would be needed for recovery purposes! Not a single one! So if you don't need them for recovery, why would they need to be included in any backup?
[Edited by jmodic on 10-04-2002 at 02:03 PM]
Jurij,Quote:
Originally posted by jmodic
If you loose everything and have to restore from cold backup (BTW, why would you *have to* restore from cold backup, you could recover from hot backup as well), why would your previous hot backups be invalidated? They are still perfectly valid, no matter if you had to open your database with RESETLOGS. You still can recover to any point in time, both prior to resetlogs (in which case your starting backup would be any hot backup taken prior to RESETLOGS) and after it (in which case you would use any backup taken after the RESETLOGS).
[Edited by jmodic on 10-04-2002 at 05:13 AM]
AFAIK Once you open database with RESETLOGS, all your previous backups are invalid because SCN gets reset. Thats why Oracle recommends to take cold backup after RESETLOGS.
please let me know if i missed something..
np70
Quote:
Originally posted by np70
Jurij,Quote:
Originally posted by jmodic
If you loose everything and have to restore from cold backup (BTW, why would you *have to* restore from cold backup, you could recover from hot backup as well), why would your previous hot backups be invalidated? They are still perfectly valid, no matter if you had to open your database with RESETLOGS. You still can recover to any point in time, both prior to resetlogs (in which case your starting backup would be any hot backup taken prior to RESETLOGS) and after it (in which case you would use any backup taken after the RESETLOGS).
[Edited by jmodic on 10-04-2002 at 05:13 AM]
AFAIK Once you open database with RESETLOGS, all your previous backups are invalid because SCN gets reset. Thats why Oracle recommends to take cold backup after RESETLOGS.
please let me know if i missed something..
np70
????
Yeah, that was a pretty stupid thing to do, but I can see it happening. I can buy your argument.Quote:
Originally posted by jmodic
The most recent backup is the cold backup from last night and DBA knows exactly what he has to do with cold backup. After all, everybody knows that - simply restore everything from the tape and that's it. After restore is complete, he begins recover database to apply archlogs. And after all archlogs are applied - bummer! He realised that he has overwritten online redo logs with the ones from cold backup! So he has to open database with resetlogs anyway, but more importantly - he lost all the transactions from online redo logs! He has lost them forever, unrecoverable, because redo logs were overwritten by unusable ones from cold backup.
[Edited by jmodic on 10-04-2002 at 02:03 PM]
No, they are not "invalid" as such. I mean I wouldn't get read of all my old backups once I had to open the database with resetlogs.Quote:
Originally posted by np70
AFAIK Once you open database with RESETLOGS, all your previous backups are invalid because SCN gets reset.
Those backups are still perfectly valid and usable if you want to perform point-in-time recovery to any time prior to your resetlogs. They are only not usable for recovery operations that require you to perform recovery to any time past resetlogs. And that is why you need to perform full backup ASAP after you open the database with resetlogs.
"Export/import is not a backup methodology..."
- Yoda
Firday afternoon humor :)
it's log sequence that gets reset not SCNQuote:
Originally posted by np70
AFAIK Once you open database with RESETLOGS, all your previous backups are invalid because SCN gets reset. Thats why Oracle recommends to take cold backup after RESETLOGS.
please let me know if i missed something..
Hey, hey, hey. I'm all for the YodaMeister, but I believe that was my homework for DscoBob... http://www.dbasupport.com/forums/sho...6&pagenumber=3Quote:
Originally posted by gopi
"Export/import is not a backup methodology..."
- Yoda
Firday afternoon humor :)
Back up of redo log files are used only when we go for cloning. Cloning is done only to produce replica of production data base, for testing purpose.
I dont think we use backup set of redo logs to recover any production database (we apply archive logs intead). If you consider the amount risk (oooof, you have replaced the the present redo log file with back up redo log files) involved in taking backup of redologs files and the amount of data you lose (while cloning to produce test database) if you dont take back up of the redo files, we can avoid taking back up of redo log files.
It's risk of replacing the production logfiles with older ones versus losing small amount of data in test databases.
Again, it all depends on persons.. (Sanjay and Modi)
jmodic, I agree about the risk you mentioned.Quote:
Originally posted by jmodic
After restore is complete, he begins recover database to apply archlogs. And after all archlogs are applied - bummer! He realised that he has overwritten online redo logs with the ones from cold backup!
Thanks for the link, pando. That clears my doubt about RMAN restore from coldbackup.Quote:
Originally posted by pando
please refer Note:61801.1 on Metalink
BTW, there is no risk of losing any data (even if you don't backup redo logs) provided you shutdown your database normal or immediate before backup.Quote:
Originally posted by nagarjuna
It's risk of replacing the production logfiles with older ones versus losing small amount of data in test databases.
Again, it all depends on persons.. (Sanjay and Modi)
I was only arguing about the need of RESETLOGS if you don't backup redo logs.
Sanjay
We dont apply archive logs when we refresh testdatabase. Instead we just restore all the database files. Inthis case, we lose small amount of data if we dont have backup of logs files ;)Quote:
Originally posted by SANJAY_G
BTW, there is no risk of losing any data (even if you don't backup redo logs) provided you shutdown your database normal or immediate before backup.Quote:
Originally posted by nagarjuna
It's risk of replacing the production logfiles with older ones versus losing small amount of data in test databases.
Again, it all depends on persons.. (Sanjay and Modi)
I was only arguing about the need of RESETLOGS if you don't backup redo logs.
Sanjay
[/B]
And we all good DBAs I guess and we know that it's mandatory to give RESETLOGS when we set our log sequence to zero. :)
You do not lose any data (not even small) if you had shutdown the database in NORMAL or IMMEDIATE mode before cold backup.Quote:
Originally posted by nagarjuna
We dont apply archive logs when we refresh testdatabase. Instead we just restore all the database files. Inthis case, we lose small amount of data if we dont have backup of logs files
The datafiles are checkpointed when you do a NORMAL or IMMEDIATE shutdown and all trnasactions in redo logfiles are already written to datafiles.
You just need to do RESETLOGS to recreate the redo log files if you have lost them. :)
Sanjay
Oppps I missed out the check point thing during database shutdfown.. I dont learn from my mistakes :) I missed out the same question in my OCP exam. and missing it in work place too.. It looks like I am not a good DBA ;)Quote:
Originally posted by SANJAY_G
You do not lose any data (not even small) if you had shutdown the database in NORMAL or IMMEDIATE mode before cold backup.
The datafiles are checkpointed when you do a NORMAL or IMMEDIATE shutdown and all trnasactions in redo logfiles are already written to datafiles.
You just need to do RESETLOGS to recreate the redo log files if you have lost them. :)
Sanjay