Same situation...
Sven Bleckwedel, October 22, 2001 - 9:43 am UTC
Hi,
I had the same situation on my site and changed the backup of the tablespaces (datafiles and also the archived redologs, configuration files like init<sid>.ora, password files and others...) to disk-to-disk. The new backup strategy had decreased the backup time (from 1 hour to 5 minutes) and the possibility of instance problems (redo log increasing with the tablespaces in backup mode and so on...). Later, this backup area is sended to tape, and the two copies of the same backup are guaranteed. This is a good approach for faster recovery, too...
Rgds,
Sven Bleckwedel
Oracle hot backup
Anthony, Cheuk Tung, LAI, May 31, 2002 - 11:37 pm UTC
Most of us would like to save time and effort, however, when disaster comes, there is no shortcut to save.
This is a great approach to remind ourselves:
Keep 2 Backup copies and don't shutdown the database for backup indeed.
By the way, I believe that it is necessary to set up a staging machine for backup and recovery plan rehearsal regularly. It likes a firefighting rehersal, am i correct?
so as to keep our plan fresh in mind and keep people/team updated.
June 01, 2002 - 8:59 am UTC
Yes it is correct.
Here is a fun thing as well. On this backup/staging machine -- do a restore. Now, split your DBA team in half (assuming you have more then one DBA). One DBA is the "evil" DBA, the other the "good" DBA. Evil DBA will do something to the test machine to simulate a failure -- anything they want. Good DBA must fix it. Good DBA wins if they can -- Evil DBA wins if they can fix it when good DBA cannot. Both lose if neither can recover from it (but they should learn from that and be able to recover next time). Now switch posititions.
You'll cover lots of ground that way (you'll find out how to recover from anything)
Hot backup question
atul, October 04, 2002 - 9:52 pm UTC
Sir,
When in my system hot backup is on...I checked status colum from v$backup view
it shows active..
so when i query v$recover_file and v$recover_logs it shows no rows..
I want to know why?
because when you are doing hot backup some files are in recovery stage..
so we should get this info in v$recovery_file and v$recover_logs views..
or
we get this info whenever our database is mount but not open
Thanks.
atul
October 05, 2002 - 11:03 am UTC
the files are not in recovery mode, they are in hot backup mode. huge, big, total difference.
The files are NOT in need of recovery.
Great Game Idea!
Robert, January 23, 2003 - 3:51 pm UTC
Tom,
Thanks for the idea of the game!
I think playing this regularly would go far in making a crack DBA team, not to mention fun.
Thanks,
Robert.
tempfiles LMTs?
Reader, January 24, 2004 - 9:52 pm UTC
Tom,
<quote> In 8 with tempfiles (not datafiles, tempfiles) you won't be backing them up (cannot back them up actually).<quote>
I thought true temp tablespace is when we do with syntax
create temporary tablespace tempfile and it is possible only with a LMT? If so, how is a true temp tablespace is possible with version 8 as we did not have LMT then? Please correct me if mu understanding is wrong. Thanks for your valuable time.
January 25, 2004 - 10:01 am UTC
c/8/8i/g
Here goes...
Invisible, March 16, 2004 - 6:45 am UTC
Appologies for waking this thread up again. (Such a simple question, it's supprising the Search page didn't answer it. But anyway...)
We have an Oracle 8i database in ARCHIVELOG mode. Currently I perform a cold backup (almost) every Friday. (And yes, it's on multiple tapes & all the rest...)
This DB used to be almst totally unused, but as people get the training it's becoming more utilised. Thus, it's now becoming harder to find a window where no one is using it so I can shut it down. Anyway, I'd like to start doing HOT backups instead.
So, the other week, I tried to take a hot backup of our production database. It seemed to work perfectly. However, all that means is that I have a set of files on tape and Oracle didn't give me any errors. ( = doesn't mean much.)
So I immediately decided to attempt to restore this backup to a totally seperate PC, using only the hot backup I just made. (Seems like a good test to me.) And guess what? I can't make it work! [*SO* glad I found this out NOW...]
All of our archived redo logs are always automatically put on tape anyway, without human intervention. So all I did was to put each datafile into hot backup mode, copy it, and take it out of hot backup mode again. Oh, and I asked for a textual controlfile.
So, on my tape, I have:
* Parameter file.
* Trace file with the CREATE CONTROLFILE command in it.
* All datafiles.
* All archived redo logs (for the entire week since the backup, and also predating the backup).
I put these files on my test PC, and edited the parameter file to reflect the new file locations. I created an instance with ORADIM and tried to start it up. No problem.
I edited the CREATE CONTROLFILE command to reflect the new file locations, and tried to execute it.
BAM! No-go.
ORA-01503: CREATE CONTROLFILE failed.
ORA-01565: Error in identifying file <Redo01>
ORA-27041: Unable to open file.
OSD-04002: Unable to open file.
Of course, I never backed up the ONLINE redo - only the ARCHIVED redo. So *of course* it can't find it - it isn't there!
I found the instructions for recreating the online redo logs... guess what? The database has to be mounted first. And guess what you need to mount a database? Yup - a controlfile!
After much reading of the Oracle documentation (and my magic Oracle book), it appears that
-> you can recreate the controlfile - but only if you have the online redo logs.
-> you can recreate the online redo logs - but only if you have the controlfile.
How the heck do I get OUT of this catch-22??? (Surely this is something reeeealy simple... but I can't figure it out!)
Thanks.
March 16, 2004 - 9:18 am UTC
You need to use "RESETLOGS" on the create controlfile statement.
What you've simulated is the total loss of production -- no current controlfiles, no access to online redo logs. It is as if your data center blew up (worst situation). Normally you have access to at least one copy of online redo and a controlfile.
In this case what you do is
o restore the datafiles
o use the CREATE CONTROLFILE statement but use RESETLOGS
o use the RECOVER DATABASE USING BACKUP CONTROLFILE command
o when you've applied all of the archives -- alter database open resetlogs
(that'll create the new redo files for you)
Thanks.
Invisible, March 16, 2004 - 9:37 am UTC
>What you've simulated is the total loss of production.
Yups. If I can recover from this then (presumably) I can recover from anything less serious.
>You need to use "RESETLOGS" on the create controlfile statement.
Aahhh... I see.
I was worried that this would prevent me then applying the archived redo logs. (Which would obviously be BAD.) But since you only actually "reset" them as you open the database... that's not a problem.
Heh - I _knew_ it would be something simple!
Thankyou for your time.
after backup cancelled
jasdeep, July 02, 2004 - 10:29 pm UTC
hi tom
i was taking online backup and it was cancelled due to lto tape error.
after backup cancelled i had taken routine view of status from dba_tablepsaces where it is showing status of system tablespace online while all other tablespaces status are left blank a i had taken the backup again which is successful but the status is same. while on other instance
status is online for all the tablespaces. we have oracle 9i rac on AIX. while staus of system files are online and other files blank in v$datafiles.
what can be the reason.
July 03, 2004 - 10:18 am UTC
how did you "cancel" the backup. what are you using to backup. and you did file a tar with support right?
A reader, July 03, 2004 - 1:35 pm UTC
we are using brightstor as a backup utility.
it was cancelled due to lto was filled.
July 03, 2004 - 1:54 pm UTC
no idea what brightstor is or does -- you'll want to work with support.
hot backup and cloning
Teddy, July 29, 2004 - 8:12 am UTC
Hi
I have a backup script which performs hot and cold backup quite happily. However when we try to use the hot backup to clone a database we always get this message
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u02/backup/system01.dbf'
dont quite understand because the script work flow is correct, parte of logfile shows this
Thu Jul 29 04:53:56 CEST 2004 - Archive current log by switching the logfile...
=====================================================================================
Thu Jul 29 04:54:02 CEST 2004 - Putting tablespace DRSYS in hot backup state
Thu Jul 29 04:54:13 CEST 2004 - Putting tablespace DRSYS back to normal state
=====================================================================================
blah blah
=====================================================================================
Thu Jul 29 04:56:03 CEST 2004 - Putting tablespace SYSTEM in hot backup state
Thu Jul 29 04:56:29 CEST 2004 - Putting tablespace SYSTEM back to normal state
=====================================================================================
Thu Jul 29 04:56:36 CEST 2004 - Putting tablespace TOOLS in hot backup state
Thu Jul 29 04:56:47 CEST 2004 - Putting tablespace TOOLS back to normal state
=====================================================================================
Thu Jul 29 04:57:27 CEST 2004 - Archiving current redo log and creating backup control file
Thu Jul 29 04:57:48 CEST 2004 - Copying archive logs name...
the last part what it does is this
alter system archive log current;"
sleep 20 # gives time for oracle to archive the file
copy all archive logs in the archive dir
what is wrong with this flow? I cant see any!
July 29, 2004 - 11:57 am UTC
cannot tell anything from this -- but a hot backup always needs recovery upon restoration. that is "normal" and "to be expected"?
not sure what the percieved issue is?
a consistent hot backup
Teddy, July 29, 2004 - 6:59 pm UTC
Hi
I thought we could make a sort of "consistent" hot backup set to perform incomplete recoveries. For example if I have 4 tablespaces, system, users, indx, rbs if I perform a hot backup of these 4 tablespaces and after last tablespace backup I do a switch logfile to archive the latest changes I thought the hot backup plus this last switched log could used to perform an incomplete recovery. Isnt this correct?
Let's say when performing first tablespace backup this has scn 10, the second 20, third 30 and fourth 40, after switch the logfile I force a checkpoint so all file header will have same scn, say 50. If I take these backups and the archived logs I can incomplete recover till 50 no?
July 30, 2004 - 7:18 am UTC
the hot backup PLUS all of the logs (the last one plus perhaps some of the earlier ones) can be used to perform an incomplete recovery -- yes.
if you
a) switch logfile
you can recover upto the point in time of the switch logfile.
all of the datafiles you have predate the last bit of data in that logfile.
you can restore them, that logfile, perhaps some of the prior archive log files and apply those logs and open the database resetlogs.
that is not a "consistent hot backup", consistent hot backup is an oxymoron. hot backups are always inconsistent (in need of recovery)
an example would be
Teddy, July 29, 2004 - 7:02 pm UTC
Hi again
for example if I perform a hot backup yesterday and then today I have lost everything including all archived logs since the last backup was performed (the last hot backup is in the tapes) how would we recover? We cannot perform incomplete recovery because it always says file 1 needs more recovery! (in our case)
exactly that´s what I mean
Teddy, July 30, 2004 - 9:03 am UTC
Hi
What you have said is what I wanted to explain. Our hot backup script does the steps whcih you say, it switches the log at the end then copied the archive logs however I cant seem to be able to perform an incomplete recovery with that backup! I wonder we must several times the log before copy the archived logs?
July 30, 2004 - 4:25 pm UTC
process is outlined (well, done) below, comments in bold:
sys@ORA9IR2> @connect "/ as sysdba"
sys@ORA9IR2> set linesize 121
sys@ORA9IR2> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 308 104857600 1 YES INACTIVE 32602148 30-JUL-04
2 1 309 104857600 1 NO CURRENT 32602150 30-JUL-04
3 1 307 104857600 1 YES INACTIVE 32602056 30-JUL-04
sys@ORA9IR2>
sys@ORA9IR2>
sys@ORA9IR2> host rm -rf /home/ora9ir2/bkup
sys@ORA9IR2> host mkdir /home/ora9ir2/bkup
sys@ORA9IR2> host mkdir /home/ora9ir2/bkup/ora9ir2
sys@ORA9IR2>
all of my database files - control, redo, data -- are in $ORACLE_HOME/oradata/ora9ir2 on this test system (normally, control and redo would be spread out). I'm going to backup
a) my control file (could have been to trace, this was just easier to script here)
b) my datafiles
c) but not my temp and not my online redo
arch is going to "/tmp"
sys@ORA9IR2> begin
2 for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )
3 loop
4 dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' begin backup');
5 execute immediate 'alter tablespace ' || x.tablespace_name || ' begin backup';
6 end loop;
7 end;
8 /
alter tablespace SYSTEM begin backup
alter tablespace CWMLITE begin backup
alter tablespace DRSYS begin backup
alter tablespace EXAMPLE begin backup
alter tablespace ODM begin backup
alter tablespace TOOLS begin backup
alter tablespace USERS begin backup
alter tablespace XDB begin backup
alter tablespace BIG_TABLE begin backup
alter tablespace UNDO_NEWER begin backup
alter tablespace MANUAL begin backup
PL/SQL procedure successfully completed.
sys@ORA9IR2>
sys@ORA9IR2> set heading off
sys@ORA9IR2> set feedback off
sys@ORA9IR2> set linesize 121
sys@ORA9IR2> set termout off
sys@ORA9IR2>
sys@ORA9IR2> host sh -vx /tmp/cp.sh
cp /home/ora9ir2/oradata/ora9ir2/system01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/system01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/o1_mf_undo_new_046rbnhr_.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/o1_mf_undo_new_046rbnhr_.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/cwmlite01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/cwmlite01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/drsys01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/drsys01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/example01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/example01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/o1_mf_manual_07p78r54_.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/o1_mf_manual_07p78r54_.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/odm01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/odm01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/tools01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/tools01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/users01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/users01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/xdb01.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/xdb01.dbf /home/ora9ir2/bkup/ora9ir2
cp /home/ora9ir2/oradata/ora9ir2/o1_mf_big_tabl_zw9m3qh5_.dbf /home/ora9ir2/bkup/ora9ir2
+ cp /home/ora9ir2/oradata/ora9ir2/o1_mf_big_tabl_zw9m3qh5_.dbf /home/ora9ir2/bkup/ora9ir2
sys@ORA9IR2>
sys@ORA9IR2> begin
2 for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )
3 loop
4 dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' end backup');
5 execute immediate 'alter tablespace ' || x.tablespace_name || ' end backup';
6 end loop;
7 end;
8 /
alter tablespace SYSTEM end backup
alter tablespace CWMLITE end backup
alter tablespace DRSYS end backup
alter tablespace EXAMPLE end backup
alter tablespace ODM end backup
alter tablespace TOOLS end backup
alter tablespace USERS end backup
alter tablespace XDB end backup
alter tablespace BIG_TABLE end backup
alter tablespace UNDO_NEWER end backup
alter tablespace MANUAL end backup
sys@ORA9IR2>
there are my datafile backups...
sys@ORA9IR2> alter database backup controlfile to '/home/ora9ir2/bkup/ora9ir2/control01.ctl';
sys@ORA9IR2> host cp /home/ora9ir2/bkup/ora9ir2/control01.ctl /home/ora9ir2/bkup/ora9ir2/control02.ctl
sys@ORA9IR2> host cp /home/ora9ir2/bkup/ora9ir2/control01.ctl /home/ora9ir2/bkup/ora9ir2/control03.ctl
and my controlfiles
sys@ORA9IR2>
sys@ORA9IR2> alter system switch logfile;
sys@ORA9IR2> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving
got all of my archives, you could put those in the backup set now..
sys@ORA9IR2> select * from v$log;
1 1 308 104857600 1 YES INACTIVE 32602148 30-JUL-04
2 1 309 104857600 1 YES ACTIVE 32602150 30-JUL-04
3 1 310 104857600 1 NO CURRENT 32602252 30-JUL-04
sys@ORA9IR2> pause
I need (want) log sequence upto 309 for an incomplete recovery later....
sys@ORA9IR2>
sys@ORA9IR2> shutdown abort;
ORACLE instance shut down.
crash...
sys@ORA9IR2> !mv /home/ora9ir2/oradata /home/ora9ir2/oradata.old
bamm, media failure -- all is lost
sys@ORA9IR2> !mv /home/ora9ir2/bkup /home/ora9ir2/oradata
restore backup from 'tape'
sys@ORA9IR2>
sys@ORA9IR2> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
sys@ORA9IR2>
sys@ORA9IR2> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel
ORA-00279: change 32602164 generated at 07/30/2004 16:08:56 needed for thread 1
ORA-00289: suggestion : /tmp/1_309.dbf
ORA-00280: change 32602164 for thread 1 is in sequence #309
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
hit enter
ORA-00279: change 32602252 generated at 07/30/2004 16:09:21 needed for thread 1
ORA-00289: suggestion : /tmp/1_310.dbf
ORA-00280: change 32602252 for thread 1 is in sequence #310
ORA-00278: log file '/tmp/1_309.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
sys@ORA9IR2> alter database open resetlogs;
sys@ORA9IR2> alter tablespace temp add tempfile;
sys@ORA9IR2>
and done
How to recover specific tablespace from hot backup
Yogesh, July 30, 2004 - 12:18 pm UTC
How to recover specific tablespace from hot backup ? can we follow the same procedure used in cold backup ?
July 30, 2004 - 5:36 pm UTC
sure, absolutely.
found the problem
Teddy, July 31, 2004 - 2:27 pm UTC
Hi
I found out that we used recover database using backup controlfile and not specifying until cancel and that's why we get system.dbf needs more recovery
What is the difference between
recover database using backup controlfile
recover database using backup controlfile until cancel
recover until cancel database using backup controlfile
?
July 31, 2004 - 2:34 pm UTC
the until cancel lets you stop the recovery at any point (which we want to do after the last log we have is in fact applied).
if you don't do that, it tries to go "all of the way" and that is why you were getting prompted for the logs that were online.
Cold backup and cancel based recovery
Yogesh, August 02, 2004 - 10:16 am UTC
If I've a cold backup, i.e. the files which I backup are in consistant state ... I want to recover the data (one specific tablespace) till last cold backup.
In this scenario why should we perform cancel based recovery ?
August 02, 2004 - 10:22 am UTC
in this scenario -- you would not? i'm not sure why you would think you would be???
but if you have a "cold backup" and you are in archive log mode (as you need to be) you are missing out big time. they should all be hot, period.. no if an or buts about it. hot is the only way to go.
Yogesh, August 02, 2004 - 10:45 am UTC
I agree with you 100% ... But when I was performing the recovery mentioned in the above scenario, it was asking me for archive logs .. I could not understand why !!
August 02, 2004 - 11:00 am UTC
you don't perform recovery on a restored cold backup if you don't want to??
if you have a cold backup, you can just plop down system, rollback, the tablespace(s) you want and open resetlogs. done. no recovery is required.
HOT BACKUP
A reader, September 15, 2004 - 5:03 pm UTC
Tom,
what is dataguard?
hot backups?
archive logs?
how to do hot backups?
September 15, 2004 - 8:33 pm UTC
I've got the perfect link for you!
</code>
https://docs.oracle.com <code>
there is a data guard manual there (first chapter is "what is this")
there is a backup and recovery guide there (hot backups are backups done while the database is up and running, archive logs are redo logs that have been "archived" off to the side, they are not "online" anymore)
Mini database
atul, September 17, 2004 - 3:44 am UTC
Hi,
I have one database created.
I have HOT BACKUP of the databse(Datafiles/controlfiles/archivelog files)
In that DB i have test tablespace & test table in that tablespace.
I want onlt test table and not other data in that tablespace?
How could i create mini database using my hotbackup?.
I tried following way but its not working?
1)create new sid=test
2)Restore only datafile of test tablespace,system & rollback datafiles to proper test location
3)created control file(From original database & only kept lines for test.dbf)
4)recover database using backup controlfile;
5)Its failing asking for not existing archive log file
Thanks,
Atul
September 17, 2004 - 9:12 am UTC
there is no "table level backup/restore"
you will restore the test tablespace (or not)
(hint -- read about "until cancel" on the recover database statement)
Suspend mode backups...
RD, November 14, 2004 - 11:37 pm UTC
Hi Tom,
How would you rate the - Backups in SUSPEND Mode. Would you recommend them to anyone ? Do you use it yourself ?
and what is the method you use yourself mostly ? Do you recomment third party application for backup and recovery or just good old RMAN itself?
Thanks in advance,
Regards,
RD.
November 15, 2004 - 6:35 am UTC
suspend mode? no. you use suspend IO so you can safely split mirrors in some cases to perform a backup of the other mirror.
I do not use it.
Methods for backup and recovery
RD, November 16, 2004 - 3:37 pm UTC
Hi Tom,
Thanks for that.
Hot backup definitely. But using SQL/PLSQL scripts via SQLPLUS or RMAN?
Another thing - would you recommend using third party softwares like Backup Agent or Oracle by Computer Associates and others in the market?
In our company(which I just joined) the backing up is a task assigned to the system administrators. Do you think that's a smart thing to do or would you suggest the whole backing up is or should be a DBA resposiblity??
Thanks again.
Regards,
RD.
November 16, 2004 - 11:21 pm UTC
rman is the only answer....
backing up a database without DBA involvement and without the DBA being the lead is somewhat "not too smart (tm)"
How many SA's have i seen crash a database at 3am every morning on windows -- simply because "oh, i was backing up the file systems as normal (including the oracle stuff) -- didn't realize oracle would actually have the nerve to want to write to a control file at 3am"
not that the backups they did were of any use since they didn't know they needed to use "begin backup" when using OS tools.
if the DBA isn't the lead on backup...
Then the DBA shouldn't be the lead on recovery...
meaning -- why bother backing up, you won't be able to recover anyway and that is what is relevant....
do your SA's know "about databases"
do they want the recovery job?
if so, they are NOT SA's, they are DBA's in disguise.
Backup
ARU, November 17, 2004 - 3:39 pm UTC
Hi Tom,
Can you please explain what exactly is
4)recover database using backup controlfile;
I am practicing cloning databases and am using this statement but do not understand that all the control file here has is the locations and names of the data and log files and no other information what so ever.
OR I bet there is something happening behind the scene in other steps that I have not understood.
Regards,
ARU.
November 17, 2004 - 4:24 pm UTC
<quote>
but do not
understand that all the control file here has is the locations and names of the
data and log files and no other information what so ever.
</quote>
sorry -- could not "parse that"
but using backup controlfile is for doing cancel based recovery, so we can open resetlogs.
question
ARU, December 08, 2004 - 6:35 pm UTC
Hi Tom,
I am new to backup and recovery and have two very basic questions which I have found for but have not got any from the docs.
1) When we do a hot backup and then a full restore and recovery, we use the backed up control file. Right? Then from where does oracle get infomation till when the archive logs have been generated?
2) Suppose some one drops a table by mistake we can do a incomplete recovery. What if the dba drops the tablespace by mistake can we do the same? What other options are there?
Thanks in advance,
Regards,
Aru.
December 09, 2004 - 12:58 pm UTC
1) you don't want to use the backed up control file if at all possible. that is where oracle would get it's information from. if you have to use the backed up control file because of total loss of everything, the restore is more "complex"
using RMAN would be recommended as well to help you automate things
3) you can use TSPITR to recover from these sorts of mistakes.
hot backup
abc, December 21, 2004 - 7:54 pm UTC
Hi Tom,
1. user A created a prvate synonym on table X and created a public synonym on table x ... now whihc sysnonym he is going to use?
2. what happend when we start hot backup?
begin backup --> datafile header frezzed --> redo log will go to redolog ( suppose its taking 5 hrs then whats going on exactly? ) ---> end backup
3. Very good question: how to prepare development database from production?
Difference between Alter Database backup Controlfile and Copy Controlfile
Vivek Sharma, January 22, 2005 - 1:28 pm UTC
Hi Tom,
I was going thru the Oracle 9i Documentation :
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/osbackups.htm <code>
Chapter 2 of this documentation, under heading "Making USer Managed Backups of the whole database" states that
"Control files play a crucial role in database restore and recovery. For databases running in ARCHIVELOG mode, Oracle recommends that you back up control files with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement. If you back up the control file with an operating system utility during a closed, consistent whole database backup, then you should only use this control file when restoring the other datafiles taken in the backup. Although a control file backed up with an operating system utility during a consistent backup can sometimes be used for recovery (but only if you specify the USING BACKUP CONTROLFILE clause of the RECOVER statement), Oracle does not recommend this practice because neglecting to specify the USING BACKUP CONTROLFILE clause can cause recovery problems."
Is there any difference between
ALTER DATABASE BACKUP CONTROLFILE TO 'filename';
and
OS Copy command of the Control file.
If yes, then what is the difference and how does it affects the recovery process ?
Thanks and Regards
Vivek
January 22, 2005 - 4:29 pm UTC
yes -- if the database is open -- you had better use the database to copy it. else you are copying a file we are writing to frequently and all of the time -- even when "nothing is going on" in the database. (meaning you would be copying "garbage").
Follow the documentation, do it right (actually, do it with rman and let it do most of the heavy lifting for you)
ALTER DATABASE END BACKUP
nadeesh, March 09, 2005 - 1:45 pm UTC
Tom,
While reading making user managed backups, I came across term:-
Caution:
Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup
Why it is, because even if we restore from backup then these are the files which we backed up by issuing "alter tablespace .... begin backup"
RMAN is recommended but still I want to know everything about user managed backup for better understanding.
March 09, 2005 - 3:09 pm UTC
well, a) you wouldn't need to and b) you want to recover them -- not mark them as "done"
nadeesh, March 09, 2005 - 2:00 pm UTC
Everthing doesn't mean that everything from you but just the above point..
nadeesh, March 09, 2005 - 4:49 pm UTC
questioning and then answering myself, does this means that when we run "alter tablespace ..... end backup" to restored datafiles then we won't be able to perform media recovery, that's why the caution is??
March 09, 2005 - 6:27 pm UTC
well, it won't let you end the tablespace backup
ops$tkyte@ORA9IR2> alter tablespace example end backup;
alter tablespace example end backup
*
ERROR at line 1:
ORA-01140: cannot end online backup - all files are offline
and neither the datafile actually
ops$tkyte@ORA9IR2> alter database datafile '/home/ora9ir2/oradata/ora9ir2/example01.dbf' end backup;
alter database datafile '/home/ora9ir2/oradata/ora9ir2/example01.dbf' end backup
*
ERROR at line 1:
ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/home/ora9ir2/oradata/ora9ir2/example01.dbf'
ORA-01208: data file is an old version - not accessing current version
ops$tkyte@ORA9IR2> recover tablespace example
Media recovery complete.
ops$tkyte@ORA9IR2> alter tablespace example online;
Tablespace altered.
tablespace .... end backup
nadeesh, March 11, 2005 - 1:01 am UTC
If oracle doesn't allow to end backup in this way then why caution is there, Is this not create confusion while reading documentaion.
"Oracle9i User-Managed Backup and Recovery Guide"
March 11, 2005 - 6:11 am UTC
don't know.
Restoring a table
j, March 25, 2005 - 12:04 pm UTC
Dear Tom,
My database(9.2) its on archive mode
and I did the following step..
SQL> create table ttt ( numero numeric)
2 tablespace users;
SQL> alter tablespace users offline;
copy users01.dbf to different directory
SQL> alter tablespace users online;
Tablespace altered.
SQL> insert into ttt values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> drop table ttt;
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL> shutdown
copy back the users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\OPERA\USERS01.DBF'
SQL> recover database until cancel;
ORA-00279: change 2309543 generated at 03/25/2005 19:56:02 needed for thread 1
ORA-00289: suggestion : C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_14.ARC
ORA-00280: change 2309543 for thread 1 is in sequence #14
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 2309643 generated at 03/25/2005 19:56:41 needed for thread 1
ORA-00289: suggestion : C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_15.ARC
ORA-00280: change 2309643 for thread 1 is in sequence #15
ORA-00278: log file 'C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_14.ARC' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 2309662 generated at 03/25/2005 19:56:56 needed for thread 1
ORA-00289: suggestion : C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_16.ARC
ORA-00280: change 2309662 for thread 1 is in sequence #16
ORA-00278: log file 'C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_15.ARC' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'C:\ORACLE1\ORADATA\OPERA\ARCHIVE\ARCH_1_16.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\OPERA\SYSTEM01.DBF'
after this is always asking for media recovery for SYSTEM
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\OPERA\SYSTEM01.DBF'
1) Did I make any wrong step?
2) Can I recover my database?
thanks
March 25, 2005 - 6:31 pm UTC
you should just recover that database or datafile -- completely. no cancel based recovery (you cannot cancel until all redo is applied in this case, you only restored A FILE)
it was trying to apply the online redo logs, but didn't know that.
instance failure versus media failure
Kubilay, March 31, 2005 - 4:52 am UTC
Hi Tom
I have been reading your site for the last few weeks and trying to find an answer to the following user managed (OS) hot-backup scenario: (NO RMAN!) Hot backup is every night and keep at least 2 successful backups with their archive logs on the disk. Software Oracle 9i Enterprise v2.
Datafiles, redo, control, archive all are in a Storage Area Network (SAN) with RAID on different volumes at the datacentre (I know for you SAN or not SAN is the same old disk, no difference).
Oracle software is installed seperately in a box which connects to this SAN and startups 2 instances using the files on SAN.
What happens if the box where the Oracle Software is installed crashes (ie. motherboard burns, the box is for garbage). That is an instance failure, correct? There is no media failure or dammage to the database files?
I have another free box which I can connect to this SAN, with same hardware, same OS installed on it. Because it is a 24/7 environment, I want to recover quickly.
Can I just install Oracle (same patchset, version) on this indentical new machine and startup the instances from the new machine? How can I do that? Do I need to do a recovery? It is an instance crash, correct?
Can you correct/verify my steps please?
1) Install Oracle Software to new machine.
2) Use the controlfile text from udump trace of the last backup to create database (NORESETLOGS case), change no file locations since the same on SAN.
3) Recover database(complete until minute of crash ) so shouldn't use RECOVER until CANCEL?
4) Open database NORESETLOGS or RESETLOGS?
Thanks
March 31, 2005 - 8:03 am UTC
<quote>
What happens if the box where the Oracle Software is installed crashes (ie.
motherboard burns, the box is for garbage). That is an instance failure,
correct? There is no media failure or dammage to the database files?
</quote>
that would cause an instance to crash, and then you would just goto any other machine that could attach to that san and be able to startup the database there (assuming it was installed and you have the parameter files in place)
<quote>
Can I just install Oracle (same patchset, version) on this indentical new
machine and startup the instances from the new machine? How can I do that? Do I
need to do a recovery? It is an instance crash, correct?
</quote>
Yes, you can do that, just "install" -- make sure the parameter files are the same (should be on SAN perhaps as well and backed up to tape).
You don't need to do anything "special", if you have access to the database files and all, the database really doesn't care of the server named "BOB" or the sever named "MARY" starts it up.
Switching logs after hot backup
CLG, March 31, 2005 - 10:21 am UTC
Tom in your example you did:
sys@ORA9IR2>
sys@ORA9IR2> alter system switch logfile;
sys@ORA9IR2> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving
got all of my archives, you could put those in the backup set now..
T or F?: You switch logs to perform an implicit checkpoint.
You then archived the logs to make sure you had all
the online redo to arc.
If the above is T then my question is does RMAN perform this step "behind the scenes" when you do a backup?
March 31, 2005 - 10:30 am UTC
T or F?: You switch logs to perform an implicit checkpoint.
False, did that so we could get all of the logs (archives) needed to take this hot backup and restore/recover it on another system. If we left the "last online redo log" on the system -- our backup set would not be recoverable by itself (we need all of the redo to make it consistent).
T or F?: ou then archived the logs to make sure you had all
the online redo to arc.
true.
yes, rman automates many things for us.
Read your book again....
CG, March 31, 2005 - 11:09 am UTC
....pg. 67 of Expert One on One about checkpointing.
Realized that you didnt care about writing the logs to datafiles because you already copied them.
Im assuming a log switch also gets ARCn to write the log "switched from".
And that is what you wanted. When you issued
alter system archive logs all;
you were just "Explicitly demonstrating" that you got all that you needed ( because you actually didnt have to do that)?
March 31, 2005 - 11:33 am UTC
i do not see any alter systems on that page?
Look at my original post above this one.....
me again, March 31, 2005 - 1:13 pm UTC
... I cut and pasted your example from this post ( all the way up ) your command
alter system archive logs all;
returned and error that no logs needed to be archived......
March 31, 2005 - 1:20 pm UTC
what does page 67 have to do with anything than?
you asked "why did you do this alter system" and gave me true/false. I did not do it to cause a checkpoint. That a checkpoint happened was a side effect of me doing it (sure), but I did it for the reason stated above.
I mentioned it...
yeah me again, March 31, 2005 - 2:20 pm UTC
page 67 to state that it cleared up in my mind that the checkpoint had nothing to do with it.
My question to you was if you had not issued
alter system archive logs all;
what would the difference had been?
Nothing right?
Because there were no logs to archive.
It was just to show that "you had all the logs you needed".
i did not say page 67 had anything to do with that it told me by rereading it that CHECKPOINTING had nothing to do with it.
March 31, 2005 - 3:12 pm UTC
I wanted to make sure the logs were archived.
they might not have been, no reason they where -- log archive start might have been false, might have been lagging behind.
Thank you
Satisfied, March 31, 2005 - 4:25 pm UTC
your last sentence is what I wanted.
My thinking was that you DEFINITELY would have all the logs you needed.
But that is not true. (And you gave statements as to why it MAY not be true )
I wanted my thinking to be "tried and true" and "accurate", thats all.
reader
A reader, April 02, 2005 - 7:19 pm UTC
In the situation of regular hot backup ( alter tablespace begin backup .. end
backup etc.) the filesystems are continually being written to but the datafile
header SCN is frozen.Therefore during recovery, we need to apply the
archive log taken after the end of thebackup to synchronize the SCNs of all
the datafiles and open the database in a consistent state.
In case of RMAN, I am not sure about the mechanism. Suppose I take a
backup of datafiles using RMAN. During recovery, I use this backup set and
try to recover. Suppose also, I do not have any archive logs available. Is it
possible to bring the database to PIT recovery to a SCN common to all the
datafiles, since some datafiles will have a higher SCN than other datafiles
which were backed up earlier but in the same backupset. Is it possible to
open the database specifying upto what SCN to recover.
April 03, 2005 - 9:07 am UTC
"we need to apply all of the redo generated since the 'begin backup' command was issued" should replace the phrase "the archive log taken after the end of the backup"
You have to have the same exact amount of redo, that of all redo generated from the point in time the backup *began* to the point in time you wish to recover to.
if you don't have that redo, those hot backups are just bits in files.
reader
A reader, April 03, 2005 - 9:39 am UTC
"if you don't have that redo, those hot backups are just bits in files"
This statement applies for the most recent incremental
backup in an incremental backup stategy as well, I guess.
Because during recovery, the previous incremental backups
use the incremental backup sets instead of the redo.
Therefore in an incremental strategy, is it possible to
(ex: I have level 0(sun), level 1(mon), level1(tues))
recover the database with level0(sun) and then level 1
(mon) and open. level 1(tue) only can be used with
available redo
I am a bit unclear
April 03, 2005 - 10:12 am UTC
you need the redo from the last incremental.
sun: level 0
mon: level 1 (changes from last level 0)
everything (redo) that happened AFTER the mon level 1 started would be needed
I don't understand the flow of your last "level 1 (tue) only can be used with available redo"
reader
A reader, April 03, 2005 - 10:27 am UTC
"
everything (redo) that happened AFTER the mon level 1 started would be needed
"
The redo you are referring to will be in
tue: level 1.
For example we have,
Sun: level 0 ( no redo available)
Mon: level 1 ( no redo available)
Tue: level 1 ( no redo available)
Can we recover the database to some SCN which is consistent
in tue:level 1
April 03, 2005 - 12:05 pm UTC
no redo, no recovery of fuzzy files, in archivelog mode you would have a bunch of bits and nothing else.
reader
A reader, April 03, 2005 - 2:58 pm UTC
Thanks for the useful information. Very helpful.
I remember to have read from the Recovery Manger
manual, in the concept section, that when RMAN have a
option to read from the archive logs or the backup set,
it would choose to read from the backup set.
This is why, I was thinking that in the sceario from the
previous posting, RMAN will use the data from
tue:level1 (differential) to recover the mon:level1 and
would be able to recover until the lowest SCN of tue:level1.
April 03, 2005 - 4:13 pm UTC
"when RMAN have a option "
it can recover to tuesday but if that was a hot backup in archive log mode, it'll still need REDO to finish the job.
backup/recovery
Nirali, April 18, 2005 - 5:33 pm UTC
Yes, this is very helpful.
I am coming from UNIX OS and now working on Windows 2000. I have always used loadme.sh to do the cp files from the tape to restore the database. I am not sure what would be the process for Windows. We are on 9.2.0.2.
Any information would be appreciated!
Thanks,
Nirali
April 18, 2005 - 6:53 pm UTC
no idea, don't know what "loadme.sh" did or does.
don't know what capabilities you have under windows. It isn't as easy as unix when it comes to tapes.
backup/recovery
Prasad, July 22, 2005 - 8:05 am UTC
Tom,
When exactly datafile header get updated with SCN#? At every checkpoint ? If so will update all the datafiles? I read that different data files can have different SCN numbers. How come we can have different CHECKPOINT number for different data files? Is that mean that CHECKPOINT will not update all the datafile headers with same CHECKPOINT number? Would you Please clear my doubt.
If we do SHUTDOWN ABORT datafiles are not going to be in SYNC. or if we get a datafile from backup, the checkpoint# in datafile header is not matched with the controlfile datafile header then it needs recovery right? Will controlfile keeps checkpoint information per datafile? How does applying redo logfiles will get the CHECKPOINT information insync. with controlfile? sorry for a big question.
July 22, 2005 - 9:06 am UTC
the information in the datafile headers is used to tell "how current this file data is as of at least", it might be more current than that (eg: in a hot backup, we suspend some of this header maintanance). Basically, it tells recovery how far back into the redo stream it might have to go to fix this file.
since a checkpoint cannot atomically and simultaneously update every file at the same time, you'll have files with different bits of information all of the time, it is normal, expected and nothing to be really concerned about.
think of the scn like a timestamp (it is in a way). it is the time back to which we might have to go in order to bring this file up to date.
alter system switch logfile & Archive Log Process
Richard, July 22, 2005 - 8:12 am UTC
If, during a hot backup, ALTER SYSTEM SWITCH LOGFILE is issued, then is the archival of the switched logfile implicit? i.e. is the switch AND the archival effectively a transaction - that is, both are considered to have happened, once the ALTER SYSTEM SWITCH LOGFILE statement returns? Or, could the archival potentially lag behind?
July 22, 2005 - 9:08 am UTC
switching a log file and archiving of the log file are two very independent activities.
you can switch and have NO archive take place (because automatic archiving is not enabled)
normally a switch taps archiver on the shoulder and says "here is more work to do, talk to you later"
archiving can definitely be way behind, hence the "cannot allocate new log, archival required" message.
Thank You! Most Useful... but
Richard, July 25, 2005 - 9:56 am UTC
I have been testing the above ("crash, bamm") scenario of yours, with puzzling results.
Basically, if I introduce redo-intensive transactions during the entire process, then perform a Cancel-based recovery, I end up with:
===========================================================
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:ORADATARJAF1SYSTEM01.DBF'
===========================================================
I am able to reproduce this problem consistently. What might be the issue here?
July 25, 2005 - 11:12 am UTC
your exact steps where what precisely?
it just sounds like you need to apply some more of the archived redo logs to make the file consistent (eg: this is normal and expected)
Whoops!
Richard, July 25, 2005 - 9:59 am UTC
Datafile 1 is, in fact, 'C:\ORADATA\RJAF1\SYSTEM01.DBF' (something odd obviously happened during cut and paste).
Crash! Bamm! Doh!!!
Richard, July 26, 2005 - 3:43 am UTC
My apologies; being an RMAN sort of chap, I had misunderstood the correct way to do *olde worlde* recovery until cancel. All is well. Thanks for your help.
Hot Backup Work Load
CG, August 25, 2005 - 4:57 pm UTC
What would should you think about when considering issuing hot backup commands for all tablespaces one after the other
versus
Issuing the command for one tablespace. Waiting for that backup to complete before issuing the next?
August 25, 2005 - 6:46 pm UTC
why not be using rman and forget about putting tablespaces into backup mode?
(it is most efficient to put a tablespace in backup mode, back it up, take it out, put the next into backup mode and so on...)
RahulC
Rahul, August 27, 2005 - 12:12 am UTC
Hi Tom,
in Hot Backup when i'm giving the
alter tablespace users begin backup;
what does it do actually, means does it stop access to write into users tablespace datafile unless end backup is given or it write some were else and then after end backup is given it stores the data into the datafile.
please explain me.
Regards
Rahul C
How to restore cold backup?
A reader, September 14, 2005 - 2:55 pm UTC
Tom,
I have a test installation of 10gr1 database. I am using ASM to manage datafile storage and database is in archivelog mode. OEM grid control is setup and works fine. I want to take a cold backup of this database so that even after a month, I can bring it back to the original state. I do not want to backup archive log files, nor do I want to use them for recovery. Here is what I did using RMAN:
RMAN>shutdown immediate
RMAN>startup mount
RMAN>backup database tag='xyz_whole_backup' include current controlfile;
RMAN>alter database open;
I am taking backup to the flash recovery area and in Grid Control I can see the backup pieces. My questions are:
a) Is this the correct way to take a cold backup?
b) Assuming the above commands are correct, what RMAN commands do I issue to restore this backup so my database is back to when the backup was taken?
Thanks...
September 14, 2005 - 3:47 pm UTC
a) that is one way sure.
b) restore database; it can be as simple as that.
but practice, read, and get it down -- for you see, you can mess up ANYTHING EXCEPT recovery. If you cannot do that, you are "hosed"
Are you sure you don't want to be in archive log mode, if not you must - have to - accept the fact that someday you WILL (not might, WILL) lose all of your data changes since your last full backup. You must accept that and be willing to have it happen because it is only a matter of time.
If you have another way to "restore" the changes - fine, but you must accept you will lose them all and need to either be OK with that or have some other way to fix it.
How to restore cold backup?
A reader, September 14, 2005 - 5:01 pm UTC
Tom,
Thanks so much for your quick reply. Let me clarify the environment and the need for doing what I am doing.
This is not a production database. It is used for doing load tests. The database is run in ARCHIVELOG mode to simulate production environment but the archived logs do not participate in backup or recovery.
We do one round of load test. The load test changes a lot of data in the database. We find deficiencies in the application code. We fix it. The data is changed so we cannot use it for running another round of load test. We need to bring the database back to the point where we started. This iterative cycle can last for weeks till we fix all the problems before moving to production. Most times, we need to take many cold backups in this cycle because the database code also changes. In addition, the database is a consolidation of three applications. Each application has its own load test calendar. When load tests are not being done, developers and DBAs use this database for query tuning. We do not care about changing the data.
In this situation, a cold backup serves our purpose well. When I restore from a cold backup, there is no doubt about the state of the database. The load test scripts work against a fixed set of data. They will not work with any random data.
Tom, you are an expert in Oracle. You said a) is one way. What would you do? You are hiding some cool technique here. Now I am dying to know...This is not a production database, so if something goes wrong, I can recreate from past backup. I will test any script that you give me and will not hold you responsible for anything that goes wrong. What else can I say? Please give me some knowledge.
Thanks...
September 14, 2005 - 5:55 pm UTC
I'm not hiding any cool technique, that is one way to go. Really, nothing "fancy"
How to restore cold backup
A reader, September 15, 2005 - 11:47 am UTC
Tom,
When I try to restore cold backup, here is what I get:
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 167772160 bytes
Fixed Size 788304 bytes
Variable Size 158333104 bytes
Database Buffers 8388608 bytes
Redo Buffers 262144 bytes
RMAN> restore database;
Starting restore at 15-SEP-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DG_EXT/orcl/datafile/system.256.557846231
restoring datafile 00002 to +DG_EXT/orcl/datafile/undotbs1.258.557846241
restoring datafile 00003 to +DG_EXT/orcl/datafile/sysaux.257.557846237
restoring datafile 00004 to +DG_EXT/orcl/datafile/users.259.557846245
restoring datafile 00005 to +DG_EXT/orcl/datafile/example.269.557846511
restoring datafile 00006 to +DG_EXT/orcl/datafile/undotbs2.271.557848747
restoring datafile 00007 to D:ORACLEPRODUCT.1.0DB_1DATABASESYSAUX
restoring datafile 00008 to D:ORACLEPRODUCT.1.0DB_1DATABASEUSERS
channel ORA_DISK_1: restored backup piece 1
piece handle=G:BACKUPOBGUMJLO_1_1 tag=TEST_COLD_BKUP
channel ORA_DISK_1: restore complete
Finished restore at 15-SEP-05
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/15/2005 11:14:41
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DG_EXT/orcl/datafile/system.256.557846231'
RMAN>
Why do I need recovery? This was a closed, consistent backup, it should not ask for recovery. The database was cleanly shutdown and was in mount state when the backup was taken.
Thanks...
How to restore cold backup
A reader, September 15, 2005 - 3:14 pm UTC
Thanks for you reply. I always used to use OS file copy to take a cold backup. Restoring such a backup never required recovery.
Obviously I do not understand backup and recovery through RMAN enough, but it is what I am trying to do. I understand that you may not have enough time to explain the concepts in this forum.
Is there any other resource like a book on backup and recovery that you can recommend?
Thanks...
September 15, 2005 - 4:30 pm UTC
well, the issue here was - you had "new controlfiles" and old datafiles ;) You could have restored the control files from the backup as well.
How to restore cold backup
A reader, September 17, 2005 - 9:18 pm UTC
Tom,
Thanks again for your answer. After restoring the controlfile, I was able to restore database without performing recovery. It worked fine on the database running on my basement server but failed to work at site. The problem was with retention policy. At home database, it was set to recovery window of 30 days. At site, the retention policy was at default of redundancy 1. Because of this policy, whatever controlfile I restored, the datafiles were always restored from the most recent backup. The mismatch in controlfile and datafiles always prompted for recovery. Since I had opened the database with resetlogs option, I could not perform a recovery. After I changed the retention policy to window of 30 days, the problem was gone.
However here is another problem. I have a perfectly good closed, consistent whole database backup but the controlfile in this backup has retention policy of redundancy 1. Whenever I restore this controlfile, the entire problem reappears. Is there a way to use this backup for restore without resorting to recovery?
September 17, 2005 - 11:50 pm UTC
why is recovery a problem? why is it "resorting"?
but if you restore the control file from the backup (the one taken during the cold backup), it shouldn't make you "recover"
How to restore a cold backup
A reader, September 18, 2005 - 3:01 pm UTC
Here is the timeline and what I did:
a) Cold backups were taken on 8/25, 9/8, 9/12, 9/14. In between these cold backups, the database was restored several times from these backups, everytime using variety of trial and error methods and everytime opening with resetlogs option.
I was having a hard time understanding what is going on. Then I realized that this is what is happening.
Say, I want to restore from the cold backup taken on 9/8/05. I restore the controlfile from the 9/8/05 backup using the syntax:
restore controlfile from '<location of 9/8/05 'restore >';
alter database mount;
When I issue the command 'restore database', RMAN starts restoring the datafiles from the cold backup taken on 9/14/05. There is no way I can make RMAN restore the datafiles from the 9/8/05 backup. I tried using the:
restore database from tag='<tag_name of 9/8/05 backup>';
RMAN tells me something like there are no files to restore.
My explanation of this behaviour is that because retention policy is set to redundancy of 1, RMAN will always restore from the most recent full backup of datafiles no matter where I restore the controlfile from. Now since there is a mismatch between controlfile and datafiles, Oracle will ask for recovery.
I do not know if my thinking is correct or not but RMAN is not letting me restore any of the previous cold backups. Maybe there is a way I just don't know.
Thanks for your help. These questions must be like very rudimentary to you, but it is helping me a lot since I cannot explain what is going on.
I am yet to test what happens when I set the retention policy to recovery window of 30 days. Will Oracle let me restore from an old cold backup even when a new backupset is present? I have to test this out.
very useful info
Kishor Bhalwankar, December 05, 2005 - 2:24 pm UTC
Tom,
Can you please explain
1) Hot backup was taken for a database on day 5 and time taken to compete backup is 10 Hrs
2) On day 6 the database server crashed and all datafile,controlfile, redo logs lost.
3) Even the archivelogs are lost, no multiplexing of archivelogs setup.
4) Later it was noticed that archivelogs of day 4,5 and 6 was not existing in the backup.
5) And only one backup which was taken on day 5 is available.
6) how can we open the database considering the scenario.
Thanks in advance
Kishor
December 06, 2005 - 5:24 am UTC
you are "hosed" technically speaking.
you need the hot backup and the redo generated during the hot backup which you have just said you don't have.
meaning, you didn't really do a backup. You just moved some random bits from one place to another.
You didn't do it right. It is not that the archivelogs were not existing in the backup, it is that you did not backup the archivelogs necessary to make the backup complete, therefore you do not really have a backup at all.
Useful
Kishor Bhalwankar, December 06, 2005 - 12:21 pm UTC
Thanks tom for the answer,
This is just a scenario, not a real life problem.
Please can you please explain me .. How to open this database without having archive logs.
Thanks in advance once again
Regards
Kishor Bhalwankar
December 06, 2005 - 3:23 pm UTC
I tried to say "you are hosed", meaning "no go, you lose, you haven't done a proper backup, we cannot manifest bits and bytes from thin air, data gone, game over"
the first mistake in the question was stating "we took a backup", you didn't - you don't have a backup. A backup includes the things you say you don't have. A backup is something that can be recovered from, you don't have that here.
Time!
María G Saavedra, April 10, 2006 - 2:38 pm UTC
Hi Tom!
Is there any way to get better times with this hotbackup script?
I mean, i use it for a 9Gb Database (Oracle 7) and takes almost 40 minuts. (Sun 5.6)
Thanks!
April 11, 2006 - 11:38 am UTC
do not know what script you mean.
and if you just copy files from disk a to disk b (no oracle, just copy files), what kind of throughput do you observe.
Time!
Maria Saavedra, April 12, 2006 - 3:45 pm UTC
I mean, about the script that you explain in this article (using begin and end backup, using cp ...) ... So i know we depend of cp command and the time it gets to copy those archives.... so is any other way?
April 12, 2006 - 7:38 pm UTC
you can definitely run the cp's in parallel - you would just background the cp's and "wait" for them before ending the backup.
Hot Backup of Online redo logs
Des B, April 13, 2006 - 7:04 am UTC
Tom,
At the start of this you recommend backing up the online redo's during a hot backup. The document no. Note:94114.1 says, when describing how to run a hot backup:-
'9. Backup all archived log files determined from steps 2 and 8.
Do not backup the online redo logs. These will contain the end-of-backup marker and can cause corruption if use doing recovery.
Given your advice was from 2000 and this document is dated 2004 does this show a change in Oracle's thinking or is there a contradiction ?
April 13, 2006 - 8:06 am UTC
where did I say that?
I have always said "you NEVER backup online redo logs, it is useless"
I said you want to convert the active online redo logs into archives after you backup the datafiles and then backup the archives - so you have all of the stuff you need to do a consistent restore in your backup set.
Hot backup of online redos
DesB, April 13, 2006 - 7:09 am UTC
Tom - apologies re my previous question. I appear to have misread what you said because I can't now find any recommendation to backup the online redo's !
Alexander, April 13, 2006 - 11:42 am UTC
Tom I get confused too when talking about online redo logs because to me
online redo logs = redo logs with online status (good to back up)
and not the current redo log being written to (bad to backup)
Is this correct? It's tricky because they sound similar and yet one is essential for backup and recovery, and the other will destroy your database?
April 14, 2006 - 11:51 am UTC
online redo logs = BAD TO BACK UP REGARDLESS.
the only redo you back up is ARCHIVED redo - never the online redo logs. The only thing you could do with them would be to accidently RESTORE them over the current online redo logs - wiping out the ability to perform a complete recovery.
only ARCHIVES
Difference between archived redo and online redo.....
Mark J. Bobak, April 13, 2006 - 7:18 pm UTC
I hope Tom doesn't mind me jumping in here.
Alexander, online redo is the set of redo logs that your database rotates through as log switches happen. They are all online redo, regardless of what STATUS they may be in at any given moment. To see a list of online redo, do:
select name from v$logfile;
Those are your online redo logs. NEVER EVER attempt to back these up.
If your database is in archivelog mode, then after a log fills, and the log writer switches to the next log in the rotation, archiver kicks in and makes a copy of that most recently filled log, writing it to your log_archive_dest directory. Use 'show parameter log_archive_dest to see what directory that is.
The archived logs that appear in that directory, THOSE are the ones you MUST backup.
Hope that helps,
-Mark
April 14, 2006 - 12:11 pm UTC
no problem :) consensus is good.
A Help
sagar, April 28, 2006 - 3:48 am UTC
Hi Tom,
I am reading your site forums its very helpful well i am new to oracle so please do let me know about backup and recovery like eg HOT BACKUPs with RMAN . If you have any links for this then please let me know .
sorry to say but i need step by step documentation so can u provide me any link for this .
Regards
sagar
April 28, 2006 - 12:16 pm UTC
"u" isn't around.
but the RMAN documentation actually *is*.
Robert Freeman has a decent book on RMAN (Oracle Press) as well that you might be interested in.
RECOVERY is the only thing a DBA cannot mess up, the only thing they have to get right. Don't try to fly by the seat of your pants here, invest the necessary time to really learn this.
weird ORA-01113 error
John, May 30, 2006 - 10:11 am UTC
Tom:
while doing a trial recovery here using a copy of hotback, I can apply all archived logs without trouble using "recover database...", and check v$recover_file, change# for all data files are the same and keeping on advancing when applied new logs from the source (production) database...but any attempt of open database using "alter database open resetlogs" will return error:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data03/oradata/system_rp.dbf'
what is the problem then?
May 30, 2006 - 10:26 am UTC
insufficient data, but sounds like you need to apply MORE redo to catch that file up.
You really didn't list all of the steps you've gone through, status of control files, etc.
peek at Note 186137.1 on metalink
John, May 30, 2006 - 11:45 am UTC
thanks Tom for response.
i did not encounter too much trouble when before doing restoration using tablespace hotback, just this most recent incident make me feel less confident, as everything appears to be normal, but i just can not bring the restored database back online. thus unsure if the previous backup can be reused or not.
here is a summary for steps taken:
1. alter database backup controlfile to trace
2. one by one, put each tablespace into backup mode and use OS copy command to copy respective datafiles. all datafiles, init file and trace file eventually transferred to the another testing server for trial recovery. verified via v$backup
3. did NOT copy any online redo log and controlfile from the original database
4. rebuilt control file using the script generated from the trace file (I removed the "reuse" clause when re-generating controlfile, and directory setting of datafiles on the new server are different from the original database)
5. issue command "recover database using backup controlfile"
6. tested using "cancel" and "auto" mode while apply archived redo log. No error ever returned.
7. any attempt of "alter database open resetlogs;" will return error of ORA-01113
May 30, 2006 - 11:48 am UTC
step 3 is likely the problem.
you didn't not switch out of all current log files and archive them, you have changes in the online redo logs that would be necessary to make the set of files you copied in step 2 consistent with eachother.
you backup the database, switch out of current log file, archive log ALL and then you need to apply all of the archives you have to the backup. You need the changes that happened DURING THE BACKUP to recover these files.
A reader, May 30, 2006 - 11:52 am UTC
yeh, thanks Tom, i miss this important step...thanks
Reader, July 24, 2006 - 7:12 pm UTC
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
ORA-00214: controlfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL'
version 4324 inconsistent with file
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL' version 4321
I copied the control01.ctl over control03.ctl, and I did startup. But I am getting
the error "File 2 needs media recovery" . Please advise
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF'
SQL>
July 24, 2006 - 7:19 pm UTC
before you do ANYTHING - before you do one more thing, please verify that you
a) have backups
b) know what to do with them
c) know you can do that with them
then you can try the recover datafile command - you should know the command (as you have tested restores before right?)
Duplicating the database box
J B, July 25, 2006 - 9:08 am UTC
Hi Tom,
I am trying to duplicate the production box so as to create a testing box.
I took the complete hot backup of the production box. The log of RMAN states
In beginning
Sun Jul 23 17:00:18 2006: Getting sequence# of current log number
Sun Jul 23 17:00:18 2006: Last sequence# is: 13087
Sun Jul 23 17:00:18 2006: Creating new backup record
In the End
Backup length - start to finish (in seconds): 6444
Sun Jul 23 18:47:25 2006: Logging status and completion time for current backup run
Sun Jul 23 18:47:25 2006: Getting sequence# of current log number
Sun Jul 23 18:47:25 2006: Last sequence# is: 13138
This means that when RMAN started the log sequence number was 13087 and when it ended the log sequence was 13138.
After restoration is done, is it safe to execute
run {
allocate channel ch1 type 'SBT_TAPE';
set until sequence 13138;
send 'NB_ORA_CLIENT=db-prod-a.dc';
recover database;
}
to get a consistent database up and running, after we open the database using RESETLOGS option.
Am I correct, please do let me know since the restoration of the complete database is going on.
July 25, 2006 - 11:52 am UTC
why don't you chat with the DBA in charge of being able to restore your database? They probably already have the scripts all set up and everything since they have practiced this procedure hundreds of times to make sure they can do the only thing they are not allowed to get wrong!
Restore
J B, July 25, 2006 - 1:34 pm UTC
Hi Tom,
When doing the recovery, it applied two logs 13088 and 13089 and failed with 13090.
Any ideas on the accompanying error messages,I searched various sites but could not find the resolution to this.
archive log filename=/arch-01/databases/admw/redolog-13090-1-583227512.arc thread=1 sequence=13090
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2006 17:22:27
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/arch-01/databases/admw/redolog-13090-1-583227512.arc'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '/opt/app/oracle/local/config/admw.track-blockchange'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
July 25, 2006 - 2:34 pm UTC
did you correct the cause of the error?
Recovery
J B, July 27, 2006 - 2:41 am UTC
Hi Tom,
Couldn't figure out what the error is? Is it related to block change tracking file.
Should I disable the block change tracking to further proceed.
Thanks
July 27, 2006 - 11:49 am UTC
please utilize support.
How to bring all datafiles to the same scn# ?
Ralph, October 10, 2006 - 6:50 am UTC
Hi Tom:
An ex-officemate (non-dba, company still has not found a new dba) once did a shutdown abort when the application appears to have "locked-up". What was supposed to be 2- minute downtime turned into a 4-hour downtime when the recoverer started asking for archived redo logs that have since been moved to tape. Turns out that one of the datafiles is way behind in SCN.
Q1 :Is there a way to bring the datafiles closer to the same scn# without bouncing the database?
Q2: What system tables contain the current SCN# for each of the datafiles?
Thanks!
October 10, 2006 - 8:21 am UTC
I'd need more information - a recovery from a shutdown abort should not require archives. was the datafile offlined?
archive logs after shutdown abort
Roderick, October 10, 2006 - 12:38 pm UTC
Another possibility is that some tablespace was left in "hot backup" mode for a long time leading up to the shutdown abort.
Datafile needing archivelogs
Ralph, October 17, 2006 - 12:02 am UTC
Hi Tom:
What logs can I give you for information? please advise.
I don't think the tablespace is offlined. It contains a major transaction table that is being hit everyday. At first I suspected it might be a datafile of a partition. I partitioned the table before I left the company for ease of maintenance based on your "sliding windows" that I read here on your site.
I need to go back to the office and look at it for sure, and also to provide whatever logfile you may require.
Thanks again for your help
October 17, 2006 - 4:22 am UTC
read your alert log - see what Roderick said right above - was the tablespace left in backup mode for an extended period of time?
BACKUP CONTROLFILE
A reader, October 24, 2006 - 6:41 pm UTC
Tom,
I restored my user managed hotbackup over to new machine for testing purpose.
This is the sequence i followed :
1. Restored my hot backup with datafiles on the another machine.
2. Recreated the control file with new ORACLE_SID
Now ran the script to recreate the controlfile..
after this step, when i tried to open i got the error message :
Recovery required message using backup controlfile...
Question is, From where oracle reads the controlfile details at this point.. i recreated the controlfile before this step (to change the SID)..
I did not restore my backup controlfile either.
Thanks for your reply.
October 25, 2006 - 9:33 am UTC
your init.ora pointed us to the control files you said to use.
Cloning/Recovery using Hotbackup
Rameez, December 27, 2006 - 8:33 am UTC
Hi Tom,
I am using hot backup for cloning so I need to apply the logs
to make it consistent but if I lost one of the between archive log file so does it means I cannot recover the database and hot backup is of no use ?
Thanks in advance
December 28, 2006 - 9:22 am UTC
yes, if you lose part of your backup, your backup is not useful.
so I recommend you don't LOSE part of it.
Query about full hot backups
Keith Jamieson, January 08, 2007 - 9:17 am UTC
Hi Tom
Database version: 10.2.0 SE Windows NT
(Managed through Enterprise Manager Grid Control)
I'm not sure if I am totally getting this, but I am investigating creating a daily scheduled full hotbackup.
I understand that it will need media recovery, and that is okay.
The order as I understand it is:
Take a full cold backup, to give me a starting point.
Day1: Take a full hotbackup, including archivelogs, controlfile, etc
Day2: Take a full hotbackup.
Day3: Take a full hotbackup
Day4 .. etc
Now, suppose I have a failure at day 4. Can I restore the database just from Day 3, or do I have to start the media recovery from the cold backup.
My assumption at the moment, is , that the previous full hot-backup + recovery should be sufficient.
January 08, 2007 - 1:06 pm UTC
you do not need "take a full cold backup"
you need to constantly be backing up your archives
You would take your last backup, restore files from it, apply archives generated since then
backup
mahen, September 22, 2007 - 7:33 am UTC
copy from backup dir. to original destination .
-startup mount
-alter database recover datafile using backup controlfile until cancel;
-recover cancel;
-alter database open resetlogs;
why the time stamp is not up to date
Yuna, February 04, 2008 - 11:09 am UTC
tim Tom,
I prepared a backup schedule and works fine except the time stamp of the copied datafile is not the same as the original datafile. The size of the file is correct.
sometimes if I run the backup procedure the first time, it will gives the timestamp one day ealier; If I run it the second time, it will gives the correct timestamp.
I run some procedure and get the following SQl command for the backup of the datafile. Please help me to find out what the reason? whether it matters.
lter tablespace ADM begin backup;
host copy D:\ORACLEXE\ORADATA\XE\ADM.DBF D:\Oracle\HotBackUp\XE\data 1>> D:\Oracle\HotBackUp\XE\log\hbackup.log 2>> D:\Oracle\HotBackUp\XE\log\herrors.log
alter tablespace ADM end backup;
Best reagrds,
February 04, 2008 - 4:13 pm UTC
sounds like a window-ism. given we don't care about the timestamp and everything is registered correctly - so what?
windows doesn't update the timestamps all of the time, I guess you could try checkpointing everything and see if windows does it then, but I would not be concerned.
time stamp not changed
yuna, February 05, 2008 - 8:52 am UTC
Hi Tom,
Thank you very much for the reply.
I am wondering why the timestamp for other file changed, for example, the control file and archivelog files have correct time stamp in the backup directory, but not the data files.
best regards,
February 05, 2008 - 10:19 am UTC
because you are using windows.
restoring but can't success
Nikunj Thaker, February 13, 2008 - 6:41 am UTC
dear tom,
i followed steps which you mention on below link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:894628342039
in discussion below.
exactly that´s what I mean July 30, 2004 - 9am US/Eastern
Reviewer: Teddy
after following your procedure try to restore but failed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 135339876 bytes
Fixed Size 454500 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database using backup controlfile until cancel
ORA-00279: change 418629 generated at 02/12/2008 15:39:56 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\ORAARCH\ARC00005.001
ORA-00280: change 418629 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 438945 generated at 02/13/2008 16:08:51 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\ORAARCH\ARC00006.001
ORA-00280: change 438945 for thread 1 is in sequence #6
ORA-00278: log file 'D:\ORACLE\ORADATA\ORAARCH\ARC00005.001' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORA92\SYSTEM01.DBF'
ORA-01112: media recovery not started
SQL>
Regards,
Nikunj Thaker.
February 13, 2008 - 1:07 pm UTC
did you notice this:
ORA-01195: online backup of file 1 needs more recovery to be consistent
you canceled prematurely, you need to apply more redo
more details
Nikunj Thaker, February 14, 2008 - 1:45 am UTC
Dear Tom,
canceled because its saying ARC00005.001 no more required for recovery.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 438945 generated at 02/13/2008 16:08:51 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\ORAARCH\ARC00006.001
ORA-00280: change 438945 for thread 1 is in sequence #6
ORA-00278: log file 'D:\ORACLE\ORADATA\ORAARCH\ARC00005.001' no longer needed for this recovery
And also tried with AUTO but did not success.
Thanks & Regards,
Nikunj Thaker
February 14, 2008 - 9:27 am UTC
you have not given sufficient information here as to what you did from step one, but you have NOT applied everything needed for media recovery, it could well be that you have to manually supply the filename - for the ONLINE REDO log files if you are doing a restore with old control files (they do not know what was archived and what was not, it is suggesting what the names would be - it is not sure)
Why Redo logs cant be backed up
Hubert, May 10, 2008 - 5:50 am UTC
Dear Tom
Why cant i take backup of online redolog files.
May 12, 2008 - 12:17 pm UTC
well, you can take a backup of them.
but one would be forced at that point to ask you "so, what would you possibly do with these??"
The only thing you could do with them might be to accidentally restore them over the current online redo logs when you panic during a recovery and wipe out committed transactions (eg: nothing good would come of it)
so, tell me - what would you do with them exactly?
A reader, May 13, 2008 - 9:29 am UTC
Thanxs Tom that is what i wanted .
I trie dto experiment with these redo logs by restoreing over redolog files and try to restore.
Below where my observation
1) if we go for a complete recover ,while recverig from redo log it fails stating redo log is of older version.
2) In case of incomplete recovery it flushes out.
I read few documents stating that if older redo logs get recovered database might get corrupted .I dont find any way where in database will use with older redologs .
I have one question what happens when we take bckup of controlfile.I dumped backup controlfile ,only difference I found was filetype was changeed to 4 and stop scn of all datafiles was infinity ,which usually is when db is open stop scn is infinity. Does oracle recognise backup controlfile with change in filetype.
May 13, 2008 - 11:11 am UTC
... I dont find any way where in database will use with older
redologs .
...
correct it cannot - what the things you were reading were saying was "if you restore redo from a backup the only thing you'll do is OVERWRITE THE REDO ON DISK - which you might need for recovery and if you do that, well, you won't be using them to recover will you"
when you take a control file backup, you back up the control file - not sure what else could be said?
Controlfile Backup
A reader, May 14, 2008 - 7:11 am UTC
Dear Tom,
My question was what is difference between normal controlfile and backup controlfile?
I dumped the normal controlfile and the backup controlfile.
The only difference i find between both the controlfile was.
file type of normal control file was 1 ,where backup controlfile file was 4.
As usual stop scn of all the datafiles on both controlfiles dump was infinity.
Does oracle only req controlfile type change to recognise that controlfile was the backup.
Or is there any other change in backup controlfile apart from file type.
Thanxs
May 14, 2008 - 3:29 pm UTC
the backup control file is just a file used to recover a database that lost all of its real controlfiles - or to do a point in time recovery.
that is all - otherwise it is just an old copy of the controlfile, not useful with the current database - only useful as a last ditch recovery thing.
Oracle hot backup
Nishant Santhan, May 15, 2008 - 9:07 am UTC
Hi tom,
Can we use an old hot backup once we open the database with resetlogs?
for example
I have taken a hot backup with archivelogs.My backup containse datafiles controlfile and archive logs. My current log sequence is 10 meance I have all the archive logs from 1_1, 1_2 till 1_9 in my backup. In this situation, I restored all my datafiles and control files from the backup and applied logs till 1_9.arch then I opend with resetlog mode. After that I did not take any backup and I did some more changes on the database, my current log sequence is 3 and I have archive which generated after reset logs (1_1.arc and 1_2.arc). What happen if my database crash now.
Is there any way to restore my old backup again and apply logs till 1_9 then manualy apply 1_1 & 1_2 which generated after opening resetlogs?
Thanks
May 19, 2008 - 12:04 pm UTC
Oracle hot backup
Nishant Santhan, May 23, 2008 - 4:05 am UTC
Thanks Tom,
I just wanted to know one more thing. Lets suppose I lost my controlfile and my instance terminated. I have recreated the controlfile again and I mounted the instance. From the mount stage I can query v$datafile_header veiw to find the scn in the all datafile header. I think I can use V$ARCHIVED_LOG or V$LOG_HISTORY to know previously archived logs scn details but how will I know the current redo logs last scn number? I can recover the database until cancel but just to get current state of my database and How many changes has to apply to make my datafiles current...
May 23, 2008 - 9:05 am UTC
see the other place you put this same exact text.
Hot backup script
Sinan Topuz, May 25, 2008 - 4:31 pm UTC
Hi Tom,
Below is my RMAN backup script which runs weekly to take a hot backup of our production database. I am not sure whether this script takes a consistent backup and I can trust it fully. I am backing up archive log files every day until the next full backup.
Am I supposed to execute:
alter system switch log file;
alter database backup controlfile to '....';
after backing up the database, or do you see any "don't"s in this script?
Thanks,
Sinan
[root@oraprod RMAN_PROD_BACKUP]# more config.txt
rman catalog=rman/rman@prod target=sys/**************
PROD (DBID=***********)
configure channel device type disk format '/u0/oracle/RMAN_PROD_BACKUP/Backup%d_DB_%u_%s_%p';
run {
allocate channel d1 type disk;
backup
tag saturday_full
format '/u0/oracle/RMAN_PROD_BACKUP/df_%d_DB_%u_%s_%p'
database;
copy current controlfile to '/u0/oracle/RMAN_PROD_BACKUP/saturday.ctl';
sql 'alter system archive log current';
backup
format '/u0/oracle/RMAN_PROD_BACKUP/al_%d_DB_%u_%s_%p'
archivelog all
delete input;
release channel d1;
resync catalog;
}
May 27, 2008 - 7:51 am UTC
... I am not sure whether this script takes a consistent backup and I can trust it fully. I am backing up archive log files every day until the next full backup. ...
have you considered testing?
or any of the validate commands?
have you ever RESTORED - if not, do it now, right away, before anything else - restore and recover your database on some other system.
are you backing your archives up frequently during the week? if not, do it - think about what you need to recover if you lost the machine.
Forgot to add. DB version is 9.2.0.3 and Linux platform.
Sinan Topuz, May 25, 2008 - 4:32 pm UTC
Oracle Hot Backup
nishant santhan, May 26, 2008 - 1:41 am UTC
>>> see the other place you put this same exact text.
Sorry I forgot to mention that...yes!!! I put this on Oracle forums once but I did not get a proper answer. They suggested to check v$database view where controlfile checkpoint but its not the last scn in the current log file. In case of shutdown abort, this is far less than the current redo.
May 27, 2008 - 7:55 am UTC
no, I meant the other place ON THIS VERY SITE - you posted the same text in multiple locations ON THIS ONE SITE.
asking over and over and over and over is, well, not productive.
Sinan, May 27, 2008 - 12:17 pm UTC
Tom,
Thanks for the response.
We do not have any server where I can actually test restoring it. I frequently run "restore database validate;" and get no error messages. What I was actually trying to ask is, from the script, as far as you can see, does it seem like I am backing up everything I need for a restore to the most recent time in the past, in an efficient way? Am I missing any crucial step? (Assuming that my archive logs are in good hands ;)
Thanks,
Sinan
May 27, 2008 - 3:17 pm UTC
... We do not have any server where I can actually test restoring it. ...
GET ONE - period, nothing else should be done until you do. Either that or report to management "we have no clue if we can actually restore, if we suffer a failure, there is a better than good chance we cannot recover - because we have never actually DONE IT"
I already pointed out about the archives, what does "in good hands" mean - are you or are you not backing them off frequently??
The only way to know if you can restore is to PRACTICE IT, period. Even if I 'blessed your script' (which I absolutely refuse to do, it would only instill a false sense of confidence), it doesn't mean you can actually RESTORE ALL DATA and recover it.
Sinan, May 27, 2008 - 11:06 pm UTC
Tom,
I knew that you would not like the idea of blessing the script :) and I agree on that with you 100%. I back up archive logs frequently. I wanted to just make sure that the script covers all necessary parts of the database, even if, as you said, I cannot make sure that I am going to be able to restore it without problems in case of an event. I know that I should work on getting an extra server.
Thanks for your time.
Sinan
Cold backup
Bob, June 22, 2008 - 1:52 pm UTC
Hi Tom,
I am learning about backup and recovery and this thread is really useful. If you do a cold backup in NOARCHIVELOG mode and the database has been shutdown cleanly (i.e. consistent shutdown). If I then move the datafiles, online redo logs, control file to another area/mount point and restore the backup. e.g I would have done
"backup control file to trace" beforehand, then opened the control file with resetlogs, will the online redo log files be automatically created or do I have to manually create them. Sorry but I don't have a test ground to do this..thanks!
June 22, 2008 - 9:40 pm UTC
you can only do a cold backup in noarchivelog mode - there is no other way to backup.
you would never want to backup online redo log in your situation. it would be harmless at best, a potential disaster at worst. It is not necessary
you would restore, recover, open resetlogs - they would "just come back"
Cold backup
Bob, June 23, 2008 - 5:25 am UTC
Hi Tom,
Thanks for your answer. Just so I can understand this, if I going to restore and recover the database, I have to be at the mount stage. Wouldn't it complain if I tried to do "startup mount" because it will be trying to open the control file and the online redo logs are not there.
It's only after the database is mounted that I can issue:
"alter database open resetlogs", correct?
Please could you take me through the series of steps with an example?
Many thanks for your invaluable input!
June 23, 2008 - 8:10 am UTC
[tkyte@dellpe ~]$ sysdba
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 23 07:58:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
<b>
sys%ORA9IR2> select * from v$logfile;
</b>
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------------------------
4 STALE ONLINE
/home/ora9ir2/oradata/ora9ir2/redoA.log
5 ONLINE
/home/ora9ir2/oradata/ora9ir2/redoB.log
<b>
sys%ORA9IR2> shutdown;
</b>Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA9IR2> !
[tkyte@dellpe ~]$ su - ora9ir2
Password: *
<b>[ora9ir2@dellpe ~]$ ls /home/ora9ir2/oradata/ora9ir2/redo[AB].log
/home/ora9ir2/oradata/ora9ir2/redoA.log /home/ora9ir2/oradata/ora9ir2/redoB.log
[ora9ir2@dellpe ~]$ rm /home/ora9ir2/oradata/ora9ir2/redo[AB].log
[ora9ir2@dellpe ~]$ ls /home/ora9ir2/oradata/ora9ir2/redo[AB].log
ls: /home/ora9ir2/oradata/ora9ir2/redo[AB].log: No such file or directory
</b>[ora9ir2@dellpe ~]$ exit
logout
[tkyte@dellpe ~]$ exit
exit
<b>
sys%ORA9IR2> startup mount
</b>ORACLE instance started.
Total System Global Area 571544368 bytes
Fixed Size 451376 bytes
Variable Size 167772160 bytes
Database Buffers 402653184 bytes
Redo Buffers 667648 bytes
Database mounted.
<b>sys%ORA9IR2> recover database until cancel;
Media recovery complete.
sys%ORA9IR2> alter database open resetlogs;
</b>
Database altered.
<b>
sys%ORA9IR2> select * from v$logfile;
</b>
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------------------------
4 ONLINE
/home/ora9ir2/oradata/ora9ir2/redoA.log
5 ONLINE
/home/ora9ir2/oradata/ora9ir2/redoB.log
<b>
sys%ORA9IR2> !ls /home/ora9ir2/oradata/ora9ir2/redo[AB].log
/home/ora9ir2/oradata/ora9ir2/redoA.log /home/ora9ir2/oradata/ora9ir2/redoB.log
</b>
sys%ORA9IR2>
Cold backup
Bob, June 23, 2008 - 9:07 am UTC
That's fantastic! Thanks so much for your time - completely cleared my doubts up and crystal clear! Cheers
Cold backup
Bob, June 23, 2008 - 9:07 am UTC
That's fantastic! Thanks so much for your time - completely cleared my doubts up and crystal clear! Cheers
Ricardinho, July 07, 2008 - 3:10 pm UTC
hi TOM
When I restore controlfile from autobackup
and than restore database and recover database;
I can only open the database with openresetlogs option.
why?
but I can apply archivelogs, that is created after the old controlfile.why force to open resetlogs?
July 07, 2008 - 3:29 pm UTC
because you will have applied them manually - you restore the control file, you apply as many archives as you want, and then you apply as many of the online redo's you have (as if they were archives) and then you open - resetting the logs.
ricardinho, July 08, 2008 - 10:10 am UTC
Tom
sorry but I didnt understand.
First I restore control file and then restore database
finally I recover database(archivelogs+redologs)
after this operation the scn of the control file, datafiles and redologs should be same.
so why forced to open resetlogs??
Could you explain it a little bit more?
July 08, 2008 - 4:18 pm UTC
you restore an OLD control file - so all we can use from it is "this is where the files are", none of the other control information is useful (it is OLD, it tells us about an OLD state of the database)
you restore the datafiles
you recover with as much log as you can or want to - and tell us "ok, stop now"
we do not recognize what you are calling online redo logs as online redo logs - they are processed just like archives at this point, they are just logs nothing special - and they might contain a 'future' that will never happen (you can stop anytime after the files are consistent - you need not apply all of them and in fact if you are restoring the control files and datafiles - you likely have lost all or some of your 'online' redo - and you haven't applied all of it, something really bad happened)
So, then you just open and ask us to "please reinitialize the online redo logs, lets get back to business"
why do you care? What is your goal - what is the question behind the question here...
ricardinho, July 09, 2008 - 7:16 am UTC
I am just thinking about the scenerio that my all control files are lost.
Since I restore control file from autobackup; it will force me to open resetlogs therefore I w¿ll lost data.
July 09, 2008 - 10:51 am UTC
no you won't, why do you think you will. We already said:
... because you will have applied them manually - you restore the control file, you apply as many archives as you want, and then you apply as many of the online redo's you have (as if they were archives) and then you open - resetting the logs. ...
you only "lose" data if you lost redo log files. Else, you recover everything and then open - resetting the logs, but that is OK because YOU ALREADY APPLIED THEM.
But chances are ...
kr, July 09, 2008 - 1:08 pm UTC
there are no chances.
While True that complete recovery is possible as shown by Tom, you describe an unduly restrictive scenario. Here are the issues :-
1) if you lost all controlfiles, you probably lost all online redologs as well. Because lets say you are using OMF and use 3 separate locations. If all 3 locations are gone, then you lost both control and onlinelogs.
db_create_online_log_dest_1/DBNAME/onlinelog
db_create_online_log_dest_1/DBNAME/controlfile
db_create_online_log_dest_2/DBNAME/onlinelog
db_create_online_log_dest_2/DBNAME/controlfile
db_create_online_log_dest_3/DBNAME/onlinelog
db_create_online_log_dest_3/DBNAME/controlfile
2) It is a Timing issue also:
I mean you took an autobackup, and immediately lost all controlfiles without advancing the checkpoint scn after the autobackup. So I think, in that case creating instead of autobackup, you may have to create your own controlfile with the NoRestLogs mode.
July 09, 2008 - 2:36 pm UTC
I agree, I said as much above - that if you suffered such a catastrophic loss of all controlfiles, you probably lost some online stuff as well - that is why we are recovering from a backup in the first place...
ricardinho
A reader, July 09, 2008 - 3:13 pm UTC
so lets say I have 2 active redolog groups.
During recover in this scenerio both 2 active redologs are applied and then resetting logging is forced right?
If I can apply online redologs why resetlogs then?
July 09, 2008 - 3:34 pm UTC
because we lost track of where we WERE.
you lost all of the controlfiles, you've blinded the data, we don't know what end is up.
You apply the onlines "as if" they were archives at this point, you eventually stop feeding them to us and say "cancel recovery"
and then you want to open up, we need a fresh start.
they are not online redo logs anymore really, they are just logs. you are in a recovery process here - it is not a normal open.
A reader, April 06, 2009 - 3:38 am UTC
hey tom
When u make the tablespace offline, no need for recovery.
However when u make the datafile offline, u need recovery.
Why?
April 13, 2009 - 9:53 am UTC
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo when YOU take a tablespace offline, YOU do so in "normal" mode by default. Meaning, a checkpoint is performed, and then the tablespace is taken offline. It needs no recovery, it is totally consistent and clean.
YOU can also take a tablespace offline in "immediate" mode, in which case YOU will have to perform recovery on it.
YOU can read about this in the documentation:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3002.htm#sthref5528 and this describes the datafile offline:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1004.htm#sthref3512 and states that no checkpoint is done, if you take a single datafile offline, it must be recovered when you online it (and hence, you have to be in archivelog mode in order to offline a datafile - or use "offline drop" to basically remove it from the database)
A reader, June 06, 2009 - 9:52 pm UTC
Hi Sir,
1-)When I open the db, controlfile checks the checkpoint system change numbers(csn) of the datafile headers.
If I copy a datafile online, (without alter tablespace begin backup),The csn of datafile will not be changed.
When I restore this datafile, what makes recovery impossible or why I cannot open the db?
2-) If I issue alter tablespace begin backup, and after this command, lets say there are transactions to this tablespace.
Is the data inserted into tablespace during this mode, or Is the data inserted into redologs and recovery is performed after alter tablespace end backup?
A reader, June 13, 2009 - 3:20 am UTC
Respected Mr Sir;
During recovery,
If there is new tablespace or datafile added in archivelogs or redologs I have to manually issue:
alter database create datafile .. as ..
Why doesnt oracle automatically create the datafile and update the controlfile?
June 15, 2009 - 11:55 am UTC
you do not in all cases - it depends on whether the controlfiles you are using KNOW of that file, if that file has ever been backed up.
If you restore a backup control file - as you might if you lost the entire database and were restoring to a new host - then guess what - the backup control file doesn't have the name (you do! we do not!)
if you restore from a media failure and you use the current control files and you have a backup of the file (you should schedule a backup very shortly after an operation like that in order to not be in a strange situation) - you would not have to tell us.
It all depends on whether we have the information or not.
controlfile backup
Reader, September 09, 2009 - 12:16 am UTC
Do I really need to take a backup of controlfile (binary copy using alter database command) when I can create a script (CREATE CONTROLFILE...)? if I lose my controlfiles, as I can execute CREATE CONTROLFILE script to recreate the controlfiles, I was wondering what is the use of binary copy of controlfile. Thanks.
September 09, 2009 - 6:13 am UTC
one less step to go through when you are already under pressure. If you need to recreate your controlfiles, you are in a massive recovery operation already. Why make it complex?
and the binary controlfile has the backup information/archive history/etc in it. the create controlfile statement will not have this information
clone from hot backup problem with flash_rec
Andreas, September 22, 2009 - 1:28 pm UTC
Hi Tom,
We have a server with a UAT Oracle 10gr2 being used 24x7.
This UAT runs with ARCHIVELOG.
We need to clone the database using HOT BACKUP
As it is a large database (0.6 TB), I tried with a very small demo DB to ensure that clone would work.
After taking each tablespace into a backup mode and copy dbf files I created a controlfile from the source db, edited to point to the new dbf files (names + locations) and after executing the create control file script OK - I tried to execute database recovery.
The recovery process suggested a new file in flash_recovery area, that I have not copied from the source to the target db (i.e. clone). So I copied that and hit the return to get a message that it could not open an archive log. I tried with cancel then - but when I tried to ALTER DATABASE OPEN RESETLOGS I got a message to say that file 1 needs media recovery.
Do I need to handle flash_recovery in some special way..?
PLEASE HELP
September 28, 2009 - 1:08 pm UTC
... As it is a large database (0.6 TB) ...
I think you mean "as it is a pretty small database, only 600gb" right?
just use the rman duplicate database command.
Question regarding restore from such scenario
Gaston DASSIEU-BLANCHET, December 06, 2009 - 6:03 am UTC
Hello Tom,
Thank you always for all your explanations. It really helps us underlings understand how this really works ^_^
Regarding the restore from an online backup (as you kindly showed us on the followup July 30, 2004 - 4pm), I have 3 existential questions:
A) Is there a way to avoid having to add again the tempfiles after restore from hot backup? I have backed them up in my online backup, so I would like to avoid having to remember the size, auto extend, etc... attributes of each temp file (The simpler the restore procedure, the less likely someone's gonna screw it up...).
B) Suppose my datacenter just went up in smoke. I have my (say) 4 days old weekly online backup + all the daily archived log backups since the online backup. I have successfully tested the below restore procedure:
1. restore the online backup from the weekly backup (including backed up control file and archived logs)
2. restore the archived logs from the daily backups
3. recover database until cancel using backup controlfile, CANCEL when I run out of archived logs, alter database open resetlogs.
Two doubts here:
B.1) Does this ALWAYS work, or do I need to take special measures when daily backing up the archived logs (currently I am just doing a file level copy). Can we ALWAYS restore until the end of any given archived log (except archived logs generated during an online backup), or should these logs be generated in a special way? (switch logfile, ...)
B.2) is there any advantage to having a control file backup taken with the last archived logs, or can I just restore with the one from the online backup without any risk?
Thanks in advance!
Gaston
December 07, 2009 - 2:25 pm UTC
existential questions - I must be branching out from the realm of technology :)
a) you never backup tempfiles, you cannot restore them, it makes no sense, there is nothing we can recover from them.
So, suggest you change your procedure - save time and space on your backups, skip the tempfiles and just add the required create's during recovery.
I'm assuming you DO NOT use rman (the book-keeper) but do you own backup and recovery. If so, just issue "alter database backup controlfile to trace" and backup the resulting trace file with your backup. In that file, you'll have stuff like:
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/ora11gr2/app/ora11gr2/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
you won't have to remember, we'll remember..
b.1) if doing user managed recovery, you want to backup - then switch your logfiles to rotate thru them and archive all of them 'alter system archive log all' and backup all of the archives you have.
You need all of the redo generate during the backup to have a consistent set of files to recovery with.
do this to test. IMMEDIATELY after you backup, restore your backup to a test machine.
b.2) it'll cover you in the event of a physical change to your database - adding/removing a file. It won't hurt, that is for sure.
Question regarding restore from such scenario (2)
Gaston DASSIEU-BLANCHET, December 07, 2009 - 6:35 pm UTC
Hello Tom,
Thanks for your reply.
> I'm assuming you DO NOT use rman (the book-keeper)
> but do you own backup and recovery
Yes, I am using a snapshot based backup (VMWare VCB)
> you never backup tempfiles, you cannot restore them,
> it makes no sense, there is nothing we can recover
> from them.
That was my understanding also. However, if you backup your VMs using VCB you backup everything. The advantage is that your DRP is very simple (restore the whole VM and click "play") but the disadvantage is that you waste tape and time.
> backup the resulting trace file with your backup
> In that file, you'll have stuff like:
Exactly what I am doing now ^_^
> b.1) if doing user managed recovery, you want to backup...
Sorry, I was not clear enough. My question was not about what to get with the online backup (you already clarified this beyond any doubts, I think).
My question is about the daily backup of archived logs, say, 3 days after the hot backup. Do we need to switch logfile / archive log all (in order to generate a checkpoint) before backing up the archived logs, or can we just back them up directly? In other words, are there some recovery scenarios where "alter database recover until cancel" by stopping after the last archived log you got does not fully recover all your datafiles?
Actually, I am quite confused about these two:
> alter system switch logfile;
> alter system archive log all;
I don't understand what each of them does and why both are necessary for the hot backup. I also don't understand well what a checkpoint is and when it is needed
Thank you in advance
December 10, 2009 - 8:12 am UTC
VMWare VCB is an os backup, not a database backup as far as I know.
...
My question is about the daily backup of archived logs, say, 3 days after the
hot backup. Do we need to switch logfile / archive log all (in order to
generate a checkpoint) before backing up the archived logs, or can we just back
them up directly?
......
well, you would want to make sure you only backup archives that are DONE archiving (this would all be so much easier and flexible if you just did real database backups with rman). As it is, you will want to query the database to generate a list of archives to backup - you want to make sure you don't try to backup a file we are still writing to.
...
Actually, I am quite confused about these two:
> alter system switch logfile;
> alter system archive log all;
I don't understand what each of them does and why both are necessary for the
hot backup. I also don't understand well what a checkpoint is and when it is
needed
.....
did you read the documentation on them? That would alleviate the confusion on "what they do".
In the hot backup, you need
a) datafiles
b) all redo generated since the backup began
so, suppose you have 10x1gb online redo logs. Suppose you generate 5gb of redo per hour. Suppose your backup take 1hour.
Ok, you start the backup and copy the datafiles.. Now, one hour later - we have 5gb of redo in the online redo logs that may or may not be in the archive destination yet (it could be, it might not be). In order to make sure we get everything - you just cycle through the logs (switch logfile) to make sure each one is "clean" - and then you archive log all - this command will wait for all outstanding archives to be created. Now you KNOW you have in your archive area all redo (at least) generated since your backup began - you need all of that to recovery with - so back it up right now. (technically a single log file switch would suffice, you don't need to cycle through them all)
Hot backup question
Erman Arslan, December 19, 2009 - 8:12 am UTC
Hi Tom,
I m cloning our prod database everynight by storage techniques.(by block level snapshots). While these snapshots are begin taken, database is in hot backup mode
During the process, following steps are taken,
ALTER SYSTEM CHECKPOINT; FROM DUAL;
ALTER SYSTEM SWITCH LOGFILE;
ARCHIVE LOG ALL;' FROM DUAL;
ALTER DATABASE BEGIN BACKUP;
--TAKE The snapshot(contains controlfiles,redo,dbf's,and undo, no archive) ----
ALTER DATABASE END BACKUP;
So , when I open this database, I take the following actions. ( all the dbf's,undo and redo are in the same File system paths,so no need to create controlfile)
startup mount;
alter database end backup; (*)
recover database;
alter database open;
My Question is that,
sometimes end backup(marked with *) gets error ORA-1235 , and sometimes everyting goes just fine.
If it fails with ORa-1235 , I use recover database using backup controlfile and apply current redolog, but sometimes applying current redolog also gets error, so i need to apply active redolog...
So what is the reason for the ORA-1235 and why this error is produced randomly ? And for the workaround as I mentioned, I apply current redo and open resetlogs, but sometimes it doesnt work neither, so I need to apply active redo and open resetlogs
??
Thanks
December 20, 2009 - 8:32 am UTC
In general, you need all of the redo UP TO and INCLUDING the instant of time you issue the alter database end backup command.
So you
a) checkpoint
b) switch log file
c) archive log all
d) AND THEN BEGIN BACKUP MODE <<<<====
At point a,b,c and during d - the database is still active, after b it is writing to online redo logs. another checkpoint can definitely be happening (they are happening pretty much constantly these days - incremental checkpoints to limit the recovery time in the event of a failure). So the datafiles you copy after d happens - in general need the ONLINE redo logs.
Normally when you backup you:
0) note where in the archive stream you are - you'll need all archives generate during a-d as well
a) put into backup mode (that actually checkpoints)
b) backup files
c) then switch
d) then archive log all
and to restore, you need that set of files.
You have it in the wrong conceptual order - but no big deal in this case since you are cloning.
Hot backup mode
Erman Arslan, December 19, 2009 - 9:27 am UTC
Also I have following questions..
1)Am I right with the following?
In hot backup mode, datafiles are written as normally.
Only difference is the backup scn's in dbf's headers is updated rather than normal scn,and more redologs are produced than usual because of the redo vector containing the whole images of the changes.( every change requires relevant db block to be stored in redo.)
2)So everything than can be needed for recovery is in redologs and posibility of a block split is eliminated.
Then so what is done when we use end backup command?
2a)-does automatic redo application occur? From the scn to the the backup_scn?
2b)-How can oracle decide whether a block is a splitted state and copy the whole block image from the redo?
-Should we use recover database; command after a restore of this full db (backed up with begin/end backup and copied with storage techs which are very fast--> takes 5 seconds. )
Thanks
December 20, 2009 - 8:39 am UTC
read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:271815712711 #2) the redo is not read, we are checkpointing to datafiles all of the time, we are writing to them (you said as much). The redo would not need to be read and applied - it was already 'applied', we were writing to the datafiles.
archived redo is needed for media recovery.
online redo is needed for crash (instance) recovery.
You are suffering neither of those when you end backup, hence redo is not needed.
If you do a restore, you would be using backups and redo logs, and we just know how to apply them correctly.
Begin/end backup ora-1235 & some more questions
Erman Arslan, December 21, 2009 - 1:43 pm UTC
Thanks fot the explanations,
But I have some more doubts..
I restore the database to another server. I backup the followings in 2-3 seconds.
redo's at the between the begin and end backup statements.
datafiles at the between the begin and end backup statements.
undo dbf's at the between the begin and end backup statements.
so , I copy the redo too.
1)So the redo's can be fractured,yes?But that never happened.Why?
2)Because redo is written sequentially? Dbf's is written random access?
3)Does this make the dbf 's more fracturable than online redo's?
4)And what could be the problem, if I do the following.
Begin backup
log switch..
etc..
etc..
Block level snapshot copy of all the db files (redos, undos, dbf's) --> 2 second
End backup
Restore the backup to another server(no archives there,init.ora doesnt contaion archive start)
startup mount
alter database end backup;
alter database open;
OR
startup mount
recover database using backup controlfile;
apply the current or active redo.
alter database open resetlogs;
What do I lose here? OR what kind of risk or negative effect is there?
5)And why I take ORA-1235 sometimes when I use end backup in the restored database when i m in mount state? CAn it be a bug? there are some bugs in metalink, maybe related..
5)Normally in prod database,When we end backup after we take storage backup,oracle simply update the frozen scn's in the relevant datafiles with the current scn.
So we restore it to another server, and mount it. Then issue end backup.
In that scenario,How can oracle now if it s a restored copy, because We are starting the db right?
But anyway, Should oracle do the same?I mean it should update the frozen scn only(like in the prod system where backup is taken).
So why need recovery? maybe there is no need? Is it a formality?
Is there a scneario that recovery is not needed for this kind of backup?
December 21, 2009 - 3:58 pm UTC
1) we write redo using OS blocks, not database blocks.
2) no, because datafiles use database blocks as the unit of writing - and database blocks consist of many OS blocks. Redo uses the OS block size.
3) it makes the datafiles subject to fractured blocks - since the IO size is greater than the OS block size. It takes multiple OS writes to get one database block safely to disk.
4) A database that is crashed during a MANUAL (non-rman) backup operation might be subject to needing media recovery.
I don't care how fast you think it is, it is a long time as far as computers are concerned. It is not a bug, you are doing things in the wrong order.
5) You need the online redo logs from a point in time AFTER the end backup, those onlines can contain changes needed by those files. I wrote that above.
Normally when you backup you:
0) note where in the archive stream you are - you'll need all archives generate during a-d as well
a) put into backup mode (that actually checkpoints)
b) backup files
c) then switch
d) then archive log all
c and d happen AFTER the end backup.
You have them happening BEFORE the end backup.
end backup begin backup block split doubts.
Erman Arslan, December 22, 2009 - 8:01 am UTC
1)I dont copy archives, I copy the online redologs.
SO why need to "archivelog all"?
Because I use end backup after I copied the dbf's and redos,I dont copy archives after that.
2)So in the target system,
startup mount;
alter database end backup; ( sometimes ORA-1235 )
What is the reason for that ORA-1235?
And when ORA-1235 produced,I restart the target database; and then issue end backup, it succeeds.
Or I use recover database using backup controlfile ...
3) Which one is the Blocksize that OS kernel (not the cp or other utilities) use for IO. This blocksize will be used when DBWR attempts to make an IO right?
Filesystem blocksize?
or
As We use multiple of os pagesize for making the db blocks aligned to the pages in the memory? Is it OS pagesize? ( OS pagesize= OS File system buffer size?)
For ex: If filesystem blocksize= 4k, than can we say that, 1 IO is made with 4k ?
4)After a System crash , there is no gurantee for instance recovery to be succeed , as long as OS uses write back, right? IF Os uses write through, it will decrease the possiblity of corruptions due to system crash?
December 31, 2009 - 7:45 am UTC
1) I'm telling you how it is normally done, I'm not telling you what you are doing (which is a bit of a "do it yourself hack". I'm trying to tell you "this is what you would normally do, therefore, you need to do something that has the same effect"
You have to manually apply your online redo logs (because they wouldn't have a history in the control files) sometimes during your faked recovery process. That is all. If you want to avoid that, you'll do things in the right order instead.
2) I told you why, please re-read above. Sometimes you need to apply media recovery in your case - because of "unlucky timing". It is not any different than an instance crashing whilst in backup mode (forget your scenario, you can mimic your situation by a) enter backup mode, b) wait a short time, c) shutdown abort, d) startup - you might well need media recovery. This is documented, to be expected. You are mimicking exactly those set of steps)
3) Ask your system admin what your OS block size is, typically 512b is a popular one.
cp uses OS block sizes for copying, everything does in fact. When Oracle writes an 8k block, it'll be done as 16 512b blocks for real - this is EXACTLY what causes a fractured block.
4) After a system crash, if the database was not in backup mode, instance recovery should always be possible since the database waits for writes to be made to disk - we open files in a way that ensures our IO's are complete. Only of the OS is faulty and reports "write complete" when the write was in fact NOT complete would instance recovery fail.
And that would be a major p1 bug in the OS itself.