Shulou(Shulou.com)06/01 Report--

I. Overview

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

1.1 the system rollback segment is used when undo_management is set to MENUAL, that is, the undo records is recorded to the SYSTEM segment under the SYSTEM tablespace.

SQL > col segment_name format A25

SQL > select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='ROLLBACK'

SEGMENT_NAME TABLESPACE_NAME BYTES NEXT_EXTENT

-

SYSTEM SYSTEM 393216 57344

Through the above statement, we found that the system segment for rollback exists in the system tablespace. By default, there is only one segment, and it is relatively small, so if you use system segments to store undo records. It will definitely affect the performance of the database. So Oracle recommends using Undo tablespace to manage undo records.

1.2 use UNDO tablespace to manage rollback segments when undo_management is set to AUTO. At this point, we will have multiple undo segment, and these segment are stored in the UNDO tablespace. This will improve the performance of DB.

SQL > select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='TYPE2 UNDO'

SEGMENT_NAME TABLESPACE_NAME BYTES NEXT_EXTENT

-

_ SYSSMU1_3724004606 $UNDOTBS1 8519680 65536

_ SYSSMU2_2996391332 $UNDOTBS1 8519680 65536

_ SYSSMU3_1723003836 $UNDOTBS1 2228224 65536

_ SYSSMU4_1254879796 $UNDOTBS1 3276800 65536

_ SYSSMU5_898567397 $UNDOTBS1 8519680 65536

_ SYSSMU6_1263032392 $UNDOTBS1 8519680 65536

_ SYSSMU7_2070203016 $UNDOTBS1 8519680 65536

_ SYSSMU8_517538920 $UNDOTBS1 1179648 65536

_ SYSSMU9_1650507775 $UNDOTBS1 8519680 65536

_ SYSSMU10_1197734989 $UNDOTBS1 8519680 65536

10 rows selected.

SQL >

From the query results of the above SQL, we can see that there are 10 undo segment to store the undo records.

The above results are viewed through the dba_segment view. You can also view the information through the v$rollstat and v$rollname views, which display information for all rollback segments, including system and undo segments.

SQL > col name format A25

SQL > select s. Usn where s.usn=n.usn n. Name where s.usn=n.usn s. ExtentsParent s.hwmsizeJournal s.status from v$rollstat s, v$rollname n where s.usn=n.usn

USN NAME EXTENTS HWMSIZE STATUS

--

0 SYSTEM 6 385024 ONLINE

1 _ SYSSMU1_3724004606 $3 8511488 ONLINE

2 _ SYSSMU2_2996391332 $3 8511488 ONLINE

3 _ SYSSMU3_1723003836 $4 2220032 ONLINE

4 _ SYSSMU4_1254879796 $4 3268608 ONLINE

5 _ SYSSMU5_898567397 $3 8511488 ONLINE

6 _ SYSSMU6_1263032392 $3 8511488 ONLINE

7 _ SYSSMU7_2070203016 $3 8511488 ONLINE

8 _ SYSSMU8_517538920 $3 1171456 ONLINE

9 _ SYSSMU9_1650507775 $3 8511488 ONLINE

10 _ SYSSMU10_1197734989 $3 8511488 ONLINE

11 rows selected.

SQL >

Second, the treatment of UNDO damage 2.1 method 1, use system segment

One method mentioned is to use the rollback segment of SYSTEM. The steps are as follows:

(1) create a pfile with spfile, and then modify the parameters:

# * .undo _ tablespace='UNDOTBS1'

# * .undo _ management='AUTO'

# * .undo _ tablespace

# * .undo _ retention

Undo_management='MANUAL'

Rollback_segments='SYSTEM'

(2) restart DB with the modified pfile

SQL > STARTUP MOUNT pfile='F:/initorcl.ora'

(3) delete the original tablespace and create a new UNDO tablespace

SQL > drop tablespace undotbs

SQL > create undo tablespace undotbs1 datafile'/ u01max oradata size undotbs1.dbf'Unip 10m

(4) close the database, modify the pfile parameters, then create the spfile with the new pfile, and start the database normally.

* .undo_tablespace='UNDOTBS1'

* .undo_management='AUTO'

# undo_management='MANUAL'

# rollback_segments='SYSTEM'

2.2. Method 2: skip the damaged segment

In method one, we used system segment. We learned from the first part that there are multiple undo segment, and we can use alert log to see which segment is being used, and these segments may be corrupted. We just need to skip these corrupted segment, start DB normally, create a new UNDO tablespace, and switch again.

(1) modify pfile and add parameters:

*. _ corrupted_rollback_segments='_SYSSMU11 $','_ SYSSMU12 $','_ SYSSMU13 $'

The values of these fields are viewed through alert log. You can also view it with the following command:

# strings system01.dbf | grep _ SYSSMU | cut-d $- f 1 | sort-u

(2) start DB with the modified pfile

DB can start normally because which damaged segment has been skipped.

(3) create a new UNDO tablespace and switch it over

SQL > create undo tablespace undotbs1 datafile'/ u01max oradata size undotbs1.dbf'Unip 10m

SQL > alter system set undo_tablespace=undotbs1

SQL > drop tablespace undotbs

(4) modify pfile, create spfile, and start normally

Delete as follows:

*. _ corrupted_rollback_segments='_SYSSMU11 $','_ SYSSMU12 $','_ SYSSMU13 $'

Method 3: use rman backup to restore

Startup mount

-- verify that the backup has bad blocks

RMAN > backup validate datafile 22

-- query bad block information

RMAN > select * from v$database_block_corruption where file#=22

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID

--

22 32120 1 0 CORRUPT 1

-- repair bad blocks

RMAN > blockrecover datafile 22 block 32120 from backupset

-- Open the database

Alter database open

Note: bad blocks of other files (system/sysaux/ user data files) can also be repaired in this way.

Method 4: use the parameters _ offline_rollback_segments and _ corrupted_rollback_segments

In the absence of backup

Use _ offline_rollback_segments and _ corrupted_rollback_segments

1. First, offline the data file and execute it in the mount state:

Alter database datafile'/ oracle/app/oracle/oradata/cus/undotbs01.dbf' offline

Alter database open

two。 Query which undo segments need to be restored (need recover)

Select segment_name,status from dba_rollback_segs where status = 'NEEDS RECOVERY'

3. Create a new rollback tablespace and generate pfile

Create undo tablespace undotbs2 datafile'/ oracle/app/oracle/oradata/cus/undotbs02.dbf' size 300m

Alter system set undo_tablespace='undotbs2'

Create pfile='/home/oracle/pfile.txt' from spfile

4. Edit pfile to add implicit parameters

-- query the undo segments to be restored according to step 2

? *. _ offline_rollback_segments= ('_ SYSSMU3_1499641855 $','_ SYSSMU4_3564003469 $',.)

*. _ corrupted_rollback_segments= ('_ SYSSMU3_1499641855 $', _ SYSSMU4_3564003469 $',.)

5. Restart to delete the old undo

Startup pfile='/home/oracle/ pfile.txt'

Drop tablespace undotbs1 including contents and datafiles

? select segment_name,status from dba_rollback_segs where status = 'NEEDS RECOVERY'

6. Remove the implicit parameters and restart the database

Remove the first two implicit parameters _ offline_rollback_segments and _ corrupted_rollback_segments from the pfile.txt file, and then restart the database

Shutdown immediate

Startup pfile='/home/oracle/pfile.txt'

Create spfile from pfile='/home/oracle/pfile.txt'

Shutown immediate

Startup

Summary

The above are several methods to deal with the failure of UNDO, in which method 2 is similar to method 4, which uses parameters to recover.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.