专栏首页杨建荣的学习笔记关于db_files和maxdatafiles的问题(r4笔记第31天)

关于db_files和maxdatafiles的问题(r4笔记第31天)

昨天在做生产监控的时候发现有个库的表空间不够了,就发邮件给客户的dba去处理,但是得到的反馈是尝试添加的时候发现已经超过了数据文件的最大数限制。这个错误毫无疑问就是"ORA-00059: Maximum Number Of db_files Exceeded" 一看到这个问题,一下子感觉就头大了。这个参数是在创建数据库的时候就应该考虑到的。竟然没有考虑周全,如果控制文件中的maxdatafiles没有值太小,只能重建控制文件了。如果本身数据文件的数量超过了最大限制,甚至还需要考虑重建数据库,如果碰到这种事就太郁闷了。 查看oracle的文档,赫然可以看到以下的参数是需要在创建数据库(尤其是RAC)中需要考虑的。

  • ACTIVE_INSTANCE_COUNT
  • ARCHIVE_LAG_TARGET
  • CLUSTER_DATABASE
  • CLUSTER_DATABASE_INSTANCES
  • CONTROL_FILES
  • DB_BLOCK_SIZE
  • DB_DOMAIN
  • DB_FILES
  • DB_NAME
  • DB_RECOVERY_FILE_DEST
  • DB_RECOVERY_FILE_DEST_SIZE
  • DB_UNIQUE_NAME
  • UNDO_MANAGEMENT 对于参数db_files的解释如下:

Property

Description

Parameter type

Integer

Default value

200

Modifiable

No

Range of values

Minimum: the largest among the absolute file numbers of the datafiles in the databaseMaximum: operating system-dependent

Basic

No

Real Application Clusters

Multiple instances must have the same value.

当前库是11gR2版本,默认值是200。 在其它版本中的情况关于maxdatafiles的情况如下:

 ---------------- V7      8.0.5    8.1.6    9.2      10.1     10.2
   —————————————————-------- -------- ------ -------- -------- -------- 
     UNIX    Per TS          1022     1022    1022     1022     1022
             Per DB 1022     65536    65536   65534    65334    65334 
     VMS     Per TS          1022       -       -        -      1022
             Per DB 1022     65536      -                       65334
     WINDOWS Per TS          1022     1022       
             Per DB 1022     65536    65536   65534    65334    65334 11g中对datafiles略有增长,为65533,表空间中最大数据文件数也是1022,根据操作系统可能还要略微低一些。回到两个参数db_files和maxdatafiles,db_files是一个"soft" limit,而maxdatafiles是一个“hard" limit.maxdatafiles是在create database语句中包含的,写入了controlfile,如果要修改maxdatafiles,则需要重建控制文件。db_files则是在实例级别的限制,这个值需要小于maxdatafiles,但是可以在参数文件中根据需要来做变更。具体可以参考MOS Doc ID 119507.1,里面给出了详细的解决步骤,就不再赘述了。SOLVING ORA-1118 and ORA-1165 :
------------------------------

The following are possible options to get around these errors :     
   
1.  You should verify that you haven't reached a hard limit for MAXDATAFILES 
   imposed by the Operating System. This limit can be found in the O/S specific
   documentation.
   
   If MAXDATAFILES is already equal to the maximum value, then you must 
   restructure the database by reducing the number of datafiles associated with 
   each individual tablespace:

     a) export the objects from the tablespace
     b) drop the tablespace
     c) recreate the tablespace with less datafiles
     d) import the objects back into the tablespace

    In some cases, if the datafile size is too small, it might be usefull to 
    resize the datafiles. Make sure that you keep the datafile size lower than 
    the Operating System limitation on file sizes :

     > ALTER DATABASE DATAFILE 'D:\Oradata\Orcl\datafile\usr01.dbf' RESIZE 500M;

    Query DBA_SEGMENTS to find out all objects belonging to a particular  
    tablespace.

2.  Increase the MAXDATAFILES parameter.  It is possible for you to increase 
    the MAXDATAFILES limit without recreating the entire database.  This is done
    by recreating the control file only.     
   
3. Especially for Oracle8+ you should make sure that you do not encounter an 
   error against the maximum number of open database files (DB_FILES). It is 
   more likely that the value for DB_FILES is too low since the controlfile in 
   Oracle8 expands automatically as long as the number of the added datafile is
   lower then the value for DB_FILES. Normally the error message should 
   indicate this:

     ORA-00059 : maximum number of DB_FILES exceeded

4.  Recreate the database.  Since the MAXDATAFILES parameter is specified    
    upon database creation, recreating the database allows you to increase   
    this parameter.  
   
    In addition to your operating system documentation, you may want to    
    reference the following bulletins:   
 
    O7 - Creating an Oracle 7.X Database under Unix - Note:10280.1 RECREATING THE CONTROL FILE:   
----------------------------  
 
In Oracle7 or higher, you can create the control file.  In addition, you can get
Oracle to create the script for you.   To do this, perform the following steps:   
   
1.  With the database mounted or open, issue the following commands:    
    
    SQL> alter database backup controlfile to trace;    
    SQL> exit    
   
2.  A trace file will have been generated in your 'user_dump_dest'.   
    User_dump_dest is an init.ora parameter, and can be found by    
    issuing:   
   
    SQL> show parameter user_dump_dest   
   
    The easiest way to locate the correct trace is to look at its date.  A   
    file will exist with the current date and time.  The naming convention    
    for these files is operating system specific.   
   
3.  Once the file is located, search through the file for the word "CONTROL"    
   
    You should find:    
   
    # The following commands will create a new control file and use it    
    # to open the database.    
    # No data other than log history will be lost.  Additional logs may    
    ETC.ETC.    
    
4.  Copy this trace file to some location and rename it to end it ".sql",    
    for this example, it is called "recr_con.sql".     
   
5.  Edit the "recr_con.sql" deleting the trace header information.  Then  
    increase the value that you find next to the word "MAXDATAFILES".    
    
6.  Shutdown the database (NORMAL or IMMEDIATE, but not ABORT).    
   
7.  Take a full database backup at this time.    
   
8.  Remove the current control files.  It is essential to remove all control   
    files, otherwise, you will receive an error.  In addition, you CANNOT   
    REUSE the control file, since the size of the control file will increase   
    when you increase MAXDATAFILES.   
    
9.  Create the controlfile within SQLDBA:   
   
    SQL> connect internal     [if using sqldba or svrmgrl]
         or 
         connect / as sysdba  [if using sqlplus]
    SQL> @recr_con.sql    
    SQL> alter database open noresetlogs;    
    
    If you receive a "Statement processed" message, then your database is    
    now back up and running with a higher datafile limit.  It is recommended   
    to shutdown at this time and take a full backup.    
    
CREATE CONTROLFILE SYNTAX:   
-------------------------
   
The following is information on the create control file syntax, this    
information is fully documented in the Oracle SQL Reference Manual.   
   
The syntax of this command is similar to CREATE DATABASE.    
The defaults for any missing clauses are the same as the    
DATABASE defaults for CREATE DATABASE.    
    
CREATE CONTROLFILE [REUSE]    
   SET DATABASE name    
   [LOGFILE filespec [, filespec] ...]    
    RESETLOGS | NORESETLOGS    
   [MAXLOGFILES integer]    
   [DATAFILE filespec [, filespec] ...]    
   [MAXDATAFILES integer]    
   [MAXINSTANCES integer]    
   [ARCHIVELOG | NOARCHIVELOG]    
   [SHARED | EXCLUSIVE] 

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:r4笔记第31天

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-01-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 探究AWR 第一篇

    statspack相比awr算是比较通用,而且免费,可以在标准版,企业版中使用,awr是新企业版本中才有的,算是statspack的一个升级版,而且代码不公开。...

    jeanron100
  • alert日志中出现ash size的警告

    今天查看数据库的alert日志总出现了如下的警告。 Archived Log entry 202 added for thread 1 sequence 2...

    jeanron100
  • 由一条日志警告所做的调优分析(r3笔记第40天)

    这个案例发生有段时间了,但是今天无意中看到当时的邮件,感觉还是收益匪浅,看来还是细节决定成败啊。从一些日志或trace 文件中的警告信息中我们可以发掘出潜在的问...

    jeanron100
  • From High Ceph Latency to Kernel Patch with eBPF/BCC

    There are a lot of tools for debugging kernel and userspace programs in Linux. M...

    Linux阅码场
  • SAP S/4HANA里如何创建Customer主数据以及执行后续处理

    1, Launch tcode: BP and select the Organization 2, Maintain the information for...

    Jerry Wang
  • 机器学习实战(二) - 单变量线性回归Model and Cost Function1 模型概述 - Model Representation2 代价函数 - Cost Function3 代价函数(

    To establish notation for future use, we’ll use

    JavaEdge
  • Evaluating the linear regression model评估线性回归模型

    In this recipe, we'll look at how well our regression fits the underlying data. ...

    到不了的都叫做远方
  • 操作系统 Interrupt 执行的具体步骤

    本文内容主要摘抄于 Intel® 64 and IA-32 Architectures Software Developer's Manual,其详细介绍了 i...

    wangyuntao
  • Feature selection特征选择

    This recipe along with the two following it will be centered around automatic fe...

    到不了的都叫做远方
  • 论综合 | 是什么让一个数字前端实现硅农开始学习Floorplan 的?

    如题,是什么让一个数字前端实现硅农开始学习Floorplan 的?是制造工艺的进步,是实现方法学的被迫更新,是养家糊口生的本能,正可谓:头发落完终不悔,为伊消得...

    老秃胖驴

扫码关注云+社区

领取腾讯云代金券