What about undo corruption in Oracle without backup

Source: Internet
Author: User
Tags manual rollback backup

If Oracle unfortunately encounters undo corruption in operation, the best approach is full recovery, but if there is no backup, you can take an unconventional approach (taking advantage of the hidden parameters of Oracle) and if undo contains uncommitted transactions at this time, can result in a little bit of data loss (which is generally tolerable) and no data loss if there are no uncommitted transactions. The main steps are:

1. Modify the Undo Table space management for manual;

2. Set the hidden parameter (_offline_rollback_segments or _corrupted_rollback_segments) to identify the affected rollback segment so that Oracle ignores uncommitted transactions on it;

3. Manually delete the affected rollback segment and undo table space, and then rebuild the new undo table space;

4. Restore Undo table space is managed automatically.

The experiment is as follows:

Step 1.

If the undo data file is corrupted, the database can only go to the Mount state, and the following error occurs when open:

Ora-01157:cannot identify/lock data file 14-see DBWR trace file

Ora-01110:data file: ' I:\INTEL_DATA\O06DMS0\UNDO1. O06dms0 '

Description The undo file is corrupted or missing, offline the file to open the database:

sql> ALTER DATABASE DataFile ' I:\INTEL_DATA\O06DMS0\UNDO1. O06dms0 ' offline drop;

sql> ALTER DATABASE open;

The purpose of opening the database is to find the affected rollback segment:

Sql> select Segment_name,status from Dba_rollback_segs;

Segment_name STATUS

-------- ---------------------- ----------------

SYSTEM ONLINE

_syssmu10_1201331463$ OFFLINE

_syssmu9_2926456744$ OFFLINE

_syssmu8_640224757$ OFFLINE

_syssmu7_3984293596$ OFFLINE

_syssmu6_3694658906$ OFFLINE

_syssmu5_3475919656$ OFFLINE

_syssmu4_168502732$ OFFLINE

_syssmu3_1987193959$ OFFLINE

_syssmu2_3908286755$ OFFLINE

_syssmu1_3281912951$ OFFLINE

Sql> Show Parameter Undo

NAME TYPE VALUE

--------------------------------- --- ----------- -------------

Undo_management string AUTO

Undo_retention Integer 900

Undo_tablespace string Undo1

To close the database:

sql> shutdown immediate;

Step 2.

To create a temporary pfile:

sql> create pfile= ' H:\initO06DMS0.ora ' from SPFile;

Modify Pfile as follows:

*.undo_management= ' Manual '--undo Table space Management method modified to Manual

*.undo_tablespace= ' Undo2 '--Specify a new undo table space

*._offline_rollback_segments= (' _syssmu10_1201331463 $ ', ' _syssmu9_2926456744$ ', ' _syssmu8_640224757 $ ', ' _SYSSMU7_ 3984293596$ ', ' _syssmu6_3694658906 $ ', ' _syssmu5_3475919656$ ', ' _syssmu4_168502732 $ ', ' _syssmu3_1987193959$ ', ' _ syssmu2_3908286755 $ ', ' _syssmu1_3281912951$ ')--all affected rollback segments are listed here

and restart the database by changing pfile:

sql> startup pfile= ' H:\initO06DMS0.ora '

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.