10G当前日志文件删除并不会导致数据库立马DOWN掉

10G当前日志文件删除并不会导致数据库立马DOWN掉

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------------------
NOARCHIVELOG

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

    GROUP# STATUS
---------- --------------------------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 CURRENT

4 rows selected.

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

    GROUP# MEMBER
---------- ----------------------------------------
         4 /oradata/dbtest/redo04.rdo
         3 /oradata/dbtest/redo03.rdo
         2 /oradata/dbtest/redo02.rdo
         1 /oradata/dbtest/redo01.rdo

4 rows selected.

SQL> ! rm /oradata/dbtest/redo04.rdo
SQL> ! ls -l /oradata/dbtest/redo04.rdo
ls: 0653-341 The file /oradata/dbtest/redo04.rdo does not exist.

SQL> ! ls -l /oradata/dbtest/redo0*
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:56 /oradata/dbtest/redo01.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:57 /oradata/dbtest/redo02.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:57 /oradata/dbtest/redo03.rdo

SQL> insert into t select * from t;

211664 rows created.

SQL> insert into t select * from t;

423328 rows created.

SQL> commit;

Commit complete.

日志文件中并没有发现错误（数据库处于非归档模式）。

Wed Feb 23 10:57:33 2011
Thread 1 advanced to log sequence 251 (LGWR switch)
  Current log# 3 seq# 251 mem# 0: /oradata/dbtest/redo03.rdo
Wed Feb 23 10:57:34 2011
Thread 1 advanced to log sequence 252 (LGWR switch)
  Current log# 4 seq# 252 mem# 0: /oradata/dbtest/redo04.rdo
Wed Feb 23 10:59:28 2011
Thread 1 advanced to log sequence 253 (LGWR switch)
  Current log# 1 seq# 253 mem# 0: /oradata/dbtest/redo01.rdo
Wed Feb 23 10:59:33 2011
Thread 1 advanced to log sequence 254 (LGWR switch)
  Current log# 2 seq# 254 mem# 0: /oradata/dbtest/redo02.rdo
Wed Feb 23 10:59:41 2011
Thread 1 advanced to log sequence 255 (LGWR switch)
  Current log# 3 seq# 255 mem# 0: /oradata/dbtest/redo03.rdo
Wed Feb 23 10:59:46 2011
Thread 1 advanced to log sequence 256 (LGWR switch)
  Current log# 4 seq# 256 mem# 0: /oradata/dbtest/redo04.rdo
Wed Feb 23 10:59:51 2011
Thread 1 advanced to log sequence 257 (LGWR switch)
  Current log# 1 seq# 257 mem# 0: /oradata/dbtest/redo01.rdo

可以看到日志文件能正常切换到日志组1。

SQL> ! ls -l /oradata/dbtest/redo0*
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 11:00 /oradata/dbtest/redo01.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:59 /oradata/dbtest/redo02.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:59 /oradata/dbtest/redo03.rdo

SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance dbtest (thread 1)
ORA-00312: online log 4 thread 1: '/oradata/dbtest/redo04.rdo'

SQL> alter database clear  unarchive logfile group 4;
alter database clear  unarchive logfile group 4
                      *
ERROR at line 1:
ORA-01900: LOGFILE keyword expected

SQL> alter database clear  unarchived logfile group 4;

Database altered.

SQL> ! ls -l /oradata/dbtest/redo0*
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 11:05 /oradata/dbtest/redo01.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:59 /oradata/dbtest/redo02.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 10:59 /oradata/dbtest/redo03.rdo
-rw-r-----    1 oracle   oinstall  104858112 Feb 23 11:05 /oradata/dbtest/redo04.rdo

 

 

 

如果数据库处于归档模式，操作也能继续，但是由于日志文件不存在，导致归档错误。

SQL> select log_mode from v$database;

LOG_MODE
------------------------
ARCHIVELOG

1 row selected.

SQL> select * from v$logfile;

    GROUP# STATUS         TYPE           MEMBER                                   IS_REC
---------- -------------- -------------- ---------------------------------------- ------
         4                ONLINE         /oradata/dbtest/redo04.rdo        NO
         3                ONLINE         /oradata/dbtest/redo03.rdo        NO
         2                ONLINE         /oradata/dbtest/redo02.rdo        NO
         1                ONLINE         /oradata/dbtest/redo01.rdo        NO

4 rows selected.

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

    GROUP#  SEQUENCE# STATUS
---------- ---------- --------------------------------
         1          1 INACTIVE
         2          2 CURRENT
         3          0 UNUSED
         4          0 UNUSED

4 rows selected.

SQL> ! rm -rf /oradata/dbtest/redo02.rdo 

SQL> delete  from ysp.t where rownum<=100000;

100000 rows deleted.

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

    GROUP#  SEQUENCE# STATUS
---------- ---------- --------------------------------
         1          1 INACTIVE
         2          2 CURRENT
         3          0 UNUSED
         4          0 UNUSED

4 rows selected.

SQL> commit;

Commit complete.

SQL> delete  from ysp.t where rownum<=100000;

100000 rows deleted.

SQL> /

100000 rows deleted.

SQL> commit;

Commit complete.

日志文件中将会出现不能归档的错误：

Wed Feb 23 12:16:12 2011
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /oradata/dbtest/redo03.rdo
Wed Feb 23 12:16:12 2011
Errors in file /u01/oracle/admin/dbtest/bdump/dbtest_arc0_20603108.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/dbtest/redo02.rdo'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Wed Feb 23 12:16:12 2011
Errors in file /u01/oracle/admin/dbtest/bdump/dbtest_arc0_20603108.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/dbtest/redo02.rdo'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

即使重建日志组2也不能解决问题，因为日志组2必须得归档成功。

SQL> alter database clear  unarchived logfile group 2;

Database altered.

alter database clear  unarchived logfile group 2
Wed Feb 23 12:20:40 2011
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE 02/23/2011 12:16:12 (CHANGE 3873779536) CANNOT BE USED FOR RECOVERY.
Clearing online log 2 of thread 1 sequence number 2。

数据库日志中将会一直报如下错误：

Wed Feb 23 12:21:38 2011
Errors in file /u01/oracle/admin/dbtest/bdump/dbtest_arc0_20603108.trc:
ORA-00314: log 2 of thread 1, expected sequence# 4 doesn't match 0
ORA-00312: online log 2 thread 1: '/oradata/dbtest/redo02.rdo'

删除日志组也不行：

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance dbtest (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/oradata/dbtest/redo02.rdo'

此时需要将数据库切换到非归档模式进行重建日志组2才行。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                  2122472 bytes
Variable Size            2.1072E+10 bytes
Database Buffers          385875968 bytes
Redo Buffers               14651392 bytes
Database mounted.
SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-00314: log 2 of thread 1, expected sequence#  doesn't match
ORA-00312: online log 2 thread 1: '/oradata/dbtest/redo02.rdo'

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE  OPEN;

Database altered.

SQL>