Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2520688
  • 博文数量: 323
  • 博客积分: 10211
  • 博客等级: 上将
  • 技术积分: 4934
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-27 14:56
文章分类

全部博文(323)

文章存档

2012年(5)

2011年(3)

2010年(6)

2009年(140)

2008年(169)

分类: Oracle

2009-10-31 01:16:41

突然提起oracle的这两个参数是源自我今天翻ITPUB 01年的一个老帖子。帖子如下:
 
紧急求助,ORACLE崩溃,unix重启,ORACLE还是启不了
ORACLE崩溃时检查文件系统发现安装ORACLE的文件系统的空间已满.
清除CORE文件后文件系统有约7%的空间,重新启动数据库报的出借为
ORA-03113: end-of-file on communication channel
重启UNIX系统, 启动ORACLE时报的出错也为
ORA-03113: end-of-file on communication channel
以下是oracle support给的一个解决方案:
Problem Description:
====================
You are attempting to start your database and receive the following
error:
ORA-03113: end-of-file on communication channel
     Cause: An unexpected end-of-file was processed on the communication
            channel. The problem could not be handled by the SQL*Net,
two task,
            software. This message could occur if the shadow two-task
process
            associated with a SQL*Net connect has terminated abnormally,
or if
            there is a physical failure of the interprocess
communication
            vehicle, that is, the network or server machine went down.
    Action: If this message occurs during a connection attempt, check
the setup
            files for the appropriate SQL*net driver and confirm SQL*Net
            software is correctly installed on the server. If the
message
            occurs after a connection is well established, and the error
is not
            due to a physical failure, check if a trace file was
generated on
            the server at failure time. Existence of a trace file may
suggest
            an Oracle internal error that requires the assistance of
customer
            support.
The database is mounting, but fails to open with ORA-3113 "end-of-file
on
communication channel".
The following errors are reported in the alert log:
     Rolling back half complete log switch of thread 1.
     Instance terminating with error 204.
     Instance terminated by LGWR.
If you execute select * from "v$log_history" with the database mounted
you
recieve these errors:
ORA-00204: error in reading (block 4244119, # blocks 1) of controlfile
     Cause: A disk read-failure occurred while attempting to read the
specified
            control file.
            The block location of the failure is given.
    Action: Check that the disk is online.
            If it is not, bring it online and shut down and restart
Oracle.
            If the disk is online, then look for operating system
reasons for
            Oracle's inability to read the disk or control file.
            Refer to the for
information
            about recovering from the loss of a control file.
            See also your operating system-specific Oracle
documentation.
ORA-00202: controlfile: '/oradata/SID/control01.ctl'
     Cause: This message reports the name of the file involved in other
            messages.
    Action: See the associated messages for a description of the
problem.
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
     Cause: internal error, the range of blocks being read or written is
            outside the range of the file, additional information
indicates the
            starting block number, number of blocks in I/O, and the last
valid
            block in the file
    Action: check for trace file and contact Oracle Support

Solution Description:
=====================
1. Edit the "init.ora" file and add the parameter
   CONTROL_FILE_RECORD_KEEP_TIME = 0
2. Mount instance: startup mount
3. Execute alter database backup controlfile to trace
4. Edit the generated trace and make necessary changes
5. Recreate control file
6. Startup the database
or
Workaround:
Change the value of MAXLOGHISTORY to something smaller than UB2MAXVAL
(which is usually 65,535), and repeat the operation.

Explanation:
============
The MAXLOGHISTORY section of the control file has been extended to its
maximum
size of 65535 entries, specified when the database was created, and
cannot be
extended further.  Also, none of the existing log history entries are
eligible
for reuse because the "init.ora" parameter
CONTROL_FILE_RECORD_KEEP_TIME > 0.
The default value for CONTROL_FILE_RECORD_KEEP_TIME = 7 (days), so you
will
receive these errors if you generate more than 65535 redo logs within a
7 day
period.
Per - CREATE DATABASE AND CREATE CONTROLFILE DON'T ENFORCE
LIMITS ON MAXLOGHISTORY, fixed in 8.1.6
-->> The max value for this parameter is 65535.
The SQL statements CREATE DATABASE and CREATE CONTROLFILE did not
properly
enforce limits on the MAXLOGHISTORY parameter.
It was possible to get a control file whose MAXLOGHISTORY section was
smaller
than the requested size; or even to get a corrupted control file that
would
cause LGWR and the instance to crash.
Recreating the control file will clean the LOG HISTORY section of the
control
file, and setting CONTROL_FILE_RECORD_KEEP_TIME = 0 in the
"init.ora" file
will prevent the error from happening again.   Log history entries will
be
reused as they are needed with no keep time restrictions.

理解这个解决方案需要一些背景知识,其中最重要的就是理解本篇文章标题的两个参数。

参考资料:

V$LOG_HISTORY
Contains log history information such as which
logs have been archived and the SCN range for
each archived log.

metalink对于control_file_record_keep_time的解释:
CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days
before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed

Note:
This parameter applies only to records in the control file that are circularly reusable (such as archive log records and various backup records). It does not apply to records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.


这段话的意思是说:control_file_record_keep_time表示控制文件里可重复使用的记录所能保存的最小天数。如果新增加一条记录到控制文件可以重复使用的部分,这时最老的记录尚没有超出最小保留天数,那么记录将控制文件的这一部分将扩展。如果将该参数设置为0,那么控制文件可以重复使用的部分将永远不会扩展。

注意,这个参数只应用于控制文件中可循环利用的部分,如归档日志文件,各种备份记录。不应用于诸如数据文件,表空间,重做线程等,这些内容只有当其从对应的表空间中删除后才能重用。

可重用的部分包括如下几种类型(可以通过查询视图$contofile_record_section得到):
ARCHIVED LOG
BACKUP CORRUPTION
BACKUP DATAFILE
BACKUP PIECE
BACKUP REDO LOG
BACKUP SET
COPY CORRUPTION
DATAFILE COPY
DELETED OBJECT
LOGHISTORY
OFFLINE RANGE

而MAXLOGHISTORY参数,其意义在于限制控制文件里和通过v$log_history视图的归档日志文件数目。归档日志最多维持在MAXLOGHISTORY所设置的数目。如果超过这个数目,将从头覆盖以前的归档日志文件项。

最终,当归档日志数目超过MAXLOGHISTORY所设定的值时,v$log_history中的所有项都将被覆盖。

当创建控制文件时,MAXLOGHISTORY的决定为归档日志信息分配多大的空间。MAXLOGHISTORY并不动态增加。当日志归档时,该归档文件的信息将被更新到控制文件。只有在如下情况下,才覆盖原来的项:
控制文件中该区域所有项都被使用并且有一项超出了control_file_record_keep_time设定的时间

当使用RMAN目录时,control_file_record_keep_time保存的时间不能比同步目录的时间间隔短。换句话说,如果每天备份一次,那么controlfile_record_keep_time不能少于1。

如果不使用RMAN目录,应该设置control_file_record_keep_time为你需要往后恢复的最大天数。

--我的理解:在建库的时候MAXLOGHISTORY指定了归档日志的数量,指定数量的归档日志信息记录在控制文件中(也决定了控制文件用来保留这些信息所需的空间)。控制文件中关于归档日志的这块可重用区域不会动态增加。所以当control_file_record_keep_time参数指定以后,比如7天。如果7天内归档的数量超过MAXLOGHISTORY的值则会产生矛盾。什么矛盾?控制文件中关于归档日志的这块可重用区域的最老记录还没有达到control_file_record_keep_time参数指定值,所以不能覆盖原有的项。我标记红色的地方也正好说明了这点。oracle 8.1.6版本在建库和建控制文件的时候已经不强制执行MAXLOGHISTORY的限制了。这个案例的oracle版本是8.0.5。

 


阅读(3275) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~