In either case, the easiest way is to drop the entire tablespace that contains the datafile. The steps that are to be executed from within SQL*Plus are: 1. STARTUP MOUNT 2. For each deleted datafile, issue the command ALTER DATABASE DATAFILE ’full path of filename’ OFFLINE [DROP]; Note: You must use the DROP option if the database is in NOARCHIVELOG mode, because you cannot recover this file if you apply incomplete media recovery on it via the command ALTER DATABASE OPEN RESETLOGS. See the SQL Reference for details. 3. ALTER DATABASE OPEN; 4. DROP TABLESPACE INCLUDING CONTENTS [CASCADE CONSTRAINTS];. | Worldwide Support Bulletins Bulletin ORA-01157 or ORA-07368: CANNOT START UP DATABASE - MISSING DATAFILE Problem Description: If a file is physically removed from the operating system directory, you may either get errors while the database is up or while the DBA is trying to start up the database. Problem Explanation: The Oracle Server verifies the existence and consistency of each datafile registered in the control file after the database is successfully mounted. If the file is bad or is being unintentionally removed, the following error may occur: ORA-01157: cannot identify datafile %n - file not found ORA-01110: datafile %n: ’%s’ The following errors may also occur when trying to do a normal shutdown or when DBWR attempts to write to the file that is being removed: ORA-01116: error in opening database file %n ORA-01110: datafile %n: ’%s’ ORA-07368: sfofi: open error, unable to open database file. These errors are followed by an operating system specific error (for . | Worldwide Support Bulletins Bulletin ORA-01157 or ORA-07368: CANNOT START UP DATABASE - MISSING DATAFILE Problem Description: If a file is physically removed from the operating system directory, you may either get errors while the database is up or while the DBA is trying to start up the database. Problem Explanation: The Oracle Server verifies the existence and consistency of each datafile registered in the control file after the database is successfully mounted. If the file is bad or is being unintentionally removed, the following error may occur: ORA-01157: cannot identify datafile %n - file not found ORA-01110: datafile %n: ’%s’ The following errors may also occur when trying to do a normal shutdown or when DBWR attempts to write to the file that is being removed: ORA-01116: error in opening database file %n ORA-01110: datafile %n: ’%s’ ORA-07368: sfofi: open error, unable to open database file. These errors are followed by an operating system specific error (for instance, error number 2 in most UNIX platforms). Solution Description Warning: This solution can only be applied if the removed datafile does NOT belong to the system tablespace or to a rollback tablespace. If the file belongs to the system tablespace or to a rollback tablespace, please contact Oracle customer support. There are cases in which the DBA inadvertently removes a datafile from the file directory, maybe with the incorrect assumption that by removing the file, any reference to it from the Oracle Server is also removed. It may also be due to the fact that an operating system error or hardware problem rendered the file unreadable or inaccessible. If the file is inaccessible by the Oracle Server, the DBWR may force the datafile to go offline, in which case you would get the following error when trying to access the datafile by any means: ORA-01135, 00000, ”file %s accessed for DML/query is offline” // *Cause: Attempted to access a datafile that is offline // *Action: Bring the .
