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部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1151908/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 11g 資料庫恢復-場景2：所有據檔案損壞，關庫狀態

相關文章