一个归档模式无备份丢失数据文件的恢复

　　在没有备份的场景下执行恢复在我看来可以恢复的场景得是具备了特定的条件的，今天实验一个简单的无备份有归档日志，丢失数据文件的恢复，只要是丢失的数据文件从创建开始的所有数据都有日志中记录，那么执行recover database通常都可以恢复，不管数据是在归档日志中还是在在线日志中；当然，丢失的文件既不可以是system表空间的也不必是temp表空间的，针对system表空间的恢复以下方式是不可行的，要是temp文件的话其实呢也就不用操心恢复了，因为开启数据库的过程中会重建。

实验环境：Red Hat Enterprise Linux Server release 5.2 + Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

1）首先确认环境，归档模式

SYS@db10g>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

2）创建一个test的表空间，指定一个用户testuser

SYS@db10g>create tablespace test datafile
  2  '/u01/app/oracle/oradata/db10g/test.dbf' size 50m;

Tablespace created.

SYS@db10g>grant connect,resource to testuser identified by testuser ;    

Grant succeeded.

SYS@db10g>alter user testuser default tablespace test;

User altered.

3）向test表空间中初始化一些数据

TESTUSER@db10g>create table t as select rownum as x from dual connect by rownum < 11;

Table created.

TESTUSER@db10g>select count(*) from t;

  COUNT(*)
----------
        10

4）手动模拟误删该表空间的数据文件

SYS@db10g>! rm -rf /u01/app/oracle/oradata/db10g/test.dbf

5）停库，出现报错

SYS@db10g>shutdown immediate;
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/db10g/test.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@db10g>shutdown abort;
ORACLE instance shut down.

6）mount下开始执行恢复

SYS@db10g>startup mount;
ORACLE instance started.

Total System Global Area  364904448 bytes
Fixed Size                  2020832 bytes
Variable Size             117443104 bytes
Database Buffers          243269632 bytes
Redo Buffers                2170880 bytes
Database mounted.

7）手工创建文件，执行介质恢复

SYS@db10g>alter database create datafile '/u01/app/oracle/oradata/db10g/test.dbf' as '/u01/app/oracle/oradata/db10g/test.dbf';

Database altered.

SYS@db10g>recover datafile 6;
Media recovery complete.

8）打开数据库，检查数据是否恢复

SYS@db10g>alter database open;

Database altered.

SYS@db10g>select count(*) from testuser.t;

  COUNT(*)
----------
        10

SYS@db10g>select table_name,tablespace_name from dba_tables where table_name ='T';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              TEST

使用RMAN同样可以实现上述恢复，操作如下：（可以看到恢复过程中使用到了归档日志文件）

[oracle@bean db10g]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 19 17:34:08 2012

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

connected to target database: DB10G (DBID=186430046, not open)

RMAN> restore datafile 6;

Starting restore at 19-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

creating datafile fno=6 name=/u01/app/oracle/oradata/db10g/ts_cj.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 19-SEP-12

RMAN> recover datafile 6;

Starting recover at 19-SEP-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch1_10_794088478.dbf
archive log thread 1 sequence 11 is already on disk as file /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch1_11_794088478.dbf
archive log thread 1 sequence 12 is already on disk as file /u01/app/oracle/product/10.2.0.1/db_1/dbs/arch1_12_794088478.dbf
archive log filename=/u01/app/oracle/product/10.2.0.1/db_1/dbs/arch1_10_794088478.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-SEP-12

RMAN> alter database open;

database opened

查看恢复结果

SYS@db10g>select count(*) from testuser.t;

  COUNT(*)
----------
        10

 

恢复成功！