今天做了一个实验,先创建表空间及数据文件,然后在操作系统下删除该表空间的数据文件(前提是该表空间已经备份了,方便恢复)。
STEP1: 创建表空间 ,数据文件, 用户并赋权:
- SQL> create tablespace TBS1 datafile \'/u01/app/oradata/ORCL/df1.dbf\' size 5M autoextend on next 2M maxsize 100M;
- SQL> create user test identified by test default tablespace TBS1;
- SQL> GRANT DBA TO TEST WITH ADMIN OPTION;
- SQL> conn TEST/TEST;
- SQL> create table t1(id int, name varchar2(20));
- SQL> begin
- 2 for i in 1..200000 loop
- 3 insert into t1 values(i,\'AAA\'||i);
- 4 end loop;
- 5 end;
- 6 /
STEP3: 用RMAN备份数据(非归档模式不能用RMAN备份数据的)
RMAN> backup tablespace TBS1 format '/home/oracle/db_backup/TBS1_20150121:1037.bak';
STEP4: 删除新建表空间下的数据文件
[oracle@REDHAT6 ORCL]$ mv df1.dbf df2.dbf.bak
SETP5: 关闭数据库,重新启动数据,问题来了
- SQL> shutdown immediate;
- SQL> startup
-
- ORACLE instance started.
- Total System Global Area 889389056 bytes
- Fixed Size 2233480 bytes
- Variable Size 494930808 bytes
- Database Buffers 385875968 bytes
- Redo Buffers 6348800 bytes
- Database mounted.
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01110: data file 6: \'/u01/app/oradata/ORCL/df1.dbf\'
下面来解决上面的问题:
step1: 开启数据库处于mount状态, 使文件 6 脱机(6指的是上面删除的数据文件df1.dbf),并开启数据库
- SQL> select status from v$instance;
- STATUS
- ------------
- MOUNTED
-
- SQL> alter database datafile 6 offline;
-
- SQL> alter database open;
- Database altered.
-
- SQL> select status from v$instance;
- STATUS
- ------------
- OPEN
SQL> SELECT FILE_ID, FILE_NAME,STATUS, ONLINE_STATUS FROM DBA_DATA_FILES;
SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;
STEP3: 恢复备份数据
RMAN> list backup of tablespace TBS1;
RMAN> restore tablespace TBS1;
由于是测试环境,没有修改数据(即没有脏块),所以没有 recover tablespace TBS1;
STEP4: 数据文件联机
- SQL> alter database datafile 6 online;
OK 到此结束,数据文件恢复完成!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1449908/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30150152/viewspace-1449908/