Undo tablespace repair Summary

Undo tablespace repair Summary

First, you must know that the rollback segment cannot be offline or deleted under the automatic management of undo tablespace. You can change it to manual before performing the operation, we know that the undo tablespace is used to store the image before the data is changed. If there is a problem, we can handle it in two cases;
First case: If the corrupted rollback segment does not have a transaction being executed, the problem is relatively simple. You can directly Delete the rollback segment without data loss.

Specific process: assume that the file undotbs01.dbf is lost or damaged.

1. Put the data file offline and run the following command in the mount status:

SQL> alter database datafile '/software/oradata/JLPROJCT/undotbs01.dbf' offline drop;

2. Open the database

SQL> alter database open;

3. we know that a data file corresponds to n undo segments, and now many undo segments have been offline. We will not perform any operations on them first, and check that the undo segments are not offline, you will find that these undo segments of offline need to be restored (need recover)

SQL> select status, count (*) from dba_rollback_segs group by status;

Status count (*)

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

ONLINE 23

Need recovery 5

OFFLINE 143

SQL> select segment_name, status from dba_rollback_segs where status <> 'offline'; all user rollback segments need to be restored, and the status is need recovery ., this statement does not display offline rollback segments due to corrupted data files.

SEGMENT_NAME STATUS

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

System online ### this is the SYSTEM rollback segment.

_ Syssmu154_20171636531 $ need recovery

_ Syssmu155_000060000895 $ need recovery

_ Syssmu156_0000802683 $ need recovery

_ SYSSMU157_2723916652 $ need recovery

_ SYSSMU158_1435464080 $ need recovery

4. Create a rollback tablespace,

SQL> create undo tablespace undo2 datafile '/software/oradata/JLPROJCT/undotbs02.dbf' size 100 m;
 

Tablespace created

5. Set the rollback segment to adult work management, and delete the damaged rollback segment.

SQL> alter system set undo_tablespace = 'undo2' scope = spfile; # specify the newly created undo tablespace.
 

System altered

SQL> alter system set undo_management = 'manual' scope = spfile;

System altered
 

6. Create a pfile

SQL> create pfile = '/Oracle/app/pfile. ora from spfile;
 

File created

7. Close the database consistently,

SQL> shu immediate
 

8. Add a hidden parameter to the pfile file and list these rollback segments in this parameter value,

*. _ Offline_rollback_segment = ('_ blank $', '_ syssmu155_000060000895 $', '_ syssmu156_0000802683 $', '_ blank $', '_ blank $ ')

9. Create a spfile and start the database.

SQL> create spfile from pfile;
 

Spfile created

SQL> startup

10. At this time, the number of rollback segments has not changed,

SQL> select segment_name, status from dba_rollback_segs where status <> 'offline ';

SEGMENT_NAME STATUS

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

SYSTEM ONLINE

_ Syssmu154_20171636531 $ need recovery

_ Syssmu155_000060000895 $ need recovery

_ Syssmu156_0000802683 $ need recovery

_ SYSSMU157_2723916652 $ need recovery

_ SYSSMU158_1435464080 $ need recovery
11. Because it is manually managed, you can directly delete those rollback segments.
SQL> drop rollback segment "_ syssmu154_20171636531 $ ";

Rollback segment droped
 

.

.

.

.

Delete the original undo tablespace.

SQL> drop tablespace undo1 including contents;

13. Restart the database,

Shu immediate

Startup

14. Pay attention that the undo management is manual at this time, so the previous changes and corrections will be automatically managed. Delete the added implicit parameter *. _ offline_rollback_segment.

SQL> alter system set undo_management = 'auto' scope = spfile;

Case 2: when there are active transactions in the rollback segment of the corrupted undo tablespace, these transactions must be forcibly committed, resulting in data loss.

1. Start the database to the mount status. It can only be started here,

2. roll back the problematic segment offline

SQL> alter database datafile '/software/oradata/JLPROJCT/undotbs01.dbf' offline drop;

3. Check the status of the rollback segment, which is slightly different from the first case. She does not have an offline rollback segment.

SQL> select usn, xacts from v $ rollstat;

SQL> select status, count (*) from dba_rollback_segs group by status;

Status count (*)

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

ONLINE 23

Need recovery 5

QL> select segment_name, status from dba_rollback_segs where status <> 'offline ';

SEGMENT_NAME STATUS

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

_ Syssmu154_20171636531 $ need recovery

_ Syssmu155_000060000895 $ need recovery

_ Syssmu156_0000802683 $ need recovery

_ SYSSMU157_2723916652 $ need recovery

_ SYSSMU158_1435464080 $ need recovery

4. Try to create an error. In actual work, you can judge from here. In the first case, you can create a new one.
You must first disable the use of the old rollback segment and rollback space:

SQL> create pfile =/oracle/app/pfile. ora from spfile
 

File created

SQL> shutdown immediate;

Add and modify the following content in pfile:

*. Undo_management = 'manual' ### you can delete rollback segments only after manual management.

*. _ Offline_rollback_segments = ') ### Delete these rollback segments

*. Undo_tablespace = 'undo2' ### this will prevent future transactions from using old rollback segments and undo tablespaces,

5. Create a spfile and start the database.

SQL> create spfile from pfile;
 

Spfile created

SQL> startup

6. Delete the old rollback segments and tablespace:

SQL> drop rollbackup segment '_ syssmu154_20171636531 $ ';

'

'

'

'

After deleting all the rollback segments of the need recovery,

SQL> drop undo tablespace undo1 including contents; ### it is unnecessary to add a data file because it has been lost (and datafiles option)

7. Create a New undo tablespace, start the database, and modify undo_management to automatic.

Note that all uncommitted transactions are committed.

Conclusion: if an undo error occurs, you can first offline the data file, change undo_management to manual, then find the rollback segment in the need recover status, and modify the implicit parameter *. _ offline_rollback_segments: add the rollback segments whose status is need recover and delete them. Re-create the undo tablespace, then undo_tablespace = the newly created tablespace, open the database, and finally set undo_management = auto.

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

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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