恢复丢失的datafile

1.创建测试表空间

SQL> create tablespace tbs_test1 datafile '/opt/oracle/oradata/mydb/test01.dbf' size 20m autoextend on next 10m maxsize 10000m;

Tablespace created.

SQL> create table sys.my_dba_tables  tablespace tbs_test1 as select * from dba_tables;

Table created.

 

2.镜像相应数据文件

RMAN> report schema;   

Report of database schema for database with db_unique_name mydb

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     /opt/oracle/oradata/mydb/system01.dbf
2    620      SYSAUX               ***     /opt/oracle/oradata/mydb/sysaux01.dbf
3    70       UNDOTBS1             ***     /opt/oracle/oradata/mydb/undotbs01.dbf
4    5        USERS                ***     /opt/oracle/oradata/mydb/users01.dbf
5    100      CAT                  ***     /opt/oracle/oradata/mydb/cat01.dbf
6    20       TBS_TEST1            ***     /opt/oracle/oradata/mydb/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /opt/oracle/oradata/mydb/temp01.dbf

RMAN> backup as copy datafile 6 format '/opt/oracle/backup/tbs_test1_cp.bak';

Starting backup at 21-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oracle/oradata/mydb/test01.dbf
output file name=/opt/oracle/backup/tbs_test1_cp.bak tag=TAG20131021T145623 RECID=1 STAMP=829407384
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-OCT-13

Starting Control File and SPFILE Autobackup at 21-OCT-13
piece handle=/opt/oracle/fast_recovery_area/mydb/autobackup/2013_10_21/o1_mf_s_829407385_969n0s2w_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-OCT-13

 

3.删除数据文件

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> !rm /opt/oracle/oradata/mydb/test01.dbf

SQL> !ls -l /opt/oracle/oradata/mydb/test01.dbf
ls: /opt/oracle/oradata/mydb/test01.dbf: No such file or directory

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from sys.my_dba_tables;

  COUNT(*)
----------
      2814

SQL> exit

-bash-3.2$ cd mydb
-bash-3.2$ ls
cat01.dbf  control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 21 15:06:04 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> select count(*) from sys.my_dba_tables;
select count(*) from sys.my_dba_tables
                         *
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/opt/oracle/oradata/mydb/test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

 

4.恢复数据文件
SQL> !cp /opt/oracle/backup/tbs_test1_cp.bak /opt/oracle/oradata/mydb/test01.dbf

SQL> !ls -l /opt/oracle/oradata/mydb/test01.dbf
-rw-r----- 1 oracle oinstall 20979712 Oct 21 15:25 /opt/oracle/oradata/mydb/test01.dbf

SQL> alter database datafile 6 offline;

Database altered.

SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

SQL> select count(*) from sys.my_dba_tables;

  COUNT(*)
----------
      2814

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26870952/viewspace-774736/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26870952/viewspace-774736/