hi,
I ve upgraded an database for 9i to 10g. The upgrade was sucessfull and the Db was in normal usage. Due to some functionality testing the database was restored to a state from the backup taken after upgrade. But while restoring the database i forgot to include the path of the datafile of sysaux tablespace in the control file re-creation script.
I didnt notice this issue for long time until i realised the mistake made by me.
I found in the alert logs which showed errors such as
ORA-01157: cannot identify/lock data file 124 - see DBWR trace file
ORA-01110: data file 124: '/oracle/ora/dbs/MISSING00141'
ORA-27037: unable to obtain file status
since the path of the sysaux tablespace was not referred in the script i thought of referring it now and i issued the following command
alter tablespace sysaux add datafile '<path>/sysaux01.dbf' reuse;
i issued this as the datafile was already present and i thought of jus reusing it again.
But what had happened is this datafile had been added as an additional datafile for the sysaux tablespace,which means the sysaux is still referring '/oracle/ora/dbs/MISSING00141' as one of the datafile.
but one important thing is that there isnt any such datafile ie '/oracle/ora/dbs/MISSING00141' when i checked inside the dbs folder.
The path is getting referred as part of the sysaux tablespace but its physical location is not found.
So i did took this datafile offlinedrop issuing
alter database datafile '/oracle/ora/dbs/MISSING00141' ofline drop
but the problem i m facing now is the AWR is not opening up and it throw error stating first datafile of sysaux tablespace '/oracle/ora/dbs/MISSING00141' not found.
ORA-00376: file 124 cannot be read at this time
ORA-01110: data file 141: '/oracle/ora/dbs/MISSING00141'
ORA-06512: at line 21.
any solution for solving the problem would be appreciated
thanks,
ahmed