Oracle 误删DBF文件恢复
非归档时(实际测试过)
一。
Dbf文件产生前：
SQL> startup
ORACLE instance started.
Total System Global Area402653184 bytes
Fixed
Size1267716 bytes
Variable
Size205522940 bytes
Database
Buffers188743680 bytes
Redo
Buffers7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace
file
ORA-01110: data file 5:
'/home/oracle/oradata/orcl/subcentrex.dbf'
二。查看DBF文件是否存在
select file#,name from v$datafile;
三．重创DBF文件
alter database create datafile
'/home/oracle/oradata/orcl/subcentrex.dbf'
四。给新创DBF文件加权限
Cd /home/oracle/oradata/orcl/
chmod 775 subcentrex.dbf
Dbf 文件产生后错误提示有所不同：
SQL> startup
ORACLE instance started.
Total System Global Area402653184 bytes
Fixed Size1267716
bytes
Variable
Size205522940 bytes
Database
Buffers188743680 bytes
Redo
Buffers7118848 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5:
'/home/oracle/oradata/orcl/subcentrex.dbf'
五．DBF文件在ONLINE状态
SQL> alter database datafile
'/home/oracle/oradata/orcl/subcentrex.dbf' online;
Database altered.
六．恢复DBF文件
SQL> alter database recover datafile
'/home/oracle/oradata/orcl/subcentrex.dbf';
Database altered.
七．ALTER数据库到OPEN状态
SQL> alter database orcl open;
Database altered.
归档时(未实际测试过)：
2个月以前使用swingbench的时候，cc这个user创建了17GB的测试数据(ccdata.dbf)，测试完成后，好久没用了，后面发现测试机空间不足，直接手动rm
–rf
/Data/oradata/ora11g/ccdata.dbf这个文件之后因其他测试时重启DB时碰到了ORA-01157错误，
ORA-01157: cannot identify/lock data file 6 - see DBWR trace
file
ORA-01110: data file 6: '/Data/oradata/ora11g/ccdata.dbf'
root case:手动ccdata.dbf文件，既然问题已经知道，那就下手处理吧：
方案有2种:
1.测试库，数据对象不需要了，在这种情况下，最简单的方法是offline并删除该数据文件；
2.正式库，必须使用rman和archive恢复了
因为是测试库，我暂时采取方案1：
ora11g$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 27 08:28:41
Copyright (c) 1982, , Oracle.All rights
reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> startup force;
SQL> startup force;
ORACLE instance started.
Total System Global Area 7816675328 bytes
Fixed
Size2243712 bytes
Variable
Size4529849216 bytes
Database
Buffers3271557120 bytes
Redo
Buffers13025280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace
file
ORA-01110: data file 6: '/Data/oradata/ora11g/ccdata.dbf'
SQL> col file# for a10;
SQL> col name for a50;
SQL> select file#,name from v$datafile;
FILE#
NAME
----------
--------------------------------------------------
1 /Data/oradata/ora11g/system01.dbf
2 /Data/oradata/ora11g/sysaux01.dbf
3 /Data/oradata/ora11g/undotbs01.dbf
4 /Data/oradata/ora11g/users01.dbf
5 /Data/oradata/ora11g/example01.dbf
6 /Data/oradata/ora11g/ccdata.dbf
7 /Data/oradata/datafile/dp01.dbf
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> DROP TABLESPACE CCDATA INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
SQL> col file_name for a35;
SQL> col tablespace_namefor a15;
SQL> select tablespace_name,file_name from dba_data_files
TABLESPACE FILE_NAME
---------- -----------------------------------
USERS/Data/oradata/ora11g/users01.dbf
UNDOTBS1/Data/oradata/ora11g/undotbs01.dbf
SYSAUX/Data/oradata/ora11g/sysaux01.dbf
SYSTEM/Data/oradata/ora11g/system01.dbf
EXAMPLE/Data/oradata/ora11g/example01.dbf
DP_TABLE/Data/oradata/datafile/dp01.dbf
6 rows selected.
至此问题已经解决
测试库，暂时用startup force启动一次吧：
SQL> startup force;
ORACLE instance started.
Total System Global Area 7816675328 bytes
Fixed
Size2243712 bytes
Variable
Size4529849216 bytes
Database
Buffers3271557120 bytes
Redo
Buffers13025280 bytes
Database mounted.
Database opened.
