資料庫處於歸檔模式，新建的表空間資料檔案被刪除了，沒有該資料檔案的備份，如何進行恢復？
SQL> startup
ORACLE instance started.

Total System Global Area 580395008 bytes
Fixed Size            2255392 bytes
Variable Size          427820512 bytes
Database Buffers      146800640 bytes
Redo Buffers            3518464 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ANDREA/test.dbf'
資料恢復的步驟包括：restore 和recover兩個主要過程。
restore 需要透過已有的備份對丟失的檔案進行重建，recover透過應用online redo log archived redo log   undo data使丟失的資料檔案恢復到一定的狀態。
先檢視資料庫目前的狀態：
SQL> select status from v$instance;
STATUS
------------
MOUNTED

SQL> select * from v$recover_file;

     FILE# ONLINE ONLINE_ ERROR                               CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
   6 ONLINE ONLINE FILE NOT FOUND                                  0
資料庫處於mount狀態,資料檔案6已丟失且沒有備份檔案，先使用下面的命令來"restore"丟失的檔案
SQL> alter database create datafile '/u01/app/oracle/oradata/ANDREA/test.dbf';
然後就可進行recover了。
sql> recover datafile 6;

SQL> alter database open;

Database altered.
-------------------------------------------------

Use the CREATE DATAFILE clause to create a new empty data file in place of an old one. You can use this clause to re-create a data file that was lost with no backup. The filename or filenumber must identify a file that is or was once part of the database. If you identify the file by number, then filenumber is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view.

Specify AS NEW to create an Oracle-managed data file with a system-generated filename, the same size as the file being replaced, in the default file system location for data files.
Specify AS file_specification to assign a file name (and optional size) to the new data file. Use the datafile_tempfile_spec form of file_specification (see ) to list regular data files and temp files in an operating system file system or to list Oracle Automatic Storage Management (Oracle ASM) disk group files.

If the original file (filename or filenumber) is an existing Oracle-managed data file, then Oracle Database attempts to delete the original file after creating the new file. If the original file is an existing user-managed data file, then Oracle Database does not attempt to delete the original file.

If you omit the AS clause entirely, then Oracle Database creates the new file with the same name and size as the file specified by filename or filenumber.

During recovery, all archived redo logs written to since the original data file was created must be applied to the new, empty version of the lost data file.

Oracle Database creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.

Restrictions on Creating New Data Files The creation of new data files is subject to the following restrictions:

You cannot create a new file based on the first data file of the SYSTEM tablespace.
You cannot specify the autoextend_clause of datafile_tempfile_spec in this CREATE DATAFILE clause.

資料檔案丟失---ora-01110

相關文章