丟失已歸檔日誌檔案下恢復資料庫

丟失已經歸檔了的日誌檔案恢復資料庫時候，還算是相對簡單的事情，只要把丟失
了的歸檔日誌檔案清空就可以了。然後直接開啟資料庫。以下是測試的過程：

----丟失已歸檔重做日誌檔案：

--檢視測試表的原來的記錄：

suxing@PROD>select * from t2;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK            800         20

      7566 JONES      MANAGER         2975         20

      7788 SCOTT      ANALYST         3000         20

      7876 ADAMS      CLERK           1100         20

      7902 FORD       ANALYST         3000         20

      7777 SUSU       CLERK           3000         20

--往測試表中再插入6條記錄：

suxing@PROD>insert into t2              

  2  select * from t1 where deptno = 30;

6 rows created.

suxing@PROD>commit;

Commit complete.

 

--再次檢視測試表的記錄：

suxing@PROD>select * from t2;

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7369 SMITH      CLERK            800         20

      7566 JONES      MANAGER         2975         20

      7788 SCOTT      ANALYST         3000         20

      7876 ADAMS      CLERK           1100         20

      7902 FORD       ANALYST         3000         20

      7777 SUSU       CLERK           3000         20

      7499 ALLEN      SALESMAN        1600         30

      7521 WARD       SALESMAN        1250         30

      7654 MARTIN     SALESMAN        1250         30

      7698 BLAKE      MANAGER         2850         30

      7844 TURNER     SALESMAN        1500         30

     EMPNO ENAME      JOB              SAL     DEPTNO

---------- ---------- --------- ---------- ----------

      7900 JAMES      CLERK            950         30

12 rows selected.

#6條記錄插入成功。

 

--切換日誌：

sys@PROD>alter system switch logfile;

System altered.

 #重做日誌已經切換。

--已經切換日誌：

sys@PROD>select group#,archived,status from v$log;

    GROUP# ARC STATUS

---------- --- ----------------

         1 YES ACTIVE

         2 NO  CURRENT

         3 YES UNUSED

#日誌切換成功，第一組日誌正在歸檔。

 

--再次檢視日誌檔案：

sys@PROD>select group#,archived,status from v$log;

    GROUP# ARC STATUS

---------- --- ----------------

         1 YES INACTIVE

         2 NO  CURRENT

         3 YES UNUSED

#歸檔完成。

--系統層刪除已經歸檔的地第1組日誌檔案組：

!rm /u01/app/oracle/oradata/PROD/redo01.log

sys@PROD>!rm /u01/app/oracle/oradata/PROD/redo01.log

sys@PROD>!ls /u01/app/oracle/oradata/PROD/redo01.log

ls: /u01/app/oracle/oradata/PROD/redo01.log: No such file or directory

sys@PROD>!rm /u01/app/oracle/oradata/PROD/redo01b.log

sys@PROD>!ls /u01/app/oracle/oradata/PROD/redo01b.log

ls: /u01/app/oracle/oradata/PROD/redo01b.log: No such file or directory

 

--嘗試開啟資料庫：

sys@PROD>startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 8104

Session ID: 1 Serial number: 5

 

--強行關閉：

sys@PROD>shutdown abort;

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

sys@PROD>

--重新啟動資料庫到mount狀態： 

[oracle@enmo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 21 22:50:46 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

PROD>startup mount;

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

Database mounted.

 

--清空第1組日誌檔案：

PROD>alter database clear logfile group 1;

Database altered.

--嘗試開啟資料庫：

PROD>alter database open;

Database altered.

PROD>select group#,archived,status from v$log;

    GROUP# ARC STATUS

---------- --- ----------------

         1 YES UNUSED

         2 YES  INACTIVE

         3 NO  CURRENT

#資料庫已經開啟，並且已經清空日誌組1。

--當丟失已經歸檔了的日誌檔案時候嘗試開啟資料庫，如果不能開啟並報錯，
並報ORA-03113錯誤，此時強行關庫重新啟動到mount狀態，清空丟失了的日誌檔案組，
然後再啟動到open狀態，這樣不會丟失資料庫的資料，也不許以resetlogs方式開啟資料庫，
就可以輕鬆恢復資料庫。