Oracle備份恢復(fù)中，redo的恢復(fù)相對(duì)來(lái)說(shuō)還是比較簡(jiǎn)單的，只要保證每組的成員不止一個(gè)，出現(xiàn)問(wèn)題的幾率就相當(dāng)小，即使出了問(wèn)題我們也可按照不同的方法將他們恢復(fù)，所以如果碰到日志文件損壞，完全不必緊張！按照下面的方法來(lái)做，基本上都能搞定！
試驗(yàn)一：用命令清空日志組方法
1、 查看原來(lái)表中數(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ù)庫(kù)
4、 利用os command刪除所有redo文件
5、 啟動(dòng)數(shù)據(jù)庫(kù)
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
看來(lái)redo01.log不是當(dāng)前日志，對(duì)于這類非當(dāng)前日志可以直接clear,系統(tǒng)會(huì)重新自動(dòng)生成一個(gè)redo文件
7、SQL> alter database clear logfile group 1;
Database altered.
7、 繼續(xù)啟動(dòng)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、 看來(lái)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過(guò)來(lái)一個(gè)，再作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、 但是對(duì)于非當(dāng)前日志就都可以，下面看看redo03
SQL> alter database clear logfile group 3;
Database altered.
結(jié)論：
如果數(shù)據(jù)庫(kù)是正常shutdown,非當(dāng)前日志都可以直接clear來(lái)重新生成，而且不丟失數(shù)據(jù)，因?yàn)檎ｊP(guān)閉db,數(shù)據(jù)已經(jīng)寫入dbf文件了。唯獨(dú)當(dāng)前日志不可以，當(dāng)前日志必須用其他方法恢復(fù)，不管是不是正常關(guān)閉，
方法二：用cancel模式恢復(fù)數(shù)據(jù)庫(kù)
前面的出錯(cuò)提示，步驟都一樣，唯獨(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
看來(lái)redo都丟了
直接recover
SQL> recover database until cancel;
Media recovery complete.
這個(gè)時(shí)候redo還沒(méi)有生成
SQL> host ls /T3/ORACLE/oradata/ORA9/redo*
/T3/ORACLE/oradata/ORA9/redo*: No such file or directory
啟動(dòng)數(shù)據(jù)庫(kù)
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,否則會(huì)錯(cuò)誤的
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其實(shí)就是根據(jù)控制文件讓系統(tǒng)自動(dòng)重新生成redo,如果noresetlog的話，就不會(huì)重新生成redo,缺少了文件，db自然無(wú)法啟動(dòng)）
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>
檢驗(yàn)
SQL> select * from test.test;
TEL
----------
1
2
3
4
SQL>
數(shù)據(jù)一點(diǎn)兒都沒(méi)有丟失
結(jié)論：
如果數(shù)據(jù)庫(kù)是正常關(guān)閉的，用recover database until cancel可以輕松恢復(fù)或者說(shuō)重新建立所有的redo，不再區(qū)分是否是當(dāng)前日志，而且由于正常關(guān)閉，不會(huì)丟失任何數(shù)據(jù)，唯一可能丟失的情況就是如果日志還沒(méi)有歸檔
這種恢復(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>
實(shí)驗(yàn)三：通過(guò)重新生成控制文件來(lái)恢復(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、 修改一下剛才生成的那個(gè)文件
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
;
另存為一個(gè)腳本，運(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ù)庫(kù)文件中，所以不會(huì)由數(shù)據(jù)存在redo種，所以clear的話也不會(huì)有數(shù)據(jù)丟失