The question about whether Online Redo Logs should be backed up keeps coming up.
Those of us who've worked with Oracle since before ARCHIVELOG was introduced do know about Cold Backups -- backup *everything* that is part of the database !
This question came up again on a forums thread, and I replied here.
My previous post on this issue, with more arguments is here.
.
.
.
6 comments:
Yes, you've made the answer more clear than most.
The only weak part is a fundamental problem with backup/recovery: it requires manual operations and thought under circumstances of duress. The prerequisite of knowing what to do gets thrown out the window in the middle of the night with the newbie operator on duty. Management expects rigid procedures for a recovery, which just plain conflicts with Oracle's way of dealing with it, though perhaps the more modern RMAN implementations makes that better. Until someone wants to use a remote disk copy to try to be better than dataguard, without understanding the most basic difference between Oracle blocks, redo, and OS blocks.
word: imwsest
I understand "do not backup redologs because you could accidently restore them" is a bit painful. On the other hand, why waste time to backup and disk/tape storage to backup something you do not technically need?
Laurent,
Let's say I've lost my database (or need to revert to the previous backup because there have been some bad data updates) :
ora10204>pwd
/oracle_fs/Databases
ora10204>rm ORT24FS/*
Now, my previous Cold Backup does NOT include Online Redo Logs :
ora10204>ls -l ORT24FS_coldbackup
total 1345844
-rw-r----- 1 ora10204 dba 7290880 May 29 22:39 control01.ctl
-rw-r----- 1 ora10204 dba 7290880 May 29 22:39 control02.ctl
-rw-r----- 1 ora10204 dba 7290880 May 29 22:39 control03.ctl
-rw-r----- 1 ora10204 dba 104865792 May 29 22:39 example01.dbf
-rw-r----- 1 ora10204 dba 283123712 May 29 22:39 sysaux01.dbf
-rw-r----- 1 ora10204 dba 618668032 May 29 22:39 system01.dbf
-rw-r----- 1 ora10204 dba 20979712 May 27 22:39 temp01.dbf
-rw-r----- 1 ora10204 dba 104865792 May 29 22:39 test_a_tbs.dbf
-rw-r----- 1 ora10204 dba 162537472 May 29 22:39 undotbs01.dbf
-rw-r----- 1 ora10204 dba 78651392 May 29 22:39 users01.dbf
ora10204>
Note that this is a "Consistent" Backup and if we argue that, technically, I do not need Onine Redo Logs.
So I restore the backup :
ora10204>cp -rp ORT24FS_coldbackup/* ORT24FS/
ora10204>
I now try to startup the database. It is a complete, consistent backup. It should be as easy to startup as it was to restore ?
ora10204>sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 29 22:53:50 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 192938952 bytes
Database Buffers 679477248 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle_fs/Databases/ORT24FS/redo01.dbf'
SQL>
Hmm. I did not backup and restore my online redo logs. Hey, I am a junior DBA 3 or 6 months in the job, never having had to restore a Production Database. I did follow "Best Practice" in not backing up the Online Redo Logs. I'm screwed. Let me ask a DBA who has been here for 12 months. He advices that I should do an OPEN RESETLOGS.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL>
Yikes ! What "incomplete database recovery" ? I have a CONSISTENT Backup. What Recovery. My manager has picked up a truncheon now. What do I do ? What do I do ? I followed Best Practices.
OH ! You say that Best Practices is to use RMAN only. No way ! Even the 10g documentation allows User Managed Backup and Recovery. This could have been a 1TB Datawarehouse which we backup using SplitMirrors (BCV) with the database shutdown. OR it could have been a database for a small 10 man business doing a few hundred $$ in revenue. In either case a Cold, Consistent, Backup would have sufficed. What went wrong ?
Along comes a heavy weight expert who "recovers" my database even though it was a CONSISTENT Backup :
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
Laurent:
There are other scenarios where it helps, and also there is value in redundancy beyond single-process repetition, also called "belt-and-braces" or "belt-and-suspenders."
For example, I think it is cool to have a local offsite offline mirror copy of the db (say, on a removable usb disk), for a bare-metal replacement. Imagine you have a big site and a little site, with the big site having most of the heavy OLTP inputters, and the little site far away with a smaller standby system. Depending on what sort of disaster happens, it may be nice to have the option of plugging in a large size replacement in your main site if the toilet upstairs breaks over the weekend, and you can get the new hardware in there and bring the few archived logs back, leaving you a lot better off than having failed over to the smaller system and have to fail back. In other words, you have the option of getting back up by opening time Monday rather than slaving away for days on a smaller system.
Kind of the opposite of what Hemant is demonstrating, the more common scenario of insufficient training or procedure documentation. But it is also common to have cheap management ignoring Oracle's old recommendation of sizing standby the same as primary. There are all kinds of disasters possible, and you can't plan for them all, but you can have some cheap flexibility. Make that, you must have flexibility.
word: promoo
Joel,
Yes, good point.
Similar and other scenarios at http://hemantoracledba.blogspot.com/2008/03/backup-online-redo-logs.html
Post a Comment