恢復(fù)：
1. 完全恢復(fù)
- 恢復(fù)的起點
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1191483
2 1191483
3 1191483
4 1191483
5 1191483
- 恢復(fù)的終點
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1191483
2 1191483
3 1191483
4 1191483
5 1191483
- 我們需要的是：橫向比較，各個文件之間進行比較
2. 不完全恢復(fù)
添加新的日志成員 - 給日志文件做鏡像SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO01.LOG' to group 1;
數(shù)據(jù)庫已更改。
SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO02.LOG' to group 2;
數(shù)據(jù)庫已更改。
SQL> alter database add logfile member 'D:\app\Administrator\oradata\orcl\log\REDO03.LOG' to group 3;
數(shù)據(jù)庫已更改。
--======剛開始都是INVALID狀態(tài)，因為沒有使用過======
SQL> select group#, sequence#, members from v$log;
GROUP# SEQUENCE# MEMBERS
---------- ---------- ----------
1 28 2
2 29 2
3 27 2
SQL> select status, member from v$logfile;
STATUS MEMBER
-------------- --------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
INVALID D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO01.LOG
INVALID D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO02.LOG
INVALID D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO03.LOG
--=====使用一次之后，就有狀態(tài)了====
SQL> alter system switch logfile;
系統(tǒng)已更改。
SQL> select status, member from v$logfile;
STATUS MEMBER
-------------- --------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
INVALID D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO01.LOG
INVALID D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO02.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\LOG\REDO03.LOG
如果有日志文件有備份的鏡像（member 壞了一個，但是還有別的），則不影響數(shù)據(jù)庫的正常啟動。
但是在跟蹤日志文件中，還是會有一些“警告”提示。
NOTE一個member損害：- drop鏡像，相當于沒有做過鏡像，修改控制文件...
SQL> alter database drop logfile member '*****';
增加/刪除日志組-- 沒有指明group#，系統(tǒng)會自動搜索&使用最小編號
SQL> alter database add logfile 'E:\ORACLE\WPENG\WPENG\LOG\REDO04.LOG' size 10m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 133 52428800 2 YES INACTIVE 2837020 15-OCT-12
2 1 134 52428800 2 NO CURRENT 2837025 15-OCT-12
3 1 132 52428800 2 YES INACTIVE 2837012 15-OCT-12
4 1 0 10485760 1 YES UNUSED 0
-- 也可以自己指明所需要的group#
SQL> alter database add logfile group 6 'E:\ORACLE\WPENG\WPENG\LOG\REDO05.LOG' size 10m;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 133 52428800 2 YES INACTIVE 2837020 15-OCT-12
2 1 134 52428800 2 NO CURRENT 2837025 15-OCT-12
3 1 132 52428800 2 YES INACTIVE 2837012 15-OCT-12
4 1 0 10485760 1 YES UNUSED 0
6 1 0 10485760 1 YES UNUSED 0
可以看到，剛添加的日志組，狀態(tài)都是為UNUSED。
--drop不會刪除物理日志文件
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
recover database until ***？？？恢復(fù)當下的 = 判別是否丟失數(shù)據(jù)
--關(guān)閉數(shù)據(jù)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--=====刪除當前日志文件組 group 3 所有的member file====
--啟動數(shù)據(jù)庫到mount狀態(tài)
SQL> startup mount
ORACLE instance started.
Total System Global Area 1319546880 bytes
Fixed Size 2114656 bytes
Variable Size 503319456 bytes
Database Buffers 805306368 bytes
Redo Buffers 8806400 bytes
Database mounted.
--啟動數(shù)據(jù)庫到open狀態(tài)
--失敗：找不到group 3 的 members
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'
--嘗試刪除 group 3
--失敗：group 3是當前日志組
SQL> alter database drop logfile group 3 ;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance wpeng (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'
--嘗試切換當前日志組
--失敗：數(shù)據(jù)庫沒有open
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-01109: database not open
恢復(fù)以前的數(shù)據(jù)文件-- 關(guān)閉數(shù)據(jù)
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
-- ===刪除文件 + 使用用以前的備份文件====
-- 啟動數(shù)據(jù)庫到 mount狀態(tài)
SQL> startup mount
ORACLE instance started.
Total System Global Area 1319546880 bytes
Fixed Size 2114656 bytes
Variable Size 503319456 bytes
Database Buffers 805306368 bytes
Redo Buffers 8806400 bytes
Database mounted.
-- 查看checkpoint_change#
SQL> select file#, checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2839508
2 2839508
3 2839508
4 2839508
5 2839508
SQL> select file#, checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2782605
2 2782605
3 2782605
4 2782605
5 2782605
-- 啟動數(shù)據(jù)庫 open，報錯
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\WPENG\WPENG\SYSTEM01.DBF'
-- recover database until cancel
SQL> recover database
ORA-00279: change 2782605 generated at 09/27/2012 10:09:32 needed for thread 1
ORA-00289: suggestion : E:\APP\WPENG\PRODUCT\11.1.0\FLASH_RECOVER_AREA\WPENG\ARCHIVELOG\2012_09_27\O1_MF_1_118_867GK3OW_
.ARC
ORA-00280: change 2782605 for thread 1 is in sequence #118
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\LOG\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00312: online log 3 thread 1: 'E:\ORACLE\WPENG\WPENG\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01112: media recovery not started
-- group 3 member刪除，只能cancel
SQL> recover database until cancel
ORA-00279: change 2839398 generated at 10/15/2012 09:17:16 needed for thread 1
ORA-00289: suggestion : E:\APP\WPENG\PRODUCT\11.1.0\FLASH_RECOVER_AREA\WPENG\ARCHIVELOG\2012_10_15\O1_MF_1_138_%U_.ARC
ORA-00280: change 2839398 for thread 1 is in sequence #138
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
-- resetlogs方式open database
SQL> alter database open resetlogs;
Database altered.
-- ====刪除的member創(chuàng)建由系統(tǒng)創(chuàng)建===
-- 5 commit的數(shù)據(jù)，在當前group 3，由于被誤刪除，丟失，不完全恢復(fù)
SQL> select * from t;
ID
----------
1
2
3
4
-- 由于resetlogs方式打開數(shù)據(jù)庫，所以log的sequence#重新開始了
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 1 52428800 2 YES ACTIVE 2839399 15-OCT-12
2 1 2 52428800 2 YES ACTIVE 2839935 15-OCT-12
3 1 3 52428800 2 NO CURRENT 2839937 15-OCT-12