Oracle 11g 数据库恢复-场景2：所有据文件损坏，关库状态
|
--切日志 sys@TESTDB11>alter system switch logfile;
System altered. --修改数据提交 scott@TESTDB11>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 6000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 2600 10
14 rows selected.
scott@TESTDB11>update emp set sal = 1800 where ename = 'SMITH';
1 row updated.
scott@TESTDB11>commit;
Commit complete. --修改数据不提交 scott@TESTDB11>select * from dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
scott@TESTDB11>delete from dept where deptno = 40;
1 row deleted.
--切日志3次 sys@TESTDB11>alter system switch logfile;
System altered.
--查看检查点 sys@TESTDB11>select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE# ------------------------------------------------------------ ------------------ /u01/app/oracle/oradata/TestDB11/system01.dbf 2726220 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf 2726220 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf 2726220 /u01/app/oracle/oradata/TestDB11/users01.dbf 2726220 /u01/app/oracle/oradata/TestDB11/example01.dbf 2726220 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf 2726220
6 rows selected.
--关库 sys@TESTDB11>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
--删除所有的dbf文件 [oracle@S1011:/export/home/oracle]$ cd /u01/app/oracle/oradata/TestDB11 [oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ ls control01.ctl example01.dbf redo01.log redo02.log redo03.log rotbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf [oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ rm *.dbf [oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ ls control01.ctl redo01.log redo02.log redo03.log
--启库 sys@TESTDB11>startup ORACLE instance started.
Total System Global Area 855982080 bytes Fixed Size 2230792 bytes Variable Size 641730040 bytes Database Buffers 209715200 bytes Redo Buffers 2306048 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/u01/app/oracle/oradata/TestDB11/system01.dbf'
--查看需要恢复的文件的信息 sys@TESTDB11>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- --------------- ---------- --------- 1 ONLINE ONLINE FILE NOT FOUND 0 2 ONLINE ONLINE FILE NOT FOUND 0 3 ONLINE ONLINE FILE NOT FOUND 0 4 ONLINE ONLINE FILE NOT FOUND 0 5 ONLINE ONLINE FILE NOT FOUND 0 6 ONLINE ONLINE FILE NOT FOUND 0
6 rows selected.
--还原文件 [oracle@S1011:/]$ cp /backup/inconsistent_backup/*.dbf /u01/app/oracle/oradata/TestDB11
--检查一下控制文件中记录的数据文件中的检查点号（一致，因为刚才做的shutdown immediate) sys@TESTDB11>select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE# ------------------------------------------------------------ ------------------ /u01/app/oracle/oradata/TestDB11/system01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/users01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/example01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf 2726683
6 rows selected. --检查还原回来的数据文件头中的检查点号 sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE# ------------------------------------------------------------ ------------------ /u01/app/oracle/oradata/TestDB11/system01.dbf 2654775 (最小的检查点) /u01/app/oracle/oradata/TestDB11/sysaux01.dbf 2654801 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf 2654893 /u01/app/oracle/oradata/TestDB11/users01.dbf 2654911 /u01/app/oracle/oradata/TestDB11/example01.dbf 2654924 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf 2654946
6 rows selected. --查看恢复过程中需要使用的日志 sys@TESTDB11>col archive_name for a30 sys@TESTDB11>select * from v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME ---------- ---------- --------- ------------------------------ 1 98 09-AUG-13 /archive2/1_98_813665348.dbf 1 99 09-AUG-13 /archive2/1_99_813665348.dbf 1 100 10-AUG-13 /archive2/1_100_813665348.dbf 1 101 10-AUG-13 /archive2/1_101_813665348.dbf 1 102 10-AUG-13 /archive2/1_102_813665348.dbf 1 103 10-AUG-13 /archive2/1_103_813665348.dbf 1 104 10-AUG-13 /archive2/1_104_813665348.dbf
7 rows selected.
sys@TESTDB11>select sequence#, group#, status from v$log;
SEQUENCE# GROUP# STATUS ---------- ---------- ---------------- 106 1 INACTIVE 105 3 INACTIVE 107 2 CURRENT
--计算一下，为什么需要上述这些归档(找还原回来的数据文件头中最小的检查点） sys@TESTDB11>col name for a50 sys@TESTDB11>select first_change#, next_change#, sequence#, name from v$archived_log;
FIRST_CHANGE# NEXT_CHANGE# SEQUENCE# NAME ------------- ------------ ---------- -------------------------------------------------- 2547678 2572509 89 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_89_9088fgfy_.arc
2572509 2580755 90 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_90_908m64sj_.arc
2580755 2581128 91 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_91_908mld6s_.arc
2581128 2581689 92 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_92_908n7jll_.arc
2581689 2583163 93 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_93_908o4bd8_.arc
2583163 2583270 94 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_94_908obsky_.arc
2583270 2603385 95 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_08/o1_mf_1_95_908oq470_.arc
2603385 2635256 96 /u01/app/oracle/fast_recovery_area/TESTDB11/archiv elog/2013_08_09/o1_mf_1_96_909wogyw_.arc
2635256 2641003 97 /archive1/1_97_813665348.dbf 2635256 2641003 97 /archive2/1_97_813665348.dbf 2641003 2660981 98 /archive1/1_98_813665348.dbf 2641003 2660981 98 /archive2/1_98_813665348.dbf 2660981 2667783 99 /archive1/1_99_813665348.dbf 2660981 2667783 99 /archive2/1_99_813665348.dbf 2667783 2679804 100 /archive1/1_100_813665348.dbf 2667783 2679804 100 /archive2/1_100_813665348.dbf 2679804 2699110 101 /archive1/1_101_813665348.dbf 2679804 2699110 101 /archive2/1_101_813665348.dbf 2699110 2725650 102 /archive1/1_102_813665348.dbf 2699110 2725650 102 /archive2/1_102_813665348.dbf 2725650 2726122 103 /archive1/1_103_813665348.dbf 2725650 2726122 103 /archive2/1_103_813665348.dbf 2726122 2726220 104 /archive1/1_104_813665348.dbf 2726122 2726220 104 /archive2/1_104_813665348.dbf 2726220 2726249 105 /archive1/1_105_813665348.dbf 2726220 2726249 105 /archive2/1_105_813665348.dbf 2726249 2726261 106 /archive1/1_106_813665348.dbf 2726249 2726261 106 /archive2/1_106_813665348.dbf
28 rows selected.
--执行恢复 sys@TESTDB11>recover database; ORA-00279: change 2654775 generated at 08/09/2013 21:25:44 needed for thread 1 ORA-00289: suggestion : /archive2/1_98_813665348.dbf ORA-00280: change 2654775 for thread 1 is in sequence #98
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--输入回车
ORA-00279: change 2660981 generated at 08/09/2013 22:19:48 needed for thread 1 ORA-00289: suggestion : /archive2/1_99_813665348.dbf ORA-00280: change 2660981 for thread 1 is in sequence #99
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 2667783 generated at 08/10/2013 00:00:55 needed for thread 1 ORA-00289: suggestion : /archive2/1_100_813665348.dbf ORA-00280: change 2667783 for thread 1 is in sequence #100
ORA-00279: change 2679804 generated at 08/10/2013 03:00:28 needed for thread 1 ORA-00289: suggestion : /archive2/1_101_813665348.dbf ORA-00280: change 2679804 for thread 1 is in sequence #101
ORA-00279: change 2699110 generated at 08/10/2013 08:29:58 needed for thread 1 ORA-00289: suggestion : /archive2/1_102_813665348.dbf ORA-00280: change 2699110 for thread 1 is in sequence #102
ORA-00279: change 2725650 generated at 08/10/2013 10:27:18 needed for thread 1 ORA-00289: suggestion : /archive2/1_103_813665348.dbf ORA-00280: change 2725650 for thread 1 is in sequence #103
ORA-00279: change 2726122 generated at 08/10/2013 10:29:03 needed for thread 1 ORA-00289: suggestion : /archive2/1_104_813665348.dbf ORA-00280: change 2726122 for thread 1 is in sequence #104
-- 为什么没有应用序号为105和106的日志? Log applied. Media recovery complete.
--查看一致性 sys@TESTDB11>select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/TestDB11/system01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/users01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/example01.dbf 2726683 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf 2726683
6 rows selected.
sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ /u01/app/oracle/oradata/TestDB11/system01.dbf 2726681 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf 2726681 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf 2726681 /u01/app/oracle/oradata/TestDB11/users01.dbf 2726681 /u01/app/oracle/oradata/TestDB11/example01.dbf 2726681 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf 2726681
6 rows selected.
--开库 sys@TESTDB11>alter database open;
Database altered.
--查看数据 --提交的修改 scott@TESTDB11>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 1800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 6000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 2600 10
14 rows selected.
--未提交的修改 scott@TESTDB11>select * from dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17013648/viewspace-1151908/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 2015.09.18 网络公开课 《Oracle 11G OCP试题精讲（4）》
- 2015 最新OCM 10g考试资料汇总 Skillset 7（1）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（2）
- 2015 最新OCM 10g考试资料汇总 Skillset 6（1）
- 2015.05.15 网络公开课 《Oracle 11G RAC深入探索之RAC和RAC One Node之间的切换》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（5）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（3）
- 2015 最新OCM 10g考试资料汇总 Skillset 5（2）
- 2015.04.10 网络公开课 《玩转DG系列之-使用RMAN创建物理DG》
- 2015 最新OCM 10g考试资料汇总 Skillset 5（1）
