Oracle手工完全恢复案例

发布时间：2020-08-16 03:48:04 作者：迷倪小魏
来源：ITPUB博客阅读：160

手工完全恢复

1、完全恢复基于三个级别

recover database：所有数据文件损坏，或包括大部分datafile丢失（大面积丢失）

recover tablespace：非关键表空间损坏，表空间下某些数据文件不能访问recover datafile：单一或少数数据文件损坏 (如果是系统表空间损坏，可以在mount下，使用recover datafile恢复)

2、恢复过程可以查看的视图：

v$recover_file 查看需要恢复的datafile

v$recovery_log 查看recover 需要的redo 日志

v$archvied_log 查看已经归档的日志

3、适用的场景

（1）recover database (所有或大部分数据文件损坏，mount或open下进行)

OS：使用cp 还原受损的dbf（不一定是全部，v$recover_file记录的都需要还原）

SQLPLUS：

①recover database;

②alter database open;

（2）recover tablespace (针对表空间的非关键数据文件损坏，一般是open下进行)

OS:使用cp 还原该表空间XXX下的所有数据文件

SQLPLUS：

①alter tablespace XXX offline;

②recover tablespace XXX;

③alter tablespace XXX online;

（3）recover datafile (单个或几个数据文件损坏，关键文件在mount下进行，非关键文件在open下进行)

第一种情形

OS:使用cp 还原相关的关键数据文件(mount)

SQLPLUS：

①recover datafile 6,8;

②alter database open;

第二种情形

OS:使用cp 还原相关的非关键数据文件(open)

SQLPLUS：

①alter database datafile 6,8 offline;

②recover datafile 6,8;

③alter database datafile 6,8 online;

示例一：recover database

示例二：recover tablespace

示例三：recover datafile

情况1：关键数据文件

情况2：非关键数据文件

实验环境：

操作系统：CentOS7.1

数据库：Oracle 11.2.0.4

示例一：recover database（介质失败，丢失大量的数据文件）

1、模拟环境：

创建一个seiang表空间，在scott用户下创建一张表test

SYS@seiang11g>create tablespace seiang datafile '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf' size 20M;

Tablespace created.

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>create table test(id number,name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>insert into test values(1,'wjq');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from test;

ID NAME

---------- ------------------------------------------------------------

1 wjq

SYS@seiang11g>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down

干净的关闭数据库之后，操作系统下对数据库中的Datafile做一个完全冷备

[oracle@seiang11g OraDB11g]$ cp ./* /u01/app/oracle/UMAN_Backup/

[oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/UMAN_Backup/

total 2123572

-rw-r----- 1 oracle oinstall 9748480 Jul 25 11:53 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 11:54 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:54 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:54 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:54 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 11:54 rman01.dbf

-rw-r----- 1 oracle oinstall 20979712 Jul 25 11:54 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 11:54 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 11:54 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 11:54 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 11:54 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 11:54 users01.dbf

启动数据库

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

Database opened.

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>insert into test values(2,'wjq1'); //注意该条数据提交

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>insert into test values(3,'wjq2'); //注意该条数据未提交

1 row created.

SCOTT@seiang11g>select * from test;

ID NAME

---------- ------------------------------------------------------------

1 wjq

2 wjq1

3 wjq2

查看当前日志，第二、三条数据的插入记录在redo2中；

SYS@seiang11g>select group#,sequence#,members,status from v$log;

GROUP# SEQUENCE# MEMBERS STATUS

---------- ---------- ---------- ----------------

1 31 1 INACTIVE

2 32 1 CURRENT

3 30 1 INACTIVE

进行日志切换

SYS@seiang11g>alter system switch logfile;

System altered.

SYS@seiang11g>select group#,sequence#,members,status from v$log;

GROUP# SEQUENCE# MEMBERS STATUS

---------- ---------- ---------- ----------------

1 31 1 INACTIVE

2 32 1 ACTIVE

3 33 1 CURRENT

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>insert into test values(4,'wjq3'); //注意该条记录也为提交

1 row created.

SCOTT@seiang11g>select * from test;

ID NAME

---------- ------------------------------------------------------------

1 wjq

2 wjq1

3 wjq2

4 wjq3

2、模拟介质损坏

数据库在打开的情况下删除数据文件

[oracle@seiang11g OraDB11g]$ rm *.dbf

[oracle@seiang11g OraDB11g]$ ll

total 163132

-rw-r----- 1 oracle oinstall 9748480 Jul 25 12:06 control01.ctl

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:56 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:02 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:06 redo03.log

换一个session关闭数据库，然后重新启动，数据库只能启动到mount状态，open时报错

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'

SYS@seiang11g>select file#,error from v$recover_file;

FILE# ERROR

---------- -----------------------------------------------------------------

1 FILE NOT FOUND

2 FILE NOT FOUND

3 FILE NOT FOUND

4 FILE NOT FOUND

5 FILE NOT FOUND

6 FILE NOT FOUND

7 FILE NOT FOUND

查看控制文件和数据文件头中记录的SCN

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1501761

2 1501761

3 1501761

4 1501761

5 1501761

6 1501761

7 1501761

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 0

2 0

3 0

4 0

5 0

6 0

7 0

由于没有数据文件，所以数据文件头的SCN为0

从冷备的Datafile中还原丢失的数据文件

[oracle@seiang11g OraDB11g]$ cp /u01/app/oracle/UMAN_Backup/*.dbf ./

[oracle@seiang11g OraDB11g]$ ll

total 2123572

-rw-r----- 1 oracle oinstall 9748480 Jul 25 12:15 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 12:13 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:56 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:02 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:07 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 12:13 rman01.dbf

-rw-r----- 1 oracle oinstall 20979712 Jul 25 12:13 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 12:14 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 12:14 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 12:14 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 12:14 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 12:14 users01.dbf

再次查看控制文件和数据文件头的SCN，发现数据文件头的SCN比控制文件中记录的SCN要小

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1501761

2 1501761

3 1501761

4 1501761

5 1501761

6 1501761

7 1501761

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1501758

2 1501758

3 1501758

4 1501758

5 1501758

6 1501758

7 1501758

执行手工完全恢复，并比较控制文件和数据文件头的SCN，发现完全恢复后，控制文件和数据文件中记录的SCN一致；

SYS@seiang11g>recover database;

Media recovery complete.

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1522474

2 1522474

3 1522474

4 1522474

5 1522474

6 1522474

7 1522474

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1522474

2 1522474

3 1522474

4 1522474

5 1522474

6 1522474

7 1522474

SYS@seiang11g>select * from v$recover_file;

no rows selected

打开数据库，并进行验证

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select * from scott.test;

ID NAME

---------- ----------

1 wjq

2 wjq1

3 wjq2

4 wjq3

示例二：recover tablespace

针对的是非关键表空间的损坏恢复，基于表空间的完全恢复实际上还是对其下的datafile的恢复；模拟这种情形非常实用，通常某个非关键表空间下的数据文件受损，但并没有造成Oracle崩溃，我们只需针对个别有问题的tablespace去做单独的在线恢复操作，也就是说恢复时数据库整体是online的，而局部表空间是offline的，数据库不需要shutdown。

1、模拟环境

在scott用户下创建一个表test1，并插入相应的数据

SCOTT@seiang11g>create table test1(id number,name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>

SCOTT@seiang11g>insert into test1 values(100,'wjq');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

查看当前redo信息

SYS@seiang11g>select group#,sequence#,members,status from v$log;

GROUP# SEQUENCE# MEMBERS STATUS

---------- ---------- ---------- ----------------

1 34 1 CURRENT

2 32 1 INACTIVE

3 33 1 INACTIVE

进行日志的切换

SYS@seiang11g>alter system switch logfile;

System altered.

SYS@seiang11g>select group#,sequence#,members,status from v$log;

GROUP# SEQUENCE# MEMBERS STATUS

---------- ---------- ---------- ----------------

1 34 1 ACTIVE

2 35 1 CURRENT

3 33 1 INACTIVE

以下插入的两条记录未提交

SCOTT@seiang11g>insert into test1 values(200,'wjq2');

1 row created.

SCOTT@seiang11g>insert into test1 values(200,'wjq3');

1 row created.

SCOTT@seiang11g>select * from test1;

ID NAME

---------- ----------

100 wjq

200 wjq2

200 wjq3

2、模拟表空间损坏

数据库open下，直接删除表空间下的数据文件

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2103124

-rw-r----- 1 oracle oinstall 9781248 Jul 25 14:14 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 12:20 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:10 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:14 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:20 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 12:20 rman01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 14:13 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 14:13 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 14:13 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 12:20 users01.dbf

清除data buffer cache的记录

SYS@seiang11g>alter system flush buffer_cache;

System altered.

SCOTT@seiang11g>select * from test1;

select * from test1

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 5524

Session ID: 42 Serial number: 91

重新启动数据库，在数据库open的时候出现报错

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'

查看控制文件和数据文件头的SCN

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1522477

2 1522477

3 1522477

4 1522477

5 1522477

6 1522477

7 1522477

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1527707

2 1527707

3 1527707

4 1527707

5 1527707

6 1527707

7 0

丢失的数据文件7没有SCN

SYS@seiang11g>recover database;

ORA-00279: change 1501758 generated at 07/25/2017 11:52:18 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_949237404_32.log

ORA-00280: change 1501758 for thread 1 is in sequence #32

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

打开数据库，并进行验证控制文件和数据文件头的SCN一致

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1548052

2 1548052

3 1548052

4 1548052

5 1548052

6 1548052

7 1548052

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 1548052

2 1548052

3 1548052

4 1548052

5 1548052

6 1548052

7 1548052

SYS@seiang11g>select * from scott.test1;

ID NAME

---------- ----------

100 wjqs

实验发现：未提交的两条数据被回滚掉了

示例三：recover datafile

情况1：关键数据文件损坏

1、模拟环境

同示例2不同的是模拟UNDO文件损坏: 因UNDO数据文件也是关键文件，所以只能在mount状态下恢复。

SCOTT@seiang11g>insert into test1 values(200,'wjqgood');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from test1;

ID NAME

---------- ----------

100 wjq

200 wjqgood

SCOTT@seiang11g>select * from test1;

ID NAME

---------- ----------

100 wjq

200 wjqgood

删除test1中的数据，但是没有提交，老值记录在UNDO中

SYS@seiang11g>delete scott.test1;

2 rows deleted.

在线备份UNDO数据文件

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host ls -l /u01/app/oracle/backup_Temp

total 107528

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

2、模拟UNDO数据文件丢失

备份完成后，在线UNDO数据文件

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/

total 2016084

-rw-r----- 1 oracle oinstall 9781248 Jul 25 15:22 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:27 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:27 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:21 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 14:27 rman01.dbf

-rw-r----- 1 oracle oinstall 20979712 Jul 25 15:21 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:21 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:20 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 14:27 users01.dbf

干净的关闭数据库，并重新启动数据库

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf'

从备份中还原UNDO数据文件

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/undotbs01.dbf /u01/app/oracle/oradata/OraDB11g/

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/

total 2123612

-rw-r----- 1 oracle oinstall 9781248 Jul 25 15:26 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:27 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 14:27 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:22 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 14:27 rman01.dbf

-rw-r----- 1 oracle oinstall 20979712 Jul 25 15:21 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:23 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:23 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:26 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 14:27 users01.dbf

执行恢复操作

SYS@seiang11g>recover datafile 3;

Media recovery complete.

完成恢复操作后，打开数据库，会完成UNDO表空间的数据回滚操作，并验证恢复成功

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select * from scott.test1;

ID NAME

---------- ----------

100 wjq

200 wjqgood

情况2：非关键数据文件损坏

1、模拟环境

模拟users和seiang表空间的数据文件损坏，这两个表空间的数据文件是非关键数据文件

SYS@seiang11g>select FILE#,TS#,name,status from v$datafile;

FILE# TS# NAME STATUS

---------- ---------- -------------------------------------------------- -------

1 0 /u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

2 1 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

3 2 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

4 4 /u01/app/oracle/oradata/OraDB11g/users01.dbf ONLINE

5 6 /u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 7 /u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 8 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf ONLINE

对这两个表空间的数据文件进行备份

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/users01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/seiang01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host ls -l /u01/app/oracle/backup_Temp

total 133144

-rw-r----- 1 oracle oinstall 20979712 Jul 25 15:34 seiang01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 15:34 users01.dbf

在scott用户下创建两张表，wjq1隶属于users表空间，wjq2隶属于seiang表空间

SCOTT@seiang11g>create table wjq1(id number);

Table created.

SCOTT@seiang11g>insert into wjq1 values(111);

1 row created.

SCOTT@seiang11g>insert into wjq1 values(222);

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from wjq1;

----------

111

222

SCOTT@seiang11g>create table wjq2(name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>insert into wjq2 values('wjq100');

1 row created.

SCOTT@seiang11g>insert into wjq2 values('seiang200');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from wjq2;

NAME

----------

wjq100

seiang200

SYS@seiang11g>select table_name,tablespace_name,status from dba_tables

2 where table_name in ('WJQ1','WJQ2');

TABLE_NAME TABLESPACE_NAME STATUS

------------------------------ ------------------------------ --------

WJQ1 USERS VALID

WJQ2 SEIANG VALID

2、模拟users和seiang多对应的数据文件丢失

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/users01.dbf

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2097996

-rw-r----- 1 oracle oinstall 9781248 Jul 25 15:44 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:44 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:27 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:27 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 15:27 rman01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:44 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:44 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 15:27 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:44 undotbs01.dbf

清除data buffer cache的记录

SYS@seiang11g>alter system flush buffer_cache;

System altered.

SYS@seiang11g>select * from scott.wjq1;

select * from scott.wjq1

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/app/oracle/oradata/OraDB11g/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SYS@seiang11g>select * from scott.wjq2;

select * from scott.wjq2

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

还原介质，将users和seiang对应的数据文件还原

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/users01.dbf /u01/app/oracle/oradata/OraDB11g

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/seiang01.dbf /u01/app/oracle/oradata/OraDB11g

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2123612

-rw-r----- 1 oracle oinstall 9781248 Jul 25 15:51 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:51 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:27 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 15:27 redo03.log

-rw-r----- 1 oracle oinstall 31465472 Jul 25 15:27 rman01.dbf

-rw-r----- 1 oracle oinstall 20979712 Jul 25 15:51 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:48 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:48 system01.dbf

-rw-r----- 1 oracle oinstall 30416896 Jul 25 15:27 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:48 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 Jul 25 15:51 users01.dbf

offline这两个数据文件

SYS@seiang11g>alter database datafile 4 offline;

Database altered.

SYS@seiang11g>alter database datafile 7 offline;

Database altered.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

FILE_ID FILE_NAME ONLINE_

---------- -------------------------------------------------- -------

4 /u01/app/oracle/oradata/OraDB11g/users01.dbf RECOVER

3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1 /u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 /u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf RECOVER

恢复这两个数据文件

SYS@seiang11g>recover datafile 4,7;

Media recovery complete.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

FILE_ID FILE_NAME ONLINE_

---------- -------------------------------------------------- -------

4 /u01/app/oracle/oradata/OraDB11g/users01.dbf OFFLINE

3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1 /u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 /u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf OFFLINE

online这两个数据文件

SYS@seiang11g>alter database datafile 4 online;

Database altered.

SYS@seiang11g>alter database datafile 7 online;

Database altered.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

FILE_ID FILE_NAME ONLINE_

---------- -------------------------------------------------- -------

4 /u01/app/oracle/oradata/OraDB11g/users01.dbf ONLINE

3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1 /u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 /u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf ONLINE

验证恢复的正确性

SYS@seiang11g>select * from scott.wjq1;

----------

111

222

SYS@seiang11g>select * from scott.wjq2;

NAME

----------

wjq100

seiang200

相关链接:
Oracle手工不完全恢复（一）：使用当前控制文件

作者：SEian.G（苦练七十二变，笑对八十一难）

ITPUB：http://blog.itpub.net/31015730/

51CTO：http://seiang.blog.51cto.com/

Oracle手工完全恢复案例

相关阅读