What is Undo ?
Undo records are used to:
Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
- Roll back transactions when a Rollback statement is issued
- Recover the database
- Provide read consistency
- Analyze data as of an earlier point in time by using Oracle Flashback Query
- Recover from logical corruptions using Oracle Flashback feature
When a Rollback statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
How to handle the Undo Corruption Scenario.
1. Create a new system management undo tablespace:
SQL> connect / as sysdba
SQL> create undo tablespace undotbs2 datafile ‘/u02/oracle/oradata/test/undotbs2.dbf’ size 500m;
2. Determine the problem undo segment:
SQL> select SEGMENT_NAME, STATUS from dba_rollback_segs;
The problem segment will show a “Needs Recovery” status.
3. Alter the system to use the new undo tablespace after creating the new undo tablespace
SQL> create undo tablespace UNTOTBS2 datafile ‘/u02/oradata/undotbs2.dbf’;
SQL> alter system set undo_tablespace=undotbs2 scope=both; (Note if you are using an pfile, set scope=memory)
4. If you are using an spfile, create a pfile from it:
SQL> connect / as sysdba
SQL> create pfile=’/u01/oracle/admin/test/pfile/inittest.ora’ from spfile;
5. Edit the inittest.ora pfile and add (using the undo segment from our example error):
*._offline_rollback_segments=” _SYSSMU8$”
*._corrupted_rollback_segments=”_SYSSMU8$”
6. Now shutdown your instance, this may require a shutdown abort,
but try a shutdown immediate first.
7. Startup using the manual startup command:
SQL> startup pfile=’/u01/oacle/admin/test/pfile=inittest.ora’
8. Alter the old undo tablespace offline:
SQL> alter tablespace undotbs1 offline;
9. Drop the offending tablespace:
SQL> drop tablespace undotbs1 including contents and datafiles;
10. Shut down immediate.
11. Edit the inittest.ora file to eliminate the underscore parameters.
12. Restart the instance using the pfile option.
13. Create an spfile from the pfile:
SQL> create spfile from pfile=’/u01/oracle/admin/test/pfile/inittest.ora’;
