備份恢復之redo日誌組member成員丟失

執行環境:

[root@localhost ~]# lsb_release -a

LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch

Distributor ID: RedHatEnterpriseServer

Description: Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Release: 5.4

Codename: Tikanga

oracle版本：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

一、在非歸檔模式

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 36

Current log sequence 38

檢視redo日誌組資料成員：

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 1 INACTIVE

2 1 1 ACTIVE

3 1 1 CURRENT

現在把每組新增一個member：

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

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

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

檢視：

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 2 INACTIVE

2 1 2 ACTIVE

3 1 2 CURRENT

新增成功。

現在刪除：redo01b.log

[root@localhost lzcdb]# rm redo01b.log

rm: remove regular file `redo01b.log'? y

[root@localhost lzcdb]# ls

control01.ctl lzcdb0101 redo02b.log redo03.log temp01.dbf

control02.ctl lzcdb0102 redo02.log sysaux01.dbf undotbs01.dbf

control03.ctl redo01.log redo03b.log system01.dbf users01.dbf

已經刪除，現在切換redo日誌組，切換到第一組

SQL> alter system switch logfile;

System altered.

檢視此時警告日誌檔案的內容：提示如下錯誤

Errors in file /u01/app/oracle/admin/lzcdb/bdump/lzcdb_lgwr_13747.trc:

ORA-00321: log 1 of thread 1, cannot update log file header

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/lzcdb/redo01b.log'

Mon Jun 11 20:36:49 2012

解決方案：

重建日誌組：

SQL> alter database clear unarchived logfile group 1;

Database altered.

切換日誌組：

SQL> alter system switch logfile;

System altered.

此時再次檢視警告日誌：

Completed: alter database clear unarchived logfile group 1

Mon Jun 11 20:45:06 2012

Thread 1 advanced to log sequence 42

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

Current log# 1 seq# 42 mem# 1: /u01/app/oracle/oradata/lzcdb/redo01b.log

檢視redo01b.log是否存在：

[root@localhost lzcdb]# ls redo01b.log

redo01b.log

問題解決!

二、在歸檔模式下

更改歸檔狀態：

1.先將資料庫起到mount狀態,在關閉庫時一定要使用一致性關閉

本次使用：shutdown immediate

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

2.執行：

SQL> alter database archivelog;

Database altered.

檢視歸檔狀態：

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 39

Next log sequence to archive 42

Current log sequence

現在進行日誌組切換測試：

SQL> alter system switch logfile;

System altered.

現在再次在刪除redo01b.log這個member

[root@localhost lzcdb]# rm redo01b.log

rm: remove regular file `redo01b.log'? y

[root@localhost lzcdb]# ls redo01b.log

ls: redo01b.log: No such file or directory

進行日誌組切換：

SQL> alter system switch logfile;

檢視警告日誌：

Errors in file /u01/app/oracle/admin/lzcdb/bdump/lzcdb_arc1_18090.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/lzcdb/redo01b.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

出現如上錯誤。

解決問題：

先建立一個新的redo日誌組，併為其新增一個資料成員

SQL> alter database add logfile group 4;

Database altered.

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 2 INACTIVE

2 1 2 INACTIVE

3 1 2 CURRENT

4 1 1 UNUSED

SQL> alter database add logfile member '/u01/app/oracle/oradata/lzcdb/redo04.log' to group 4;

Database altered.

現在將redo日誌組1 刪除：

alter database add logfile group 1;

Database altered.

在本地上將其日誌組member也刪除

然後重新建立日誌組1，併為其新增member

SQL> alter database add logfile group 1;

Database altered.

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

Database altered.

SQL> alter system switch logfile;

System altered.

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 2 CURRENT

2 1 2 INACTIVE

3 1 2 ACTIVE

4 1 2 ACTIVE

切換成功現在將中間日誌組4刪除

要先將redo日誌組4置為inactive狀態

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 2 CURRENT

2 1 2 INACTIVE

3 1 2 INACTIVE

4 1 1 INACTIVE

然後將其移除：

SQL> alter database drop logfile group 4;

Database altered.

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

GROUP# THREAD# MEMBERS STATUS

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

1 1 2 CURRENT

2 1 2 INACTIVE

3 1 2 INACTIVE

總結：

一、在歸檔模式下，若丟失member

1、若丟失的是非當前使用的member

則直接將改組刪除，重建即可

alter database drop logfile group N

刪除時若當前只有三個日誌組則需要建立一箇中間日誌組。

2、若丟失的為當前使用的member

則需要先進行恢復

1、startup mount

2、recover database until cancel; (cancel immediately)

3、alter database open resetlogs;

二、在非歸檔模式下

直接重建日誌組即可

SQL> alter database clear unarchived logfile group N；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26723566/viewspace-739848/，如需轉載，請註明出處，否則將追究法律責任。

備份恢復之redo日誌組member成員丟失

相關文章