ORACLE 测试数据文件丢失 与 恢复

----------测试表空间
create tablespace sms_test
logging
datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf'
size 32m
autoextend on
next 32m maxsize 100M

extent management local;

--------------------查看数据文件

select file#,name,status,enabled from v$datafile;

-------创建测试表
create table testdatefile(name varchar2(32))
tablespace sms_test
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

----------插入数据
select * from testdatefile;
-----
insert into testdatefile(name)values('test');
------置于离线
alter database datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf' offline drop;
-----查询文件状态
select file#,name,status,enabled from v$datafile;
-------恢复数据文件
recover datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf'
-----设置在线
alter database datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf' online;

------数据文件丢失 创建同名称新文件
alter database create datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf' as '+RECOVERYDEST/orcdb/datafile/sms_test.dbf';
-----查询文件状态
select file#,name,status,enabled from v$datafile;
-------恢复数据文件
recover datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf'
-----设置在线
alter database datafile '+RECOVERYDEST/orcdb/datafile/sms_test.dbf' online;

经过上述步骤，数据正常恢复