日志组状态的深入分析（转）-CSDN博客

最近遇到因为redo log而当掉的情况，转来篇前辈的详细记录，留作参考。

日志组的3钟状态：

CURRENT：表示LGWR正在写日志文件；

ACTIVE：表示LGWR正在写的文件，实例恢复时需要这个文件；

INACTIVE:表示LGWR正在写的文件，实例恢复时不需要这个文件。

非归档日志下的研究

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

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

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 NO CURRENT
         2          1          1 NO ACTIVE
         3          1          1 NO INACTIVE

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

SQL> alter database clear unarchived logfile group 2;

为什么需要加“UNARCHIVED”的参数呢？

如果初始化没有归档，那么就需要增加这个参数。所以对ACTIVE的日志组，需要使用“alter database clear unarchive logfile group 2";

如果初始化已经归档，比如日志组在INACTIVE状态时，需要使用"alter database clear logfile group 2"就可以。

日志组由"ACTIVE”转化成“INACTIVE”有2种方法：

1、alter system switch logfile;这样需要切换很多次；

2、alter system checkpiont ;强制日志写入归档；

比如：

SQL> alter system checkpoint;

System altered.

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

    GROUP#    THREAD#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          1          1 NO INACTIVE
         2          1          1 NO CURRENT
         3          1          1 NO INACTIVE
两个慎用的参数：ARCHIVE_LAG_TARGET和unrecoverble datafile

ARCHIVE_LAG_TARGET控制日志切换的时间间隔，默认60S到7200S，可以设置切换时间间隔来提高日志切换频率；如果ARCHIVE_LAG_TARGET设置为0的时候，表示不基于日志切换机制。

使用alter database clear logfile group 2 unrecoverable datafile;

这个时候，如果有OFFLINE的表空间，那么这个表空间就不能重新再线。

创建表空间：

SQL> create tablespace test
2 datafile '/oracle/oradata/dg1/test.dbf' size 50M
3 extent management local uniform. size 128K
4 segment space management auto;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> conn test/test;
Connected.

SQL> create table test (a int);

Table created.

SQL> insert into test values (10);

1 row created.

SQL> select * from test;

A
----------
10

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter tablespace test offline;

Tablespace altered.

onnected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/oracle/oradata/dg1/test.dbf'

SQL> conn sys/oracle as sysdba
Connected.
SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 OFFLINE

6 rows selected.

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

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          1 CURRENT
         2          1          1 UNUSED
         3          1          1 INACTIVE

SQL> alter database clear logfile group 3 unrecoverable datafile;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          1          1 ACTIVE
         2          1          1 ACTIVE
         3          1          1 CURRENT

SQL> alter system checkpoint;

System altered.

SQL> alter tablespace test online;

Tablespace altered.

SQL> conn test/test
Connected.
SQL> select * from test;

A
----------
10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25658563/viewspace-709776/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25658563/viewspace-709776/