Error 376 encountered while recovering transaction

Messages in the alert.log (ORA-00376 Error 376 encountered while recovering transaction) after partial restore and open resetlogs. [ID 283846.1]

---

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.1.0.2
Information in this document applies to any platform.
Trying to clone the database with a subest of tablespaces from production backup

Offline dropped unwanted datafiles or the controlfile is created without them.

Did SCN/SEQUENCE/TIME based incomplete recovery and opened the database with resetlogs option successfully.

But SMON transaction recovery is failing with the following messages in the alert.log

ORA-00376: file 263 cannot be read at this time
ORA-01111: name for data file 263 is unknown - rename to correct file
ORA-01110: data file 263: '/usr/app/oracle9i/9.2.0.4/dbs/MISSING00263'
ORACLE Instance STAGE (pid = 8) - Error 376 encountered while recovering transaction (8, 81) on object 1426463.

Goal

This is expected that SMON will report the error if the datafile is offline dropped or the controlfile is created without the datafile.While processing the alter database open resetlogs command all the missing datafiles and tablespaces will be added into the controlfile after checking with dictionary because oracle always thinks that dictionary always contains the right information.Corresponding messages will be written to the alert.log

Creating OFFLINE file 'MISSING00117' in the controlfile.
This file can no longer be recovered so it must be dropped.

Tablespace 'AMERITRADE_APP_INDX' #233 found in data dictionary,
but not in the controlfile. Adding to controlfile.

Solution

This is explained with an example.

Suppose we are trying to do an incomplete recovery until archivelog seq 10 and assume that one long running delete on table test which is in datafile 2 of tablespace TEST was going on and it was committed in archivelog seq 11 only.

With this scenario if we are trying clone this database with tablespace system,rbs and data.

1) Recreated the controlfile to exclude all datafiles other than that of SYSTEM,RBS,DATA.

2) Mounted the database and did an incomplete recovery until sequence 10.

3) Opened the database with resetlogs successfully.

At this point SMON checks the undo segments headers to see all the failed the transaction for doing transaction recovery and it sees that the delete on table test was going on and needs to be rolled back.But it fails because the datafile 2 cannot be read and its name in the controlfile is 'MISSING..'.So it reports the error in the alert.log .

select object_name from dba_objects where object_id=1426463

Dropping the object or the tablespace itself will stop SMON trying TX recovery.