ORA-00321、ORA-00312错误模拟实验

2012年6月11日

20:07

运行环境:

[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> 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

现在把每组添加一个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-732505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26723566/viewspace-732505/