【備份恢復】 恢復重做日誌組成員

 恢復重做日誌組成員
1) 檢視當前 redo 日誌組情況，從結果看是有 3 個日誌組，每個日誌組有 1 個成員， 1 號日誌組為當前正在使用的日誌組

SQL> conn / as sysdba

Connected.

SQL>

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

 

    GROUP#    MEMBERS STATUS

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

         1          1 CURRENT

         2          1 UNUSED

         3          1 UNUSED

 

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

 

    GROUP# MEMBER

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

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

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

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

 

前期準備：增加日誌組成員

SQL> alter database add logfile member '/u01/app/oracle/oradata/ORA11GR2/redo01_a.log' to group 1;

 

Database altered.

 

SQL> alter database add logfile member '/u01/app/oracle/oradata/ORA11GR2/redo02_a.log' to group 2;

 

Database altered.

 

SQL> alter database add logfile member '/u01/app/oracle/oradata/ORA11GR2/redo03_a.log' to group 3;

 

Database altered.

驗證：

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

 

    GROUP#    MEMBERS STATUS

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

         1          2 CURRENT

         2          2 UNUSED

         3          2 UNUSED

 

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

 

    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.log

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

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

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

 

6 rows selected.

2) 刪除當前正在使用的第一組 redo 的其中一個成員

[oracle@wang backup]$ cd $ORACLE_BASE

[oracle@wang oracle]$ cd 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 redo01_a.log

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls redo*

redo01.log  redo02_a.log  redo02.log  redo03_a.log  redo03.log

[oracle@wang ORA11GR2]$

 

1)  此時資料庫在使用上是沒有問題的，我們一致性關閉資料庫，並重啟，看看有沒有異常

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.

Database opened.

 

2)  資料庫重啟的步驟沒有異常（也就是說，丟失一個 redo 成員並未影響使用），只是在 alert 日誌中有如下錯誤

[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 07:58:23 2016

ARC0 started with pid=20, OS id=7827

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_7736.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_7736.trc:

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

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

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

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

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

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/ORA11GR2/redo01.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Fri Sep 23 07:58:24 2016

ARC1 started with pid=21, OS id=7839

Fri Sep 23 07:58:24 2016

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

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo01_a.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_m000_7843.trc:

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

Fri Sep 23 07:58:24 2016

ARC2 started with pid=23, OS id=7847

Checker run found 1 new persistent data failures

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Fri Sep 23 07:58:24 2016

ARC3 started with pid=22, OS id=7855

[7823] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:125398924 end:125399034 diff:110 (1 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Fri Sep 23 07:58:25 2016

QMNC started with pid=24, OS id=7859

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Completed: ALTER DATABASE OPEN

Starting background process CJQ0

Fri Sep 23 07:58:26 2016

 

5）修復這個問題也是很簡單的，因為 redo 的成員也是互為映象的，我們首先一致性關閉資料庫

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

3)  複製第一組 redo 成員

[oracle@wang ORA11GR2]$ ls redo*

redo01.log  redo02_a.log  redo02.log  redo03_a.log  redo03.log

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ cp redo01.log redo01_a.log

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls redo*

redo01_a.log  redo02_a.log  redo03_a.log

redo01.log    redo02.log    redo03.log

[oracle@wang ORA11GR2]$

 

4)  啟動資料庫，恢復完成

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.

Database opened.

檢視alert日誌也沒有報錯資訊了！！！！！！！！！！