Oracle恢復實驗（一）

環境：Oracle10g、Red Hat 4，Oracle執行在歸檔模式。

模擬場景：user01.dbf資料檔案損壞，關閉資料庫條件下恢復。

具體步驟：

[b]1、先做個全庫冷備份，備份控制檔案、資料檔案、引數檔案[/b]
SQL> select name from v$controlfile
2 union
3 select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/product/oradata/orcl/control01.ctl
/home/oracle/oracle/product/oradata/orcl/control02.ctl
/home/oracle/oracle/product/oradata/orcl/control03.ctl
/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf
/home/oracle/oracle/product/oradata/orcl/system01.dbf
/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf
/home/oracle/oracle/product/oradata/orcl/users01.dbf

7 rows selected.

　也就是關庫，備份上面幾個檔案，並且備份引數檔案，口令檔案可備可不備。

[b]2、用scott使用者登入，建個表，插入些資料。提交，檢查一下資料行數。[/b]

SQL> conn scott/oracle
Connected.
SQL> create table recover_test tablespace users as select * from emp;

Table created.

SQL> insert into recover_test (select * from emp);

14 rows created.

SQL> insert into recover_test select * from recover_test;

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> commit;

Commit complete.

SQL> select count (*) from recover_test;

COUNT(*)
----------
448

[b]3、多做幾次日誌切換，這樣有且於瞭解Oracle恢復的原理。[/b]

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

[b]4、主機斷電，刪除user01.dbf，模擬資料檔案損壞[/b]
SQL> shutdown abort
ORACLE instance shut down.
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/user01.dbf

[b]5、嘗試開啟資料庫[/b]
SQL> startup
ORACLE instance started.

Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf'

此時提示資料檔案找不到。警告日誌中可以看到更加詳細的資訊。

[b]6、恢復資料檔案，嘗試開啟資料庫[/b]
SQL> !cp /disk2/bak/users01.dbf /home/oracle/oracle/product/oradata/orcl/

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf'

此時由於資料庫不一致，提示需要介質恢復。檢查一下scn情況。
select 'scnOfDatafile',name,checkpoint_change# from v$datafile
union
select 'scnOfDatafileHeader',name,checkpoint_change# from v$datafile_header;

'SCNOFDATAFILE' NAME CHECKPOINT_CHANGE#
------------------- ------------------------------------------------------------ ------------------
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf 743872
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/system01.dbf 743872
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf 743872
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/users01.dbf 743872
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf 743872
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/system01.dbf 743872
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf 743872
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/users01.dbf 721971

9 rows selected.

可以明顯的看到資料檔案users01.dbf的scn比較小，這是備份時的scn。

[b]7、介質恢復[/b]
SQL> recover database;
ORA-00279: change 721971 generated at 09/25/2011 13:27:36 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_7_760842868.dbf
ORA-00280: change 721971 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 723634 generated at 09/25/2011 14:05:56 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_8_760842868.dbf
ORA-00280: change 723634 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/oracle/product/archive/1_7_760842868.dbf' no longer needed for this recovery


ORA-00279: change 723636 generated at 09/25/2011 14:05:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_9_760842868.dbf
ORA-00280: change 723636 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/oracle/product/archive/1_8_760842868.dbf' no longer needed for this recovery


ORA-00279: change 743752 generated at 09/25/2011 14:10:32 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_10_760842868.dbf
ORA-00280: change 743752 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/oracle/product/archive/1_9_760842868.dbf' no longer needed for this recovery


ORA-00279: change 743864 generated at 09/25/2011 14:10:58 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_11_760842868.dbf
ORA-00280: change 743864 for thread 1 is in sequence #11
ORA-00278: log file '/home/oracle/oracle/product/archive/1_10_760842868.dbf' no longer needed for this recovery


ORA-00279: change 743866 generated at 09/25/2011 14:10:59 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_12_760842868.dbf
ORA-00280: change 743866 for thread 1 is in sequence #12
ORA-00278: log file '/home/oracle/oracle/product/archive/1_11_760842868.dbf' no longer needed for this recovery


ORA-00279: change 743868 generated at 09/25/2011 14:11:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_13_760842868.dbf
ORA-00280: change 743868 for thread 1 is in sequence #13
ORA-00278: log file '/home/oracle/oracle/product/archive/1_12_760842868.dbf' no longer needed for this recovery


使用auto選項進行恢復，可以看到使用了8-13號日誌，到作業系統中看一下：
[oracle@localhost archive]$ ls -lt
total 4112
-rw-r----- 1 oracle oinstall 4608 Sep 25 14:11 1_15_760842868.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 25 14:11 1_14_760842868.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 25 14:11 1_13_760842868.dbf
-rw-r----- 1 oracle oinstall 2048 Sep 25 14:11 1_12_760842868.dbf
-rw-r----- 1 oracle oinstall 1024 Sep 25 14:10 1_11_760842868.dbf
-rw-r----- 1 oracle oinstall 53760 Sep 25 14:10 1_10_760842868.dbf
-rw-r----- 1 oracle oinstall 1536 Sep 25 14:10 1_8_760842868.dbf
-rw-r----- 1 oracle oinstall 30720 Sep 25 14:10 1_9_760842868.dbf
-rw-r----- 1 oracle oinstall 4043776 Sep 25 14:10 1_7_760842868.dbf
還有14、15兩個歸檔日誌，可這兩個並沒有使用。這是為什麼？
檢視一下警告日誌
[oracle@localhost bdump]$ tail -f alert_orcl.log
Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo01.log
Sun Sep 25 14:33:19 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 15 Reading mem 0
Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo02.log
Sun Sep 25 14:33:19 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0
Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo03.log
Sun Sep 25 14:33:19 2011
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
根據以上資訊，可以看到：Oracle使用的redo日誌。
[color=red]結論：Oracle介質恢復，使用歸檔日誌+Redo日誌，優先使用Redo日誌。應用完Redo日誌，資料庫就可以開啟，如果需要Undo日誌，繼續Undo日誌做回滾。可以在第2步發起一個未提交事務，abort關庫後，可以看到效果。[/color]

[b]8、驗證一下SCN[/b]
select 'scnOfDatafile',name,checkpoint_change# from v$datafile
union
select 'scnOfDatafileHeader',name,checkpoint_change# from v$datafile_header
union
select 'scnOfDatabase','database',checkpoint_change# from v$database;

'SCNOFDATAFILE' NAME CHECKPOINT_CHANGE#
------------------- ------------------------------------------------------------ ------------------
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf 743906
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/system01.dbf 743906
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf 743906
scnOfDatafile /home/oracle/oracle/product/oradata/orcl/users01.dbf 743906
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf 743906
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/system01.dbf 743906
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf 743906
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/users01.dbf 743906

8 rows selected.
發現SCN號已經一致。此時資料庫已經處於同步狀態。

[b]9、檢查資料是否恢復[/b]
SQL> select count(*) from scott.recover_test;

COUNT(*)
----------
448