恢复原理: 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: {=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
相关连接:
Oracle手工完全恢复案例:http://blog.itpub.net/31015730/viewspace-2142669/
作者:SEian.G(苦练七十二变,笑对八十一难)
ITPUB:http://blog.itpub.net/31015730/
51CTO:http://seiang.blog.51cto.com/
文章名称:Oracle手工不完全恢复(一):使用当前控制文件
当前路径:
http://cqcxhl.com/article/pjhcdj.html