Oracle 日志状态为stale解决方法-mysql教程-PHP中文网

首页

数据库

mysql教程

Oracle 日志状态为stale解决方法

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:55 PM

oracle数字

解决方法总结：1，千万不要在发生stale的情况下强制关闭数据库。2，多查询几次日志状态，看stale状态是否会消失。

SQLDBA> SELECT * FROM V$LOGFILE;

    GROUP#       STATUS        MEMBER
    ---------- ------- ------------------------------
             1          STALE           /Oracle/7.3.4/dbs/log1P734.dbf

             2                              /oracle/7.3.4/dbs/log2P734.dbf

             3                              /oracle/7.3.4/dbs/log3P734.dbf

解决方法总结：

1，，千万不要在发生stale的情况下强制关闭数据库。

2，多查询几次日志状态，看stale状态是否会消失。

3，如果再数次查询无效后，可以切换日志。

4，切换日志过户，可以执行强制检查点操作。

5，不断切换日志，强制检查点，查询，直至stale状态消失。

WARNING:

DO NOT ISSUE A SHUTDOWN ABORT BEFORE GOING THROUGH STEPS 1 AND 2 BELOW.

Solution Description:
=====================

In general, the stale status of a redo log member should not be a cause for
great concern, unless you observe that this happens frequently or
systematically. Keep in mind that a stale log is not necessarily an invalid
log, but more of an "in-doubt" one. Once the corresponding redo group becomes
the current one again, the stale status will go away by itself.

Here is the recommended procedure to deal with a stale redo log:

1. Issue the following query:

        SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
                V1.STATUS GROUP_STATUS
        FROM V$LOG V1, V$LOGFILE V2
        WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';

        This will show you the group status for all stale log files.

2. For each stale log file,

        2.a) If the GROUP_STATUS is 'INACTIVE', do nothing. That implies
             Oracle has already checkpointed past that redo group, and thus
             its contents are no longer needed for instance recovery.
             Once the redo group becomes current again, the stale status of
             the log file will go away by itself.

        2.b) If the GROUP_STATUS is 'ACTIVE', go back to Step 1 and repeat
             the query a few more times. This status usually means that
             the log group is no longer the current one, but the corresponding
             checkpoint has not completed yet. Unless there is a problem,
             the log group should become inactive shortly.

        2.c) If the GROUP_STATUS is 'CURRENT', force a log switch now.

                ALTER SYSTEM SWITCH LOGFILE;

             This will also force a checkpoint. If the checkpoint
             completes successfully, the contents of the redo group
             are no longer needed for instance recovery. Go back to
             Step 1 and repeat the query a few more times until the
             log group becomes inactive.

        IMPORTANT: If the stale logfile belongs to an active group
        or the current group (cases 2.b and 2.c above), DO NOT ISSUE
        A SHUTDOWN ABORT UNTIL THE GROUP BECOMES INACTIVE.

3. Investigate the extent of the problem.

        Examine the alert.log file for this instance and the LGWR
        trace file, if one can be found in your background_dump_dest.
        See if there is any pattern to the problem. Do you see any
        recent errors, such as ORA-312, referencing that particular log
        file? If so, there may be some corruption problem with the file
        or a problem with the I/O subsystem (disk, controllers, etc.)    .
        If you are running any other Oracle version on any other platform
        If there is no pattern to the problem, it is more likely an
        isolated incident.

4. If you are archiving, make sure the log has been correctly archived.

        Before archiving a redo log group, the ARCH process actually
        verifies that its contents are valid. If that is not the case,
        it issues an error such as ORA-255 ("error archiving log %s
        of thread %s, sequence # %s"。Therefore, if the log group to
        which the stale member belongs has been successfully archived,
        it means the redo contents of the group are good, and that
        archived log can be safely used for recovery. ARCH errors, if
        any, will be reported in your alert.log file and in an ARCH
        trace file.

说明：
============

过时的重做日志文件是 Oracle 认为由于某种
原因可能不完整的文件。当临时错误阻止 LGWR
后台进程写入重做日志组成员时，通常会发生这种情况。如果 Oracle 在任何时候都无法
写入重做日志文件，则该日志文件将不再
受信任，并且 Oracle 将其标记为“STALE”。这表示日志文件
不能依赖提供写入日志的所有数据。