ORA-00059: maximum number of DB_FILES exceeded 处理
今天添加数据文件，结果报错，ORA-00059: maximum number of DB_FILES exceeded 。
---------------
Cause: An unavailable resource was requested.
The maximum number of datafiles is specified by the DB_FILES parameter in the initialization parameter file.
When this maximum is reached, no more requests are processed.
Action: Try again when the resource is freed.
If this message occurs often, Oracle must be shut down and restarted after increasing the DB_FILES parameter in the initialization parameter file.
If the DB_FILES parameter cannot be changed because it is already set to the MAXDATAFILES parameter value, set at database creation, you must create a new control file.
------------------
数据库参数显示，db_files 设置为 200， 而 select count(*) from v$datafile; 之后得到的数据也是 200 ， 说明已经达到了设置的最大值。
SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL>
不过通过 SQL> alter database backup controlfile to trace ; 在udump下面找到控制文件文本，查看发现 MAXDATAFILES 1024 ， 如下面：
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 3857
LOGFILE
GROUP 1 (
'/ocfs_ctrl_redo/orcl/redo01.log',
'/ocfs_data/orcl/redo01b.log'
) SIZE 100M,
由于db_files 参数值 200没有超过 maxdatafiles 1024 的大小，所以可以通过直接加大参数db_files 值来防止此错误出现，以便正确加入数据文件。 但是如果 db_files 已经设置和maxdatafiles 一样大小了， 报错ORA-00059, 那么就只有修改控制文件中的值了，需要重新生成控制文件。使用 alter database backup controlfile to trace , 到你的udump 目录下找到这个刚刚生成的trc文件，去掉其中的解释部分，保留Create controlfile 部分，编辑此文件，增加MAXDATAFILES 的值，然后关闭数据库shutdown immediate , 全备份一次数据库， 将现有的控制文件备份到其他地方， 然后从控制文件目录删除，假设将编辑好的trc文件命名为 recreate_control.sql ; 那么我们现在可以执行重新建立控制文件了。 注意修改的trc 文件开头都会有 STARTUP NOMOUNT 。
SQL> @recreate_control.sql
SQL> alter database open noresetlogs;
如果你收到了 Statement processed 的反馈信息，那么生效 。
=================================
Metalink 上的建议解决方法：
==================================
Cause: An unavailable resource was requested.
The maximum number of datafiles is specified by the DB_FILES parameter in the initialization parameter file.
When this maximum is reached, no more requests are processed.
Action: Try again when the resource is freed.
If this message occurs often, Oracle must be shut down and restarted after increasing the DB_FILES parameter in the initialization parameter file.
If the DB_FILES parameter cannot be changed because it is already set to the MAXDATAFILES parameter value, set at database creation, you must create a new control file.
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
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]
Parameters:
REUSE: If present the control files may already exist. The
new control files will overwrite the existing files. If
this option is missing, the new control files must not
yet exist. As in CREATE DATABASE, the names of the
control files are determined by the init.ora parameter
control_files.
DATABASE: Must match the database names in the data and log
files.
LOGFILE: This clause lists all the online logs that will be
used for this database. If not specified the port
dependant defaults will be assumed. The interpretation
of the filespecs depends on the next parameter.
RESETLOGS: If this flag is present the current contents of
the online logs are ignored. The new control files will
contain flags requiring ALTER DATABASE OPEN RESETLOGS,
which initializes the logs. Media recovery may be
applied as needed before the open. Note that either
RESETLOGS or NORESETLOGS must be specified. It is safest
to choose RESETLOGS and follow it with normal media
recovery.
NORESETLOGS: If specified, the log files must be the current
online logs. They must not be restored backups, and all
log files must be listed. Their headers are read to
construct the control file entries. They are used for
recovery. If archiving is enabled all the online logs
must be archived, even if they were already archived.
The SIZE option in the filespecs, if present, will be
used to validate the size of the file named.
MAXLOGFILES: Same as for CREATE DATABASE. May be different
than the value in the original control file, but it may
not be smaller the maximum number of log files the
database ever contained - including ones that have been
dropped. Set it greater than or equal to the value used
at CREATE DATABASE time.
DATAFILE: To ensure proper behavior, all datafiles for the
database must be listed. It is possible to omit a non
system tablespace file only if media recovery is enabled
and you will not be doing an open reset logs on the first
open after the create controlfile. If the omitted
file(s) contain(s) active rollback segments, the open
will most likely fail, in which case the missing
datafile(s) must be found, and the controlfile recreated.
All datafiles listed must be accessible since they are
assumed to be online. They MAY be backup copies needing
recovery. Their headers are read to construct the
control file records. The SIZE option in the filespecs,
if present, is used to validate the size of the file
named. The reuse option is ignored. The next database
open validates that all the files are specified and that
the sizes match.
MAXDATAFILES: Same as for CREATE DATABASE. May be different
than the value in the original control file, but it may
not be smaller the maximum number of data files the
database ever contained - including ones that have been dropped.
MAXINSTANCES: Same as for CREATE DATABASE. May be different
than the value in the original control file.
[NO]ARCHIVELOG: Same as for CREATE DATABASE. May be different
than the value in the original control file.
If you wish to archive logs, it is recommended that the
ARCHIVELOG option be used with CREATE CONTROLFILE even
though the option can later be enabled with an
ALTER DATABASE command. NOARCHIVELOG is the default.
SHARED: Same as for CREATE DATABASE.
EXCLUSIVE: Same as for CREATE DATABASE.
EXAMPLE:
---------------------------------------------------------------
CREATE CONTROLFILE
SET DATABASE ORACLE
LOGFILE '/releases1/6036p/dbs/log2ORACLE.dbf '
'/releases1/6036p/dbs/log3ORACLE.dbf '
DATAFILE '/releases1/oracle/dbs/data_space.dbf',
'/releases1/6036p/dbs/usrORACLE.dbf'
MAXDATAFILES 121
NORESETLOGS;
---------------------------------------------------------------
