Undo Tablespace recovery

Undo tablespaceFrom my understanding i amjust elobrating howards outstanding contribution

Day:FridayBackup the UNDO tablespace on Friday
2:PMSTMT:update table After 2: PM
Action 1: Generates UNDO for the OLD VALUES and inserts into UNDO TABLESPACE…Action 2: Generates REDO for the update stmt and stored in REDOLOG files which will be archived later…Need for Recovery…This will have committed and uncommitted data where committed data will have SCN to it where as uncommitted redo will not have SCN…

Changes happening for next days…Undo Tablespace corrupted…
Scenario starts here
We restore the Friday Undo Backup here…
Recover the UNDO Tablespace to bring the DB upApplying Archive logs starts here which will have commited and uncommitted data hereSince the log has the SCN for every commit

1. it starts inserting all the data Here is the thing which may be crucial as far as I have understoodIt has both commited and uncommitted data…

The Undo will be generated for those statements and corresponding redo will be generated but the internal transaction table will be updated for every committed transaction with the help of SCN from the archived logs so here the inserted stmts which have the SCn will be commited which is roll forward and the internal transaction tables gets updated with the respective SCN…

Now all the statement were executed and UNDO will have some uncommitted transactions after recovery in fact during the recovery. Those are the one which will not have an entry n the internal transaction table.

Now SMON starts rollback with the help of internal transaction table.That statement which does not have commit entry in the Internal Transactions table will all be rolled back.


So the Internal transaction tables uncommitted entry and the undo generated during the recovery will help in rolling back.This is what I have understood …

Hope this helps

Undo tablespace Concepts

Hi ,

I would like to share my experience about the UNDO tablespace problem i faced while running a customised masking scripts

I was involved in resynching the UAT with Production.

The Production was restored on UAT database after recreating the control file.We have a script file which masks the sensitive data on the UAT before bringing to the office.

Its has serious of update statements which updates the column values to zero but unfortunately we had only one commit statement which will be fired at the end of the script

Since the number of records were very high i started hitting the undo tablespace reaching more 6gigs and almost we ran out of space in the system.

se we changed the script to have commit after every DML which resulted in reusing the UNDOTBS

Now i have clearly understood what undo serves for

Regards
Elamaran

Redo Log Thread Number

I was just wondering for few days why the thread number is always constant in the archive log but i am working with the single instance database so i was unaware of that ..

Now in RAC database each instance has its own thread so if we two have nodes we will have 2 threads numbers representing each instances..