Shulou(Shulou.com)05/31 Report--

This article mainly explains the "what are the missing Oracle undo tablespace files?" the content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the cases of missing Oracle undo tablespace files?"

Scenario 1:

Undo tablespace files are missing, undo data files are backed up, and logs are present and available.

If the backup file is a RMAN backup:

Restore

Recover

If you back up manually:

Cp

Recover

Scenario 2:

Undo tablespace files are missing, no undo data files are backed up, and logs are present and available.

Mount mode execution:

Recover performs read log recovery.

"alter database create datafile" is not allowed to rebuild the data file before the control file was created after the control file was rebuilt.

Scenario 3:

Undo tablespace files are missing, no undo data files are backed up, and there is an uncommitted transaction need recovery in the undo segment header.

Case simulation:

View the current rollback segment status

SYS@prod > select segment_name,status from dba_rollback_segs

SEGMENT_NAME STATUS

SYSTEM ONLINE

_ SYSSMU10_1197734989 $ONLINE

_ SYSSMU9_1650507775 $ONLINE

_ SYSSMU8_517538920 $ONLINE

_ SYSSMU7_2070203016 $ONLINE

_ SYSSMU6_1263032392 $ONLINE

_ SYSSMU5_898567397 $ONLINE

_ SYSSMU4_1254879796 $ONLINE

_ SYSSMU3_1723003836 $ONLINE

_ SYSSMU2_2996391332 $ONLINE

_ SYSSMU1_3724004606 $ONLINE

Perform a transaction:

HR@prod > update employees set salary = 100

107 rows updated.

Destroy undo:

[oracle@service1 prod] $cp / etc/passwd undotbs01.dbf

SYS@prod > shutdown abort

ORACLE instance shut down.

SYS@prod > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 541068368 bytes

Database Buffers 285212672 bytes

Redo Buffers 6565888 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3-see DBWR trace file

SYS@prod > alter system set undo_management=manual scope=spfile

SYS@prod > alter database datafile 3 offline

Database altered.

SYS@prod > select name,status from v$datafile

/ u01/app/oracle/oradata/prod/undotbs01.dbf

RECOVER

There is a transaction on the undo segment and a recover is required.

But we can't recover without a backup.

SYS@prod > alter database open; (DML operation is not allowed)

Database altered.

Create an undo tablespace:

Size 10M autoextend on

Modify the undo_tablespace/undo_management parameters:

SYS@prod > alter system set undo_tablespace=undotbs2 scope=spfile

SYS@prod > alter system set undo_management=auto scope=spfile

SYS@prod > startup mount force

SYS@prod > drop tablespace undotbs1 including contents and datafiles

Error:

ORA-01548: the active fallback segment'_ SYSSMU1 $'has been found to terminate the deletion of the tablespace

View the status of the rollback segment:

SYS@prod > select segment_name,status from dba_rollback_segs

SEGMENT_NAME STATUS

SYSTEM ONLINE

_ SYSSMU10_1197734989 $NEEDS RECOVERY

_ SYSSMU9_1650507775 $NEEDS RECOVERY

_ SYSSMU8_517538920 $NEEDS RECOVERY

_ SYSSMU7_2070203016 $NEEDS RECOVERY

_ SYSSMU6_1263032392 $NEEDS RECOVERY

_ SYSSMU5_898567397 $NEEDS RECOVERY

_ SYSSMU4_1254879796 $NEEDS RECOVERY

_ SYSSMU3_1723003836 $NEEDS RECOVERY

_ SYSSMU2_2996391332 $NEEDS RECOVERY

_ SYSSMU1_3724004606 $NEEDS RECOVERY

Modify hidden parameters:

SYS@prod > alter system set "_ offline_rollback_segments" = true scope=spfile

SYS@prod > alter system set "_ corrupted_rollback_segments" ='_ SYSSMU1_3724004606 $'

'_ SYSSMU2_2996391332 $','_ SYSSMU3_1723003836 $'

'_ SYSSMU4_1254879796 $','_ SYSSMU5_898567397 $'

'_ SYSSMU6_1263032392 $','_ SYSSMU7_2070203016 $'

'_ SYSSMU8_517538920 $','_ SYSSMU9_1650507775 $'

'_ SYSSMU10_1197734989 $' scope=spfile

SYS@prod > drop tablespace undotbs1 including contents and datafiles

Tablespace dropped.

Check the status of the rollback segment again:

SYS@prod > select segment_name,status from dba_rollback_segs

SEGMENT_NAME STATUS

SYSTEM ONLINE

_ SYSSMU30_260568829 $ONLINE

_ SYSSMU29_1074352905 $ONLINE

_ SYSSMU28_3910680688 $ONLINE

_ SYSSMU27_3692739459 $ONLINE

_ SYSSMU26_345096822 $ONLINE

_ SYSSMU25_2172562784 $ONLINE

_ SYSSMU24_109115100 $ONLINE

_ SYSSMU23_3673005684 $ONLINE

_ SYSSMU22_2483286186 $ONLINE

_ SYSSMU21_2267006838 $ONLINE

The data can be verified and the data will be submitted by default.

Conclusion in the state of open, the undo data file is corrupted and there are uncommitted transactions. After recovery, the data will be committed by default.

Scenario 4:

Undo tablespace files are missing, no undo data files are backed up, there are no uncommitted transactions, and no recover recovery is required.

Briefly describe the processing method:

After the undo data file offline, it directly changes to the offline state, not the recover state.

Create a new undo tablespace directly and modify the undo_tablespace parameter to replace it.

Thank you for your reading, these are the contents of "what are the missing Oracle undo tablespace files?" after the study of this article, I believe you have a deeper understanding of the situation of missing Oracle undo tablespace files, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.