Oracle備份恢復(fù)中，redo的恢復(fù)相對來說還是比較簡單的，只要保證每組的成員不止一個，出現(xiàn)問題的幾率就相當(dāng)小，即使出了問題我們也可按照不同的方法將他們恢復(fù)，所以如果碰到日志文件損壞，完全不必緊張！按照下面的方法來做，基本上都能搞定！
試驗一：用命令清空日志組方法
1、 查看原來表中數(shù)據(jù)
SQL> conn test/test
Connected.
SQL> select * from test;
TEL
----------
1
2
3
2、插入新數(shù)據(jù)
SQL> insert into test values(4);
1 row created.
SQL> commit;
Commit complete.
SQL>
3、 正常關(guān)閉數(shù)據(jù)庫
4、 利用os command刪除所有redo文件
5、 啟動數(shù)據(jù)庫
SQL> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
6、 查看當(dāng)前日志狀態(tài)
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 2 104857600 1 YES INACTIVE
487837 01-9月 -05
2 1 4 104857600 1 NO CURRENT
487955 01-9月 -05
3 1 3 104857600 1 YES INACTIVE
487839 01-9月 -05
看來redo01.log不是當(dāng)前日志，對于這類非當(dāng)前日志可以直接clear,系統(tǒng)會重新自動生成一個redo文件
7、SQL> alter database clear logfile group 1;
Database altered.
7、 繼續(xù)啟動db
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
8、 看來redo也得恢復(fù)，但是redo02是當(dāng)前redo,直接clear是不行的
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of thread 1 needs to be archived
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
9、 按照oracle的某些做法也是不行的
SQL> alter database clear unarchived logfile group 2 unrecoverable datafile;
alter database clear unarchived logfile group 2 unrecoverable datafile
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
10、 嘗試從其他冷被分cp過來一個，再作clear,還是不行
SQL> host cp /T3/ORACLE/oradata2/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/
SQL> alter database clear unarchived logfile group 2 unrecoverable datafile;
alter database clear unarchived logfile group 2 unrecoverable datafile
*
ERROR at line 1:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
SQL>
11、 但是對于非當(dāng)前日志就都可以，下面看看redo03
SQL> alter database clear logfile group 3;
Database altered.
結(jié)論：
如果數(shù)據(jù)庫是正常shutdown,非當(dāng)前日志都可以直接clear來重新生成，而且不丟失數(shù)據(jù)，因為正常關(guān)閉db,數(shù)據(jù)已經(jīng)寫入dbf文件了。唯獨(dú)當(dāng)前日志不可以，當(dāng)前日志必須用其他方法恢復(fù)，不管是不是正常關(guān)閉，
方法二：用cancel模式恢復(fù)數(shù)據(jù)庫
前面的出錯提示，步驟都一樣，唯獨(dú)恢復(fù)的方法不一樣
SQL> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
看看丟失了哪些redo
SQL> host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
看來redo都丟了
直接recover
SQL> recover database until cancel;
Media recovery complete.
這個時候redo還沒有生成
SQL> host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
啟動數(shù)據(jù)庫
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
（注意，這里必須用resetlogs,否則會錯誤的
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
SQL>
Resetlogs其實就是根據(jù)控制文件讓系統(tǒng)自動重新生成redo,如果noresetlog的話，就不會重新生成redo,缺少了文件，db自然無法啟動）
SQL> host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo01.log /T3/ORACLE/oradata/ORA9/redo02.log /T3/ORACLE/oradata/ORA9/redo03.log
SQL>
檢驗
SQL> select * from test.test;
TEL
----------
1
2
3
4
SQL>
數(shù)據(jù)一點(diǎn)兒都沒有丟失
結(jié)論：
如果數(shù)據(jù)庫是正常關(guān)閉的，用recover database until cancel可以輕松恢復(fù)或者說重新建立所有的redo，不再區(qū)分是否是當(dāng)前日志，而且由于正常關(guān)閉，不會丟失任何數(shù)據(jù)，唯一可能丟失的情況就是如果日志還沒有歸檔
這種恢復(fù)方法由于要resetlogs,所以在恢復(fù)完成后，日志清零，以前的備份不再起作用，所以建議立即備份
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /T3/ORACLE/arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>
實驗三：通過重新生成控制文件來恢復(fù)redo
前面的都一樣，只是處理方法不一樣
SQL> startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
2、 修改一下剛才生成的那個文件
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/T3/ORACLE/oradata/ORA9/redo01.log' SIZE 100M,
GROUP 2 '/T3/ORACLE/oradata/ORA9/redo02.log' SIZE 100M,
GROUP 3 '/T3/ORACLE/oradata/ORA9/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/T3/ORACLE/oradata/ORA9/system01.dbf',
'/T3/ORACLE/oradata/ORA9/undotbs01.dbf',
'/T3/ORACLE/oradata/ORA9/cwmlite01.dbf',
'/T3/ORACLE/oradata/ORA9/drsys01.dbf',
'/T3/ORACLE/oradata/ORA9/example01.dbf',
'/T3/ORACLE/oradata/ORA9/indx01.dbf',
'/T3/ORACLE/oradata/ORA9/odm01.dbf',
'/T3/ORACLE/oradata/ORA9/tools01.dbf',
'/T3/ORACLE/oradata/ORA9/users01.dbf',
'/T3/ORACLE/oradata/ORA9/xdb01.dbf',
'/T3/ORACLE/oradata/ORA9/test01.dbf'
CHARACTER SET ZHS16GBK
;
另存為一個腳本，運(yùn)行他
SQL> @clone.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL>
搞定……………
結(jié)論：這種方法的關(guān)鍵是重新創(chuàng)建控制文件，后面的步驟和前面的道理一樣的
前面的三種方法都是假設(shè)db是正常關(guān)閉的，數(shù)據(jù)已經(jīng)寫入數(shù)據(jù)庫文件中，所以不會由數(shù)據(jù)存在redo種，所以clear的話也不會有數(shù)據(jù)丟失