Oracle手工不完全恢复（一）：使用当前控制文件
实验环境
操作系统：CentOS7.1
数据库：Oracle11.2.0.4
目录
示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表
示例二：当前日志组损坏，造成数据库崩溃
示例三：归档日志丢失或损坏
示例一：基于SCN或时间点的恢复----恢复过去某个时间误删除的表
环境：
（1）提前对数据库做一次全库冷备份；
（2）在seiang用户下有一张test1表隶属于seiang表空间；
定位错误操作发生的时间或SCN：logminer
恢复原理：1.冷备份的数据文件*.dbf比现在要旧，那么数据文件的scn肯定就比现在的小；
2.使用日志挖掘方法，在日志中找出删表时的scn或时间戳；
3.在数据库shutdown的状态下，用冷备份的数据文件*.dbf，覆盖现在的*.dbf文件；保证数据文件完整性；
4.启动数据库到mount下,恢复到日志挖掘的SCN或时间点（利用日志对数据文件重做一次）
5.以resetlogs方式打开数据库，以前的日志就被覆盖了；示例二：当前日志组损坏，造成数据库崩溃
环境：
（1）提前对数据库做一次全库冷备份；
（2）在seiang用户下有一张test1表隶属于seiang表空间；
示例三：归档日志丢失或损坏
环境：
（1）提前有一套完成的数据文件的冷备份；
（2）在seiang用户下有一张test1表隶属于seiang表空间；SYS@seiang11g>select owner,table_name,tablespace_name from dba_tables where owner='SEIANG';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SEIANG TEST1 SEIANG
SEIANG TEST2 WJQ
SEIANG TEST3 WJQBEST
SYS@seiang11g>select * from seiang.test1;
ID NAME AGE
---------- ------------------------------ ----------
1 wjq 23
2 seiang 24
3 wjqdood 25
4 wjqbest 30
--查看当前日志序号为2
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 2 CURRENT
3 0 UNUSED
--修改数据并提交(sequence号2)
SYS@seiang11g>update seiang.test1 set age=100 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号3)
SYS@seiang11g>update seiang.test1 set age=200 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号4)
SYS@seiang11g>update seiang.test1 set age=300 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号5)
SYS@seiang11g>update seiang.test1 set age=400 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号6)
SYS@seiang11g>update seiang.test1 set age=500 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--修改数据并提交(sequence号7)
SYS@seiang11g>update seiang.test1 set age=600 where id=1;
1 row updated.
SYS@seiang11g>commit;
Commit complete.
SYS@seiang11g>alter system switch logfile;
System altered.
--当前联机日志序号为8
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 ACTIVE
2 8 CURRENT
3 6 ACTIVE
--查看相应的归档日志
SYS@seiang11g>select name from v$archived_log;
NAME
------------------------------------------------------
/u01/app/oracle/arch/arch_1_950962051_1.log
/u01/app/oracle/arch/arch_1_950962051_2.log
/u01/app/oracle/arch/arch_1_950971495_1.log
/u01/app/oracle/arch/arch_1_950971495_2.log
/u01/app/oracle/arch/arch_1_950972396_1.log
/u01/app/oracle/arch/arch_1_950972396_2.log
/u01/app/oracle/arch/arch_1_950972396_3.log
/u01/app/oracle/arch/arch_1_950972396_4.log
/u01/app/oracle/arch/arch_1_950972396_5.log
/u01/app/oracle/arch/arch_1_950972396_6.log
/u01/app/oracle/arch/arch_1_950972396_7.log
--关闭数据库
SYS@seiang11g >shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--模拟数据文件seiang损坏
SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
--重新打开数据库 ，由于数据文件的丢死，数据库无法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'
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE FILE NOT FOUND 0
--还原备份的数据文件
SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/seiang01.dbf /u01/app/oracle/oradata/OraDB11g/
SYS@seiang11g>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
--查看一下归档日志的详细信息
SYS@seiang11g>select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
1 /u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854
2 /u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883
1 /u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386
2 /u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401
1 /u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446
2 /u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042
3 /u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100
4 /u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110
5 /u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123
6 /u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139
7 /u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148
--模拟归档日志5丢失或者损坏
SYS@seiang11g>host rm /u01/app/oracle/arch/arch_1_950972396_5.log
[oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/arch/arch_1_950972396_5.log
ls: cannot access /u01/app/oracle/arch/arch_1_950972396_5.log: No such file or directory
--执行恢复操作，当在需要归档日志5的时候出现错误
SYS@seiang11g>recover datafile 7;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
ORA-00280: change 1914402 for thread 1 is in sequence #1
ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
ORA-00280: change 1937110 for thread 1 is in sequence #5
ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--查看数据文件头，发现检查点不一致
SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617
2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617
3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617
4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617
5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617
6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617
7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617
9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937617
--关闭数据库，还原所有冷备份的数据文件
SYS@seiang11g>shutdown abort
ORACLE instance shut down.
SYS@seiang11g>host cp/u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g/
SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/total 2279068
-rw-r----- 1 oracle oinstall 7680 Aug 2 16:08 arch_1_950972396_5.log
-rw-r----- 1 oracle oinstall 9781248 Aug 2 16:16 control01.ctl
-rw-r----- 1 oracle oinstall 363077632 Aug 2 16:17 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 2 15:52 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 2 15:58 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 2 15:52 redo03.log
-rw-r----- 1 oracle oinstall 31465472 Aug 2 16:17 rman01.dbf
-rw-r----- 1 oracle oinstall 73408512 Aug 2 16:17 seiang01.dbf
-rw-r----- 1 oracle oinstall 734011392 Aug 2 16:18 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Aug 2 16:18 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Aug 2 16:18 temp01.dbf
-rw-r----- 1 oracle oinstall 110108672 Aug 2 16:18 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 2 16:18 users01.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 2 16:18 wjq01.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 2 16:18 wjqbest01.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.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/app/oracle/oradata/OraDB11g/system01.dbf'
SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1913765
2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1913765
3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1913765
4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1913765
5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1913765
6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1913765
7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1913765
8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1913765
9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1913765
9 rows selected.
--恢复数据库 ，首先尝试完全恢复，同样在需要归档日志5的时候出现错误
SYS@seiang11g>recover database;
ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
ORA-00280: change 1913766 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
ORA-00280: change 1914386 for thread 1 is in sequence #2
ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
ORA-00280: change 1914402 for thread 1 is in sequence #1
ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
ORA-00280: change 1936446 for thread 1 is in sequence #2
ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
ORA-00280: change 1937042 for thread 1 is in sequence #3
ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
ORA-00280: change 1937100 for thread 1 is in sequence #4
ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
ORA-00280: change 1937110 for thread 1 is in sequence #5
ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950972396_5.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--执行不完全恢复 ，成功
SYS@seiang11g>recover database until cancel;
ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_5.log
ORA-00280: change 1937110 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
--再次查看数据文件头的相关信息
SYS@seiang11g>select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /u01/app/oracle/oradata/OraDB11g/system01.dbf 1937110
2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937110
3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937110
4 /u01/app/oracle/oradata/OraDB11g/users01.dbf 1937110
5 /u01/app/oracle/oradata/OraDB11g/example01.dbf 1937110
6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937110
7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110
8 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937110
9 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937110
--查看日志组信息，发现与5号脱节
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 7 INACTIVE
3 6 INACTIVE
2 8 CURRENT
--使用resetlogs打开数据库
SYS@seiang11g>alter database open resetlogs;
Database altered.
--日志序号从1开始记录
SYS@seiang11g>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 0 UNUSED
3 0 UNUSED
--确认表中的数据信息，发现在5号归档日志丢失后的修改都没有生效
SYS@seiang11g>select * from seiang.test1;
ID NAME AGE
---------- -------------------------------------------------- ----------
1 wjq 300
2 seiang 24
3 wjqdood 25
4 wjqbest 30
相关连接： Oracle手工完全恢复案例：http://blog.itpub.net/31015730/viewspace-2142669/
作者：SEian.G（苦练七十二变，笑对八十一难）
ITPUB：http://blog.itpub.net/31015730/
51CTO：http://seiang.blog.51cto.com/
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
