I have a question about backup and recovery. During the recovery of the database (a data file gets corrupted for ex), the database restores the database (meaning it will roll-forward/apply all the commited changes in the logs to the data files) and then it will recover the database (meaning that it will roll-back all uncommited transactions). My question is, does Oracle roll-back all the uncomitted transactions from the data files or just erases them from the logs? If Oracle rolls back all the uncommitted transactions from the data files, why were uncommitted transactions written to data files in the first place? Thanks for your help.
Sometimes it will have to free some buffer cache for online transactions.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
As a part of instance recovery it applies all the redo entries (changes)to the datafiles during roll forwared phase; the point to be noted here is it also applies redo entries pertainig to the rollback segment and its transction talbles.
Once it has rolled forward, it rolls back all the uncommited data, by looking into the transcaction tables of rollback segments.
Hope this clears ur doubt,
Before going into recovery first you should understand how Oracle writes information whenever a transaction occurs.
When a transaction occurs(insert or update or delete) Oracle writes the new information in the database buffer cache and the 'old' information is stored in Rollback Segment for read-consistency.
Oracle says that whenever a block undergoes changes it stores the minimal amount of information necessary for 'redo' in the logfiles. So, it stores the data block(in the buffer cache), the command and the rollback block in the logfiles.
Since Oracle writes all the information into the logfiles sequentially it does not know whether a transaction is committed or not. So, when a transaction gets committed it assigns a 'commit' scn and updates the header of rollback segment's transaction table. This information is also stored in the 'redo' logfile.
When Oracle does the recovery, it writes all the action happened when the transaction occurred. un-committed informations will be rolled back.
Say No To Plastics
Hi, please tell me whether my understanding on this issue is correct or not:
When doing recovery for a corrupted datafile, Oracle needs two things, one is the datafile itself, the other is redo log files. The process is that Oracle will open the datafile that you backed before. Then it applies redo log changes to the datafile. So in the redo log, it contains something like the SQL commands you executed before in the temporal order. If there is a commit in the redo log, Oracle will commit it. After finishing, if there is no commit for some DML, Oracle will roll back those transactions.
Is this understanding correct?
Bit incorrect , Recover process need achivelog file in this case , Oracle will first do roll forward , Based on the old datafile , and all the transactions in achivelog file will be applied to datafile and finally it will apply redo log file .
"Germany is still the best soccer team"
Surely you mean second best ??
- no intention to open a soccer thread ; )
Click Here to Expand Forum to Full Width