:

NID-00137: All datafiles that are not dropped should be readable

The Problem

You are using the DBNEWID utility (The NID command) to change the name and/or ID of your database and you receive the following error:

Cannot read the header of the following datafiles:
D:\ORADATA\GENERAL\TEMP01.DBF (1), error: 4
D:\ORADATA\GENERAL\TEMP02.DBF (2), error: 4
D:\ORADATA\GENERAL\TEMP03.DBF (3), error: 4
D:\ORADATA\GENERAL\TEMP04.DBF (4), error: 4
D:\ORADATA\GENERAL\TEMP05.DBF (5), error: 4

NID-00137: All datafiles that are not dropped should be readable

Change of database name failed during validation – database is intact.
DBNEWID – Completed with validation errors.

The Cause

You need to drop the TEMP files before running this command, but I’m not exactly sure of the technical reasons as to why this is the case. You have at least a couple of options for dropping the TEMP files and resolving this NID-00137 error.

The Solution

The first option is to manually drop the TEMP files which I have written an article about called How to Drop a TEMP File in an Oracle Database.

The second option is to run this little dynamic script which will drop all the TEMP files for you:

begin
for i in (select ‘alter database tempfile ”’||name||”’ drop including datafiles’ as cmd from v$tempfile)
loop
execute immediate i.cmd;
end loop;
end;
/

This should allow you to re-run the same command using NID and for it to work this time around.

You may find that you are hitting another error message now, the NID-00111 error message, coupled with the ORA-01116 error. If you are hitting this issue, you can read my other article NID-00111: Oracle error reported from target database.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *