Oracle手工完全恢复案例
手工完全恢复
1、完全恢复基于三个级别
recoverdatabase：所有数据文件损坏，或包括大部分datafile丢失（大面积丢失）
recovertablespace：非关键表空间损坏，表空间下某些数据文件不能访问recover datafile：单一或少数数据文件损坏 (如果是系统表空间损坏，可以在mount下，使用recover datafile恢复)
2、恢复过程可以查看的视图：
v$recover_file 查看需要恢复的datafile
v$recovery_log 查看recover需要的redo 日志
v$archvied_log 查看已经归档的日志
3、适用的场景
（1）recoverdatabase (所有或大部分数据文件损坏，mount或open下进行)
OS：使用cp 还原受损的dbf（不一定是全部，v$recover_file记录的都需要还原）
SQLPLUS：
①recover database;
②alter database open;
（2）recovertablespace (针对表空间的非关键数据文件损坏，一般是open下进行)
OS:使用cp 还原该表空间XXX下的所有数据文件
SQLPLUS：
①alter tablespace XXXoffline;
②recover tablespace XXX;
③alter tablespace XXXonline;
（3）recoverdatafile (单个或几个数据文件损坏，关键文件在mount下进行，非关键文件在open下进行)
第一种情形
OS:使用cp 还原相关的关键数据文件(mount)
SQLPLUS：
①recover datafile 6,8;
②alter database open;
第二种情形
OS:使用cp 还原相关的非关键数据文件(open)
SQLPLUS：
①alter database datafile6,8 offline;
②recover datafile 6,8;
③alter database datafile6,8 online;
目录
示例一：recoverdatabase
示例二：recovertablespace
示例三：recoverdatafile
情况1：关键数据文件
情况2：非关键数据文件
实验环境：
操作系统：CentOS7.1
数据库：Oracle 11.2.0.4
示例一：recover database（介质失败，丢失大量的数据文件）
1、模拟环境：
创建一个seiang表空间，在scott用户下创建一张表test
SYS@seiang11g>create tablespaceseiang datafile '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf' size 20M;
Tablespacecreated.
SYS@seiang11g>conn scott
Enterpassword:
Connected.
SCOTT@seiang11g>create tabletest(id number,name varchar2(10)) tablespace seiang;
Tablecreated.
SCOTT@seiang11g>insert intotest values(1,'wjq');
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
SCOTT@seiang11g>select * from test;
ID NAME
----------------------------------------------------------------------
1 wjq
SYS@seiang11g>shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down
干净的关闭数据库之后，操作系统下对数据库中的Datafile做一个完全冷备
[oracle@seiang11gOraDB11g]$ cp./* /u01/app/oracle/UMAN_Backup/
[oracle@seiang11gOraDB11g]$ ll/u01/app/oracle/UMAN_Backup/
total2123572
-rw-r-----1 oracle oinstall 9748480 Jul 25 11:53control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 11:54 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 11:54redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 11:54redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 11:54redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 11:54rman01.dbf
-rw-r-----1 oracle oinstall 20979712 Jul 25 11:54seiang01.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:54temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 11:54 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 11:54users01.dbf
启动数据库
SYS@seiang11g>startup
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2252824 bytes
VariableSize 788533224 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8892416 bytes
Databasemounted.
Databaseopened.
SYS@seiang11g>conn scott
Enterpassword:
Connected.
SCOTT@seiang11g>insert into testvalues(2,'wjq1'); //注意该条数据提交
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
SCOTT@seiang11g>insert into testvalues(3,'wjq2'); //注意该条数据未提交
1row created.
SCOTT@seiang11g>select * from test;
ID NAME
----------------------------------------------------------------------
1 wjq
2 wjq1
3 wjq2
查看当前日志，第二、三条数据的插入记录在redo2中；
SYS@seiang11g>selectgroup#,sequence#,members,status from v$log;
GROUP#SEQUENCE# MEMBERS STATUS
-------------------- ---------- ----------------
131 1 INACTIVE
2 32 1 CURRENT
3 30 1 INACTIVE
进行日志切换
SYS@seiang11g>alter system switchlogfile;
Systemaltered.
SYS@seiang11g>selectgroup#,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
Enterpassword:
Connected.
SCOTT@seiang11g>insert into testvalues(4,'wjq3'); //注意该条记录也为提交
1row created.
SCOTT@seiang11g>select * from test;
ID NAME
----------------------------------------------------------------------
1 wjq
2 wjq1
3 wjq2
4 wjq3
2、模拟介质损坏
数据库在打开的情况下删除数据文件
[oracle@seiang11gOraDB11g]$ rm*.dbf
[oracle@seiang11gOraDB11g]$ ll
total163132
-rw-r-----1 oracle oinstall 9748480 Jul 25 12:06control01.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
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2252824 bytes
VariableSize 788533224 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8892416 bytes
Databasemounted.
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#,errorfrom 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>selectfile#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1501761
21501761
3 1501761
4 1501761
5 1501761
6 1501761
7 1501761
SYS@seiang11g>selectfile#,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@seiang11gOraDB11g]$ cp/u01/app/oracle/UMAN_Backup/*.dbf ./
[oracle@seiang11gOraDB11g]$ ll
total2123572
-rw-r-----1 oracle oinstall 9748480 Jul 25 12:15control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 12:13 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 11:56redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 12:02redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 12:07redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 12:13rman01.dbf
-rw-r-----1 oracle oinstall 20979712 Jul 25 12:13seiang01.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:14temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 12:14 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 12:14users01.dbf
再次查看控制文件和数据文件头的SCN，发现数据文件头的SCN比控制文件中记录的SCN要小
SYS@seiang11g>selectfile#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1501761
2 1501761
3 1501761
4 1501761
5 1501761
6 1501761
7 1501761
SYS@seiang11g>selectfile#,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;
Mediarecovery complete.
SYS@seiang11g>selectfile#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1522474
2 1522474
3 1522474
4 1522474
5 1522474
6 1522474
7 1522474
SYS@seiang11g>selectfile#,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 * fromv$recover_file;
norows selected
打开数据库，并进行验证
SYS@seiang11g>alter databaseopen;
Databasealtered.
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 tabletest1(id number,name varchar2(10)) tablespace seiang;
Tablecreated.
SCOTT@seiang11g>
SCOTT@seiang11g>insert into test1values(100,'wjq');
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
查看当前redo信息
SYS@seiang11g>selectgroup#,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 switchlogfile;
Systemaltered.
SYS@seiang11g>select group#,sequence#,members,statusfrom v$log;
GROUP#SEQUENCE# MEMBERS STATUS
-------------------- ---------- ----------------
1 34 1 ACTIVE
2 35 1 CURRENT
3 33 1 INACTIVE
以下插入的两条记录未提交
SCOTT@seiang11g>insert into test1values(200,'wjq2');
1row created.
SCOTT@seiang11g>insert into test1values(200,'wjq3');
1row created.
SCOTT@seiang11g>select * fromtest1;
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
total2103124
-rw-r-----1 oracle oinstall 9781248 Jul 25 14:14control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 12:20 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:10redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:14redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 12:20redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 12:20rman01.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:20temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 14:13 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 12:20users01.dbf
清除databuffer cache的记录
SYS@seiang11g>alter system flushbuffer_cache;
Systemaltered.
SCOTT@seiang11g>select * fromtest1;
select* from test1
*
ERRORat line 1:
ORA-03135:connection lost contact
ProcessID: 5524
SessionID: 42 Serial number: 91
重新启动数据库，在数据库open的时候出现报错
SYS@seiang11g>startup
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2252824 bytes
VariableSize 788533224 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8892416 bytes
Databasemounted.
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>selectfile#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1522477
2 1522477
3 1522477
4 1522477
5 1522477
6 1522477
7 1522477
SYS@seiang11g>selectfile#,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
Specifylog: {=suggested | filename | AUTO | CANCEL}
auto
Logapplied.
Mediarecovery complete.
打开数据库，并进行验证控制文件和数据文件头的SCN一致
SYS@seiang11g>alter databaseopen;
Databasealtered.
SYS@seiang11g>selectfile#,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
61548052
7 1548052
SYS@seiang11g>select * fromscott.test1;
ID NAME
--------------------
100 wjqs
实验发现：未提交的两条数据被回滚掉了
示例三：recover datafile
情况1：关键数据文件损坏
1、模拟环境
同示例2不同的是模拟UNDO文件损坏: 因UNDO数据文件也是关键文件，所以只能在mount状态下恢复。
SCOTT@seiang11g>insert into test1values(200,'wjqgood');
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
SCOTT@seiang11g>select * fromtest1;
ID NAME
--------------------
100 wjq
200 wjqgood
SCOTT@seiang11g>select * fromtest1;
ID NAME
--------------------
100 wjq
200 wjqgood
删除test1中的数据，但是没有提交，老值记录在UNDO中
SYS@seiang11g>delete scott.test1;
2rows 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
total107528
-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/
total2016084
-rw-r-----1 oracle oinstall 9781248 Jul 25 15:22control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:27redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:27redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:21redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 14:27rman01.dbf
-rw-r-----1 oracle oinstall 20979712 Jul 25 15:21seiang01.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:20temp01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 14:27users01.dbf
干净的关闭数据库，并重新启动数据库
SYS@seiang11g>shutdown abort
ORACLEinstance shut down.
SYS@seiang11g>startup
ORACLEinstance started.
TotalSystem Global Area 1252663296 bytes
FixedSize 2252824 bytes
VariableSize 788533224 bytes
DatabaseBuffers 452984832 bytes
RedoBuffers 8892416 bytes
Databasemounted.
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/
total2123612
-rw-r-----1 oracle oinstall 9781248 Jul 25 15:26control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:27redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 14:27redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:22redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 14:27rman01.dbf
-rw-r-----1 oracle oinstall 20979712 Jul 25 15:21seiang01.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:20temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 15:26 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 14:27users01.dbf
执行恢复操作
SYS@seiang11g>recover datafile 3;
Mediarecovery complete.
完成恢复操作后，打开数据库，会完成UNDO表空间的数据回滚操作，并验证恢复成功
SYS@seiang11g>alter databaseopen;
Databasealtered.
SYS@seiang11g>select * fromscott.test1;
ID NAME
--------------------
100 wjq
200 wjqgood
情况2：非关键数据文件损坏
1、模拟环境
模拟users和seiang表空间的数据文件损坏，这两个表空间的数据文件是非关键数据文件
SYS@seiang11g>selectFILE#,TS#,name,status from v$datafile;
FILE# TS# NAMESTATUS
-------------------- -------------------------------------------------- -------
10 /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
total133144
-rw-r-----1 oracle oinstall 20979712 Jul 25 15:34seiang01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 15:34users01.dbf
在scott用户下创建两张表，wjq1隶属于users表空间，wjq2隶属于seiang表空间
SCOTT@seiang11g>create tablewjq1(id number);
Tablecreated.
SCOTT@seiang11g>insert into wjq1values(111);
1row created.
SCOTT@seiang11g>insert into wjq1 values(222);
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
SCOTT@seiang11g>select * from wjq1;
ID
----------
111
222
SCOTT@seiang11g>create tablewjq2(name varchar2(10)) tablespace seiang;
Tablecreated.
SCOTT@seiang11g>insert into wjq2values('wjq100');
1row created.
SCOTT@seiang11g>insert into wjq2values('seiang200');
1row created.
SCOTT@seiang11g>commit;
Commitcomplete.
SCOTT@seiang11g>select * from wjq2;
NAME
----------
wjq100
seiang200
SYS@seiang11g>selecttable_name,tablespace_name,status from dba_tables
2where 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
total2097996
-rw-r-----1 oracle oinstall 9781248 Jul 25 15:44control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:44redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:27redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:27redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 15:27rman01.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:27temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 15:44 undotbs01.dbf
清除databuffer cache的记录
SYS@seiang11g>alter system flushbuffer_cache;
Systemaltered.
SYS@seiang11g>select * fromscott.wjq1;
select* from scott.wjq1
*
ERRORat 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_64Error: 2: No such file or directory
Additionalinformation: 3
SYS@seiang11g>select * fromscott.wjq2;
select* from scott.wjq2
*
ERRORat 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_64Error: 2: No such file or directory
Additionalinformation: 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
total2123612
-rw-r-----1 oracle oinstall 9781248 Jul 25 15:51control01.ctl
-rw-r-----1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:51redo01.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:27redo02.log
-rw-r-----1 oracle oinstall 52429312 Jul 25 15:27redo03.log
-rw-r-----1 oracle oinstall 31465472 Jul 25 15:27rman01.dbf
-rw-r-----1 oracle oinstall 20979712 Jul 25 15:51seiang01.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:27temp01.dbf
-rw-r-----1 oracle oinstall 110108672 Jul 25 15:48 undotbs01.dbf
-rw-r-----1 oracle oinstall 5251072 Jul 25 15:51users01.dbf
offline这两个数据文件
SYS@seiang11g>alter databasedatafile 4 offline;
Databasealtered.
SYS@seiang11g>alter databasedatafile 7 offline;
Databasealtered.
SYS@seiang11g>selectfile_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 datafile4,7;
Mediarecovery complete.
SYS@seiang11g>selectfile_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 databasedatafile 4 online;
Databasealtered.
SYS@seiang11g>alter databasedatafile 7 online;
Databasealtered.
SYS@seiang11g>selectfile_id,file_name,online_status from dba_data_files;
FILE_ID FILE_NAMEONLINE_
------------------------------------------------------------ -------
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;
ID
----------
111
222
SYS@seiang11g>select * fromscott.wjq2;
NAME
----------
wjq100
seiang200
相关链接:
Oracle手工不完全恢复（一）：使用当前控制文件
作者：SEian.G（苦练七十二变，笑对八十一难）
ITPUB：http://blog.itpub.net/31015730/
51CTO：http://seiang.blog.51cto.com/
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
