Oracle手工完全恢复案例
手工完全恢复
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;
ID
----------
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;
ID
----------
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/
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/17.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
