【備份恢復】恢復 丟失已歸檔重做日誌檔案

丟失已歸檔重做日誌檔案

1) 檢視當前日誌組狀態，第2組日誌為當前日誌組，另外兩組為非活動狀態且已歸檔，還有，每組日誌均為 2個成員

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES INACTIVE

         2          2 NO  CURRENT

         3          2 YES UNUSED

 

SQL>  select group#,member from v$logfile order by 1;

 

    GROUP# MEMBER

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

         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

         1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

 

6 rows selected.

 

2) 刪除第3組日誌檔案

[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls redo*

redo01_a.log  redo02_a.log  redo03_a.log

redo01.log    redo02.log    redo03.log

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ rm redo03_a.log

[oracle@wang ORA11GR2]$ rm redo03.log

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls redo*

redo01_a.log  redo01.log  redo02_a.log  redo02.log

[oracle@wang ORA11GR2]$

 

3) 重啟資料庫

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

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

Process ID: 29933

Session ID: 1 Serial number: 9

 

4) 此時我們發現 alert 日誌有如下錯誤：

[oracle@wang ORA11GR2]$ cd trace/

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$ tail -100f alert_ORA11GR2.log
============================================================================

ALTER DATABASE OPEN

LGWR: STARTING ARCH PROCESSES

Fri Sep 23 13:29:02 2016

ARC0 started with pid=20, OS id=29945

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.trc:

ORA-00313: open failed for members of log group 1 of thread 1

Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.trc:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.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/ora11gr2/ORA11GR2/trace/ORA11GR2_lgwr_29821.trc:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.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/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_29933.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.log'

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03_a.log'

Fri Sep 23 13:29:03 2016

ARC1 started with pid=21, OS id=29975

USER (ospid: 29933): terminating the instance due to error 313

System state dump requested by (instance=1, osid=29933), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_diag_29801_20160923132903.trc

Dumping diagnostic data in directory=[cdmp_20160923132903], requested by (instance=1, osid=29933), summary=[abnormal instance termination].
Instance terminated by USER, pid = 29933
============================================================================================

 

5) 啟動資料庫到 mount 狀態

SQL> conn / as sysdba

Connected to an idle instance.

SQL>

SQL> startup mount; 

 (此步驟不需要資料檔案，只需要控制檔案就可以)

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

 

6) clear 修復日誌檔案

（clear的動作就是在作業系統redo日誌對應路徑下建立日誌檔案，只是建立的檔案是新的，且是未使用過的）

SQL> alter database clear logfile group 3;

Database altered.

 

——驗證：

[oracle@wang ORA11GR2]$ ls redo*

redo01_a.log  redo02_a.log  redo03_a.log

redo01.log    redo02.log    redo03.log

[oracle@wang ORA11GR2]$

 

7） 開啟資料庫

SQL> alter database open;

 

Database altered.

 

SQL> select status from v$instance;

 

STATUS

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

OPEN

 

——再次驗證：

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

 

    GROUP#    MEMBERS ARC STATUS

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

         1          2 YES INACTIVE

         2          2 NO  CURRENT

         3          2 YES UNUSED

 

SQL> select group#,member from v$logfile;

 

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

         1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log

 

6 rows selected.

完成！！！！！！！！！！！！！！！！！！！