Performing Oracle User-Managed Database Recoveries - OCP: Oracle Database 12c Administrator Certified Professional - page 74

Total System Global Area 397557760 bytes
Fixed Size 1333452 bytes
Variable Size 289408820 bytes
Database Buffers 100663296 bytes
Redo Buffers 6152192 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\ORCL\USERS01.DBF'
It may be that you are missing more than data file 4 , since Oracle will alert you to only
the first data file that it finds missing.
2. Use the V$RECOVER_FILE , V$DATAFILE , and V$TABLESPACE views to determine exactly
which data files are missing and which tablespaces they are associated with, as shown
in this example:
SQL> l
1 select b.name ts_name, a.error, c.name datafile
2 from v$recover_file a, v$tablespace b, v$datafile c
3 where a.file#=c.file#
4* and b.ts#=c.ts#
SQL> /
TS_NAME ERROR DATAFILE
--------- -------------------- ------------------------------------
USERS FILE NOT FOUND C:\ORACLE\ORADATA\ORCL\USERS01.DBF
3. Review the results of the query.
As long as the missing data files are not part of the SYSTEM or UNDO tablespace, you can
simply take those data files offline and open the database. The intent will be to recover
those tablespaces/data files with the database open.
4. First use the ALTER DATABASE DATAFILE OFFLINE command to take the tablespaces
offline:
SQL> alter database
datafile 'C:\ORACLE\ORADATA\ORCL\USERS01.DBF' offline;
Database altered.
5. Next, open the database with the ALTER DATABASE OPEN command:
alter database open