linux 恢复member1账户,备份恢复之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

CORE10.2.0.1.0Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

一、在非归档模式

SQL> archive log list;

Database log modeNo Archive Mode

Automatic archivalDisabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence36

Current log sequence38

查看redo日志组数据成员：

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

GROUP#THREAD#MEMBERS STATUS

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

111INACTIVE

211ACTIVE

311CURRENT

现在把每组添加一个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 group2;

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

查看：

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

GROUP#THREAD#MEMBERS STATUS

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

112 INACTIVE

212 ACTIVE

312 CURRENT

添加成功。

现在删除：redo01b.log

[root@localhost lzcdb]# rm redo01b.log

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

[root@localhost lzcdb]# ls

control01.ctllzcdb0101redo02b.logredo03.logtemp01.dbf

control02.ctllzcdb0102redo02.logsysaux01.dbfundotbs01.dbf

control03.ctlredo01.logredo03b.logsystem01.dbfusers01.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 Area285212672 bytes

Fixed Size1218992 bytes

Variable Size96470608 bytes

Database Buffers184549376 bytes

Redo Buffers2973696 bytes

Database mounted.

2.执行：

SQL> alter database archivelog;

Database altered.

查看归档状态：

SQL> archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence39

Next log sequence to archive42

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

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

112 INACTIVE

212 INACTIVE

312 CURRENT

411 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

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

112 CURRENT

212 INACTIVE

312 ACTIVE

412 ACTIVE

切换成功现在将中间日志组4删除

要先将redo日志组4置为inactive状态

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

GROUP#THREAD#MEMBERS STATUS

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

112 CURRENT

212 INACTIVE

312 INACTIVE

411 INACTIVE

然后将其移除：

SQL> alter database drop logfile group 4;

Database altered.

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

GROUP#THREAD#MEMBERS STATUS

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

112 CURRENT

212 INACTIVE

312 INACTIVE

总结：

一、在归档模式下，若丢失member

1、若丢失的是非当前使用的member

则直接将改组删除，重建即可

alter database droplogfile 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 groupN；