Loading... 9.获取日志组和日志成员的信息
V$LOG
V$LOGFILE
这两个视图都存在STATUS这个列，下面主要讲一下这两个列存在的一些状态以及这些状态所表示的含义。
(1) V$LOG status 列
*UNUSED
日志组从来没有被使用过，一般会出现在，loggroup刚刚被添加，或者刚刚使用了resetlogs打开数据库，或者使用clear logfile后。
*CURRENT
当前正在被使用的日志组，这也意味着这个日志组是active状态。
*ACTIVE
日志组处于active，crash recovery需要用到，还没有完成checkpoint。可能在block recovery中需要用到。
日志组可能已经被归档也可能没有。
*CLEARING
日志组正在使用ALTER DATABASE CLEAR LOGFILE被重建。当日志被清除后状态变为UNUSED
*CLEARING_CURRENT
正在清除当前日志文件中的已关闭线程，如果切换时发生某些故障，如写入新日志标题时的I/O错误，则该日志可以停留在该状态
indicates that the current log file is being cleared of a closed thread. The log can stay in this status if there is
some failure in the switch, such as an I/O error writing the new log header.
*INACTIVE
日志组在实例恢复中不再需要，日志组可能已经被归档也可能没有
(2) V$LOGFILE status 列
*INVALID
日志文件不可访问,可能损坏了(另外，刚加入到redolog group的日志文件在被使用之前也是这个状态)
*STALE
日志文件内容不完整，具体见附文-METALINK Note:1014824.6
*DELETED
日志文件不再被使用,使用ALTER DATABASE DROP LOGFILE MEMBER删除的日志文件
*Blank
日志文件处于被使用状态
10.归档日志文件
*为什么要使用归档日志？主要有以下几点：
(1)使用归档日志结合在线重做日志可以保证恢复所有递交的事务。
(2)可以在数据库打开的状态下对数据库做备份，也就是说这样的备份不会影响数据库的使用。
*数据库归档模式和非归档模式的区别
(1)非归档模式(NOARCHIVELOG)
在非归档模式下，当所有日志组被写满后，日志文件内容会别覆盖，而这些日志内容将会丢失。LGWR不会覆盖一个
没有完成检查点的日志文件组。
(2)归档模式
处于inactive状态被写满的日志文件必须被归档。
Metalink附文：
Doc ID: Note:1014824.6
Problem Description:
====================
Querying V$LOGFILE, you notice that a redo log file has a status of “STALE”.
For example:
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
You may also see an ORA-00346 in your alert.log file or in a LGWR trace file:
ORA-00346: log member marked as STALE
Cause: A log file member no longer is complete.
Action: Correct the underlying problem by referring to the other error
messages found with this one.
In addition, you might see error messages like:
ORA-00345: redo log write error block %s count %s
Cause: An IO error has occurred while writing the log
Action: Correct the cause of the error, and then restart the system.
If the log is lost, apply media/incomplete recovery.
ORA-00312: online log %s thread %s: ‘%s’
Cause: Reporting file name for details of another error
Action: See associated error messages
and an operating system level Oracle message such as ORA-07376:
ORA-07376: sfwfb: write error, unable to write database block.
Cause: Additional information returns block number.
Action: Check errno. Check block number.
Log files may have a status of stale regardless of redo log mirroring or archival mode.
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.
Mirroring your redo logs reduces the risks associated with a stale log file.
WARNING: DO NOT ISSUE A SHUTDOWN ABORT BEFORE GOING THROUGH STEPS 1 AND 2 BELOW.
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.
Explanation:
============
A stale redo log file is one that Oracle believes might be incomplete for some
reason. This typically happens when a temporary error prevents the LGWR
background process from writing to a redo log group member. If Oracle cannot
write to a redo log file at any one time, that log file can no longer be
trusted, and Oracle marks it as “STALE”. This indicates that the log file
cannot be relied upon to provide all the data written to the log.
Popularity: 57% [?]