数据库处于归档模式，新建的表空间数据文件被删除了，没有该数据文件的备份，如何进行恢复？
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 file_specification) 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.

ITPub博客

数据文件丢失---ora-01110

最新文章