HomeНаука и техникаRelated VideosMore From: Ramkumar Swaminathan

Instance Recovery in an Oracle Database - DBArch Video 10

195 ratings | 21267 views
This video explains how Instance recovery happens in an Oracle Database. Our Upcoming Online Course Schedule is available in the url below https://docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY/edit#gid=0 Reach us at [email protected]
Html code for embedding videos on your blog
Text Comments (40)
Dino Dixit (8 months ago)
very well explained. thank you Mr. Swaminathan.
FreakyMiky (9 months ago)
Hello, I got one question. During a rollforward - all changes listed in redolog are moved straight to the datafile or they are moved to the buffer cache as buffer blocks (dirty) and then moved to datafile with next checkpoint? If we got for example situation like on your video, and user X just after starting database up after crash (with rollforward done) wants to read salary of X's employee from where he will get information: 1) block is taken from datafile to buffer cache because datafile was updated straight from redolog file - or 2) row is already in buffer cache because it was moved there from redolog after rollforward? Thank you for your reply PS Your videos are amazing. keep going because you can present a lot of knowledge in a very simple and understandable way :D
Ramkumar Swaminathan (9 months ago)
Data is always brought into memory to process... so if the dirty buffer is written to datafile, it will be brought back to Memory... If its in memory it will use it from there... During a rollforward, the datablocks in the datafiles may not not have the correct data, hence they are recovered from the Redo entries in the redo log files... thus the correct data is brought back to the blocks...
Shaik Ahmed (9 months ago)
sir, which process will perform the Rollforward and Rollback?
Ramkumar Swaminathan (9 months ago)
That's when recovery happens on next startup
Shaik Ahmed (9 months ago)
thanks. one more question what if committed data is not written to the datafile and server got crash or rebooted.
Ramkumar Swaminathan (9 months ago)
SMON is responsible for recovery
Mahat Mishra (1 year ago)
when DBWR writes un committed record to data files,what will happen to the undo block and when another user queries the same record how system will fetch the unmodified data.
Ramkumar Swaminathan (1 year ago)
It will create a read consistent copy of the block that has DMLs uncommited... It uses the Data Block and corresponding undo block to create the read consistent block in Buffer Cache...
Amiya Rout (1 year ago)
How can the DBWR will write the updated buffer into the data files without a commit....can u pls explain
Ramkumar Swaminathan (1 year ago)
That's the architecture, transaction consistency is based on redo not data files
Akshay rai (1 year ago)
Hi sir... first of all you are doing a wonderful job... I have a query... i have asked this in various forums... but i haven’t really recieved a conclusive answer.... Does oracle use undo segment during instance recovery? If yes ... why so? Coz even redo contains old value of a transaction.... according to my understanding whenever instance recovery is done, oracle will use the ORLs to roll forward all the transactions(even uncommitted) ... coz of which all the necessary undo will also be created again... then it will rollback all the uncommitted transactions using the undo generated.... correct me if am wrong... so in that case why is the old value stored in the redo? This doubt has been bugging me for a long time... please shed some light on it... correct me if i have missed something.... coz most of the texts available are ambiguous
Ramkumar Swaminathan (1 year ago)
+Akshay rai yeah once in 3 seconds lgwr writes
Akshay rai (1 year ago)
Ramkumar Swaminathan thanku fo dat
Ramkumar Swaminathan (1 year ago)
+Akshay rai generally only checkpoint updates headers
Akshay rai (1 year ago)
And also if the dbwr writes the uncommitted data into the file.... will it update the datafile headers? As the checkpoint is not issued... the files cannot be updated right?
Akshay rai (1 year ago)
Ramkumar Swaminathan thanku for your time sir.... well i was always under the impression that whenever the dbwr has to write, checkpoint has to b triggered first.... well i think that clears my doubt.... it took some time to sink in though😅 But it is highly unlikely senario right? Coz lgwr is very aggressive... and more often than not the lgwr will write the transactions to the ORLs.... committed or not
Sachin Kashid (1 year ago)
Dirty buffers are written to data files but transaction are not commited..how does that possible..can u pls explain
Ramkumar Swaminathan (1 year ago)
Videos on Block space is coming up... Stay tuned..
Sachin Kashid (1 year ago)
Yes sure..fill factor is related with leaf level blocks in indexes..
Ramkumar Swaminathan (1 year ago)
HI Sachin, good to know you understood. I presume you mean Block Space utilization (free space) by Fill Factor. Basic idea is to ensure free space is reserved for future updates. I will be making videos on that in the new series on Performance, you will understand the details when you watch that... Please stay tuned...
Sachin Kashid (1 year ago)
Thanks ..got it..can you please explain the fill factor??
Ramkumar Swaminathan (1 year ago)
+Sachin Kashid because writing to data files updated data does not confirm transactions,. The SCN also matters.. watch my video on DML processing, should help you, even then if you have a question, please let me know...
kapalavai pavan (1 year ago)
Hi sir, thanks for your simple explanation....I have one doubt........1) Did SMON background process invloved in instance recovery? 2) Rollback will be happen from undo segments or from online redo logs?
Padala Sundeep (2 months ago)
@Ramkumar Swaminathan please help what do you mean by user defined and what kind of rollback happens from redo sir.
Ramkumar Swaminathan (1 year ago)
Hi Pavan,, yes SMON is responsible for recovery. Rollback happens from Undo as well as from Redo... . User defined Rollback happens from Undo only not from Redo.
Pavan B (1 year ago)
Hi Sir , Thank you for all your valuable time & energy for making the videos on the oracle Dba and sharing the knowlede. Request:Please make a video on Multitenant Architecture in 12c .
Pavan B (1 year ago)
Thank you very much Sir.They helped me a lot.
Ramkumar Swaminathan (1 year ago)
Hi Pavan , please find below answers 1. Yes the logs are in the redo logs 2. Instance recovery is for Single Instance database, and it happens automatically when database is started again. In RAC, Crash recovery happens automatically by one of the surviving nodes, there is no concept of Master Node now. You can see my video on Crash Recovery in RAC to understand this https://youtu.be/SMGkyJKrLEE 3. Regarding Cache fusion, I shall put up videos later, you can get the answers then ( hope its ok). Would be difficult to give a detailed answer on this reply 4. Will be dealt in tuning videos later... But as a quick answer, SQL Profiles are basically statistics of Actual Execution ( blocks accessed , rows processed etc), which gives the Optimizer better/more accurate information than Object statistics to create better plans. 5. Real time apply ensures Redo is written and applied to Standby database as its written to the Primary, even if the Standby is open Read Only. This needs additional license of Active Data Guard.... Hope these answers help...
Pavan B (1 year ago)
First of All Thank you very much.Below are my doubts. 1.when any transaction failed, where the log information about the failed transaction will store?(as per me it is redologs) 2.how we will confirm which is master node?(as per me by the help of crs logs).suppose master node instance is crashed,now which will became master node on what priority it will become master node? 3.In the cache fusion process i am not able to understand about the Resource Modes & Block Access Modes.( i have already read the doc given by oracle). 4.How to create sql profiles?,what is the necessity of sql profiles?we have sqladvisor,Awr reports,sqltrace,we can get execution plan. 5.what is real time apply in dataguard?
Ramkumar Swaminathan (1 year ago)
Pavan B please post
Pavan B (1 year ago)
HI Sir, Can i ask some doubts regarding the oracle dba ?
tirupati Y (1 year ago)
Nice Sir..

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.