Shulou(Shulou.com)06/02 Report--

Add data file today, the result reported an error, 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.

-

The database parameter shows that db_files is set to 200 and select count (*) from vested datafile; the resulting data is also 200, indicating that the maximum value of the setting has been reached.

SQL > show parameter db_files

NAME TYPE VALUE

-

Db_files integer 200

SQL >

However, through SQL > alter database backup controlfile to trace; find the text of the control file under udump, and check and find MAXDATAFILES 1024, as shown below:

# 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

Since the db_files parameter value 200 does not exceed the size of maxdatafiles 1024, you can prevent this error by directly increasing the parameter db_files value so that the data file can be added correctly. But if the db_files has been set to the same size as maxdatafiles and the error ORA-00059 is reported, then the value in the control file will have to be modified and the control file needs to be regenerated. Using alter database backup controlfile to trace, find the newly generated trc file in your udump directory, remove the explanation, keep the Create controlfile, edit the file, increase the value of MAXDATAFILES, then close the database shutdown immediate, back up the database once, back up the existing control file to another place, and then delete it from the control file directory, assuming that the edited trc file is named recreate_control.sql So we can now perform the re-establishment of the control file. Note that all modified trc files begin with STARTUP NOMOUNT.

SQL > @ recreate_control.sql

SQL > alter database open noresetlogs

If you receive feedback from Statement processed, it will take effect.

= =

Recommended workaround on 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.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.