ORA-01173: data dictionary indicates missing data file from system tablespace

Posted by PDSERVICE on Mar 03, 2016 In

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline: +86 13764045638 E-mail: service@parnassusdata.com

ORA-01173: data dictionary indicates missing data file from system tablespace

Cause: Either the database has been recovered to a point in time in the future of the control file or a datafile from the system tablespace was omitted from the create control file command previously issued.

Action: For the former problem you need to recover the database from a more recent control file. For the latter problem, simply recreate the control file checking to be sure that you include all the datafiles in the system tablespace.

Oracle Server – Enterprise Edition – Version 8.1.7.4 to 10.2.0.3 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

This document presents an option to patch the SYSTEM rollback segment header when errors ORA-600 [4193] / ORA-600 [4194] are produced in the SYSTEM rollback segment. This situation could be avoiding the database to be opened.

The supported procedure to fix this problem when the SYSTEM rollback segment is affected, is to make a Point In Time Recovery before the logical inconsistency.

ORA-600 [4193] and ORA-600 [4194] are normally produced by new transactions and it happens when there is a mismatch in the undo segment header (info in TRN CTL / FREE BLOCK POOL) and the undo segment block . In the case it happens in undo segments other than SYSTEM the solution is to drop the rollback segment. Here is a procedure to manually fix these errors when the SYSTEM rollback segment is involved.

Fix

Take a backup before applying this procedure.

Using bbed set ktuxc.ktuxcnfb and ktuxc.ktuxcfbp[0..x].ktufbuba to 0 in the SYSTEM rollback segment header. In that way Oracle will use an empty undo block for the new transaction avoiding the comparison between the undo block segment header and the undo block pointed by it.

Example:

This is part of a rollback segment header dump

TRN CTL:: seq: 0x00af chd: 0x0036 ctl: 0x002a inc: 0x00000000 nfb: 0x0001
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00400006.00af.0f scn: 0x07be.a0bae152
Version: 0x01
FREE BLOCK POOL::
uba: 0x00400006.00af.0f ext: 0x0 spc: 0x13b4
uba: 0x00000000.00a8.0d ext: 0x7 spc: 0x1a2c
uba: 0x00000000.009b.0b ext: 0x3 spc: 0x1c08
uba: 0x00000000.0092.27 ext: 0x3 spc: 0x12d0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

1. With bbed set the appropriate offset and modify ktuxc.ktuxcnfb to 0x0000. In the example nfb: 0x0001.

2. Set the appropriate offset to modify all the not null ktuxcfbp[0..x].ktufbuba to 0x00000000. In this example only ktuxc.ktuxcfbp[0].ktufbuba has a not null value which is 0x00400006

3. As the block has been modified set the block checksum to the new value or disable the checksum in the block.

The partial block dump after the modification is:

TRN CTL:: seq: 0x00af chd: 0x0036 ctl: 0x002a inc: 0x00000000 nfb: 0x0000
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00400006.00af.0f scn: 0x07be.a0bae152
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.00af.0f ext: 0x0 spc: 0x13b4
uba: 0x00000000.00a8.0d ext: 0x7 spc: 0x1a2c
uba: 0x00000000.009b.0b ext: 0x3 spc: 0x1c08
uba: 0x00000000.0092.27 ext: 0x3 spc: 0x12d0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
nfb=ktuxc.ktuxcnfb “number of non-empty slots in free block pool”
ktuxc.ktuxcfbp=free block pool entries

4. OPEN the database and shrink the system rollback segment. It is just to free the extents in the segment and to start from “scratch”:

alter rollback segment SYSTEM shrink;

You are here

ORA-01173: data dictionary indicates missing data file from system tablespace