(Les16 執行資料庫恢復)-重做日誌文件恢復

丟失重做日誌文件丟失了重做日誌文件組中的某個成員，並且組中至少還有一個成員： -不會影響實例的正常操作。 -預警日誌中會收到一條信息，通知您無法找到某個成員 -可以通過刪除丟失的重做日誌成員並添加新成員來恢復丟失的日誌文件 -如果包含丟失日誌文件的組已歸檔，您可以清除日誌組來重新創建丟失的文件恢 ...

丟失重做日誌文件丟失了重做日誌文件組中的某個成員，並且組中至少還有一個成員： -不會影響實例的正常操作。 -預警日誌中會收到一條信息，通知您無法找到某個成員 -可以通過刪除丟失的重做日誌成員並添加新成員來恢復丟失的日誌文件 -如果包含丟失日誌文件的組已歸檔，您可以清除日誌組來重新創建丟失的文件

恢復步驟： 1.確定是否有缺失的日誌文件 2.恢復丟失的文件時，先刪除丟失的重做日誌成員 ALTER DATABASE DROP LOGFILE MEMBER '+DATA/orcl/onlinelog/group_1.261.691672257'; 3.然後添加新成員來替代丟失的日誌成員 ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2; 4.如果介質故障是由於磁碟或控制器缺失而造成的，需重命名丟失文件。 5.如果重做日誌組已歸檔，或者處於NOARCHIVELOG模式下，則可選擇在清除日誌組後重新創建缺失來解決問題， ALTER DATABASE CLEAR LOGFILE GROUP n; 註意：清除未歸檔的日誌組，應立即對整個資料庫執行完全備份，否則，在發生其它故障的情況下，會導致數據丟失。 ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP #;

測試環境中先新增3組，每組2個成員

alter database  add logfile group 4  ('/u01/app/oracle/oradata/xiocpt0/p_redo04_01.log','/u01/app/oracle/oradata/xiocpt0/p_redo04_02.log') size 128M;
alter database  add logfile group 5  ('/u01/app/oracle/oradata/xiocpt0/p_redo05_01.log','/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log') size 128M;
alter database  add logfile group 6  ('/u01/app/oracle/oradata/xiocpt0/p_redo06_01.log','/u01/app/oracle/oradata/xiocpt0/p_redo06_02.log') size 128M;

set pagesize 600 select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log; col member format a50 select group#,status,member from v$logfile;

SQL> set pagesize 600
 
select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;--查看重做日誌成員狀態信息
 
col member format a50
 
 
SQL> SQL>
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1          4                50          1 YES INACTIVE
         2          5                50          1 NO  CURRENT
         3          3                50          1 YES INACTIVE
         4          0               128          2 YES UNUSED
         5          0               128          2 YES UNUSED
         6          0               128          2 YES UNUSED
 
6 rows selected.
 
SQL> select group#,status,member from v$logfile;--查看重做日誌成員，物理路徑，這裡存儲方式為文件系統
 
    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1 STALE   /u01/app/oracle/oradata/xiocpt0/redo01.log
         3 STALE   /u01/app/oracle/oradata/xiocpt0/redo03.log
         2         /u01/app/oracle/oradata/xiocpt0/redo02.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_01.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_02.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_01.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_01.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_02.log
 
9 rows selected.
 
手動刪除group 1的物理文件
 
SQL> !rm /u01/app/oracle/oradata/xiocpt0/redo01.log
 
group 1的狀態以歸檔，INACTIVE
 
SQL> select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;
 
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1          4                50          1 YES INACTIVE
         2          5                50          1 NO  CURRENT
         3          3                50          1 YES INACTIVE
         4          0               128          2 YES UNUSED
         5          0               128          2 YES UNUSED
         6          0               128          2 YES UNUSED
 
6 rows selected.
 
驗證物理文件已被刪除
 
SQL> !ls -l  /u01/app/oracle/oradata/xiocpt0/redo01.log
ls: cannot access /u01/app/oracle/oradata/xiocpt0/redo01.log: No such file or directory
 
針對已歸檔的重做日誌文件組，可以使用clear命令進行恢復
 
alter database clear logfile group 1;
 
SQL> alter database clear logfile group 1;
 
Database altered.
 
驗證物理文件已恢復完成
 
SQL> !ls -l  /u01/app/oracle/oradata/xiocpt0/redo01.log
-rw-r----- 1 oracle dba 52429312 May 22 13:42 /u01/app/oracle/oradata/xiocpt0/redo01.log

切換重做日誌文件 alter system switch logfile; alter system checkpoint; 測試刪除重做日誌文件組中單個成員

SQL>  select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;
 
 
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1          6                50          1 YES INACTIVE
         2          5                50          1 YES INACTIVE
         3          3                50          1 YES INACTIVE
         4          7               128          2 YES INACTIVE
         5          8               128          2 YES INACTIVE
         6          9               128          2 NO  CURRENT
 
 
刪除group 5中的 02成員
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
-rw-r----- 1 oracle dba 134218240 May 22 13:42 /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
 
SQL> !rm /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
ls: cannot access /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log: No such file or directory
 
SQL> alter system switch logfile;
 
System altered.
SQL> /
SQL> /
SQL> /
SQL> /
 
SQL>  alter system checkpoint;
 
System altered.
 
 
oracle預警日誌文件中提示/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log以不存在，因為重做日誌group 5有2個成員，數據依舊正常。
 
 
 
Errors in file /u01/app/oracle/diag/rdbms/xiocpt0/xiocpt0/trace/xiocpt0_arc0_1460.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/xiocpt0/xiocpt0/trace/xiocpt0_arc0_1460.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
 
 
對group有多個成員的，單成員進行修複
 
alter database drop logfile member '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log';
 
alter database add logfile member '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log' to group 5;
 
先將丟失的成員從group中進行刪除
SQL> alter database drop logfile member '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log';
 
Database altered.
 
SQL>  select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;
 
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1         12                50          1 YES INACTIVE
         2         11                50          1 YES INACTIVE
         3         16                50          1 NO  CURRENT
         4         13               128          2 YES INACTIVE
         5         14               128          1 YES INACTIVE
         6         15               128          2 YES INACTIVE
 
6 rows selected.
 
 
再新增成員
SQL> alter database add logfile member '/u01/app/oracle/oradata/xiocpt0/p_redo05_02.log' to group 5;
 
Database altered.
 
SQL>  select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;
 
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1         12                50          1 YES INACTIVE
         2         11                50          1 YES INACTIVE
         3         16                50          1 NO  CURRENT
         4         13               128          2 YES INACTIVE
         5         14               128          2 YES INACTIVE
         6         15               128          2 YES INACTIVE
 
6 rows selected.
 
 
也可以對整個group 進行clear，但需註意此group 必須已歸檔
 
SQL> !rm /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
ls: cannot access /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log: No such file or directory
 
SQL> alter database clear logfile group 5;
 
Database altered.
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
-rw-r----- 1 oracle dba 134218240 May 22 13:51 /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log

丟失當前重做日誌文件 alter database clear unarchived logfile group #;

SQL>  select group#,sequence#,bytes/(1024*1024),members,archived,status from v$log;
 
    GROUP#  SEQUENCE# BYTES/(1024*1024)    MEMBERS ARC STATUS
---------- ---------- ----------------- ---------- --- ----------------
         1         12                50          1 YES INACTIVE
         2         11                50          1 YES INACTIVE
         3         16                50          1 NO  CURRENT
         4         13               128          2 YES INACTIVE
         5          0               128          2 YES UNUSED
         6         15               128          2 YES INACTIVE
 
6 rows selected.
 
SQL>
SQL> select group#,status,member from v$logfile;
 
    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1         /u01/app/oracle/oradata/xiocpt0/redo01.log
         3         /u01/app/oracle/oradata/xiocpt0/redo03.log
         2         /u01/app/oracle/oradata/xiocpt0/redo02.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_01.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_02.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_01.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_01.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_02.log
 
9 rows selected.
 
SQL> !rm -rf /u01/app/oracle/oradata/xiocpt0/redo03.log
 
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/redo03.log
ls: cannot access /u01/app/oracle/oradata/xiocpt0/redo03.log: No such file or directory
 
SQL> alter system switch logfile;
 
System altered.
 
SQL>  alter system  checkpoint;
 
System altered.
 
SQL> alter database clear unarchived logfile group 3;
 
Database altered.
 
SQL>  select group#,status,member from v$logfile;
 
    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1         /u01/app/oracle/oradata/xiocpt0/redo01.log
         3         /u01/app/oracle/oradata/xiocpt0/redo03.log
         2         /u01/app/oracle/oradata/xiocpt0/redo02.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_01.log
         4         /u01/app/oracle/oradata/xiocpt0/p_redo04_02.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_01.log
         5         /u01/app/oracle/oradata/xiocpt0/p_redo05_02.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_01.log
         6         /u01/app/oracle/oradata/xiocpt0/p_redo06_02.log
 
9 rows selected.
 
SQL> !ls -l /u01/app/oracle/oradata/xiocpt0/redo03.log
-rw-r----- 1 oracle dba 52429312 May 22 13:52 /u01/app/oracle/oradata/xiocpt0/redo03.log