数据文件丢失恢复测试记录

数据文件丢失恢复测试：

清理环境：
drop user sunhailong cascade;
drop tablespace tbl_shl including contents and datafiles;

创建用户表空间和用户:
create tablespace tbl_shl datafile '/oracle/app/oradata/gome/tbl_shl01.dbf' size 100m;
create user sunhailong identified  by sunhailong default tablespace tbl_shl  quota

unlimited  on tbl_shl;
grant dba to sunhailong;

conn sunahilong/sunhailong
create table test as select * from dba_objects;
select object_name from test where rownum<10;
SQL> select object_name from test where rownum<10;

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2

9 rows selected.
select count(*) from test;
SQL> select count(*) from test;

  COUNT(*)
----------
     74763

错误模拟：


ls /oracle/app/oradata/gome/tbl_shl01.dbf

[root@localhost ~]# rm /oracle/app/oradata/gome/tbl_shl01.dbf
rm: remove regular file `/oracle/app/oradata/gome/tbl_shl01.dbf'? y

[oracle@localhost fd]$ ps -ef | grep dbw
oracle   20748     1  0 01:10 ?        00:00:00 ora_dbw0_gome
oracle   21023 19948  0 01:17 pts/0    00:00:00 grep dbw




alter system flush buffer_cache;

SQL> show user
USER is "SUNHAILONG"
SQL> alter system flush buffer_cache;

System altered.


insert into test  select * from dba_objects;


 select name,status from v$datafile;






SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/oracle/app/oradata/gome/tbl_shl01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


[oracle@localhost ~]$ cd /proc/20748/fd
alter database datafile '/oracle/app/oradata/gome/tbl_shl01.dbf' offline drop






恢复：

 cp 263 /oracle/app/oradata/gome/tbl_shl01.dbf.orig


SQL> alter database datafile '/oracle/app/oradata/gome/tbl_shl01.dbf' offline drop
  2  ;

Database altered.



alter database rename file '/oracle/app/oradata/gome/tbl_shl01.dbf'  to

'/oracle/app/oradata/gome/tbl_shl01.dbf.orig'



SQL> alter database rename file '/oracle/app/oradata/gome/tbl_shl01.dbf'  to

'/oracle/app/oradata/gome/tbl_shl01.dbf.orig';

Database altered.

SQL> recover datafile '/oracle/app/oradata/gome/tbl_shl01.dbf.orig';
Media recovery complete.


SQL> recover datafile '/oracle/app/oradata/gome/tbl_shl01.dbf.orig';
Media recovery complete.
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/oracle/app/oradata/gome/tbl_shl01.dbf.orig'


SQL> alter database datafile 5 online;   

Database altered.

SQL> select count(*) from test;

  COUNT(*)
----------
    224289